EXERCISE: 9 Microsoft Excel

What is a Spreadsheet?
Microsoft Excel is a program that’s used for creating spreadsheets.
So what is a spreadsheet?
Before personal computers were common, “spreadsheet” referred to large sheets of lined paper, which were used by people in various businesses to record facts and figures in rows and columns, and then make calculations based on the information. When personal computers first began appearing, one of the first applications was a program released in 1979 called VisiCalc. It was used as a tool for performing spreadsheet style calculations that would have been too difficult to do on a calculator. The program quickly became so popular that people began buying personal computers for their businesses just so they could use VisiCalc.



VisiCalc

Since then, many other spreadsheet programs have been popular over the years, such as Quattro Pro and Lotus 123. Microsoft Excel was first released in 1985 with newer versions being released every couple of years. The most recent version is Excel 2003 (version 11).

How Do Spreadsheets Work?
Computer spreadsheets are based on their old paper formats. A spreadsheet on a computer uses rows and columns to record information such as text and numbers, such as the example below.




One major benefit of using computers for spreadsheets is that the computer can do a lot of the hard work for you. For example, in the table above, the computer could be told to automatically work out the summary amounts such as total, average, minimum and maximum.
A spreadsheet program can also create graphs and other types of charts, based on information in your tables. The example below shows a graph that was easily created from the table above.



Spreadsheets are often used for business documents such as invoices where numbers and totals are important. A program such as Excel can automatically add up totals for a document such as the invoice shown below. A document like this could be given a customer to provide details of how much money they owe to the business.

Spreadsheet Contents
The cells in a spreadsheet can contain 3 types of information. Excel will treat cells differently depending on the cell contents.
• Text – Any names or labels that are required on the spreadsheet
• Number – All numerical values including dates/times, percentages and dollar values
• Formula – Formulas are written in a cell to automatically calculate an answer.
Working with Excel
The Excel Screen



Creating a Simple Formula
1) Create a blank workbook.
2) Click in cell A1 and enter the following.
=5+5
3) Press [Enter] to complete the formula. Excel will calculate the result.
Using Cell Referencing
1) Enter the number 5 in cell B1.
2) Enter the number 10 in cell B2.
3) Enter the following formula in cell B3.
=b1+b2
4) Press [Enter] to complete the formula. Excel will calculate the result.
5) Change the number in cell B2 and press [Enter]. The result of the formula will be re-calculated
(if it doesn’t recalculate on its own you can press [F9] to force recalculation).

Using the Mouse for Cell Referencing
Creation of a formula can be made easier by using the mouse to create cell references.
1) Enter the number 10 in cell C1.
2) Enter the number 20 in cell C2.
3) Click in cell C3 and type a = sign.
4) Click on cell C1. The reference for that cell will appear in the formula.
5) Type a + sign.
6) Click on cell C2. The reference for that cell will appear in the formula.
7) Check that the formula reads =C1+C2 and press [Enter].

Adding Several Numbers Together
1) Make sure the Formulas workbook is still open.
2) Click in cell B9.
3) Begin your formula with a = sign and then click on cell B2.
4) Type a + sign and click on cell B3.
5) Complete the formula so that it looks like the one below.

=B2+B3+B4+B5+B6+B7+B8

You will get the correct answer, but it’s a long formula. Adding together 50 or 100 cells like this
would be a tedious process.
Functions can be used to take the hard work out of many types of calculations in Excel. Functions all follow the same format. I.e. = sign, name of the function, information to be calculated in brackets.
=NameOfFunction(information to be calculated)
There are hundreds of functions built in to Excel and custom functions can be created. The most
commonly used function is the Sum function.

Sum Function
1) Click in cell C9.
2) Type =sum(C2:C8) and press [Enter].
3) Select cells C2 to C8.

This will tell Excel to add up the sum of all the cells from C2 to C8.

4) Click in cell D9.
5) Type =sum(
6) Select cells D2 to D8.
7) You can type the right bracket, but if you don’t Excel will put it in for you. Press [Enter] to
complete the function. It should look like the one below.

=SUM(D2:D8)

Note: If you want to add together more than one group of cells, you can separate each cel range
with a comma.
E.g. =SUM(C2:C8,E2:E8,G2:G8)
This would add cells C2 to C8 and E2 to E8 and G2 to G8

Using the AutoSum Tool
Since the Sum function is used so often, a special tool has been provided to make it easy to use.

1) Click in cell E9.
2) Click the AutoSum icon on the toolbar.

