Who needs Money or Quicken when you can easily track your
spending and saving in Excel? Nick and Toni Peers reveal how simple it is
Anyone who manages their finances using a program like
Microsoft Money or Quicken knows that you save money from the word go
- simply because you know where your money's going.
But you don't actually need either program to start taking control
of your finances - you can keep an eye on your accounts quickly and simply using
Microsoft Excel. Whether you want to track a bank account, credit card or
just your Ebay sales and purchases, the following project is perfect for your
needs.
Worksheet setup
Your spreadsheet will be able to track your incomings and outgoings for a
single account. You'll be able to see your balance according to the entries
you've made, plus track specific parts of your account with the help of
categories, enabling you to generate charts of your spending and saving.
Start by creating a blank worksheet. Switch to Sheet2 and
type the following into the cells listed:
A1:Total Payments
A2: Total Deposits
A3: Account Balance
Now enter the following formulae into the cells listed:
C1: =SUM(Sheet1!D2:D1000)
C2: =SUM(Sheet1!E2:E1000)
C3: =C2-C1
Select column C and choose Format > Cells.
Choose Currency and click OK. Now select rows 1-3 and change the
font to Arial, Bold, size 14.
Switch back to Sheet1. Type the following headings into
cells A1-F1: Date, Payee, Category, Payment,
Deposit, Balance. Resize the column widths accordingly and format
your headings with a bold font to differentiate them from your entries.
Format your worksheet
It won't be long before these headings get lost as you add dozens of
entries. To keep the column headings visible at all times place the cursor
in cell A2 (just below Date) and choose Windows > Freeze
Panes.
Next, select columns D-F. Choose Format > Cells.
Select Currency, make sure two decimal places are selected and
click OK. Finally, select column A, choose Format >
Cells again, but this time choose Date and select a format
you feel comfortable working with (we suggest the dd/mm/yyyy option).
Click OK.
It's a good idea to categorise your financial ins and outs to help
you track specific areas of spending, so check the box below for details
on doing so with the help of a drop-down list. |
|
Categorise your entries
Track specific areas of spending or saving with the help of categories |
Categories enable you to track your finances according to what
you've received or what you've spent your money on - rather than having to type
in a category each time you enter a transaction, you can create a list that you
can pick from. Not only does it save time; it also ensures you file your
transactions correctly for an accurate picture of your finances.
To set up a list of categories, switch to Sheet3 of your
spreadsheet. Type your first category into cell A1, the second into cell
A2 and so on down the spreadsheet. When you've got enough categories,
click the A column so all the cells are selected and type Category
into the Name Box, which is next to the fx box.
It can be awkward scrolling through a list looking for the correct
category, so make things easier by sorting them alphabetically - to do so, click
the column header, choose Data > Sort and click OK.
Switch back to Sheet1. Click the C column header so
all cells are selected. Now choose Data > Validation > Settings
tab. In the Allow box click List, and type =Category in the Source
box - don't miss out the equals sign. Verify that In-cell drop-down is
selected and click OK.
If you need to add extra categories to this section, switch back to
Sheet3 and add extra categories in the same way as before. Don't forget
to sort them before switching back to Sheet1, where you'll find they're
immediately available for selection. |
Enter transactions
You're now ready to start recording transactions in your spreadsheet. This can
be a little awkward to set up, so we're going to store your transactions in a
list, as this is a logical way of viewing and entering them. Just follow the
three-step walkthrough below.
One of the advantages of storing your transactions in a list is the
ability to sort them by different column headings, so you can quickly view by
payee, category or even payment size.
We've already placed the balance totals on Sheet2 so they won't
interfere with the sorting process, but you also need to protect your opening
balance, as it must always be first in the list. Select row 2, right-click and
choose Hide to do so.
To sort your records by a particular column, select columns A-F and
choose Data > Sort. Pick the column you wish to sort by, make
sure Header row is selected under "My list has" and click OK.
| How to record transactions in your accounts
worksheet |
|
1 Set
opening balance
First, enter your account's opening balance. Type in the date
into cell A2, press [Tab], type Opening
Balance under Payee and press [Tab] twice.
If you're in credit, enter it in the Deposit column; if
you're in arrears, enter it in the Payments column.
Under Balance type =E2-D2 and press [Enter]. |
2 Enter
first transaction
Enter the details of your first transaction into cells
A3:E3 - don't forget to use the drop-down menu in column C
to pick your category rather than typing it in manually. When
you reach cell F3, type =F2+E3-D3 and press [Enter]. |
|
3 Enter
subsequent transactions
Continue to enter transactions in the same way as before. When
you come to entering your new balance, just copy the cell
above it in column F and paste it into your balance -
the formula will automatically update itself as it's copied
into the cell. |
EXPERT TIPS
The project has been successfully tested with Excel 97, 2000,
2002 and 2003. It should work as described in all versions.
Excel 2003 users should experiment with the Data > List
feature - this offers an easier way to enter transactions in your
spreadsheet.
Select your balance column and choose Format > Conditional
Formatting. When the cell value is less than zero, change the text
colour to red to warn you that your account is in arrears. |
|
Customise your spreadsheet
The basics of your spreadsheet are now in place - you can
track spending and payments quickly and painlessly, and always see
how much money you've got left. But don't stop there - half the
fun is improving your spreadsheet over time, and we've got some
ideas to help you.
|
Select Window > New Window, then
choose Arrange from the same Window menu. Pick Horizontal
and click OK. In the top-most window, switch to Sheet2 and
resize it so only the payments, deposit and balance figures are shown. Now
switch to the lower window and resize it so it fills the rest of the
screen. Choose File > Save Workspace to keep your layout
intact. Meaningful reports
It's all very well being able to record all your monetary transactions and
get a quick overview of the state of your finances, but how about tracking
specific areas of your spending? The best way to do this is visually, and
the walkthrough below reveals just how easy it is to do so.
To track deposits instead of payments, type the following into the
Data range box:
=Sheet1!$C$3:$C$xx,Sheet1!$E$3:$E$xx
Note that 'xx' equals the last cell in the range you wish to
include in your calculation. |
|
| Generate a chart to track your spending |
|
1 Launch
Chart Wizard
To get a visual representation of your spending, use the Chart
Wizard. Select Insert > Chart to launch it.
First, choose your type of chart - we're going for a pie
chart, but it's entirely up to you. Once selected, click
Next. |
2 Select
range of data to include
You'll be prompted to select the data range. Make sure the
cursor is flashing inside the Data range: box and then
select the cells you wish to include in your chart - in this
case, it's the category and payment cells so we can track our
spending. |
|
3 View and
update chart
When you're happy with your selection, click Finish.
The chart will appear as a separate object on-screen. You can
delete it, or update it by right-clicking it and choosing
Source Data, then changing the $D$xx reference to
equal the final entry in your accounts. |
EXPERT TIPS
If you want to track the type of spending - cash, credit card, cheque, etc
- create another set of categories following the instructions in the box.
We suggest placing them in column B.
After sorting your transactions, return them to their original order: if
you've only sorted them one way, press [Ctrl] + [Z];
otherwise, sort by Date.
Customise your workbook further by experimenting with the Format >
AutoFormat menu - try one of the lists. |
|
Copyright notice
All material on this site is the copyright of the original author. You may not reproduce any of it without our express permission. Please contact us through our appropriate Web sites to obtain that permission. Thank you Disclaimer
The advice provided on this site is given in good faith. However, we can accept no responsibility for any damage you cause your PC or your data by following it. We strongly advise backing up all your data before making any changes to your computer. We regret that we cannot provide personal PC support. However we refer anyone with specific computer queries to our forum. Full Disclaimer . Contact Us . Terms and Conditions. Privacy Policy .
|
