I created a monthly spending pivot table following the video below. Can you assist me in creating subtotals for my income and spending in it? I’ve Googled it and tried a few things but it’s just not something I’ve been able to figure out. In creating categories, I added “AA” at the beginning of every income item so that they all appear at the top of the table, with all of the expenses at the bottom. I suspect there’s an easier way to do that, too, but this seems to work and visually I can see all my income at the top followed by all of my expenses. That is illustrative of my level of understanding of pivot tables.
You might try setting your rows to contain first ‘Group’, then ‘Category’. Not sure if this does what you’re looking for, but thought it might be worth mentioning…
Thanks, but that did not provide a solution for me.
It’s weird to me that a pivot table designed for monthly financial tracking doesn’t have subtotals for income and spending built in. Am I missing something simple here? Any ideas where I can find a solution?
Is there a view within the Tiller world that is already built, maybe the Monthly Budget tab or running a Category Rollup Report would be similar to what you’re looking for?
Assuming your own pivot table is connected to the Transactions tab, I think it would work to insert a column for Type in the Transactions tab and use a formula that looks up the Type assignments you’ve made for each Category on the Categories tab. Then you could pull that Type field into the pivot table and show totals for Income/Expense/Transfer. Could do the same thing to add a column for Group, which would allow another level of detail in the pivot table.
Would this fit what you’re looking for @tonycanata ? Note two income types (Paycheck and Side Hustle) If so, you’d want to add the “Type” column to your Transactions sheet and then set up “Type” as one of the row options in the pivot table.
Thanks Heather. Yes, this is exactly what I’m trying to create. I went to the Add a Type column to the Transactions Sheet page. I created a new column to the left of my ‘sum of amount category’ column. I then copied the arrayformula and pasted it into row 1 of the new column. But I still don’t see “Type” as a suggested row in my Pivot table editor. Can you help me take the next steps to set this up? Also, if I highlight the new Type column, the pivot table editor disappears and I have no idea why that happens?
Thanks for your patience. Obviously I am very new to spreadsheets and pivot tables.
First, does the “Type” data show up in the Transactions sheet after you added the arrayformula?
If so, then click anywhere inside the pivot table and it should open the editor. Then you’d want to add an option under the “Row” section for the “Type” data and then under it is a second row configured for “Category” per the screenshot.
No, it doesn’t. I see that the arrayformula is in the function bar (if that’s what it’s called) but not data shows up in the column under the word “type.”
Hi @tonycanata, can you please confirm that the transactions where there is no data showing for the “Type” column are categorized? The formula expects them to be categorized.
Yes, the transactions where there is no data showing for the “Type” column are categorized. If it would help, is there a way to share screen shots here? I tried last time I posted but I got a message saying what I was trying to post was not allowed.
Sorry for the delay here. Did you get this figured out?
If you’re not seeing the Type data in that column there won’t be anything to pull into the pivot table so we need to figure out why that formula isn’t working for you.
Is your category column in the Transactions sheet in column D?
Is your category column on the Categories sheet in column A?
No, I haven’t figured it out. I’m pretty lost. I tried a few things, and now my Category entries all have the red triangle and indicate Input Must Fall Within A Specified Range. My Category column is C. It goes Date, Description, Category, Amount… My monthly spending sheet has a blank column A. Column B has Sum Of Amount Category. I don’t see a pivot table in my monthly spending sheet and don’t know where it went. No idea what to do now. Kinda feel like I need to fix what I apparently have messed up and then try again, and that if I try anything else I’ll just screw it up more. Any help appreciated.
