How to Group By Month and Year with Subtotals in Pivot Table?

[dgonzalez]

Hello!

I have done this one year ago, but as soon as a new year come in, for the life of me, I cannot remember how to do it (I shall keep notes this time):

I have a table with every expense I go through, and I create a summary pivot table. This is what I want to do in the pivot table:

(in parenthesis: the respective rows in the picture for reference)

• Group by year and month (Rows 10 to 24)
• Have the year show a subtotal per section (Row 10 and 23)
• When the year is over, I collapse the year (Row 5 to 9)

I was able to do it in previous years, but every year is a struggle and this time. So far I’m able to ungroup and group by year and month, but I cannot reproduce to have the summary total at the year level and to collapse individual years.

This is a screenshot of what it looks like when working (although, now I want to collapse 2022):

Any help will be greatly appreciated.

[Villeroy]

Double-click on a year cell.

[dgonzalez]

Thanks Villeroy!

Not sure if it’s common, but it seems that my Pivot Table might have gotten corrupted? This is what I get when I tried double clicking:

I created a test file and recreated the pivot table and double clicking works as you suggested (same as “Data => Group => Show/Hide Details”).

I can now do most of what I wanted after recreating the pivot table:

• Group by year and month = Done
• When the year is over, I collapse the year = Done
• Have the year show a subtotal per section = Not yet

I have not been able to reproduce the yearly subtotal that appears in the original image on 2022 and 2023 (10 and 23 rows).

[Lupp]

Why don’t you attach a .ods showing the issue for you?

If the bad habit of showing only an image for any problem continues to spread, a help system like this askj site will soon become unusable.

Attach an image ONLY if thge issue explicitly about the view.

[Villeroy]

Pivot_Month_Person_Category_Subtotals.ods (76.8 KB)

[dgonzalez]

Thanks Lupp, I will for sure do that next time. Thanks Villeroy for the file, not exactly what I was trying, but it clarified some stuff.

I was able to work around what I did and come up with my expected results . It doesn’t seem to be the way I was able to do it before. In my old file, in the Pivot properties the Row Field only has “Date” on its options instead of “Year” and “Date”, and there are no Subtotal options selected. But investigating this more I don’t think will bring any value.

I wrote down my instructions so I don’t have to deal with this again next year:

How to Collapse an expanded group?

• Double click the year or click the year, go to Data > Group > Show Less Detail
• If it doesn’t work, it might mean the table is corrupted. Do the next steps.

To Recreate the table:

• Refresh pivot table
• Click one of the years, then go to Data > Group > Ungroup which will break down the table into all the dates in one column.
• Click one of the dates, and go to Data > Group > Group
• This should bring the Group By dialog. Choose Intervals: Year and Month

To get the Subtotals:

• Click anywhere in the Pivot table and go to Properties
• Double click “Year” in the “Data” section
• Choose “User Defined” and SUM under “Subtotals”
• Choose Options…
• Choose Layout: Outline Layout with Subtotals at the top

Thanks everyone for your help!