Docs: Category Tracker

About

Dig deeper into your spending for a selected category by reviewing the Transaction details over a configurable time period. See the sum of spending for all categories for a given date range.

How to Install

Quickly add a Category Tracker sheet to any Tiller-powered Google Sheet using the Tiller Community Solutions add-on.

  1. Install or launch the Tiller Community Solutions add-on
  2. Open the add-on and choose "Analysis” from the tags dropdown on the Explore tab
  3. Click on Category Tracker
  4. Choose “Add to spreadsheet”
  5. Review your spending with the Category Tracker

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Get Help > Spreadsheet templates category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

Updates

  • April 18, 2022: Added a pie chart visualization to this popular template + a period summary in the first few columns.

@heather Any chance that a date column could be added to to this between columns C & D? When I run the report, it would be more helpful for it to also pull in the date of the transaction rather than just the transaction name and the amount.

2 Likes

Hi @cedrus,

That’s a great idea. Do you mind adding a feature request for this?

Heather

1 Like

Heather asked me to tag @jono

I found a problem with the formula in the Category Tracker sheet.

The category summary for the period has in incorrect formula. Cell A7 should be =iferror(QUERY($G$7:$I$200,“SELECT G, H WHERE I<>‘Hide’ AND G<>’’ ORDER BY G ASC LABEL G ‘’, H ‘’”, -1),"") It is currently starting on $G$8, so the first category is skipped from the period summary.

Thanks

Brent

1 Like

BTW – I had just installed this solution, so it should be the latest version.

1 Like

Hi @brenttempleton,
Thanks for catching this error. You are correct that the formula had the wrong cell reference.

I have fixed the sheet to Version 2.02, which will correct this error. It is available now.

Jon

Thanks for reaching out on this @brenttempleton! You can get the latest version via the Tiller Labs add-on under Managed Solutions.

Heather

@cedrus

Here is a workaround…sort the transactions tab on category with a secondary sort on date.

Blake

Hi there,

I’m using the category tracker but have noticed that expenses that don’t have a “-” amount show as “$0.00”.

So I have purchased some clothes and returned them for a refund, however the refund just appears as “$0.00” in the category list of the category tracker which gives me a false total for my clothing budget.

Brian

@baforshaw

I was not aware of this problem with Category Tracker but I have encountered problems elsewhere when recording negative expenses.

Workaround - Lets say you have a $100 purchase followed by a $10 return resulting in a $10 refund. I go back and split the $100 transaction into a $90 and $10 piece and code the $10 piece as a transfer and then code the $10 return/refund as a transfer too. The two $10 transactions net to zero and you are left with one $90 transaction which is what you want.

Blake

Thanks Blake. Not a bad work around, would like to find a more efficient way to report on negative expenses so that I can actually see what is a refund as opposed to viewing it as a transfer. Will give it a go and see how it goes.

Thanks
BF

@baforshaw

You have lots of options. Set up a separate transfer category for each specific vendor you want to track. Put the refund to a special refund income category…thus $100 of expense and $10 of refund. It all depends on what you want to see and what works for you. You are only limited by your imagination.

Cheers,

Blake

Thanks Blake, that’s what I love about this spreadsheet, so many ways to skin a cat!

I actually went back through my transactions again and found why it was showing $0.00. It’s actually doing what it should!

Thanks again for you help though.

BF

Hi, I setup the category tracker and it seems to be breaking down expenses, but not income - is it supposed to be working like this or am I doing something wrong?

@wyliej30

It does both. Maybe delete it and then reinstall it and see if that fixes it.

Blake

@Blake just tried that and still not working.

@wyliej30

Try and restore that sheet under Tiller Labs, Manage Solutions. If that does not work, then you need someone smarter than dumb old me.

Thanks,

Bake

@wyliej30,

You mentioned it’s not showing income categories. Are you referring to the left side of the Category Tracker where it sums all the categories for the selected time period or the dropdown menu is not showing any of your income categories? A few things to double check

  • The configured time range includes income transactions
  • The income categories are not marked as “hide”

@heather,
No, I’m referring to the right side where it shows each of the transactions for a category in a period. For me it works for expenses, but not income. I confirmed that there is income for the period I’m looking at (Jan 2020) and the income categories are not marked as “hide”.
Any other suggestions?

@wyliej30

So, when you click on cell E3, you only see expense categories but not income categories. Do your choices mirror your categories on the categories tab exactly? Do your choices go from A to Z or stop somewhere in the middle? Unhide the columns to the right. Is column J a list of your categories starting at cell J1 exactly as shown on your categories tab?

Blake