Spreadsheets can help you with your financial calculations.
Develop a spreadsheet to analyze your expenditures for a month, or for whatever period you like,
and learn the basics of organizing budgets with key features of spreadsheets:
Open a new spreadsheet
Choose File: Save As and save the file as
Make a habit of saving each spreadsheet immediately when you start because if something goes wrong (such as the dog tripping over the power cable and turning your computer off) a saved file is much easier to recover than an unsaved one
Begin to enter information:
Simply click on a cell and type your entry
If there's already something there, you don't need to delete it – just start typing and you will overwrite the current contents of the cell.
Click on cell B3 and type Date.
In future, when we want you to enter data into a cell, we'll simply write:
Don't type the (B3) part; the brackets ( ) ask you to click/select the cell
Enter the following in their cells::
From these headings, you see that we will enter dates and
then ‘analyze' the amounts into key areas of expenditures.
Your spreadsheet should now look like:
In cell G3,
the word Entertainment is too long to fit the cell, and so
it has been cut short.
Here's a simple way to make the cell wide enough. Double click on the line between the column headings G and H. This adjusts the column width to fit the information in its cell (column G).
To perform the same action to a number of cells at once,
they need to be selected.
Click on B3 then hold down the mouse while you drag across to J3 and release the mouse.
Use the drop down menu of the Border tool to put a border around all the selected cells.
Fill Color tool:
Choose a color that you like to mark the cells as headings rather than data.
Light colored backgrounds with dark text colors work best for easier reading!
Now let's spend some money!
In the first row, we will also fix up the formatting as we go and show you how to make sure that the formatting is repeated in the cells below. We start with the date.
As soon as you type 25/08 and press <Enter>
the spreadsheet assumes that you have typed a date, and formats it as a date
The program also adds the current year to the data that you have typed, which you can see by double clicking on B4. There's good news and bad news in this action of the program. It saves you time, if the date is what you want, but if you didn't mean to type a date, the cell will have been given a date format, which you may have to remove by selecting the cell and choosing Format: Cell: General.
Enter: (C4) 25.76 (D4) 10.56 (G4) 15.20
It's easy to make a mistake when typing, which is why we suggest a Check column.
Click on cell I3 and then on the Autosum button.
You'll see that the program adds the numbers in G4
But you want the Check to add together all the numbers in cells D4:H4.
Drag the bottom left corner of the selected range across to D4 and the formula will automatically adjust
This feature of adjusting the range of a formula is
really handy at times
The numbers that we have typed are all "dollar" amounts, and the formatting should reflect this.
Select D4:I4 by clicking on D4 and dragging
across to I4
On the Currency Style button (that's the one with a $ symbol).
Did you notice that there is a small triangular marker in the top left of cell I4?
Excel thinks that you might have made an error in your formula
To see the comment, click on cell I4 and then
hover over the
You will be told that there are numbers in cells next to the range that you have chosen.
But we don't want to include the Amount or Date columns in this summation. It's simply there so that we can immediately see if our analysis of the Amount is correct.
You can ask Excel to ignore this ‘error' using the dropdown menu of this warning.
Adding the following data to the table
There is an error in Row 7 but we will correct that later
(C9) = SUM(C4:C8)
(Use the Autosum Button for this)
Move the mouse pointer to the bottom right corner of C9 until it changes shape and becomes a vertical cross. This is called the Fill Handle
While it is showing, you can click down and drag
across to I9 and the formula in C9 will be copied into
or fill all the cells between C9 and I9.
The Fill Handle is a great time saver!
Here, we want the condition to be as shown in the box
Set the centre box to “not equal to” and in the
right-hand box type =C4.
Click on the Format button, choose the Pattern tab and select a colour that will show up whenever the analysis doesn't match with the amount.
Click OK to complete the formatting.
You won't see anything immediately, but if you use the Fill Handle to fill I4 down to I9, you'll see immediately where the error has occurred.
To fix the error, you make:
Both the error and the warning sign disappear when you make correction!
You may noticed that the information we added to the table is in no particular order.
Excel/spreadsheets can sort information
Select the whole of the table apart from the Totals. That is, select cells B3:I8 and choose Data: Sort . In the dialogue that shows, Excel suggests that you should sort by Date because that is the information in the first column. The program also detects that your data has a Header Row (i.e. the first row are the names of the columns in the table). For this data, both these choices are correct and we only need to click OK to have the table sorted by date. Otherwise, the program provides dropdown menus to enable you to change the Sort criteria.