It probably won’t surprise you to hear that I use a Google Sheets budget template to track my finances, both incomings and outgoing, at home and for my business.
The dashboards available through online banking sites are pretty rudimentary. They don’t give much insight into what’s happening with my finances, particularly over longer time frames.
I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable. It’s easy to use and I can share any spending or budget templates easily with my wife.
I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.
What I will do in this post however, is show you some useful tips in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals.
Here’s a summary of what we’ll cover for building a Google Sheets budget template:
- Before you build, consider your why
- Invest time little and often
- Get basic formulas dialed
- Leverage power of more advanced formulas
- Don’t reinvent the wheel! Google has specific financial formulas
- Use comments to record specific details
- Import your financial data into Google Sheets with Tiller
- Set budgets and highlight spend over the budgeted amount
- Build drop-down menus to show different categories in your reports
- Use words!
10 tips to build a Google Sheets budget template
Here are 10 tips for creating a Google Sheets budget template:
1. Before you build, consider your why
Before diving into the thick of it, and getting lost in your transactions, fancy formulas or complex charts, it’s worth spending some time thinking about why you’re doing this.
Putting aside spreadsheets, and tracking, and expenses for a moment, ask yourself what it is you’re trying to do (for example, maybe you want to pay off a student loan as quickly as possible, or reduce your credit card spending each month because it’s too high! ?).
What problem are you trying to solve?
Then, set a goal!
Work out what would help you achieve that goal (for example being able to see how much, and where you spend your money each month). Then build yourself a Google Sheet template that will do that for you (for example a Sheet showing your credit card transactions broken down into different categories).
Don’t try to create one giant spreadsheet to track everything (at least not initially). You’ll be better served by creating a single, focused Google Sheet that solves one problem for you.
Here are some ideas: credit card spending/budget templates, student loan tracker, mortgage payment tracker, stock portfolio tracker, household budget templates, childcare costs,….etc.
2. Invest time, little and often
Ok, so building a template is going to take some time up front. Anything from an hour for a basic template, up to perhaps a day or two to build something complex.
However, once you have your template, it’ll then only take a few minutes each day to keep on top of things. And that’s the beauty of this whole process.
Quickly update your transactions, tweak your categories, modify a chart and, most importantly of all, understand and glean insights about your finances.
The upfront effort pays dividends down the line when you have a clear conscience knowing what you’re spending and how you compare to your budget.
3. Get basic formulas dialed
It goes without saying that you’ll use the SUM formula in your budget templates, and I won’t insult your intelligence by covering that.
You’ll probably also find yourself using AVERAGE, MIN and MAX, which are again, easy to understand.
Another one you’ll probably want to use in your Google Sheets budget template, and one that often trips people up, is the Percentage change formula, so let’s run through a quick example of that. The formula is:
(New amount - Original amount) / Original Amount
Take your new value (e.g. this month) and minus your original value (e.g. last month). Take this answer (the difference between New and Original) and divide it by the original amount (e.g. last month).
There are other, super-useful, and relatively basic formulas you may not have used before: SUMIF and SUMIFS (and corresponding AVERAGEIF and AVERAGEIFS, same principle).
The SUMIF function allows you to sum your data, but only when a specific criterion is met. For example, maybe you want to sum everything in category X, or month Y, or on credit card Z. SUMIF (or SUMIFS) is the formula you’ll want to use.
It takes the form:
=SUMIF( range to test, test criteria, range of values to sum)
So a real example would look like this, where we test column A for “Coffee/Bites” and only add values from B to the total for rows that match:
The SUMIFS function is slightly different, because you specify the sum range (i.e. the range of values you want to sum) first, and then all your different conditions.
4. Leverage power of more advanced formulas in your Google Sheets budget template
There are a few other key formulas that are worth investing time in. You’ll be able to manipulate your data more easily with a mastery of these formulas and display it in new ways for a more sophisticated understanding of your finances.
I’d recommend learning the following formulas:
VLOOKUP
The VLOOKUP function is arguably the most famous advanced formula in the spreadsheet world. Give it a search term and it will search for this term in a separate table and return specific data if it finds the search term. It’s really useful for building dynamic tables and charts (see section 9 below).
The VLOOKUP can also be wrapped in an IFERROR function to handle any errors (if the search term is not found, you can set a custom message to display e.g. “No data found for this month”):
=IFERROR(VLOOKUP($A10,$AF:$AG,2,false),"No data found for this month")
FILTER
The FILTER function allows you to show a subset of data that passes some criteria. For example, you might want to filter so you only see transactions over $250, when you want to easily check all your big transactions. Or maybe filter on all transactions from a certain vendor, to see how much you’ve spent with them.
It’s relatively simple to implement:
=FILTER( range, conditions we're testing for)
In practice, if we had our data in the range A1:D100, with values in column D, and we wanted to see only transactions over $250, our formula would be:
=FILTER( A1:D100, D1:D100 > 250)
QUERY
If you thought the filter function was useful, wait until you learn how to wield the QUERY function. It’s arguably the most powerful function in Google Sheets. Period.
It can aggregate your data, summarize your data, filter your data, sort your data, you name it and it can probably do it. However it’s not an easy formula to learn:
=QUERY($A$1:$B,"select A, sum(B) where B > 0 group by A order by A desc label A 'Month', sum(B) 'Payments'")
This example takes data in columns A and B and groups on the month in column A (so all Jan payments get added together, all Feb payments get added together etc.). It only includes payments greater than 0. It sorts the months in order and relabels the columns of the new Query table as ‘Month’ and ‘Payments’.
As you can see, it’s a very powerful function.
5. Don’t reinvent the wheel! Google has specific financial formulas
Google Sheets has over 40 built-in financial formulas, for solving very specific needs, everything from depreciation to treasury yields.
So check out the full list here, before doing legwork you don’t need to do.
Let’s take a quick look at one of the more general formulas, FV, which calculates the future value based on constant-amount periodic payments and a constant interest rate.
The general formula is:
= FV(rate, number_of_periods, payment_amount, present_value, [end_or_beginning])
I recently used this formula to calculate the future value of a lump sum, invested at a 5% return for 30 years. The idea was to show how the amount I’d spent over my budget could have been invested instead, and show what it might have been worth in 30 years time. The lost opportunity cost.
So it’s 5% interest, divided by 12 to get a monthly rate. Next the number of monthly periods, 360. The payment amount is 0, since I don’t want to include any payments. The present value is the spend this month, less my target budget (i.e. the amount over budget). The last argument, [end_or_beginning]
, is optional and relates to whether the payments are made at the beginning or end of the month, so not relevant in this case.
=FV(0.05/12,360,,-(C26 - $E$3))
Read more about the FV function in the docs here.
6. Use comments to record specific details
Often I find myself wanting to add a note to a transaction, because something about it is different than normal and I know I’ll forget why if I don’t write it down. However, I don’t really want to have to add extra rows or columns, or rebuild my budget templates just to fit more text cells in.
So I add comments to record specific details about a transaction.
You can add comments to cells in spreadsheets by right clicking and selecting “Insert comment”:
Best of all, you can tag others in a comment to notify them, by typing “@” + their email address. For example you might tag an unusual looking transaction for your accountant to review.
If you have a lot of context to record, or find yourself wanting to add notes every month, then a dedicated notes column is the way to go. But for occasional notes, comments are the way to go. They’re low profile and won’t affect any calculations.
7. Import your financial data into Google Sheets with Tiller
You’d imagine that building all these templates in Google Sheets would require us to download our data from the financial vendors, as a CSV file we could import into Google Sheets.
Not so!
I use an amazing tool called Tiller to bring all my financial data into my Google Sheets, so I can analyze it and build templates exactly as I want.
It’s my secret weapon for building financial templates.
You can use Tiller to bring bank account data, credit card data, or other financial data into your Google Sheets. It’s super easy to setup and works flawlessly for me.
The data is updated every morning when you’ll also receive an email summary update:
It costs $79/year, which is tremendous value since you’re getting a fully customizable, automated personal finance tool.
8. Set budgets and highlight spend over the budgeted amount
The following chart shows any spend over $2,000 in a month with a red color, to differentiate it and bring it to the attention of the viewer. It’s a classic technique in data visualization, using a pre-attentive attribute to convey information more effectively.
It’s easy to do in Google Sheets. You can use simple formulas to separate amounts above the threshold and then turn your regular column chart into a stacked column chart.
The table is simply two columns. The first shows the value or the threshold, whichever is smaller. The second shows a 0, unless the value is over the threshold, in which case it displays the difference.
Assuming my threshold value of $2,000 is in cell E3, and my column of spend values is column C, the formulas are:
=MIN( C21, $E$3 )
=IF( C21 > $E$3, C21 - $E$3, 0 )
9. Build drop-down menus to show different categories in your Google Sheets budget template
It’s really easy to add category menus in Google Sheets, using Data Validation to build dropdown lists. Then you can change what data is showing in your reports.
These are the steps to create a dynamic chart in your reports:
- Categorize transactions into meaningful groups (like Groceries, Utilities, Car payments, Mortgage, Discretionary, Restaurants etc.)
- Create a unique list of the category names
- Choose the cell where you want to add the menu, right click and choose
Data Validation...
- Choose “List from range” as the criteria
- Select the unique list of categories
- Use a lookup formula (VLOOKUP) with this data validation cell as the search term
- Use the dynamic data table for your chart
- Voila! Your chart will be dynamic too
I’ve written extensively about dynamic charts in this post, so I won’t repeat it here.
10. Use words!
Perhaps the most influential dashboard article I’ve ever read is this one from Google Analytics advocate Avinash Kaushik. The takeaway is to include more context and more text in our dashboards, so they reach their potential as decision-making tools, rather than pure “data pukes”.
I always keep this in mind when I’m producing dashboard reports.
For example, in the credit card template I’ve shown you, I start with a list of bullet points, which gives me a really good overview of the month:
Rather than forcing me to interpret numbers and guess what’s happening, I simply read. The words provide all the context so I know exactly what’s happening this month versus last and where I am spend-wise.
These formulas are not conceptually difficult, but they can be long and verbose when you have several options in an IF statement with a mix of text and formulas.
The way you create these formulas is to wrap your calculation formulas with the the TEXT formula, which formats your numbers correctly. Then you combine these with any static text using the ampersand, &.
For example, here’s one of these formulas:
="You've paid off "&text(E26,"$#,##0")&" this month, which is "&if(E26 > C26,"greater","less")&" than the spend of "&text(C26,"$#,##0")&" so far."
where E6 is the amount I’ve paid towards my credit card, and C26 is how much I’ve spent this month.
Read more about how to combine text and numbers.
As always, let me know your thoughts or questions in the comments!
Note 1: I’m not a financial expert and this post does not provide financial advice. It simply shows some techniques for working with and presenting data in Google Sheets.
Note 2: Some of the links in this post are affiliate links, meaning I’ll get a small commission if you click the link and subsequently signup to use that vendor’s service. I only do this for tools I use myself and wholeheartedly recommend.
This is a great article. Do you mind if we share it in our blog? Also, can you give me your expert opinion on our template? Here it is: http://www.codedesign.org/free-ecommerce-template-for-google-datastudio/. Thanks in advance.
Hey Bruno, sorry, must have missed this comment originally. Sure, feel free to share this article. Your template looks sweet, great work!
You are a life saver! Do you have any financial spreadsheets for real estate investments? Cash flow statements?
Unfortunately not! You might find some good examples in the Excel world though, that you could easily transfer across to Google Sheets.
Is there any way you could share a copy of your template? Not all the Tiller and such but more the orginization?
This is my personal finance manager GSheet.
http://www.andrewroberts.net/rose-money-manager/
Will certainly be taking a look at Tiller.
These are great tips, but I think you could’ve explained them a little better. Building templates isn’t that difficult, but there’s some important stuff that everyone should know about it, and all of it is not mentioned here.
Thanks for this. I’m starting to build a sheet out for me and my girlfriend. I’d like to turn it into an app eventually.
Hello, I am barely starting doing budgeting, and I have little income and little expenses. So I think I wouldn’t need to pay something like Tiller for my simple student life…
But I would have loved to have an insight into the spreadsheet, so as to build one myself…
However, if I understand well from other comments above, the only way to get the spreadsheet that you are referring to in this article is by signing up to Tiller.
Hi! Do you offer this template for use? I really like it and have been looking for something similar, since most of my sending is on my credit cards.
Hi Ben.
Do you have a premade budget sheet we could use? One that has some of the functionality you have shown here?
Maybe one we could start using right away?
Great tips regardless!
Thank You.