Note: these steps are for newer Tiller Google Sheets templates. If your Transactions sheet does not have the blue header row like the image below reach out to support@tillerhq.com for the steps.
Adding Group Data To Your Transactions
By default, the Transactions sheet doesn’t include the group that’s associated with your transaction. We can quickly get this data by inserting a column and adding an arrayformula to pull in the group that’s tied to the category. The arrayformula will also continue to populate the Group column as you categorize new transactions. Here are the quick steps:
- Open your Transactions sheet and right click on the Account column and choose insert 1 right so that you have a new column to the right of the Account column.
- Paste this formula into the header (row 1) for this new column:
=arrayformula(if(row(D1:D)=1,"Group",iferror(vlookup(D1:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))Now you should see the group populate for each categorized transaction.
If you get any errors at this point, make sure that your Category column is in column D or adjust the formula where it indicates D1:D for the category column to use the correct column letter for your categories.
Note: if you use the Transaction splitter, it will paste the group data as a value after it creates a split. Simply clear this manual data out after making splits to keep the arrayformula working.
Adding the Monthly Spend by Group Pivot Table
Now you’re ready to create your pivot table. Monthly spending pivot tables are a great way to visualize the total spend per group (and category) for all your Transactions sheet data. You can even add filters if you don’t want to see the spending totals for some groups. A more detailed overview of creating a pivot table is included in this help article, but we’ve included the basic steps below.
- Select all the data in your Transactions sheet using the square in the upper left of the sheet.
- Open the “Data” menu and choose “Pivot table,” which will create and open a new sheet where you can customize the spending pivot table.
- Choose “Group” for the rows.
- Choose “Month” for the columns.
- Choose “Amount” for the values.
Now you should see the sum of your spending for each group organized by month in the table. There is a grand total column at the far right of the table so you’ll get the total spend for all months included in the table.
Filtering the Data for a Customized View of Your Spending
From here you can add filters and customize this view based on what you’d like to see. If you only want to view spending groups, you might want to create a filter that hides the data for your income related transactions. You could add a filter for month and only look at data for this year if you have several years of data in your Transactions sheet.
You might see a column that doesn’t have a month assigned and a row that doesn’t have a group assigned. If so, it’s because there are blank rows in your Transactions sheet, which is completely normal. You can add a filter for Group and Month and hide the blank lines to make those disappear.
If you see any amount totals for a blank month it might be that you have added some manual transactions and the month data is not included. Check the month column in your Transactions sheet and update the month data. If you see amount totals that aren’t assigned to a category it means you have some un categorized transactions.
Review Spending By Group AND Category
If you add a second grouping for the “Row” data and include “Category” you’ll get a spending breakdown by category that rolls up into the associated group. This is a really great view if you want to see just how much you spent each month per category, but also want to see it side by side in the context of the associated group with the overall total for the group.
If you only want to see the grand totals, and don’t care about seeing it by month, simply remove the month data from the columns in the pivot table configuration sidebar to get the sum of all spending by category and group.
Add a Visual to Your Monthly Spending By Group Sheet
If you’d like to add a chart to help visualize the spending per group for all your Transaction data (that’s not filtered out) you can easily do this too. The easiest method to add a quick chart is to remove the category data from the rows (if you added it in the steps above) and make sure the month data is also removed from the columns. From here you can select the data starting with the first group and down to the last group and sum of spending for that group. Be sure to exclude the grand total data. Then click the “add a chart” button from the toolbar in your Google Sheet. (It might be hidden under the “more” menu.)
Clicking this button should add a chart, which you can further customize in the chart editor. The best option for this type of data view is a stacked bar chart.
