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.
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
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:
A13: Food & drink
Step 2: Create some categories
Now move to cell B3 and type the following in the corresponding cells:
B6: Water rates
B7: Council tax
B10: Road tax
B11: TV licence
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.
Step 4: Enter the figures for one month
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
Now to work out the total expenditure. Select cell C16 and type:
…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.
Step 6: Work out income minus outgoings
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:
Step 7: Copy one month’s figures
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
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.
Step 9: Modify the monthly NET formula
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:
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
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.