Google Sheets Expense Tracker: Free Template & How to Use

Last updated April 22, 2022

The key to meeting your money goals is making a budget and sticking to it. You don’t need fancy paid apps to track your expenses. You can use simple spreadsheet applications like Google Sheets.

In this tutorial, we will show you two different ways in which you can use Google Sheets to track your expenses.

The tutorial is going to be split into two parts. In the first part of the tutorial, we will show you how to use a free Google Sheets Budgeting template, and customize it to your needs.

In the second part, we will show you how to create your own Google Sheets expense tracker that you can design and customize according to your requirements.

You can either choose to build your own project expense tracker from scratch or download our ready-made template and start tracking immediately.

Click here to download Spreadsheet Point’s free Google Sheets expense tracker template.

NOTE: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!

What is an Expense Tracker?

An expense tracker is a tool that lets you keep track of your expenditure. A good expense tracker helps you keep a record of not just what you’re spending, but also what you’re earning throughout the month / year. It helps you clearly see your expenditures by category, so you know which expenditures are frivolous and which ones are truly necessary.

Why Should You Track Expenses?

To have real control over your money, it’s important to plan where you want your money to go. However, just planning is not enough. You need to also hold yourself accountable and try to spend within the limits that you set out for yourself.

A small application or even just a spreadsheet that categorically shows how much money is coming in and where your money is going can help you reach your money goals sooner.

Keeping track of your income and expenditures over months can actually help you learn to set more realistic budgets and attainable goals.

How to Create a Google Sheets Expense Tracker?

Let us look at two ways in which you can get an expense tracker for absolutely no cost, using just Google Sheets:

  • Using the Google Sheets built-in monthly expenses template  (Budget Template)
  • Creating your own Google Sheets expense tracker

Creating a Bill Tracker /  Expense Tracker Using the Built-in Monthly Expenses Template in Google Sheets

Let us first look at the easier way. Google Sheets offers a quick and easy budgeting tool in its Template Gallery. This is a simple template that helps you track your monthly budget.

The bill tracker template contains two tabs, one for entering your incoming and outgoing transactions and another that gives you a summary of your expenses for a given month.

To use this template, here are the steps that you need to follow:

  • Open Google Sheets
  • Navigate to File -> New -> ‘From template gallery’.
Navigate to File -> New -> ‘From template gallery’.
template gallery
  • This opens the Google Sheets Template gallery.
  • Under the Personal section, select the ‘Monthly Budget’ thumbnail.
  • This opens a new worksheet with the Monthly Budget template.

Here’s how the template looks:

google sheets expense tracker template

You can now start using this template to track your income and expenses.

Understanding the Monthly Budget Template

When you open the Monthly Budget template, you will notice that the workbook consists of two tabs:

  • Summary
  • Transactions
summary and transactions

The Transactions tab is where you enter daily details of your income and expenditure. On the left side you enter Income details, and on the right side you enter the Expense details.

transactions tab

For both sections, you have 4 columns:

  • Date: This holds the date of the transaction.
  • Amount: This holds the amount spent.
  • Description: This holds the exact purpose of the transaction.
  • Category: This holds the category of the transaction.

It’s vital to select the right Category from the dropdown list, since this is going to be used to summarize your expenditure in the Summary tab.

The Summary tab is almost like a small dashboard that summarizes your income and expenditure, giving you a bird’s-eye view of your cash flow.

summary tab

In the Summary tab, you will find the following:

  • A slot to enter your Starting balance for the month (The balance amount of money you have at the start of the month).
Starting balance
  • A bar chart displaying your starting balance vs. end balance
A bar chart displaying your starting balance vs. end balance
  • A small block displaying the amount and percentage that you’ve saved so far in the current month.
the amount and percentage that you’ve saved so far
  • Two small charts (or sparklines) displaying your total planned and actual spending / savings.
expenses and income
  • Category-wise summary of your income for the month.
summary of your income for the month.
  • Category-wise summary of your expenditure for the month.
Expenses

