Google Sheets Expense Tracker (with Template) (2024)

I rely on my expense tracker in Google Sheets. It’s how I evaluate my finances. That’s also why I made a Google Sheets expense tracker template. It helps track financial goals, monthly bills, and everyday spending. Below, I’ll share my work.

Expense Tracker Template: Download Here

Expense Tracker Template

I consider a budget absolutely essential to hitting money goals. And I don’t like having to pay for fancy paid apps to track expenses. Instead, I prefer simple spreadsheet applications like Google Sheets.

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. You don’t need to request edit access!

Expense Tracker Tutorial

In this tutorial, I’ll show you two ways to use Google Sheets to track your expenses. The first part of my guide talks about my budgeting template. I’ll show you how to use it as is and how to customize it for your specific situation. After that, I’ll show you how to create your own Google Sheets expense tracker. By the end, you will know how to design an customize a spreadsheet according to your unique requirements.

That means you’ll be able to build your own project expense tracker, personal finance calculator, or budgeting sheet from scratch. Otherwise, you can download my ready-made template and start tracking immediately.

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 visualize expenditures by category, so you know which are frivolous and which are necessary.

Note that this is a bit different from an expense report. I use my expense tracker for personal use, too. This tool can also help track daily spending and, when modified, your net worth.

Why Should You Track Expenses?

To have real control over your money for retirement, 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, much like a rental properties spreadsheet would too..

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

Note that I also wrote about four Google Sheets budgeting templates. They’re already built, too.

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.

Thebill 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’.

• This opens the Google Sheets template gallery.
• Under the Personal section, select the ‘Monthly Budget’ thumbnail.
• This opens a new worksheet with the Monthly Budgettemplate.

Here’s how the template looks:

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

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.

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.

In the Summary tab, you will find the following:

• A slot to enter your Starting balancefor the month (The balance amount of money you have at the start of the month).

• 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.

• Two small charts (or sparklines) displaying your total planned and actual spending / savings.

• Category-wise summary of your income for the month.

• Category-wise summary of your expenditure for the month.

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.

• Select the Summary tab. You can now customize the categories in columns B and H according to your requirements.

• 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.

• Rename the new file with the name of the month that you want to track your budget for, e.g.: Jan 2021.

See Also

How to Keep a Cell Fixed in Excel Formula (4 Easy Ways) - ExcelDemyGenshin Impact Xingqiu Build Guide - Best Weapons, Artifacts, and Teams - Genshin Impact Guide - IGNHow to Change Theme Colors in Excel: A Step-by-Step GuideHow to Pull Data From Another Sheet in Excel [3 Easy Ways]

• Once you have a fresh copy of your budget for your required month, you can start budgeting.

• Enter your Starting Balanceor budget for the month in cell L8.

• 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.

• 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).

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

The Monthly Budgettemplate 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 from Scratch

Creating your own free expense tracker on Google Sheets is not as complex as you might think. That’s what I’m going to show you next. Basically, you should understand each of the parts of the expense tracker first. Then, you’ll put those pieces together to make one 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! I provided my expense tracker template above.

Project: Expense Tracker from Scratch

Let’s start with navigation. You’ll want to rename each of the tabs.

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.

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.

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.

Format the cells of column D to the ‘Currency’ format. For this, select the cells and navigate to 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 Summarysheet.

