Group report or 50-30-20 budgeting advice

Google Sheets
1

This has been asked before without an easy answer, but not recently. I am trying to implement a 50-30-20 budget and I started by creating groups of those types: Needs - Wants - Debt/Savings. But I can’t figure out how to create an expense report that includes the group. I have added a Group Column to the left of accounts on the transaction page.
Thanks for your help.

2

One quick and dirty way to do this would be tags. And then you could use Transaction Tracker for reports. But I suspect there are better, more solid ways to get what you are after.

3

Thank you. This has been the previous response. I’m just curious what the purpose of groups are if you can’t do a report from them??

4

Hi Wendy,

You could create a report using a Google Sheets Pivot Table using the Transactions as the source table. Are you familiar with using Pivot Tables in Google Sheets?

1 Like
5

Did you see the new tab report?

6

No, but I can google it!

7

Thank you for your answer.

8

Hi Wendy,

Here are a few steps to see what pivot tables can do if you want to pursue them further:

  1. In your Tiller Sheet, select the Transactions tab.
  2. Click “Insert” in the Menu Bar and select PivotTable.
  3. In the Create PivotTable dialog box, select the range of cells that you want to include in the pivot table and click the “New Sheet” button. The range should include all of the columns that you want to analyze in the pivot table. I usually just use the default prefilled range of the entire sheet. (Except I don’t add rows to the range just put the columns - Transactions!A:T for example; instead of Transactions!A1:T2000. Then you don’t have to keep updating the row number as transactions are added.)
  4. Click on the “Create” button.
  5. The pivot table will be created in a new tab in your Tiller Sheet.
  6. In the PivotTable Fields pane, you can drag the different columns from the Data area to the Rows, Columns, and Values areas. This will determine how the data is displayed in the pivot table.
  7. For example, you can drag the Category column to the Rows area to see the total amount spent in each category.
  8. You can also drag the Month column to the Columns area to see the total amount spent in each category by month.
  9. You can continue to add and remove columns from the Rows, Columns, and Values areas to customize the pivot table to your needs.
  • You can use the Filters pane to filter the data in the pivot table. This can be helpful if you only want to see data for a specific time period or category.
  • You can use the Sort button to sort the data in the pivot table. This can be helpful if you want to see the data in a specific order.

You can rename the tab to something more meaningfull once you have tweaked it to your needs. It will automatically update as transactions are added. One of the features I like the most about Pivot Tables is that you can click on any total amount in the table and it will create a separate tab showing all the individual transactions that made up that particular total.

Here’s a link to a much more detailed example from Heather via the Tiller Help Center:

1 Like
9

Just to add on to what Fred mentioned with Pivot tables, i believe you could very easily see this information with pivot tables with the group column in your transactions sheet, if you have these set as your main groups in your category sheet and you dont already then you could use this formula will fill in the group based on the category you select automatically.

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D,Categories!A:B,2,FALSE),IFERROR(1/0)))

This formula assumes your category column is in column d in your transactions sheet.

You could also theoretically add a column to your categories sheet just for this such as “50/30/20” and have each category assigned as the wants needs savings/debt and reference this column instead in the categories sheet if you wanted other groups for the other templates.

The pivot tables would then be able to sum this information up with a little bit of picking the correct column.

Adding these columns to your transactions sheet should make the pivot tables work for what it sounds like you are looking for.