10 techniques to use when building budget templates in Google Sheets

It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.

The dashboards available through online banking sites are pretty rudimentary and don’t give me 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.

Credit Card budget template with Tiller

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 techniques 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. They are:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

10 techniques to build budget templates with Google Sheets

Let’s dive into these 10 techniques in detail:

1. Why are you doing this?

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 definitely want to use in your budget templates, 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).

SUMIF allows you to sum your data, but only when a specific criteria 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:

SUMIF formula for budget templates

Read more about SUMIF here in the Google Documentation.

The SUMIFS 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. Read more here.

4. Leverage power of more advanced formulas in your budget templates

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: 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")

Read more about the VLOOKUP in the Google documentation here.

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)

Read more about the FILTER function with examples here.

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, very, very, very, very (ok that’s enough verys) powerful function. And also quite challenging at first.

Start with this comprehensive guide to the QUERY 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”:

Example comment for budget templates

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. Get all your financial data into your 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 a killer service 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.

Tiller Home page

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:

Tiller daily email

I collaborated with Tiller earlier this year to create a credit card spending template, which is available for you to use (Tiller account required):

Credit Card budget template with Tiller

Get your own copy of this credit card tracker
Click here to start a free trial >>

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.

Budget template overspend chart

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.

Budget template overspend data table

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 )

and

=IF( C21 > $E$3, C21 - $E$3, 0 )

9. Build drop-down menus to show different categories in your budget templates

Data validation dropdown menu for budget templates

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.

dynamic chart

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 this topic 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:

Credit Card budget template text formulas

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 in this in-depth post.

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.

2 thoughts on “10 techniques to use when building budget templates in Google Sheets”

Leave a Reply

Your email address will not be published. Required fields are marked *