See also:
How to create basic formulae in Excel How to create PivotTables in Excel How to delete duplicates in Excel How to add, delete, lock, format, merge, and split cells in Excel
How to budget your money in Excel: Creating the spreadsheet
The basic outlay of the spreadsheet will break down into two parts. Down the left hand side of the page we’ll create entries for the various income and expenditure, while across the top there will be sections for the different months. This will allow you to see any patterns that appear in your finances as well as making it easier to predict and plan for the weeks ahead. To start with open a new spreadsheet in Excel and then in the A1 cell enter the title Expenses. Now in the cells to the right (B1, C1, etc.) enter the months of the year, one in each. In the Expenses column add the various names of outgoings that you expect to incur. Think of everything that you’ll be paying out, including the mortgage/rent, bills, insurance, and other personal charges such as mobile phone contracts. Finally at the bottom of the list be sure to name a cell Total. When you’ve entered these fields start to put the amounts in the month column.
Once this is completed you’ll want to know the total amount of outgoings you’ve amassed. This is easy to do thanks to Excel’s AutoSum feature. Click on an empty cell beneath the column you want to add up then – while still holding down the mouse key or trackpad button – move up to highlight all of the figures. Now look in the upper right corner of the Home menu (the strip at the top of the worksheet with all of the formatting options) where you should see the ∑ symbol with AutoSum next to it. Click this and the total will be inserted into the empty cell.
Of course to see how much money you actually have we’ll need to add your income to the equation. To do this select an empty cell in the Expenses column – it has to be under the row that includes Total – and call it Income. Then enter the amount you’ve accrued, being sure that it sits under the correct month.
At the moment the two figures for outgoing and incoming are not linked, meaning you have to work out the sum manually. But wait, this is a spreadsheet, so automating the process is just a case of entering a simple formula. Create a cell called Grand Total under the Income cell and then make a note of the grid references for both Income and Total. You do this by looking at the column letter and the row number, so in our case Total is in cell B14 and Income is in B16. Now, select the cell next to Grand Total where you want to display the calculation for that month and enter the following formula: =B16-B14 Press return and you’ll see that figure in the cell shows the difference between your income and expenses.
How to budget your money in Excel: Copying the formulae
With everything now set up you’re free to use the budget spreadsheet each month, entering your expenses as and when they leave your account. One thing you don’t have to do is keep entering the formulae for the totals, as Excel can cleverly copy them. To do this highlight the Total cell which currently shows our sum for January, and press Ctrl+C to copy the formula. It might look like you’re actually copying the total that’s in the cell, but Excel is smart enough to know what you’re doing. Now hold down the Shift key and use the arrow keys to highlight the cells to the right that correspond with each month. In this case they are C14 through to G14. Press CTRL+V and a row of £0s will appear in each cell.
If you highlight one of these cells and then look in the formula bar at the top of the worksheet you’ll see that Excel has indeed copied the formula, but also amended each one so that it works with the column it represents. For example February is in the C column, so Excel altered the formula from =SUM(B3:B13) to =SUM(C3:C13)
Repeat the process for the Grand Total cell and you now have a fully automatic spreadsheet that can help you manage your finances, avoid any surprises, and hopefully give you back control of your bank account. Martyn has been involved with tech ever since the arrival of his ZX Spectrum back in the early 80s. He covers iOS, Android, Windows and macOS, writing tutorials, buying guides and reviews for Macworld and its sister site Tech Advisor.
