How To Make A Household Expenses Spreadsheet
You can record your Monthly Expenses with your own Simple Worksheet
Spreadsheets are surprisingly useful. They can be used to keep track of household or business expenses. If you are not used to working with spreadsheets, it is easier to start with a simple worksheet.
Many people have no idea how much money they are spending, which can lead to debt, so this is a useful exercise for lots of us. If you want to record how much you spend each month, or if you just want to practice making spreadsheets, this page will show you how to do it. Later, we could adapt the spreadsheet to include income, taxes, and more.
I will use Microsoft Excel for this project. If you don't have spreadsheet software, first visit my page Spreadsheets for beginners where I show the basics of spreadsheets, and how to find free spreadsheet software. The skills are transferable between different computer software and online spreadsheets.
The images are copyright to me, and must not be used without my written permission and a link to this page. Thanks!
Open Your Spreadsheet Software - This is a Microsoft Excel worksheet
You will see a blank grid, like the one above. Don't be intimidated by the array of empty cells, it will soon look better! The columns are marked with letters at the top. The rows are marked with numbers at the left side.
The cell A1 is the active cell, and will be highlighted. In other words, if you type something now, it will appear in that cell. So, try it : Type the words "Monthly spending", then click the Enter key on your keyboard. This fixes the typing in the cell, and moves the active cell down the column.
Spreadsheet Text Entry
Continue down the column, typing the following words in the cells:
A2 Rent, mortgage
A3 Heating, cooking, lights
A4 Home cleaning, maintenance, repairs
A6 Vehicle fuel, repairs, cleaning
A 7 Personal care, clothing, hair, beauty
A8 Entertainment, eating out, movies
Other could include exceptional expenses like medical bills, medicines, annual car tax, big purchases like a new TV.
The worksheet (for that is what it is called!) should now look like this:
Adjusting Column Widths
You can see that the words that we've typed look like they run over from column A into columns B, C and D. Actually, they don't, the words are still in column A, but in order to see column B clearly, we now need to adjust the width of column A.
There are several ways to do this, but for now we will a simple manual technique. Move your cursor over the line separating the letters A and B at the top of the worksheet. You will see an arrows symbol like the one here, but much smaller! Now, hold down the left button of your mouse, and pull to the right. This will widen the column. Release the mouse button with your new wider column A.
An alternative is to double-click when you see the arrows symbol. The column will auto-fit its width to your text.
Entering the Column Headings - on your spreadsheet
In cell B1, type "Week 1", then press the tab key on your keyboard. This will fix the text in cell B1, and move the active cell to the right. Be aware that If you press the enter key, you will find yourself in the cell below, B2, as shown in the picture.
Continue to add the column headings to row 1, as follows:
C1 Week 2
D1 Week 3
E1 Week 4
F1 Week 5
Saving Your Work
This is very important!
After a few minutes work, you should save your work using the SAVE AS command. This allows you to give the spreadsheet a suitable name, for example: Household Expenses, and to decide where to save the file.
After that, remember to save your work regularly, either using SAVE or SAVE AS, so you don't lose something important if your computer crashes. Using SAVE AS allows you to make extra copies of the file, with slightly different names. These can act as back-up files, in case you make a mistake and want to go back a few steps.
Adding Functions To Your Spreadsheet
In cell A10, enter the text "Weekly totals". In cell, A11, enter the text "Monthly totals". And now for the "magic"! Click on cell B10 to make it the active cell, then move your mouse over to the Editing group on the Home tab, and click on "autosum".
Cell B8 will now contain the following data string =SUM()
Between the brackets, type the following: B2:B9
Now press Enter, the cell B10 will now contain a zero.
That's because we haven't entered any amounts for our expenses. So, now you will need to enter some test amounts. As you enter each one, the total in cell B10 will update. Clever stuff!
In my example, I have added amounts to B2, B4, B5 and B6.
Adding Column Totals
"Populate" some of the other cells, for weeks 2, 3 4 and 5, with test amounts.
Then click again on cell B10.
We know that the active cell has a thick black border, but notice now that it also has a small black square in the bottom right-hand corner. Move your cursor over this tiny square, press down the left mouse key, and pull across to cell F10. This will automatically add the formula to each cell.
Check by clicking on D10. In the formula bar, it should now say:
We can also add a formula to cell B11 to add up the Weekly totals to make a Monthly total.
This is what you need to write in the cell:
The = symbol tells Excel that you are writing a formula.
SUM means you want to add the numbers in a set of cells
(B10:F10) tells Excel the set of cells is the row including B10, C10, D10, E10 and F10
When you press Enter, the answer will appear in the cell B11. You can practise changing the amounts of expenses in different weeks to see how the total is affected.
You could stop at that point, but if you are ready to learn about formatting a spreadsheet, visit the next exercise.
Remember, please, that a good household budget has to start with an accurate calculation of your income and an honest assessment of your expenses. So it is important that you are honest with yourself about your expenses.
How To Use A Spreadsheet To Record Grades
Remember that you can pause, and review, this video if you want to see something again.
Books On Using Spreadsheets
I particularly like this book, as it offers explanations, skills and tips suitable for both beginner and experienced Excel users. It is clearly laid-out, with illustrations complementing the text.
The Easy Steps series is very good as it introduces new skills, from the basic concepts to advanced concepts, in a clear way. Easy Steps Excel 2010 covers creating and manipulating worksheets, templates, tools, formulas, and advanced functions.
© 2013 savateuse