ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How To Make A Household Expenses Spreadsheet

Updated on November 20, 2014

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

A5 Groceries

A6 Vehicle fuel, repairs, cleaning

A 7 Personal care, clothing, hair, beauty

A8 Entertainment, eating out, movies

A9 Other

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:

=SUM(D2:D9)

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:

=SUM(B10:F10)

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

Easy Microsoft Excel 2010
Easy Microsoft Excel 2010

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.

 
Excel 2010 in easy steps
Excel 2010 in easy steps

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

Do you use spreadsheets? - What are they useful for?

    0 of 8192 characters used
    Post Comment

    • profile image

      JoshK47 4 years ago

      Nice guide!

    • shawnhi77 lm profile image

      shawnhi77 lm 4 years ago

      Very useful information.

    • profile image

      Thamisgith 4 years ago

      Good tips. The initial expanse of empty cells can look intimidating at first - but you're right, it soon looks better. Keeping tabs on household expenses is a great idea for just about anybody - and a simple spreadsheet is a great way to do it.

    • savateuse profile image
      Author

      savateuse 4 years ago

      @Thamisgith: Thanks for stopping by and taking the time to comment - much appreciated!

    • savateuse profile image
      Author

      savateuse 4 years ago

      @JoshK47: Thanks!

    • profile image

      tomoxby 4 years ago

      Once you get used to the format you might want to break the items down further and have sub-totals, especially with optional items or anything that is not a fixed cost so you can note changes with ease. I use a budget and it quickly identified where costs were out of line. This is a handy lens for people not already using one.

    • bossypants profile image

      bossypants 4 years ago from America's Dairyland

      This is a very useful and easy to understand explanation of using Excel to track expenses! I love Excel and this shows just how unintimidating and practical it is!

    • savateuse profile image
      Author

      savateuse 4 years ago

      @tomoxby: Thanks for visiting, and for your helpful comment!

    • savateuse profile image
      Author

      savateuse 4 years ago

      @bossypants: Thanks for visiting, and thanks for your nice comment!

    Click to Rate This Article