🏆 Categories By Month

[taylorvance]

Overview

Shows the total amount, for each of your categories, over any timeframe. Values are conditionally formatted relative to other months in that category, making it easy to identify lower and higher spending.

I wanted a way to see at a glance, with minimal input, how any category is trending. With this template, you can see ALL categories for ALL time.

Installation

Install the template from the Tiller Community Solutions Add-on .

Setup

Transactions sheet must be sorted reverse chronologically (as is default). Category columns will maintain the order from your Categories sheet. Hidden categories are excluded.

Usage

Simply select a timeframe using the RANGE dropdown.

Cells are conditionally formatted relative to visible cells in the same column. You may adjust the cutoffs using the “%” inputs at the top.

Permissions

You’re free to copy, use, and modify this template. I would appreciate an acknowledgement if you do so.

Notes

The formulas easily handle a matrix of 120 months (10 years) and 50 categories, pulling from a 10k-line transaction history.

FAQ

Q: I changed the background color of a percentile in the input area. Why isn’t that change reflected below?
A: The colors in the percentile input section are just for reference. Review Conditional Formatting rules to adjust colors.

[morgan]

This is very cool.

I’m someone who doesn’t really budget per category, just for monthly “spending” in general so my category budgets vs. actuals can be all over the place. This is a great tool for someone like me who doesn’t really know what amount to put per category and needs some data to find a target amount.

[isussman1228]

I’m often trying to tell if my budget forecast is aligned with my spending and I think this will be a good visual. Thanks for offering.

Edit: I’m a little confused. Why are so many of the cells blank or not color formatted? I’d think this would look more like a heat map with every cell having information. Maybe I’m getting the purpose a little wrong.

Thanks.

[taylorvance]

No color means it’s in the “normal range”. By default, between 25th and 75th percentile. That range can be configured at the top. Unfortunately I had to pick a number of discrete colors rather than use a continuous shaded scale. Thanks for checking it out!

[ChrisR]

This is great. Thank you. I’m using it to identify categories with sparse transactions. For example, multiple categories for bills seemed useful when I first set Tiller up, but I pay my phone bill only once per year, and the misc and services categories have few transactions, so I merged all three.

[taylorvance]

@ChrisR glad it’s useful! That’s one of the ways I’m using it too. Merging categories, seeing spending trends, determining reasonable budget ranges, finding surprising spikes or gaps that might need a cleanup.
I’m working on a more powerful version that lets you double-click into a cell to see all of the transactions in that category/month (using a pivot table).

[taylorvance]

@isussman1228 I “replied” before I figured out mentions. See my comment above. I wanted to make it more like a heatmap with gradient colors, but that would have required manually creating a conditional format rule for each of ~200 columns. This way requires just 6 rules (one for each color), applied to the full range of columns. I could be wrong about the limitations of Sheets so if you know a way I’m all ears. The complication was that each column’s colors are relative to other values ONLY in that respective column. I’m using a hidden staging area to store the percentiles, which the format rules reference.

[randy]

This is intuitive, easy-to-setup, insightful, and fun, @taylorvance. I particularly like what you’ve done to allow users to customize the color coding thresholds (based on some feedback in this thread). I appreciated that, right out of the box, the template flagged a car payment timing issue in my data.

Our Builder Rewards Program provides cash awards for new and broadly-useful template submissions. Tiller is excited to award you $100 for your Category X Month template.

Thanks for building and sharing, @taylorvance.

[randy]

If you the Categories By Month template, I’ve got some great news… it’s live in the Tiller Community Solutions add-on… so it’s never been easier to install, update, and experiment with @taylorvance’s template.

Thanks for sharing, @taylorvance.

[th5418]

I downloaded this plugin and tried various ranges but nothing seems to show up. Is there something else I need to do?

[taylorvance]

@th5418 This sometimes happens when there are no transactions in the given date range, though given your wide/recent range that’s unlikely. Can you reveal the hidden columns A-I and see if there are any errors there? That’s the staging area that pulls in your categories and the transactions for the selected range.

[th5418]

