3 Google Sheets Tricks for Better Financial Insights

If you want to be a Google Sheets whiz, Ben Collins’ website is a must read.

Ben is a spreadsheet developer, data consultant, and data analytics instructor. He’s a Google Sheets, Apps Script, and Data Studio guru.

He recently published an article on 10 Techniques to Use When Building Budget Templates in Google Sheets. Here we’ve compiled a few of our favorite tips from Ben’s website that help you customize your finance management Google Sheet.

Using QUERY to Filter Your Spending By Date

If a pivot table of monthly spending seems boring and you want a more detailed view of your spending by category for a specific date range this quick tip is for you.

In this example, we’ll summarize transactions by category for the month of November. A few simple steps will help you get this set up in your own Tiller spreadsheet.

1. Create a new sheet using the plus sign in the lower left corner and give it a name. Here we’ll call it Spending Query.
2. In column A create two labels for Start Date and End Date in rows 1 and 2.
3. In column B enter the start and end date for November, or whatever month you want to query.
4. In cell A4 (we just want to give the start and end date setting a little space) paste the following formula (or you can copy it from this example sheet).

=QUERY(Transactions!A:D,”select C,SUM(D) WHERE A >= date'”&TEXT(DATEVALUE(B1),”yyyy-mm-dd”)&”‘AND A <= date'”&TEXT(DATEVALUE(B2),”yyyy-mm-dd”)&”‘GROUP BY C”)

From there you should see a list of your categories and the sum of your spending for the specified date range.

The magic that Ben teaches us in his post is that dates in Google Sheets are stored as serial numbers, but the QUERY function requires the date in yyyy-mm-dd to work.
Ben’s solution is to convert date values (“10/1/2017” in this example) to text. That’s what the =TEXT(DATEVALUE(“10/1/2017″),”yyyy-mm-dd”) portion of the query is doing for us that makes this all possible.

Using ArrayFormula To Save You Time

Ben’s post provides an in-depth overview of ArrayFormula and how they work that makes them much easier to understand.

In short, ArrayFormula lets you enter a formula in one cell that will then work across an entire row or down an entire column so there’s no longer a need to fill formulas down or across.

It makes your data dynamic because it updates for you automatically as new rows are added.

In this example, we’ll show you how you can pull in your categories list using an ArrayForumla and then get the sum of your financial transactions using that category in a few easy steps.

1. Create a new sheet using the plus sign in the lower left corner and give it a name. Here we’ll call it Category Arrays.
2. In cell A1 paste in the following ArrayFormula to grab your list of categories and have it dynamically update as you add new categories: =Arrayformula(Categories!B:B)
3. In cell B1 paste in the following ArrayFormula to calculate the total amount for all transactions using that category. =arrayformula(if(A1:A=”Category”, “Sum”, if(A1:A<>””,sumif(Transactions!C1:C,A1:A,Transactions!D1:D),””)))

From there you’ll see the sum of all transactions using each of your categories and this will update dynamically as you add new categories and as new transactions are categorized

Using a Sparkline to Visualize Your Budget Plan

Your sheets don’t have to be just numbers and text. Graphs and charts can help you visualize your budget. Use these steps to review your planned budget for each category and how each compares to your planned income.

1. Create a new sheet using the plus sign in the lower left corner and give it a name. Here we’ll call it Budget Sparkline.
2. In Cell A1 create a label called “Planned Income”
3. In Cell B1 type = and then navigate to your Budget sheet and select cell E8 to pull in your planned income.
4. In cell A2 enter the following ArrayFormula to pull in your list of categories. =Arrayformula(Categories!B:B)
5. In cell B2 enter the following ArrayFormula to pull in your budget plan for each of those categories. =Arrayformula(Categories!E:E)
6. In Cell C3 paste in the following formula to create the sparklines =SPARKLINE(B3,{“charttype”,”bar”;”max”,$B$1})

Now you should be able to visually compare how the planned budget amount for all your categories stack up against your planned income for the month. Which budget item is hogging your income?

You can learn everything you’d ever want to know about sparklines in Ben’s very detailed post on how to use the SPARKLINE formula.

As always please let us know if you have any questions with any of these tips.