In our previous post, we introduced Microsoft Excel (MS Excel) and its basic functions. In this post we shall look at how to format worksheets, use formulas, print, and create charts in MS Excel spreadsheet application.

FORMATTING MICROSOFT EXCEL WORKSHEETS

Formatting Text and Number in a Cell

When you enter data in a cell, you may decide to format your data to suit your choice. In this case, you can bold, change the font, increase the font size, apply font color, underline or italics to your data.

To format text and number Microsoft Excel, do the following:

1. Select the cell or group of cells you want to format their content(s).
2. On the Home tab, under Font group, choose the appropriate icon to achieve your desired result as shown.
   1. To apply Bold (e.g. Bold) to your data, click the “B icon as shown in the figure above.
   1. To apply Italics (e.g. italics) to your data, click the “I” icon as shown in the figure above.
   1. To add underline (e.g. underline) to your data, click the “U” icon as shown in the figure above.
   1. To change the font in your data, click the arrow to the right of Calibri and select a font from the list of fonts.

• To increase or decrease the font size of your data, click inside the font size box and type a size for your font and press ENTER on the keyboard. You can also choose a font size from the list or click the “A^{^}” icon to increase font size and “A^v” to decrease font size.

Change the Column Width and Row Height

1. Click the cell for which you want to change the column width or row height.
   1. On the Home tab, in the Cells group, click Format.

• From the format drop-down list, select AutoFit Row Height, or AutoFit Column Width. The column width or row height will be automatically adjusted to show all the data in that cell.

 OR

• From the format drop-down list, select Row Height or Column Width. A dialog box appears. (see image above – column width)

• Type in the box the number of points you want to adjust the column or row and click OK.

Deleting Data

To delete data in a cell or group of cells in Microsoft Excel :

1. Select the cell or group of cells you want to delete.
2. Press DELETE on the keyboard. OR
3. On the Home tab, under Editing group, and select Clear.

• From the clear dropdown list, select any of Clear All, Clear Formats, Clear contents, etc. depending on what you want to delete.

How to wrap text

You can display multiple lines of text inside a cell by wrapping the text.

1. Click on the cell or range of cells you want to apply wrap text to.
   1. On the Home tab, in the Alignment group, click Wrap Text.

How to Merge Adjacent Cells

To merge cells means to combine two or more adjacent cells as one.

1. Select two or more adjacent cells that you want to merge in Microsoft Excel worksheet.
2. On the Home tab, in the Alignment group, click Merge and Center.

How to name a cell or group of cells

You can name a cell or group of cells in order to use them in formula or calculations.

1. Select a cell or group of cells you want to name, e.g. G13 OR G13:I15.
2. In the Name Box, type a name for the cell or range, say, “Glory” and press ENTER.
3. When using formula or functions, you can sum the range by simply entering [=Sum(Glory)]. The values in the range will be totaled.

Insert and Delete Cells, Rows, and Columns

How to insert blank cells on a Microsoft Excel worksheet:

1. Select the cell or range of cells where you want to insert the new blank cell(s). (Note: you should select the same number of cells as you want to insert. For example, to insert five blank cells, you need to select five cells).
2. On the Home tab, in the Cells group, click the arrow next to Insert, and then select Insert Cells… You can also right-click the selected cells and then click Insert… on the shortcut menu. The Insert cells dialog box will appear.

• In the Insertdialog box, click the direction you want to shift the surrounding cells. You can choose to Shift cells down or right of the selected cell(s).

How to insert rows on a Microsoft Excel worksheet

1. Select the row(s) where you want to insert new rows. (Note: To insert a single row, select the row or a cell in the row above which you want to insert the new row. For example, to insert a new row above row 8, click a cell in row 8. To insert multiple rows, select the rows above which you want to insert rows. Select the same number of rows as you want to insert. For example, to insert 5 new rows, you need to select three rows).
2. To insert nonadjacent rows, hold down CTRL while you select nonadjacent rows.
3. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Rows from the drop-down list.

How to insert columns on a worksheet

1. What is applicable to row above is applicable to the column, but in this case, your selection will be column-wise. Also, in the Insert drop-down list, select Insert sheet Columns, instead.

Delete cells, rows, or columns

1. Select the cell(s), row(s), or column(s) you want to delete.
2. On the Home tab, in the Cells group, do one of the following:

1. To delete selected cell(s), click the arrow next to Delete, and then click Delete Cells. From the dialog box that appear, select either Shift cells up or Shift cells left.
2. To delete selected row(s), click the arrow next to Delete, and then click Delete Sheet Rows.
3. To delete selected column(s), click the arrow next to Delete, and then click Delete Sheet Columns.