Yup! It has an error that says this

[mpresley410]

I am getting the same Value errors as th5418. When I expand my A-I columns I have an error in cell D14.

If I’m reading that correctly it seems like there is a problem in column 133, which would be ED. This sheet doesn’t have an ED column, it ends at DY. So, where should I be looking at to resolve the issue?

Thank you!

[adekunledauda]

Same error. There was no error before it was added to the community solution. Can you make installation available here instead of through the tiller solution add-in.

[taylorvance]

@th5418 @mpresley410 @adekunledauda Thanks all for the quick feedback. I’ll try to fix asap. I just reinstalled the template on my own spreadsheet and I’m not seeing this error. Can someone double click that transaction query in D14 and see if it matches my screenshot? It should be referencing 2 hidden(ish) dates in N3 and P3. Even better, if you could paste the following formula in some empty cell and tell me what the resulting query string is, that would be very helpful:

="select Col"&$D$13&",Col"&$E$13&",Col"&$F$13&",Col"&$G$13&" where Col"&$D$13&" >= date '"&TEXT($N$3,"yyyy-mm-dd")&"' and Col"&$D$13&" <= date '"&TEXT($P$3,"yyyy-mm-dd")&"' and Col"&$G$13&"<>'"&JOIN("' and Col"&$G$13&"<>'", FILTER($A$15:$A,$C$15:$C="Hide"))&"'"

(it should show something like this)
select Col2,Col3,Col5,Col4 where Col2 >= date '2020-12-01' and Col2 <= date '2023-11-30' and Col4<>'Asset Purchase' and Col4<>'Credit Card Payment' and Col4<>'HIDDEN' and Col4<>'Investment' and Col4<>'Mortgage/Rent' and Col4<>'Reimbursable' and Col4<>'Reimbursement' and Col4<>'Transfer'

[th5418]

I pasted your query and this is my outcome

select Col2,Col3,Col5,Col4 where Col2 >= date '2023-06-01' and Col2 <= date '2023-11-30' and Col4<>'Books' and Col4<>'Charity' and Col4<>'Chinese New Year' and Col4<>'Coffee Shops' and Col4<>'Computer Games' and Col4<>'Concerts' and Col4<>'Credit Card Reward' and Col4<>'Dance Lessons' and Col4<>'Dessert' and Col4<>'Doctor' and Col4<>'Down Payment' and Col4<>'Eyecare' and Col4<>'Gambling' and Col4<>'Gasoline' and Col4<>'Hair' and Col4<>'HIDDEN' and Col4<>'Investment Purchase' and Col4<>'Karaoke' and Col4<>'Laundry' and Col4<>'Legal' and Col4<>'Lottery Tickets' and Col4<>'Mobile Phone' and Col4<>'Movies' and Col4<>'Museums' and Col4<>'Newspapers & Magazines' and Col4<>'Parking Ticket' and Col4<>'Pharmacy' and Col4<>'Physical Therapy' and Col4<>'Plays and Musicals' and Col4<>'Political Donations' and Col4<>'Real Estate Attorney' and Col4<>'Rent Application Fee' and Col4<>'Shipping' and Col4<>'Shoes' and Col4<>'Spa & Massage' and Col4<>'Tax Preparation' and Col4<>'Transfer' and Col4<>'Wedding Dinner' and Col4<>'Work Expense'

[taylorvance]

@th5418 This looks fine to me. However I have something to try. Can you please try formatting your Transactions sheet Date column as a date value? Select the whole column and go to Format menu > Number > Date (exact format doesn’t matter as long as it’s a date).

If this works I’ll need to add some setup instructions here for future reference.

[th5418]

It is already date format, but I did it again anyways, nothing changed.

[taylorvance]

@th5418 this is a longshot but I wonder if there was a timing issue. Perhaps you installed as I was in the middle of making changes to the master spreadsheet. Could you try restoring the template in the TCS addon? Unfortunately I haven’t been able to recreate this issue on my end.

[th5418]

I just restored it and it has the same problem. Oh well! Thanks for your help.