Building the Outline for the Expense / Budget Summary Sheet

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

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 theExpense by Categorytable (A10:D18), Income by Categorytable(F10:I18), Cash flowtable (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.

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:

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.

Now let’s enter the formulas for the Cash Flowtable (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"))

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 2021in 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 Outsection.

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:

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:

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:

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. Below is a screenshot how it should look at this point. Remember, this is where you break down spending by type of expenses:

Repeat the same process to complete the Income by Categorytable. 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 yourIncome by Categorytable should look at this point:

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

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

Advanced Functions of the Expense Tracker

There are many more things you can do with a well-built expense tracker in Google Sheets. Here’s what to include for more advanced functionality.

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

• 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’.

• 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.

• 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’.

• 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’.

• Click Save.

You will now notice 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 theExpenses by Categorytable (of the Summary sheet).

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’.

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.

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

Create an Expense by Category Pie Chart

Let’s now create theExpense by CategoryPie chartin the ‘Expense / Budget Summary’sheet.For this, follow the steps outlined below:

• Select the range A10:C17.

• Navigate to 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 the3D Pie chart.

• Click on the box under the Value category of the Setup tab, and select Spent (instead of Planned).

• Double click on the chart title and change it to ‘Expense by Category’.

• Finally, resize and move the chart as you see fit.

Create a Cash Flow Chart

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

• Select the range E6:F7.

• Navigate to Insert->Chart.
• From theChart editor, under the Setup tab, click on the dropdown under Chart type.
• Click on theBar chart.

• Click on the Customize tab and check the box next to 3D (in theChart Stylecategory).

• Double click on the second bar (the one that corresponds to the ‘Spent’ amount).

• Under Format data point, select dropdown belowFill colorand select the color #EA$335 (Shown below).

• Click on the chart title and press the Delete key to remove it.
• Finally, resize and move the chart as you see fit.

Protect 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’.

• 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.

• 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.

• 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 Permissionsbutton.
• 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

Common Questions about Expense Tracking with a Spreadsheet

Here are some of the most common questions I hear about using an expense tracker spreadsheet template. If I missed anything, please ask in the comments!

How do I track business expenses?

While there are many useful tools available for expense tracking, I like using a spreadsheet because it’s free and private. My template tracks money in, money out, and categories for spending. I can also use it to create dashboards that show everything with visuals.

Should I use an expense tracker app?

There are tons of reasons to consider using expense tracker apps. I prefer using spreadsheet templates to track expenses because I don’t have to share all my private data (and receipts) with a company that might try to monetize my data. Google Sheets has a mobile app, and I can access my expense tracker from that.

Can I connect my bank accounts to the expense tracking spreadsheet?

No, you cannot connect bank accounts directly to the app. However, you could build a script to automatically import your transactions into the tracker for Google Sheets or Excel. That way, you could download transactions from your business credit card, debit account, and other common payment methods and automatically import them into the tracker.

How does this stack up against Quickbooks?

Hey, I love Quickbooks. It’s made for expense tracking. It also costs money. This Google Sheets and Excel expense tracker serves as a a free alternative. Unlike this spreadsheet, Quickbooks does connect to your business accounts and can help you save time when tracking business expenses.

How do I track tax deductions?

As long as you keep your receipts and log your transactions, this expense tracker will help you determine where you can claim a tax deduction. Remember that the IRS covers what you can and can’t deduct as a business. They remind business owners that deductible expenses must be “ordinary and necessary”.

How do I track mileage?

While you can create a new tab in this expense tracker to log your miles, sometimes it makes sense to create a seprate workbook. I made this mileage log spreadsheet template you can use for free. Remember you can link two or more workbooks together with IMPORTRANGE. You don’t need to build a script for integration. It’s already built into Google Sheets.

Conclusion

In this tutorial, I showed you two ways to create and use a Google Sheets Expense Tracker. The first method uses the free Google Sheets Monthly Budget Templatefrom 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. That makes this one of the most powerful free Google Sheets budget templates available.

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.

I hope this tutorial was helpful.

Related:

• Free Google Sheets Ledger Template [and How to Use]
• Free Google Sheets Profit and Loss Template

References

1. Internal Revenue Service. Publication 535 (2023), Business Expenses [Internet]. IRS; [cited 2024 Jan 23]. Available from: https://www.irs.gov/publications/p535
2. Internal Revenue Service. Credits & Deductions for Individuals [Internet]. IRS; [cited 2024 Jan 23]. Available from: https://www.irs.gov/credits-deductions-for-individuals

3. Google. Apps Script [Internet]. Google; [cited 2024 Jan 18]. Available from: https://www.google.com/script/start/