How to Use the Monthly Budget Template

To use the Monthly Budget template, follow the steps outlined below:

  • Start by clearing out all transactions from the Transactions tab. For this, simply select all the sample rows in both the tables and press delete from your keyboard.
How to Use the Monthly Budget Template
  • Select the Summary tab. You can now customize the categories in columns B and H according to your requirements.
customize the categories in columns B and H
  • Clear out the Planned values in cells D31 and J29, and replace them with 0.
Clear out the Planned values in cells D31 and J29, and replace them with 0.
  • Your template is now ready to be duplicated.
  • Every month, make a copy of the Monthly Budget file by navigating to File->Make a copy.
make a copy
  • Rename the new file with the name of the month that you want to track your budget for, e.g.: Jan 2021.
Jan 2021
  • Once you have a fresh copy of your budget for your required month, you can start budgeting.
Jan 2021 monthly budget
  • Enter your Starting Balance or budget for the month in cell L8.
Starting balance
  • Allocate your budget to each category, making sure that the total allocated budget does not exceed your budget for the month. Enter your planned expenditures for each category in the Planned columns of both tables.
Allocate your budget to each category
  • Select the Transactions tab. You can now start entering your income and expense transactions.
Select the Transactions tab. You can now start entering your income and expense transactions.

Once you’re done, select the Summary tab. You should find both Expense and Income summaries updated with the respective category sums (in the Actual columns).

Actual columns

The charts and other summaries also get automatically updated to reflect your transactions.

The charts and other summaries also get automatically updated to reflect your transactions.

The Monthly Budget template is quite helpful. However, it might not be exactly what you were looking for. Maybe you needed better visualizations, additional categories, or a completely different dashboard setup.

Of course, you can always customize the template according to your requirements, but you might find it difficult to make a small change without messing up the entire setup and formulae.

An alternative approach could be to build your own expense tracker from scratch.

Creating a Google Sheets Expense Tracker (Free) from Scratch

Creating your own expense tracker on Google Sheets is not as complex as you might think. In this section we are going to build an expense tracker from scratch.

If you prefer simply downloading the tracker and using it right away, instead of building one yourself, you are free to do so!

Click here to download Spreadsheet Point’s free Google Sheets expense tracker template.

NOTE: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!

Project: Expense Tracker from Scratch

Renaming the Transactions and Summary Tabs

The first step is to rename the two blank worksheets that we are going to work with. Create a second worksheet by pressing the ‘+’ icon next to the tab of Sheet1.

the ‘+’ icon next to the tab of Sheet1.

To rename a worksheet, simply double-click on its tab and type in the new name for the sheet.

Rename the Sheet1 to ‘Expense / Budget Summary’ and Sheet2 to any month name. For the time being, let’s just give it the name ‘Jan 2021’.

Later, once you’re done making your template, you can duplicate this tab and rename it to the current month’s name, repeating this for every month.

renamed tabs

Note: The name you use for this tab is critical because you will be using this name to refer to its data in different formulas.

Building the Outline for the Transactions Sheet

