Back to previous pageReturn to Tutorials index



Keep track of your home finances in Microsoft Excel
Free backup is finally here.  Mozy Remote Backup.
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

I
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.
 
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.
 
   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.



Step-by-step: Set up a spreadsheet to record your spending


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


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.


Crucial Green 468x60







All content copyright Support PCs and the original authors