Create an Expense tracker with Google Sheets - Part 1

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 <

There are tons of expense-tracking apps out there. Some are for desktop computers, some are for mobile. Some have tons of functionality, and can definitely be worth checking out if you don’t mind the purchase cost.

On the other hand, if all you need is something basic, you can just whip-up your own simple spreadsheet to do the essentials. That’s exactly what I’ll show you how to do in this post.

Google Sheets, MS Excel, OpenOffice… Your Choice

I’ll be using Google Sheets to set up this simple expense-tracker, but you should be able to replicate it in any other spreadsheet application, as they all generally work the same. The main difference will be with the formulas used to generate the calculations, which would need to be adjusted for the spreadsheet app you’re using.

Getting Started

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.

1. First off, we’ll need to create a new Google spreadsheet. So head over to drive.google.com, and create a new spreadsheet (either directly in My Drive, or in a folder in My Drive, whichever you prefer).

   

2. Once the new sheet has loaded, rename the default sheet from Sheet1 to Monthly Expenses by right-clicking the bottom sheet selector tab, and selecting the Rename option.

   

   Note: We won’t touch the Monthly Expenses sheet in this post, so we’ll skip it for now. We’ll get back to that sheet in Part 2 of this article.

3. Next, create a new sheet by clicking the Add Sheet button (+) to the left of the Monthly Expenses tab, and rename it to Groceries (or anything you want really), and then switch to that sheet.

Setting Up The Header Columns

The top 4 rows in our expense sheets will be header rows, to identify the columns, as well as show averages and total amounts. Any expense data we’ll enter later on will start from row 5 and on.

4. In the upper-leftmost cell, cell A1, type “Groceries” (or whatever you decided to call this sheet) and press enter, and then click in the cell and drag vertically across the next two cells to select/highlight them.

   

   Then click on the Merge button in the toolbar at the top of the window to merge the 3 cells.

   

5. Next, in cells A2 to C2 respectively, enter the following text:

   • 12-month Rolling Total
   • Avg. Cost / Trip - All Time
   • Avg. Cost / Month

   

Adding Some Formulas

6. Continuing with the header setup, we’ll now add some formulas to cells A3, B3, and C3, as follows:

   In cell A3, enter:

   =IFERROR(SUM(FILTER(C$5:C,A$5:A > EOMONTH(TODAY(),-12))),"N/A")
   

   The formula in this cell, A3, will look back a maximum of 12 months of expenses in column A, and only pull purchases from that range. This is to give us a rolling total cost over the past year, which should suffice for the purpose of calculating average spending.

   In cell B3, enter:

   =IFERROR(AVERAGE(C$5:C),"N/A")
   

   The formula in this cell, B3, will simply give us the average amount spent for all purchases we enter in this spreadsheet.

   In cell C3, enter:

   =IFERROR(A$3 / DATEDIF(EOMONTH(MIN(FILTER(A$5:A,A$5:A > EOMONTH(TODAY(),-12))),-1),EOMONTH(TODAY(),),"M"), "N/A")
   

   The formula in this cell, C3, looks only at the rolling total value from the output of the formula in cell A3, and divides by the number of months (up to 12 max depending on what dates you’ve entered), to get an average cost per month.

7. Your sheet should now look similar to this:

   

   Note: If the formulas you entered are showing as text and not actual formulas, remove any spaces to the left of the equals (=) symbol in the formula.

8. Next, in cells A4 to C4 respectively, enter the following text:

   • Date
   • Location / Description
   • Cost

   And then freeze the top 4 rows by opening the View menu, hovering over Freeze, and selecting “Up to row 4”.

   

Add a bit of styling

Optional: If you’re so inclined, you can add a bit of styling to the header cells by bolding, centering, and adding background colors. I won’t go through the steps to do that here, as I’ll let you decide how you’d like to style your own sheet.

This is what my sheet looks like now, after I’ve applied a bit of formatting and styling:

Repeat for any other expenses you want to track

We’ve just added a single expense sheet for tracking groceries, but you’ll most probably want to add more for any other expenses you want to track. For example, I have sheets for tracking my gas & transport expenses, dining out, health & dental, clothing, and miscellaneous expenses.

So to add another sheet, simply right-click on the Groceries sheet tab, and select the Duplicate option. Then simply rename the new sheet to whatever expense you want to track, and update the header row on the sheet in cell A1.

Download the Template

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

Now Start Tracking Your Spending!

That’s really all you need to do for a basic expense tracker! All that’s left is to start filling in the rows below the headers with your actual expenses, and the averages will start getting calculated.

Next Steps

At the beginning of this post we created the Monthly Expenses sheet but left it blank, as that wasn’t the focus of this post. If you’ve made it this far and you’re still awake, and want to get that sheet filled in, head on over to Part 2 of this post, where I’ll guide you on the simple steps to set up the Monthly Expenses overview sheet.