Feeling the effects of the recession? Take control of your money by tracking where it goes with the help of our step-by-step spreadsheet guide
t's been a torrid time financially. Prices are rising across the board, cheap credit is no longer readily available and people are discovering the hard way that the money coming in doesn't equate to the money going out. But it doesn't have to be like that.
Anyone who manages their finances using a program like Microsoft Money will know that you save money from the word go. The simple act of recording your financial movements as soon as they happen - as opposed to relying on a bank statement or your cash machine's balance, which is always out of date - means you know exactly where your money's gone and how much you've got left until the next pay day.
While Money has many advanced features to help you stay on top of your finances - the act of automatically entering standing orders and direct debits on certain dates is one example of this - you don't actually need to splash out on it in order 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.
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.
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.
If you need to add extra categories to this section after setting up your categories following the steps in the project below, 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.
1. Excel 2003 and 2007 users should experiment with the Data > List feature - this offers an easier way to enter transactions in your spreadsheet.
2. 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.
Set up a spreadsheet to record your spending
1. FIRST STEPS
Start by creating a blank worksheet. Switch to Sheet2 and expand the width of column A by clicking on the column header as shown above and dragging it right. Place the cursor in cell A1 and type Total Payments into it. Press [Enter] to select cell A2 and type Total Deposits. Press [Enter] and type Account Balance into cell A3.
2. ENTER FORMULAE
Select cell C1 and type the following formula into it: =SUM(Sheet1!D2:D1000)
Next, type =SUM(Sheet1!E2:E1000) into cell C2, and =C2-C1 into cell C3.
3. FORMAT CELLS
Select all of column C by clicking on the C, open the Format menu and choose Cells (click Format under Cells and choose Format Cells in Microsoft Excel 2007). Select Currency and click OK. Select rows 1 to 3 by clicking row 1, holding [Shift] and clicking row 3. Change the font to Arial, click the B button to make it bold and change its size to 14.
4. CREATE AND FORMAT COLUMN HEADINGS
Switch back to Sheet1. Type the following headings into cells A1 through F1 as shown in the screenshot above: 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.
5. KEEP HEADINGS VISIBLE
To keep the column headings visible at all times place the cursor in cell A2 (just below Date) and choose Windows > Freeze Panes (it's on the View ribbon in Excel 2007).
6. SET CURRENCY AND DATE FORMATS
Next, select columns D-F by clicking on the column headings. Choose Format > Cells. Select Currency, make sure two decimal places are selected and click OK. Select column A, choose Format > Cells again in the previous step, but this time choose Date and select a format (we suggest the dd/mm/yyyy option). Click OK.
7. SET UP CATEGORY LIST
Switch to Sheet 3 of your spreadsheet. Type your first category into cell A1, the second into cell A2 and so on down column A of sheet 3. 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 left of the fx box.
8. SORT LIST
It can be awkward scrolling through a list looking for the correct category, so sort them alphabetically - to do so, click the column header, choose Data > Sort and click OK.
9. ASSIGN CATEGORY LIST TO COLUMN
Switch back to Sheet 1. Click the C column header so all cells are selected. Now choose Data > Validation and switch to the 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've not yet done so, save your document as the basic setup is now complete.
10. RECORD OPENING BALANCE
You're now ready to start entering transactions: type the date into cell A2, then press [Tab] to jump to the Payee column. Type Opening Balance into here, and press [Tab] twice to skip the category column. If you're in the red, enter the figure under Payments; otherwise press [Tab] to enter your balance in the Deposit column. Press [Tab], type =E2-D2 and press [Enter].
11. YOUR FIRST TRANSACTION
Enter the details of your first transaction into cells A3 through E3 - when you come to the category cell (C3), click the drop-down menu to pick your category.
12. ENTER UPDATED BALANCE
Your spreadsheet can calculate the updated balance automatically with the help of a simple formula: type =F2+E3-D3 into cell F3 and press [Enter] to see your new balance.
13. INPUT NEXT TRANSACTION
Enter your second transaction following the same principle as before, but when you come to enter your new balance, select the cell above this transaction's balance (F3 in this example).
14. UPDATE BALANCE
Press [Ctrl] + [C] to copy the formula to the clipboard. Select the current transaction's balance cell (F4 in our case) and press [Ctrl] + [V] to paste an updated formula into it.
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 Sheet 2 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 (click Save Workspace on the View ribbon in Excel 2007) to keep your layout intact.
Another thing you can do is change the colour of the balance column whenever you find yourself in the red, reminding you that you need to curb your spending or pay in some extra money. To do this, select column F (the balance column) and choose Format > Conditional Formatting (in Excel 2007, choose Styles > Conditional Formatting from the Home ribbon). Set the value to 0 (zero) and change the text colour to red.
Track your spending
The best way to track specific areas of your spending is visually. To get a visual representation of your spending, use the Chart Wizard. Select Insert > Chart to launch it. First, choose your type of chart and click Next. 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 to track your spending.
To track deposits instead of payments, type the following into the Data range box, where 'xx' equals the last cell in the range you wish to include in your calculation:
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.
Sort your finances
One of the advantages of storing your transactions in a list in Microsoft Excel is the ability to sort them by different column headings, so you can quickly view by payee, category or even payment size. Whereas charts give you a quick at-a-glance look at where your money's gone, sorting your data by category makes it possible to view your spending in greater detail.
We've already placed the balance totals on sheet 2 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 the column in question by clicking it first, then choose Data > Sort. Pick the column you wish to sort by, make sure Header row is selected under "My list has" and click OK. 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.