Looking after your money has never been so important, and one of the most effective ways of clawing back a few pennies is simply by tracking where it goes. If you can’t afford a dedicated a program for managing your personal finances, you can use Microsoft Excel instead.
In this guide, we’ll show you how to use a spreadsheet 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 parts of your spending with the help of categories.
This step-by-step guide works with Microsoft Excel 2010 Starter, which comes preloaded on many Advent PCs, but should work in earlier versions of Excel too.
1. Set up a spreadsheet
Open Microsoft Excel from the Start > All Programs menu. A blank worksheet should appear. Switch to Sheet 2, using the tabs at the bottom of the spreadsheet. Expand the width of column A by clicking on the column header (as shown above) and dragging it to the right.
Now, 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 the fx box just above it:
In cell C2, type:
and finally, in cell C3 type:
3. Format cells
Select all of column C, by clicking the C column header, then click the General dropdown menu in the ribbon (as shown) and choose Currency from the list of available options.
Now click on the row 1 header to select the entire row, hold the [Shift] button and click row 3 to select all of rows 1, 2 and 3. Change the font to Arial, click the B button to make it bold and change the font size to 14.
4. Set up column headings
Switch back to Sheet 1, where your transactions will be recorded. Type the following headings into cells A1 through to F1: Date, Payee, Category, Payment, Deposit, Balance. Resise the column widths and format your headings with a bold font to make them stand out from your transactions.
To ensure these column headings are always visible, look for a box with a small horizontal line (it’s to the right of the column headings and above the vertical scrollbar). Click and drag the box so there is a thick dividing line under row 1, then release the mouse button.
5. Set currency and date formats
Next, click on the column D header, then hold the [Shift] key and click on the column F header to select columns D, E and F. Click the General dropdown menu and choose Currency.
Now select column A, click General again, but this time choose either Short date or Long date.
6. Show negative balances in red
Select column F (Balance) by clicking the column header. Now click the Conditional Formatting dropdown menu and select Highlight Cell Rules followed by Less Than__ Enter 0 (zero) in the left-hand box and choose a style (such as Red Text) and click OK.
7. Set up the opening balance
Type the date into cell A2, using the format DD/MM/YY, then press the [Tab] key to jump to the Payee column. Type Opening balance into cell A3 then press [Tab] twice to skip the Category column.
8. Enter balance
If you're in the red, enter the figure under Payments; otherwise press [Tab] to enter your balance in the Deposit column. Press [Tab], go to cell F2 and type the following formula under balance, then type
=E2-D2 and press [Enter].
9. Your first transaction
Enter the details of your first transaction into cells A3 through to E3. When you come to the Category column, type a category such as Mortgage, Salary, Cheque deposit or Utilities bill, to help categorise your spending. If the amount is a payment from your account, enter it into cell D3; if it’s a deposit, enter it in cell E3.
Your spreadsheet can calculate the new balance automatically with the help of a simple formula that you type into cell F3:
Press [Enter] to see your new balance.
10. Input next transaction
Enter the details of your next transaction, but when you come to the balance in column F, select the cell immediately above it (F3 in the example above) and press [Ctrl] + [C] to copy it to the clipboard . Now select your cell (F4 in this case) and press [Enter] to paste an updated formula into it with your new balance automatically calculated for you.
11. Keep adding transactions
Repeat for all the transactions you make on this account. As you add more data, you’ll discover shortcuts. For example, when you type the first few characters of a previously used category, it will be highlighted: press the [Tab] key to accept it. Remember to update the balance by copying and pasting the contents of the balance cell (column F) from the previous transaction.
12. Get a visual clue
You can track your spending with the help of a pie chart. Select columns C and D, then switch to the Insert Ribbon, click Pie Chart, and choose the type of chart you’d like. It will appear in a separate box and you can customise it by using the controls on the Design, Layout and Format ribbons that appear under Chart Tools. If you want to delete it, simply select it and press the [Backspace] key.