Apply Borders to a Cell

By default, Microsoft Excel does not print gridlines. It is important that users apply borders appropriately to cells as they work. To apply borders:

1. Select the cell or group of cells you want to apply a border to.
2. On the Home tab, under Font group, click the arrow next to Border icon.

• From the list, select any of Bottom Border, Top Border, etc.
• Try it to see the result.

PERFORMING SIMPLE CALCULATIONS IN MICROSOFT EXCEL

You can perform simple and complex calculations in Microsoft Excel spreadsheet. NOTE: When performing calculations in Microsoft Excel, the cell references should be used instead of the values in cells. For example, in the figure below:

Fig.: Daily Fruit sales record

To calculate the Amount for each fruit, do the following:

For Apple [=B14*c14]; For Orange [=b15*C15], etc.

Do not do the following:

For Apple [=25*50]; For Orange [=50*10], etc.

Performing Addition

To add values, either use the “+” sign on the keyboard or use the “SUM” function. For example, from the above figure on daily fruit sales record, to calculate the total quantity of fruits sold, do the following:

1. Select the cell where the result will be displayed, then calculate as follow:
2. Total Quantity of fruits is [=B14+B15+B16+B17+B18]; OR
3. Total Quantity of fruits is [=SUM(B14:B18)].

The second calculation used the SUM function. You can use AutoSum by doing the following:

1. Select the cell where you will place the result
2. On the Formulas tab, under Function Library, select AutoSum
3. From the AutoSum list, select your preferred AutoSum calculation (Sum, Average, Count Numbers, Max, Min, etc).
4. The selected function will be displayed, inclusive of the selected cell references for the calculation as shown below.

• Press ENTER on the keyboard to complete the calculation.

Perform Average or Mean Calculation

From our above figure, to calculate the average price of fruits.

1. Select the cell where your result will appear, say, {C19}
2. On the AutoSum list, select Average. OR
3. On cell {C19}, type [=AVERAGE(C14:C18)]
4. Press ENTER on the keyboard.

NOTE: When typing a function in a cell, Excel makes it easier by bringing out the list of functions to choose from after your first letter is typed. For example, to use the AVERAGE function:

1. Type “=” followed by “av” the following list is displayed.

• Double click AVERAGE from the list. OR
• Use the DOWN ARROW key on the keyboard to scroll to AVERAGE, then use the TAB key on the keyboard to select.

Perform Count Calculations

To calculate the number of fruits sold, from the sales record:

1. Select the cell where your result will appear, say, {B19}
2. On the AutoSum list, select Count Number. OR
3. On cell {B19}, type [=COUNT(B14:B18)]
4. Press ENTER on the keyboard.

Perform Multiplication

We use the product function or asterisk “*” on the keyboard to perform multiplication computations. To calculate the amount or total amount sold for each fruit in our example above, do the following:

1. Select the cell where your result will appear, especially, the first product on the list of values. In this case, {D14}, see below

• On cell {D14}, type [=B14*C14] OR
• On cell {D14}, type [=PRODUCT(B14,C14)]. Note: that a comma (,) is used here, not a colon (:).
• Press ENTER on the keyboard.
• Then copy the formula to the other remaining cells D15 to D18 {D15:D18} by doing the following:
• Highlight cells D14 through D18 {D14:D18}, see below:

• On the Home tab, under Editing group, select Fill.

• Then, select Down from the list. The formula calculates other cells for you automatically.
• You can also use Autofill to calculate other cells automatically by clicking and dragging the Autofill handle as follow:

1. Place your pointer on the autofill handle. Click and drag downwards till you reach your destination, then stop. You can also double-click the autofill, to automatically calculate the other cells.

Perform maximum and minimum calculations

Use the MAX function to calculate the maximum or highest value and MIN function to calculate the minimum or lowest value. To find the fruit with the highest or lowest price from the sales record:

1. Select the cell where your result will appear, say, {C19}
2. On the AutoSum list, select MAX or MIN.
3. On cell {C19}, type [=MAX(C14:C18)] or [=MIN(C14:C18)]
4. Press ENTER on the keyboard.

Perform Division

We use the QUOTIENT function or slash “/” on the keyboard to perform division computations. To divide the total amount sold for each fruit in our example above for their prices, do the following:

1. Select the cell where your result will appear, especially, the first product on the list of values. In this case, {E14}
2. On cell {E14}, type [=D14/C14] OR
3. On cell {E14}, type [=QUOTIENT(D14,C14)], i.e. [=QUOTIENT (numerator, denominator)]
4. Press ENTER on the keyboard.
5. Then copy the formula to the other remaining cells E15 to E18.

