Customize Your Household Budget With A Monthly Spending By Group Table

Now that Tiller’s budget spreadsheets have greater flexibility for organizing your categories by group you might wonder how much you’re spending in each group each month. The Budget sheet won’t really give you this data as it’s focused on the current budget period and the total for your prior period. What if you want to look at your spending per group for the entire year?!

It’s easy to accomplish this customized report using a pivot table and a little bit of simple arrayformula magic.

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:

1. Open your Categories sheet and sort the sheet from A>Z (click on the small drop down arrow on the category column header to reveal a menu so you can sort). Sorting by the Categories column is important in ensuring that the group data is matched correctly for the assigned category on the Transactions sheet.
2. Navigate to your Transactions sheet and right click on the column letter for the “Note” column, and click insert 1 right so that you have a new column to the right of the Note column.
3. Paste this formula into the header (row 1) for this new column:

=ARRAYFORMULA(if(C:C="Category", "Group", if(C:C="", "",Lookup(C:C,Categories!$B$2:$B,Categories!$A$2:$A))))

Now you should see the group populate for each categorized transactions. If you notice that a group doesn’t match for a category it’s likely that the Categories sheet isn’t sorted by the Categories column.

If you get any errors at this point, make sure that your Category column is in column C or adjust the formula (in three places) where it indicates C:C for the category column to use the correct column letter for your categories.

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.

1. Select all the data in your Transactions sheet using the square in the upper left of the sheet.
2. Open the “Data” menu and choose “Pivot table,” which will create and open a new sheet where you can customize the spending pivot table.
3. Choose “Group” for the rows.
4. Choose “Month” for the columns.
5. 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.

We hope these tips are helpful for providing a custom view of your spending data. Please let us know if you have any questions, suggestions or difficulty with creating these pivot tables. Email support@tillerhq.com