Create an Expense-tracker with Google Sheets - Part 2

Disclaimer: The information provided in this blog post is for educational and informational purposes only and should not be considered as financial advice. The content presented here is not intended to be a substitute for professional financial advice, and readers should seek the guidance of a licensed financial advisor before making any financial decisions.

> Click here to jump straight to the main content <

Recap

In the previous post, I laid out the steps to create a basic expense tracking spreadsheet, but left the Monthly Expenses sheet empty.

In this post, we’ll utilize the data from the expense sheets to populate the main monthly expenses overview sheet.

Pre-reqs

If you don’t have the basic setup from the previous post done yet, head over there now to get your main spreadsheet setup before continuing with the next steps.

Setting up the Monthly Expenses Sheet

Update: I’ve made a template of the spreadsheet in this post available for download. So if you prefer to just use that instead of creating your own, click here to copy the template.

Now that the prep-work is done, we can start filling the Monthly Expenses sheet with our actual monthly expenses.

I’ll be adding both some made-up fixed monthly costs one might normally have, as well as the variable monthly spending from the sheets we added.

Adding Known Monthly Expenses

This is the initial layout I’ve set up for the Monthly Expenses sheet (refer to the steps below):

The cells highlighted in yellow are auto-calculated fields, the rest are plain (hard-coded) text.

Steps to create the layout

1. In cell A1, enter Combined Income, and then merge across cells A1 and B1 to create the header.

2. In cell A2, enter Net Yearly, and then enter your yearly after-tax combined household income in cell B2.

   Note: For my Net Yearly amount, I deduct taxes, as well as any amounts that might be auto-deducted from my paycheque, like employee share purchases and employee pension. You can choose to exlude them as I do, or include them in this amount, and then add them to the monthly expenses portion below - it’s totally up to you.

3. In cell A3, enter Net Monthly, and then in cell B3 enter the following simple formula which simply divides the net yearly amount by 12:

   =B2/12
   

   Note: remember not to leave any spaces to the left of the equal sign (=)!

4. In cell A5, enter All Monthly Expenses, and then merge across cells A5 and B5.

5. In cells A6 and B6, enter Description and Amount respectively.

6. In cell A18, enter Total Monthly Expenses, and in cell B18 enter the following simple formula:

   =SUM(B7:B17)
   

   Note that depending on how many rows you end up needing to list all your monthly expenses, you may end up removing or adding rows, and therefore your Total Monthly Expenses row might end up higher or lower than mine.

7. In cell A20, enter Total Monthly Remaining, and in cell B20 enter the following simple formula:

   =B3-B18
   

   This formula simply takes the Net Monthly amount calculated in cell B3, and deducts the sum of all the monthly expenses in cell B18.

Adding the Variable Monthly Expenses

We’ll now add the variable expenses from the sheets we added previously, to the Monthly Expenses sheet.

In my case, I’ll be adding these variable amounts to rows 14 to 18, but you may of course be adding them to different rows depending on your actual monthly expenses.

For any expense sheets, I’ve done as follows:

In cell A14, enter the name of the expense sheet, for example “Groceries”, and then in cell B14, press the equal sign (=), and then without pressing enter, switch to that sheet, click with the mouse in cell C3, and then press enter.

Repeat for any other expense sheets you added.

With the above completed, the Monthly Expenses sheet is now complete, and looks like this:

That’s pretty much it for a basic expense tracker, and you can keep adding more expense sheets as suits your lifestyle.

You can also change around the Monthly Expenses sheet to add additional income sources (rental income perhaps? or possibly share dividends?), or do other customizations.

Adding a Bit of Pizazz!

If you’ve made it this far, you’ve got a basic expense tracking spreadsheet, but maybe you’d like more visuals?

Google sheets has the option to add several different types of charts, which can help give a better visualization of what percentages of your spending are going where.

I won’t go into the specifics, as it’s really rather trivial, but the charts can be accessed by going into the Insert menu at the top of the sheet, and selecting the Chart option.

I’ve added a quick 3D pie chart to show what percent of my monthly expenses each expense accounts for, to give you a quick example of what can be added:

That’s it for now! Hope this tutorial helped and is something you are able to use to better understand your spending habits.

If you have any questions or comments, feel free to leave them in the comment section below!

Download the Template

If you’d like to use the template I created for this post, click here to copy the template.

Creating a Budget Tool with the Expense Tracker

If you’d like to take this a step further, and modify the expense tracker to be a budget tool, I’ve created a new post for that here.