To create an Expense Tracker, we start by creating a basic skeleton for the ‘Transactions’ tab (in our case, the tab named ‘Jan 2021’. This is the template for the tab that will let us enter our incoming and outgoing money transactions.

You can replicate the design shown below, or design your own sheet with your personal touch, company logo or brand colors (if applicable). You can also choose to simply copy and paste the outline from our template here.

 money in money out template

Format the cells of column D to the ‘Currency’ format. For this, select the cells and navigate to Format->Number->Currency.

 Format->Number->Currency.

Repeat for cells of column J.

Format the cells of column A to the ‘Date’ format in the same way.

Note: At this point, we don’t need to add the dropdown lists for the ‘Category’ columns. We can include this after creating the main Expense/Budget Summary sheet.

Building the Outline for the Expense / Budget Summary Sheet

In the next step create the outline for the Summary tab as shown below:

 expense tracker template

Again, you can either customize it according to your liking or copy-paste the outline from our template.

If you do opt for customizing the outline, make sure you keep the Expense by Category table (A10:D18), Income by Category table(F10:I18), Cash flow table (E6:F7) and the overall summary table (B3:C5) in the same locations. This will ensure that the formulas that we are going to use refer to the right cells.

However, if you’re an intermediate level Google Sheets user and are familiar with how formulas work, you can feel free to design the sheet according to your liking.

Add the categories according to your requirements. Remember, you can always come back and edit these categories whenever you need to.

add categories

Note: Make sure to format cells B11:D18 , G11:I18, C4:C5 and F6:F7 in the Currency format.

Finally, add 0s to the Planned columns of both Income and Expense by Category tables:

add 0s to the Planned columns of both Income and Expense by Category tables

Adding Formulas to the Cash Flow Table

It’s now time to add the formulas. Before adding the formulas, let us enter a month in cell C3. The value in this cell will give us a clue as to which tab we will need to pull our transaction data from.

Adding Formulas to the Cash Flow Table

Now let’s enter the formulas for the Cash Flow table (J7:J8). Fill in the following formulas:

In F6: =SUM(INDIRECT(TEXT($C$3,"mmm yyyy")&"!J3:J"))
In F7: =SUM(INDIRECT(TEXT($C$3,"mmm yyyy")&"!D3:D"))

Cell F6 must hold the value of total money earned throughout the month. We need to pull this value from column J of our Transactions tab (the ‘Jan 2021’ tab in our case).

We want the total amount earned, so we need to sum up all the values in the range J3:J.

Normally, we would get this value by simply using the formula:

=SUM('Jan 2021'!J3:J)

But we want to make our formula dynamic, so that when we add more tabs for different months, this cell refers to the right tab depending on the month entered in cell C3. For this we need to use the INDIRECT function.

The INDIRECT function returns a reference to a cell specified in the form of text. The date specified in cell C3 is a date. We need to combine this date(which actually refers to a tab name) with the cell references that we want.

The string $C$3&”!J3:J” in our case, simply means ‘Jan 2021’!J3:J. However, this is just a string. It is not yet a cell reference. We use the INDIRECT function to return a reference to the cells specified by this string.

We can then pass this returned reference to the SUM function, as follows:

=SUM(INDIRECT($C$3&"!J3:J"))

Now there’s another problem.

The value in cell C3 is a Date value, not a string. We need to convert it to a string if we want to combine it with the string “!J3:J”. To do that, we use the TEXT function. To convert the date in C3 to a text with the format “mmm yyyy” (Short month name and full year), we use the formula: TEXT($C$3,”mmm yyyy”)

So the final formula in cell F6 becomes:

=SUM(INDIRECT(TEXT($C$3,"mmm yyyy")&"!J3:J"))
=SUM(INDIRECT(TEXT($C$3,"mmm yyyy")&"!J3:J"))

The same explanation applies to the formula in cell F7.

Adding Formulas to the Expense by Category and Income by Category Tables

The Expense by Category table contains columns to specify the expense Category, amount Planned for this category, amount Spent on this category and the Difference between amount planned and spent.

Let us tackle each column one by one:

The Spent Column

In the Spent column, we need to display the total amount spent towards each category. Let us start with the first category (the Shopping category).

In cell C11, enter the following formula:

=SUMIF(INDIRECT(TEXT($C$3,"mmm yyyy")&"!E3:E"),A11,INDIRECT(TEXT($C$3,"mmm yyyy")&"!D3:D"))

This formula finds the total amount spent on Shopping throughout the month (Jan 2021 in this case).

Let us break up the formula to understand it:

The formula INDIRECT(TEXT($C$3,”mmm yyyy”)&”!E3:E”) returns a reference to cells in the range E3:E of the ‘Jan 2021’ sheet. These cells basically correspond to the Category column for the Money Out section.

Similarly, the formula INDIRECT(TEXT($C$3,”mmm yyyy”)&”!D3:D”) returns a reference to the cells in the range D3:D of the ‘Jan 2021’ sheet. These correspond to the Amount column for the Money Out section.

The SUMIF function simply adds up all the cells in a specified range that match a specified criterion. In the above formula, we are trying to sum up all Amount values  (from ‘Jan 2021’ sheet) which have their corresponding Category values equal to the value in cell A11 (from the current sheet)

Here’s the output that we get in cell C11:

 cell C11

Copy this formula down all the way to cell C17 (using the fill handle). Here’s what the range C11:C17 looks like at this point:

range C11:C17
The Difference Column

The Difference column (cells D11:D17) contains the difference between the Planned and Actual expenses for each category. For this, enter the following formula in cell D11:

=B11-C11

Copy it down to cell D17:

=B11-C11
The Total Row

Finally, we need to calculate the totals in row 18. Enter the following formula in cell B18:

=SUM(B11:B17)

Copy this formula right up to cell D18 using the fill handle.

Your Expenses by Category table is now complete. Here’s how it should look at this point:

 =SUM(B11:B17)

Repeat the same process to complete the Income by Category table. Use the following formulas:

In cell H11: =SUMIF(INDIRECT(TEXT($C$3,"mmm yyyy")&"!K3:K"),F11,INDIRECT(TEXT($C$3,"mmm yyyy")&"!J3:J"))

Copy this down to cell H17

In cell I11: =H11-G11

Copy this down to cell I17

In cell G18: =SUM(G11:G17)

Copy this right to cell I18

Here’s how your Income by Category table should look at this point:

 Income by Category table

Adding Formulas to the Overall Summary Table

Finally, enter the formulas into the overall summary table. In cell C4 we want the total amount earned for the given month. We already have this amount calculated in cell H18. So enter the following formula into cell C4:

=H18
Adding Formulas to the Overall Summary Table

Similarly, in cell C5, we want the total budget, or the total amount that we planned to spend in the given month. We already have this amount calculated in cell  B18, so enter the following formula into cell C5:

=B18
=B18

Adding Cell Validations

You can add cell validations to your budget tracker to accomplish the following:

  • Ensure that your total budget does not exceed the total amount available
  • To ensure that you don’t accidentally enter a category in your Transaction sheet that is not reflected in the Summary sheet.
Cell Validation to Ensure the Total Budget Doesn’t Exceed Amount Available

We want to make sure that the value in cell C5 (the budget) doesn’t exceed the value in cell C4 (The total amount available). For this follow the steps below:

  • Click on cell C5
  • Navigate to Data->Data Validation
Navigate to Data->Data Validation
  • In the Data Validation dialog box, click on the dropdown next to Criteria.
  • Select Number from the dropdown list.
  • From the next dropdown list, select ‘less than or equal to’.
  • In the next input box, enter ‘=C4’.
In the next input box, enter ‘=C4’.
  • Click Save.
Cell Validation to Avoid Accidental Category Entries

We also want to add a dropdown list in the Category columns of the ‘Jan 2021’sheet. The dropdown list will give us a list of category options that we can select from, making sure that we don’t end up adding a category that is not accounted for in the Summary sheet.

We can obtain this list of category options from the Summary sheet. Here’s how:

  • Select all cells in column E (in the Jan 2021 sheet), starting from cell Es3.
Select all cells in column E (in the Jan 2021 sheet), starting from cell Es3.
  • Navigate to Data->Data Validation
  • Click on the dropdown next to Criteria.
  • Select ‘List from range’ from the dropdown list.
  • In the next input box, enter the range containing the category list. In our case, it is ‘Expense / Budget Summary’!$A$11:$A$17’.
In the next input box, enter the range containing the category list. In our case, it is 'Expense / Budget Summary'!$A$11:$A$17’.
  • Check the box next to ‘Show validation help text’ and in the input box below it, type the text ‘Please select a category from the available list’.
‘Please select a category from the available list’.
  • Click Save.

You will now notice all the cells in the Category column now have dropdown lists.

all the cells in the Category column now have dropdown lists.

When you click on this list, you will find all the categories that you have in the Expenses by Category table (of the Summary sheet).

categories

Repeat this for the cells of column K, making sure you obtain the income categories from the range ‘Expense / Budget Summary’!$F$11:$F$17’.

Repeat this for the cells of column K

At this point you can start entering some rough sample transactions into the sheet, to test out if all the formulae are working fine so far.

sample transactions

Here’s how the Expense / Budget Summary sheet updated to our sample transactions:

Expense / Budget Summary sheet

Creating the Expense by Category Pie Chart

Let’s now create the Expense by Category Pie chart in the ‘Expense / Budget Summary’ sheet. For this, follow the steps outlined below:

  • Select the range A10:C17.
Select the range A10:C17.
  • Navigate to Insert->Chart
Insert->Chart
  • You should see the Chart editor sidebar on the right. If you don’t see it, simply double-click on the blank area of the chart.
  • From the Chart editor, under the Setup tab, click on the dropdown under Chart type.
click on the dropdown under Chart type.
  • Click on the 3D Pie chart.
3D Pie chart.
  • Click on the box under the Value category of the Setup tab, and select Spent (instead of Planned).
select Spent
  • Double click on the chart title and change it to ‘Expense by Category’.
‘Expense by Category’.
  • Finally, resize and move the chart as you see fit.
resize and move the chart as you see fit.

Creating the Cash Flow Chart

Next, let’s create the Cash flow chart. For this, follow the steps outlined below:

  • Select the range E6:F7.
Select the range E6:F7.
  • Navigate to Insert->Chart.
  • From the Chart editor, under the Setup tab, click on the dropdown under Chart type.
  • Click on the Bar chart.
Bar chart
  • Click on the Customize tab and check the box next to 3D (in the Chart Style category).
Click on the Customize tab and check the box next to 3D (in the Chart Style category).
  • Double click on the second bar (the one that corresponds to the ‘Spent’ amount).
second bar
  • Under Format data point, select dropdown below Fill color and select the color #EA$335 (Shown below).
color #EA$335
  • Click on the chart title and press the Delete key to remove it.
  • Finally, resize and move the chart as you see fit.
resize and move the chart as you see fit.

Protecting Cells from Accidental Changes

Finally, we want to make sure that certain cells in the expense tracker (for example, those with formulas) are protected from accidental changes. For this, follow the steps below:

  • We basically want to protect the Expense / Budget Summary sheet, so right click on this sheet’s tab.
  • Select ‘Protect Sheet’.

Select ‘Protect Sheet’. 

  • You should now see the ‘Protected sheets and ranges’ sidebar on the right.
  • Check the box next to ‘Except certain cells’.
  • In the input box that appears under this checkbox, type the range of cells that you don’t want to protect, i.e., B11:B17.
B11:B17.
  • Click on the ‘Add another range’ button.
  • In the new input box, enter the range G11:G17.
  • Click on the ‘Add another range’ button again.
  • In the new input box, enter the range C3.
protected sheets and ranges info
  • If you want to be able to edit any other cells, you can specify them in the same way.
  • Once you’re done, click Ok and then the Set Permissions button.
  • Select the option ‘Show a warning when editing this range’.
Select the option ‘Show a warning when editing this range’.
  • Click Done.

Your whole sheet is now protected except for the cells B11:B17 , G11:G17 and C3, where the user is allowed to provide inputs.

Related: How to Protect Cells in Google Sheets

Conclusion

In this tutorial, we showed you two ways to create and use a Google Sheets Expense Tracker.

The first method uses the free Google Sheets Monthly Budget from the Template gallery, while the second method involves creating the entire expense tracker from scratch, so that you can customize it to your own needs.

If you don’t want to go through any of this trouble, you could simply download our ready-made expense tracker template below.

Click here to download Spreadsheet Point’s free Google Sheets expense tracker template.

NOTE: Click on File > Make a Copy to be able to edit your template. DO NOT request edit access!

We hope this tutorial was helpful.

Related: Free Google Sheets Ledger Template [and How to Use]

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.