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 Financials.xls.
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:
(B3) Date
Don't type the (B3) part; the brackets ( ) ask you to click/select
the cell
Enter the following in their cells::
(C3)
Amount |
(D3)
Food
|
(E3)
Clothing |
(F3)
Rent |
(G3)
Entertainment |
(H3)
Other |
(I3)
Check
|
(J3)
Comment
|
From these headings, you see that we will enter dates and amounts
then ‘analyse' 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.
Border tool:
Use the drop down menu of the Border tool to put a border around all the selected
cells.
Fill Color tool:
Choose a colour 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!
- First, a couple of examples for cell entries
(It would be best if you entered some information of your own, so that you can
see the analysis start to take shape for yourself)
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.
(B4) 25/08
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.
- So on the 25th August you spent $25.76 on a movie ($15.20) and a meal
($10.56)
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 and H4.
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
!
box.
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
- To complete Row 9 of the table, you need to make:
(B9) Total
(C9) = SUM(C4:C8)
(Use the Autosum Button for this)
Fill handle:
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!
- In our diagram, we have put borders on Row 9.
We did this by selecting B9:I9 and using the Border Tool to add the border
that we wanted
- Before we fix our error in Row 7,
here's a great technique for highlighting a part of a table that you need to
pick out from the rest. Click on cell I3 and choose Format: Conditional
Formatting. This allows you to apply a format to a cell if certain conditions
are present.
Here, we want the condition to be as shown in the box below:
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:
(C7) 23.25
Both the error and the warning sign disappear when you make correction!
Website overview: Since 1996 the
Study Guides and Strategies web site
has been researched, authored, maintained and supported by Joe Landsberger
as an international, learner-centric, educational public service. Permission is granted to freely copy, adapt,
and distribute individual Study Guides in print format in non-commercial educational settings that benefit learners. Please be aware that the Guides welcome, and are under, continuous review and revision. For that reason, reproduction of all content on the Internet
can only be with permission through a licensed
agreement. No request to link to the Web site is necessary.
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.