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
Table of Contents
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.
NOTE: Click on File > Make a Copy to be able to edit your template. You don’t need to request edit access!
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.
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.
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.
Let us look at two ways in which you can get an expense tracker for absolutely no cost, using just Google Sheets:
Note that I also wrote about four Google Sheets budgeting templates. They’re already built, too.
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:
Here’s how the template looks:
You can now start using this template to track your income and expenses.
When you open the Monthly Budget template, you will notice that the workbook consists of two tabs:
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:
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:
To use the Monthly Budget template, follow the steps outlined below:
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 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 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.
Let’s start with navigation. You’ll want to rename each of the 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.
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.
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 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 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.
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:
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 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"))
The same explanation applies to the formula in cell F7.
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:
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:
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 (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:
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 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:
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:
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:
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.
You can add cell validations to your budget tracker to accomplish the following:
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:
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:
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 the Expenses by Category table (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:
Let’s now create the Expense by Category Pie chart in the ‘Expense / Budget Summary’ sheet. For this, follow the steps outlined below:
Next, let’s create the Cash flow chart. For this, follow the steps outlined below:
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:
Your whole sheet is now protected except for the cells B11:B17 , G11:G17 and C3, where the user is allowed to provide inputs.
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!
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.
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.
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.
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.
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”.
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.
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 Template 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. 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.
I hope this tutorial was helpful.
Related:
