I have a Google spreadsheet for expenses: one column for the YYYY-MM-DD date of the expense, one column for the category, and one column for the amount. This sheet has thousands of rows. The goal is a pivot table with MM-YYYY in the column, summing amounts by the month/year.

Usually, that's easy. I create the pivot table with the ungrouped variable in the column, and then right click and create a pivot table group. However, when I create a pivot table with the date as the column of the table, Google Sheets says

This pivot table is trying to produce too many columns. Make it smaller by adjusting the column breakouts or filtering the source data.

There isn't a column to right click on to create the group because Google Sheets couldn't produce any of the columns (see screenshot)

How do I create a pivot table group here? Is the only to create another column in the sheet with the raw data (I'd prefer not to do this because that sheet is an output from another program) that does the grouping with a formula?

screenshot showing that there are no columns to right click on in the pivot table

2 Answers 2


The easiest solution is to add a very narrow filter, for example to show data for only one date: enter image description here

And then you will have one column with the date to right click on and and pick the MM-YYYY format. After that you can remove this filter and all the data will be there, grouped in the selected format.


There are two options:

1 - Create a query, then create a Pivot table based on the query
2 - Create a query that includes a pivot table

Query then Pivot

Query - Use this formula:

=query({arrayformula(text(TO_DATE(DATEVALUE(A3:A81)),"YYYY-mm")),B3:C81},"select Col1, Col2, sum(Col3) group by Col1, Col2 label Col1 'Date',Col2 'Category',Sum(Col3) 'Total'")


Pivot Table Pivot Table

Query with Pivot

Use this formula:
={query({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80},"select Col1, sum(Col3) group by Col1 pivot Col2 label Col1 'Date',Sum(Col3) 'Total'"),QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'");TRANSPOSE(QUERY({{arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}}, "select sum(Col3) group by Col2 label sum(Col3) 'Total'")),query({QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'")},"select sum(Col1) label sum(Col1) ''")}


query with pivot

Sample data

sample data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.