Excel will create a Sum function referring to the cells above.
Excel will assume the cells above are the ones to be added together.
These cells will remain selected in case you would rather select a
different group of cells. If there are numbers in the cells above,
the cells to the left will be used. Otherwise the nearest group of cells
will be selected.
3) Press [Enter] to confirm that the correct cells are selected and
complete the function. The function should look like the one below.
=SUM(E2:E8)
You can also use the AutoSum tool by selecting the cells to be added first.

4) Select cells F2 to F8.
5) Click the AutoSum icon.

A Sum function will be automatically created based on the
cells you selected. The AutoSum icon can also be used to
create other common functions such as Average and Count.

6) Click in cell G9.
7) Click the arrow next to the AutoSum icon.
A list of common functions will be displayed.
8) Click on Average.
An Average function will be created in the selected cell. Notice that it is written the same as a Sum function.
9) Check that the function reads =AVERAGE(G2:G8) and press [Enter] to complete the function.
10) Save the changes to the workbook and then close it.

Using Functions in a Table
1) Open the Grades workbook (This was created in the Getting Started exercises).
2) Click in cell F6.
3) Click the AutoSum icon.
4) Make sure cells B6 to E6 are selected and press [Enter] to complete the function.



5) Use the fill handle to copy the function down through to cell F16.
When a formula is copied to other cells, the cell references should change
for each cell.
6) Click in cell B15.
7) Click the arrow next to the AutoSum icon and click on Average.
8) Make sure cells B6 to E6 are selected and press [Enter] to complete
the function.
9) Use the fill handle to copy the formula across to cell F15.
10) Click in cell B16.
11) Click the arrow next to the AutoSum icon and click on Max.

Notice: that the function has selected all of the cells above including the average cell which we don’t want selected.

12) While these cells are still highlighted, select cells B6 to B14 and press [Enter]. The formula in cell B16 should be =MAX(B6:B14).
13) Use the fill handle to copy the formula across to cell F16.
14) Try completing the Lowest mark cells yourself by using the Min function.
15) Use a count function in cell B18 to show the number of students.

The completed table should look like the one below.
+

Using an If Function
If functions can be useful if you want an excel formula to select from 2 different answers based on criteria you specify. In our grades example, we can use an If function to say a student has passed if their mark was over 50 and fail if their mark wasn’t over 50. If functions have 3 sections with a comma between each section as shown below.

=If(condition to test, answer if condition is true, answer if condition is false)
1) Click in cell G5 and enter the heading Pass/Fail.
2) Enter the following If function.

=If(F6>=50,"Pass","Fail")

The first part of the formula checks to see if the number in cell F6 is greater than 50 or equal to 50. The = sign is important. If it was left out then someone who scored exactly 50 wouldn’t be included.The second section says that if this test condition is true, the text Pass will be displayed for the answer. Note that where text is used in a formula, it must be enclosed in quotation marks. The third section specifies the answer (Fail) if the test condition is not true.
3) When the If function has been entered, use the fill handle to copy the formula down to cell
G14. There should be 7 passes and 2 fails.

Vertical Lookup
1) Open the Grades workbook if it is not already open.
The first thing we will need to do is create the table that will contain the grade cut-off points.
2) Click in cell K6 and enter a zero.
3) Complete the information in cells K6 to L10 as shown to the right.
4) Click in cell H5 and enter the text Grade.
5) Click in cell H6. This is where we want the first grade to go.

Like an If function, a Lookup function has different parts.
The sections of a lookup function are described below.

6) Enter the following formula. =VLOOKUP(
7) Click on cell F6 (the cell with the first student’s mark).
8) Type a comma to end the first section and then select the grades cut-off table (which should
be cells K6 to L10.
9) Press F4 to make sure this section uses absolute references ($K$6:$L$10). When we copy the
function down for the other students we want to make sure it is still correctly referring to the
cells with the grade cut-off marks.
10) Type another comma and then the number 2.
11) Type another comma and then type true.
12) Type a closing bracket and then press [Enter] to complete the formula. The completed formula should be =VLOOKUP(F6,$K$6:$L$10,2,TRUE).
13) Use the fill handle to copy the function down to cell H14 so that
every student has a grade.
If your lookup table goes horizontally instead of vertically then you
canuse a horizontal lookup function. It works exactly the same except
that you type Hlookup instead of Vlookup.
14) Save and close the file.

Creating Charts
Once a table has set up, the information in the table can easily be turned in to one of several different chart types. These include:
• Column and bar graphs
• Line graphs
• Pie charts

