Making the Categories list easier to read at a glance as a table?

Google Sheets
1

In the categorizes tab, as it’s also a budget, it’s hard to see at a glance the categories available. Is there a way to show all the groups as column headers and have the categorizes in the groups popular under. I’ve tried a pivot table, but maybe i’m unfamiliar with google pivot table, but it didn’t work.
Maybe i need to use the unique function but i need it to transpose a vertical list to a horizontal one.

Edit: solved: below in case anyone would like it as well
=TRANSPOSE(UNIQUE(Categories!C2:C))
=FILTER(Categories!$A$2:$A,Categories!$C$2:$C=A1)

Note: my categories are in col C, default is B, and this only works with the groups as column headers. if you want it transposed, you’ll either need to grab the entire contents and transpose or some other workaround.

2

Doing that is bound to break a lot of templates. All the budget templates look to the Categories sheet, and with your changes, they won’t be able to find the data they need. The Transactions sheet looks there for the list of Categories to display in the Category column dropdown menu as well.

3

To clarify i don’t mean editing the tab directly because of the issues mentioned above. but to grab the data and spit it into a table format in another sheet.

4

Ahh, gotcha. I was thinking of something similar but never got around to it. I think you’d need to use a Filter command to just grab categories that matched the group of the column you’re in.

5

Yea, just tried it, if your groups are across row 1, add this formula to A2 and then drag it across under all the group names:
=filter(Categories!$A$2:$A,Categories!$B$2:$B=A1)

1 Like
6

Thank you that worked! added the header formula on top in case anyone else needs it

7

One suggestion to make your sheet even better is to add “last month spending” to the table, so you can see how you’re doing.
I use this formula in my categories sheet, but you probably would need to modify it a bit.

=abs(sumifs(Transactions!$E:$E,Transactions!$D:$D,A2,Transactions!$L:$L,text(EDATE(today(),-1),"M-1-Y")))
8

What’s the header of the column each of those are grabbing? ie I’ve had to shift a number of my columns to get it to display what I wanted so it’s not default anymore. Also would I put that formula under just the header row of the groups or do i need to format it some way?

9

amounts, categories, month, this would go by each row of category, so not an array formula. Change A2 to whatever field has the category

10

Thanks, that’s a nifty formula, I use the business dashboard instead as i need more periods and charting.