Performing Calculations
Calculating Totals
The next step is to make some calculations using the data that we have put into the spreadsheet. To make a calculation, we must decide where we want to put the answer to the calculation and create a formula in that cell. (Note: The plural of formula is formulae - or formulas in American English).
Open the file SPEND.XLS and click on OK. So far the sheet looks like this:
Firstly, we need to add the data in each column and put the totals in the Quarterly Totals cells; secondly, to add the data in each row and put the totals in the Yearly Totals cells. For both of these operations we shall the same kind of calculation.
To start, let's total the JAN-MAR column. To do so, we are going to make a SUM function in cell B15. Click in cell B15 and type in the formula that will add up all the figures from B5 to B13, like this
=SUM(B5:B13)
Replicating A Formula With The Fill Handle
Our next step is to find the totals of the remaining columns. We could simply type in the appropriate formulae one at a time in each of the cells C15, D15 and E15, but it's not necessary to use this slow method in Excel. Instead, we can use a technique called REPLICATE to put the SUM formula in these adjacent cells.
Click in cell B15 if it is not already selected, and notice the small black square in the bottom right-hand corner of the cell.
This is called the FILL HANDLE. Position your mouse over the Fill Handle until it changes from the white plus sign to the black cross. Now click and drag the mouse over the next three cells (C15-E15) until you can see a grey line around all four cells. When you release the mouse button, the cells will automatically fill up with totals. Click on each one in turn and read its contents in the Formula Bar. The correct formulae for the four cells are:
=SUM(B5:B13), =SUM(C5:C13), =SUM(D5:D13) and =SUM(E5:E13)
When you have completed this, you will notice that a small icon has appeared at the point where you released the mouse button.
Move your mouse pointer over this icon and it will expand to show a downward-pointing arrow head.
Click on this to view a drop-down menu of Auto-fill Options.
In our example, select Copy Cells. This will copy not only the formula but also any formatting in cell B15 (e.g. the font and font size).
The Autosum Function
The next thing to do is calculate the Yearly Totals in column F, and for this we will use another shortcut. By far the most used formula in Excel is =SUM, which adds up columns or rows of numbers. Because this is used so frequently, there is an icon specifically dedicated to it. It is called the AUTOSUM button.
Now calculate the yearly total for Fares and Spending.
Click in the cell F5, go up to the toolbar and click on the AUTOSUM icon. The program will automatically put in a formula for you.
Look at what it has suggested: are there dotted lines running around the correct block of cells? If the formula is =SUM(B5:E5), that is correct and you can press ENTER.
There are times when the AUTOSUM will not choose the correct block of cells. Here is an example:
Select cell F5 and then, using the FILL HANDLE, replicate the formula in the NEXT 4 ROWS ONLY.
Column F should now look like this:
Click in cell F10 and then click the AUTOSUM icon. Look at the block of cells it has chosen. It wants to add up cells F5:F9, but we want it to add up cells B10:E10 for the total of “Going Out”.
We can change the block easily by clicking on cell B10 and dragging the mouse along to cell E10. As you do this, watch the formula in F10 change. When you are satisfied that the formula is correct - =SUM(B10:E10) - press the ENTER key.
Now continue to REPLICATE the formula into the other cells in the Yearly Totals column by clicking in cell F10 and dragging down to cell F13.
Although it is a very quick way of totalling up rows and columns, it is important to use the AUTOSUM function carefully. Do not just accept the range of cells that the computer offers you. Check that they are correct and, if they are not, use your mouse to click and drag over the desired range.
To find out our grand total for the year in cell F15, we have a choice: we can add up either the Quarterly Totals or the Yearly Totals. Both will come to the same amount so you can decide which way you prefer.
Use AUTOSUM in cell F15 to calculate the Grand Total for the whole year.
Save your work again. We do not need to rename the spreadsheet so you can just click on FILE and SAVE.
Showing And Hiding The Formulae
Sometimes boss tutor will ask you to hand in a spreadsheet with all the formulae showing in their cells. If you look at what is on the screen at the moment, you can see that the formulae are hidden; in their cells we can see only the answers to the calculations. To view the formulae in their cells - Formula View - use the key combination CTRL + ` (grave accent), as follows:
Hold down the CTRL key
The result is that all the columns on your spreadsheet will double in width and all the formulae will be shown in their cells. Where there are no formulae, numbers and text will simply remain as before - the only change that you will see to these numbers is that they are now left-aligned in their cells. DO NOT try to change this - it is the way that Formula View shows data.
To turn this facility off and go back to the normal Data View repeat the key combination CTRL + `.
Editing A Formula From The Formula Bar
Finally, if you realise that you have made a mistake in a formula or want to change it for some other reason, you can edit it:
Click in the cell that has the incorrect formula
Click once on the formula as it appears in the FORMULA bar
Use the arrow keys to move your cursor backwards or forwards to the parts of the formula that you want to change
Change the formula in the same way that you would make changes in Word
When you have finished, press ENTER
Save the spreadsheet again and continue to the next in this series of instructions!
NEXT: Editing, inserting and deleting