PRINTING FROM MICROSOFT EXCEL WORKSHEETS

Printing is an important function in Microsoft Excel, because you may use the hardcopy prints at field works, and also for reporting. It is important for anyone working with Microsoft Excel to understand the different page break points. The page break shows the user where each Microsoft Excel page starts and ends. Hence, I usually advise my students to always switch to page break preview as soon as they begin to enter data in MS Excel, as this will help them organize their work in pages.

To print, do the following:

1. On the View tab, under Workbook Views,click on Page Break Preview. Your work displays to show you the page breaks with the page number on each page. See the example below.

• From the above view, you can make adjustments on the page if you want, if not, proceed.
• On File menu, select Print to display the print preview and print window as follow.

• On the top left corner, click the Print icon to print.

Note: This is a simple print operation.

WORKING WITH GRAPHS IN MS EXCEL

Constructing Standard Graphs and Charts

Charts or graphs are a graphical representation of data. Charts are used to summarize large tabular data for easy understanding. It also shows the relationship between the different parts of the data.

There are different kinds of the chart in a spreadsheet some of them include: line graphs, pie charts, bar charts, etc. when constructing charts in a spreadsheet, it is important to understand the type of data suitable for each chart. In Microsoft Excel 2013 there is a Recommended Chart command; this command helps a user to choose the appropriate chart for the data in the view.

To construct a chart, do the following:

1. Select the range of data you want to construct chart on.
2. Select the Insert tab.
3. On the Insert tab, under Charts group, select Recommended Charts or choose any chart of your choice.
4. The selected chart is automatically plotted and displayed for you, based on the selected data.

Constructing pie-charts

The pie chart is a circular graph which is used to represent numerical proportions of data in slices. Pie charts represent proportions of categorized data in percentages. To use a pie chart, your data should be categorized. For example, gender distribution, distribution of students according to tribes, etc.

The table below shows the level of the student’s response in a computer class. Construct a pie chart using the data.

To construct a pie chart, do the following:

1. Enter the above data in a worksheet, either vertically or horizontally.
2. Select the range of data, as shown below.

• On the Insert tab, under Charts group, select Pie Chart icon.
• From the list, choose any of 2-D Pie, 3-D Pie or Doughnut. Your chart is displayed as shown below.

Constructing Bar charts and Line graphs

A bar chart or graph uses vertical or horizontal rectangular bars of different heights to represent categorized data. The lengths of the rectangular bars are proportional to the values they represent. A bar chart can also be called a column chart. While in line chart series of data points are connected together with a straight line. You can plot the same data in both charts. For example, to plot the above data in bar and line graphs, do the following:

1. Ensure the data is entered in a worksheet.
2. Select the range of data, as shown above, under the pie chart.
3. On the Insert tab, under Charts group, select Column Chart icon for vertical bar charts or Bar Chart icon for horizontal bar charts or Line Chart icon for line charts.
4. From the list, choose any of 2-D Bar, or 3-D Bar or 2-D Line or 3-D Line. Your chart is displayed as shown below.

Editing and Formatting Charts

When you construct a chart in Microsoft Excel , two more tabs are added to your ribbon under CHART TOOLS. These tabs are Design and Format tabs.

Use the command on the Design tab to edit the look of your chart automatically or the commands on the Format tab to edit the text, lines, and bars of your chart individually. To edit your chart, simply do the following:

1. Select the chart to display the chart editing tools, as follow:

• Click the “plus-like” icon to display the Chart Elements list. The items on the list depend on the type of chart selected.

• Select any of the items on the list to display the element on the chart. E.g. select the Chart Title to display a title for your chart. You can edit the displayed element by clicking on it and editing it.
• Click on the arrow at the left of the selected item to display the item’s properties. E.g. you can place the Legend at any of Top, Bottom, Left or Right positions.
• Click the “brush-like” icon to display the Style and Color properties of the chart.

• Under Style tab, select a style of your choice to see your chart’s properties change. Under Color tab, select a color scheme of your choice to see the chart’s color scheme change.
• Click the “funnel-like” icon to display the Values and Names properties of the chart.

• The Values tab is split into Series and Categories. To remove or add any item on the Series and Categories, deselect or select such item and click Apply on the down-left corner of the list. E.g. in the line chart above, deselecting “Neutral” and clicking Apply will remove “Neutral” in the chart as shown below.

• Therefore, you can decide to show or not show the values of the categories or series on your chart.
• Finally, you can use the Names tab to display the titles used on your data table on the chart or not. E.g. If you do not want to display the names on the categories list, select (None) and click Apply.