Use your computer to create a budget with Microsoft Excel

If you’ve never opened the spreadsheet included in your office suite, you’re missing out on a very useful application. Here’s how to use it to create a household budget.

All office suites include a spreadsheet, but it must be one of the least-used applications of them all - not least since it’s far from obvious what you can do with it. Here, then, is a getting started guide to anyone who wants to put their spreadsheet to some use.

We’re creating a simple household budget you can use to keep track of monthly income and outgoings. You can use this kind of sheet as the basis of working out how much you can save to an expensive purchase, for example, and it will introduce some basic spreadsheet concepts that you can apply to other things.

[Read more: What is ransomware? Tips on prevention]

We’ve used Excel here, but the steps should be more or less the same in any spreadsheet application.

Step 1: Create some row headings

one

Launch your spreadsheet application and create a new, blank sheet. We’re going to start by listing types of outgoing in columns A and B. Type the following in the corresponding cells:

A2: OUTGOINGS

A3: Bills

A8: Car

A11: Entertainment

A13: Food & drink

A15: Miscellaneous

A16: TOTAL

A18: INCOME

A20: NET

Step 2: Create some categories

two

Now move to cell B3 and type the following in the corresponding cells:

B3: Rent

B4: Gas

B5: Electricity

B6: Water rates

B7: Council tax

B8: Petrol

B9: Insurance

B10: Road tax

B11: TV licence

B12: Broadband

B13: Groceries

B14: Takeaways

B15: ATM

B18: Salary

B20: NET

This has created a simple list of outgoings and income, organised into categories — feel free to modify them if they don’t suit your particular circumstances.

Step 3: Create column headings for months

Finally, type Jan in cell C2 and then continue with Feb, Mar and so on across the rest of that row until you get to N2 to create column headers for 12 months.

three

[Read more: How to restart Windows and whatever happend to Safe Mode?]

Step 4: Enter the figures for one month

four

Now to enter some figures for monthly outgoings and income, starting with the Jan column. Again, it’s simpler to copy our figures here, but feel free to use your own, particularly if you’ve modified the categories in columns A and B.

Step 5: Add up the total outgoings

five

Now to work out the total expenditure. Select cell C16 and type:

=SUM(C3:C15)

…and press the Return key. This is known as a ‘formula’ in spreadsheet parlance and the opening = sign tells Excel that it needs to perform a calculation using what follows. sum is the actual formula and it adds together the range of cells in brackets - C3 to C15, inclusive, in this case.

[Read more: How to keep your Windows desktop tidy]

Step 6: Work out income minus outgoings

six

Enter your income in cell C18 and now we can work out how much money is left at the end of the month. This is a much simpler formula that just subtracts the total outgoings from the income, so type the following into cell C20:

=C18-C16

Step 7: Copy one month’s figures

seven

Rather than type more figures into the Feb (D) to Dec (N) columns, it’s simpler to copy and paste the Jan (C) column. Click cell C3 and drag the mouse down to cell C20 to select all of the figures. The press the Ctrl + C keyboard shortcut for Copy. The selected range of cells will now be highlighted with a blinking dotted outline.

Step 8: Paste the figures to the rest of the sheet

eight

Now to select the range of cells to paste the Jan column to. Select cell D3 and, just as before, drag the mouse to cell N20. Now press the [Ctrl] + [V] keyboard shortcut to paste the cells copied in Step 7.

Excel will apply a bit of intelligence here to paste the cells and update any formulas to keep them consistent. In our example, we’ve then added quarterly gas and electricity payments in the Mar, Jun, Sep and Dec columns.

[Read more: Discover the tech to help you budget]

Step 9: Modify the monthly NET formula

nine

The only problem now is that the formulas in row 20 (NET) don’t take into account the previous month’s balance, so we’ll need to make a minor change. Select cell D20 and type:

=D18-D16+C20

This will replace the existing contents of the cell and will add the NET figure for Jan to the NET figure for Feb. You can now copy cell D20 and paste it to cells E20 to N20, using the same technique as Step 8.

Step 10: Format the sheet

ten

The final step is to format the spreadsheet to make it more understandable. In our example, we’ve made the row (Row 2) and column (A and B) headings bold, along with the rows for TOTAL, INCOME and NET. We’ve also applied some colour and alignment, and formatted all figures as Currency (£ English).

We’ll leave you to figure out how to do this — you just need to select the ranges of cells you want to format and use the formatting options on the Home tab, if you’re using Excel. You may also need to adjust the column widths so that the contents fit — just drag the dividing line between each column header (A, B and so on) to do this.

Stay safe online with our broadband extras: Protect your computer from viruses and keep your files and photos safe

Not with us? Check out our broadband deals

More from BT