Creating a chart in Excel involves following the steps below:
1) Select the data to be included in the chart.
2) The selection should include table headings. Excel can use headings as labels on the chart.
3) The selection should not include totals.
4) Begin the chart wizard which will prompt you for details about the chart.
5) Customise the completed chart.

The example below shows the kind of information that should be selected before creating a chart,
along with a sample of the kind of chart that might be created from the selected data.



Creating a Column Graph
1) Open the Grades.xls file that you have been working on previously. We will create a graph to
show the results for the students each term.
2) Select the cells A5:E14. Note that this includes headings but no totals.
3) Click the Chart Wizard icon.

The chart wizard will begin. As you can see from the title bar of the wizard, this will be the first of four steps. The first step is to choose a chart type.



4) From the list of Chart Types, make sure Column is selected.
5) In the list of Chart sub-types, choose the first option in the second row as shown above.
6) Click and hold the Press and Hold to View Sample button to see a basic preview of your chart.
7) Click Next to proceed to the second step of the wizard.
8) The second step allows you to confirm that the correct data is selected for the chart. It has automatically selected to use the student names along the bottom of the chart. For Series In, select Rows to have the terms along the bottom of the chart.








8) Click the Series tab at the top to see what labels and series are being used for the chart.

Notice that for each student name in the Series list, you are shown which cell the Name of the series comes from and which cell range the Values for that series come from. You can use this to add additional students if needed. The Category (X) axis labels box specifies which cells the labels along the bottom of the chart will come from (Term 1-4).
10) Click Next to continue to the third step of the wizard.
This step allows you to add additional information such as a chart title. These options can all be changes later but we will use some of them now.


11) In Chart Title enter Eastern Goldfields Student Results.
12) Click Next to proceed to the last step of the wizard.


This step allows you to place the chart on the current sheet, or to place it on an entirely new sheet
(more on working with sheets later).
13) Make sure the second option is selected as shown above and click Finish.
A chart will now be created on the sheet. Since the chart’s probably been placed overlapping your table, you’ll probably want to move it.
14) Position your mouse pointer over a blank area of the chart and drag the chart until the outline of the chart is below your table as shown to the right.


Changing Chart Type
1) Make sure your chart is still selected (it should still have the black selection squares around it
if it is).
2) Click the arrow next to the Chart Type icon on the Chart toolbar. A list of
chart types like the one to the right will appear.
3) Click on the 3D Column Chart icon (the one with the circle around it to the right.
Your chart will now change to a 3D Column format.
4) Use the Sizing handles to make the chart bigger . When you make the chart bigger the text on the labels may also become bigger.
5) Double-click the labels on the X axis to show the formatting options.


6) When the Format Axis options appear, change the font size to 10 and the alignment angle to 60º so that the axis labels look like the example below.


7) Format the side axis labels in a similar way so that they look like the example below.

8) Now that there are labels along each axis, there is no longer any need for the chart legend.
Click on the legend and then press the [Delete] key to remove it. This will leave more room
for the chart itself.

Creating a Pie Chart
One of the differences between a pie chart and a normal chart is that a pie chart will only show data for one series at a time. In the following exercise, we’ll create a pie chart from the year’s data. We need to begin by selecting the names of the students (to be used as labels) and the data itself.
1) Select the cells with the student names including the student heading (A5:A14). Since we need to select more than one group of cells we’ll need to use the [Ctrl] key.
2) Hold down [Ctrl] and select the cells with the year data (F5:F14)

A5:A14 and F5:F14 should now be selected.

3) Click the chart wizard icon to begin creating the chart from the selected data.



4) Select Pie for the Chart type and the second sub-type as shown in the example above.
5) Click Next to continue
6) Confirm that the selected cell range reads =Sheet1!$A$5:$A$14,Sheet1!$F$5:$F$14 and click Next to continue.
7) Change the Chart title to Student Marks for the Year and click Next to continue.

8) For the final step, select As new sheet and enter Pie Chart for the name of the sheet.


9) Click Finish to complete the chart.

10) Click on the chart to select it.
11) Right-click on the selected chart and select Format Data Series.
12) Select the Data Labels tab and select the Category Name option as
shown below.

13) Clock OK to make the change. The name of each student will now appear
next to their pie slices. This means we no longer need the chart legend so we
can delete it to make more room.
14) Click the legend to select it and press [Delete].
15) Click the chart to select it again and then click the pie piece for Lita
Alexander. That piece will now be the only one selected.
16) Slowly drag that piece away from the centre of the pie. This technique is
often used to emphasise a certain figure (such as the highest mark).


You can return to the original sheet (Sheet 1) at any time by using the sheet tabs at the bottom of the window.