The double-click to drill down is amazing! Do I just delete the new tab when I’m done reading it?
@lh369 welcome, Lisa! I’m glad you find it useful! And yes, that’s what I do.
This is very useful. I have two questions:
- I like to track my expenses, independent of income. Is there a way that I can exclude the income from the “grand total”? If I hide the Income column, the values are still included in the total.
- This may be more of a general sheets question, but is there a way to expand/collapse all the category groups at once? I had to click on each group to collapse the categories. Is there a shortcut?
Thanks!
- You can mark those categories as hidden in the Categories sheet Hide From Reports column. Hidden categories are not pulled into the pivot table.
- I don’t think there’s a way to collapse all column groups at the same time, unless you write a custom script.
1 Like
Thanks so much for your quick response and for building this report. Very helpful!
1 Like
This is great, thank you for making this avalible!
I added a small addition which I found helpful and wanted to share (Also wanted to solicit feedback on how to improve it)
I added another row which displays the average budget for each category under the selected time frame. This gives a quick visual of where you’re over, on average, for each category.
I pull in the avg budget information in a helper column to get an address look up, and the average budge over the selected range
Averager budge per caregory is calculated as so
=ARRAYFORMULA(MAP(F16:F, LAMBDA(f, IF(f = "", "", if(OFFSET(f,0,-3)="Income",1,-1)*SUM(OFFSET(INDIRECT($G$12), ROW(INDIRECT(f)) - ROW(INDIRECT($G$12)), -$R$3, 1, $R$3))/$R$3))))
2 Likes
Welcome, @bdhammel! This is a nice addition. I wish I could offer feedback on the formula, but I’m not that good at reading them. I think I get the gist though, and it’s a very clever solution.
This add-on has been very helpful, thanks! I’ve encountered a small problem recently: I’ve added a couple of new expense categories, but your add-on is not updating with the new categories. I’ve refreshed, logged out and back in again, and even uninstalled the add-on and reinstalled, but nothing seems to fix it. Any ideas?
@csedita Do you have any transactions for those categories in the active date range? Are the categories marked “Hide” on the Categories sheet?
That solved it! Categories automatically appear or disappear depending on whether there are transactions in that category during the date range entered. Thanks so much!
1 Like
I also have a suggestion for the layout of this add-on sheet… most budgets are written with the categories going down the far left column and the amounts in the cells next to them. This allows the category column to increase in width so everything is legible and not at an angle as it currently is. You can also freeze that column so it is always visible and each month can be scrolled horizontally. Also it allows the totals to read like a standard arithmetic equation with the total and average of each group underneath each category total.
@csedita That’s not a bad idea and I can see why it might be your preference. However, I prefer to locate a category and see all of its data without scrolling as much, especially horizontally. I’d rather see more months than more categories. But I appreciate the feedback!
1 Like
