How to Create a Pivot Table in Excel and Group It by Month

Build a pivot table for a monthly report, group dates by month and year, fix "Cannot group that selection" and compare each month with the last one.

Founder, Glow Reports. , 3 min read
  • Excel
  • Pivot Tables
  • Reporting Workflows
Daily rows gathered into monthly blocks in a pivot table, with one faulty cell being fixed
On this page
  1. How do you create a pivot table in Excel?
  2. How do you group dates by month in a pivot table?
  3. Why does Excel say "Cannot group that selection"?
  4. How do you compare this month with last month?
  5. How do you update the pivot table next month?
  6. Where does Glow Reports fit?
  7. Common questions

Most monthly reports in Excel start with a pivot table: sales by month, costs by month, orders by month. It works until the day you try to group the dates and Excel answers "Cannot group that selection". The question has come back on Excel forums for years, from accountants and analysts alike Microsoft Tech Community. Below: how to build the pivot table, group it by month, fix that error, compare months and update it next month.

How do you create a pivot table in Excel?

Turn your data into a table first, then select InsertPivotTable. Excel opens an empty pivot table and a field list. Drag the date to Rows and the amount to Values, and you have totals by date.

Step by step:

  1. Check the data. Microsoft asks for a header on every column, no blank rows or columns, and one data type per column: dates and text never mixed in the same column Microsoft Support.
  2. Click inside the data and select InsertTable. A table grows with the rows you add later, and the pivot table picks them up on refresh.
  3. Select InsertPivotTable, keep the table as the source and place the pivot table on a new worksheet.
  4. In the field list, drag the date field to Rows, the amount to Values, and a category such as region or product to Columns.

Values sum by default. To count orders instead, click the field in Values, open Value Field Settings and pick Count.

How do you group dates by month in a pivot table?

Recent versions of Excel group a date field on their own when you add it to Rows, into years, quarters and months. If yours doesn't, right-click any date in the pivot table, select Group, then select both Months and Years under By.

Select both. Months alone puts January 2025 and January 2026 in the same row, and the totals look plausible while mixing two years. With Months and Years, each month of each year gets its own row.

Microsoft documents the Group dialog, including the Starting at and Ending at boxes that limit the range Microsoft Support. To undo it, right-click a grouped date and select Ungroup.

Why does Excel say "Cannot group that selection"?

At least one cell in the date column is not a real date. Excel groups only when every value is a date. One date stored as text, often from an export, blocks the whole field. Forum threads report blank cells blocking it too Chandoo forum.

To find the culprits:

  • Text dates sit on the left of the cell instead of the right, and often carry a small green triangle Microsoft Support.
  • Add a helper column with , where A2 is the first date. A real date returns TRUE. Filter on FALSE to list every bad row.
  • Filter the date column on blanks.

To fix them, convert the text with , paste the result as values and format it as a date. Delete or fill the blank rows. Then refresh the pivot table and group again.

Dates exported in another country's format, such as 03/22/2026 read with UK settings, often refuse DATEVALUE too. Power Query reads them with the right locale, as shown in how to merge Excel and CSV files with Power Query.

How do you compare this month with last month?

Add the amount to Values a second time and show it as a change from the previous month. The first column keeps the totals, and the second shows each month against the one before.

  1. Drag the amount field to Values again.
  2. Right-click a number in the new column and select Show Values As% Difference From.
  3. Pick the date field, grouped by month, as Base field, and (previous) as Base item.

Microsoft defines the result as "the percentage difference from the value of the Base item in the Base field" Microsoft Support. Rename the column header to something readers understand, such as "vs last month".

How do you update the pivot table next month?

Paste the new month's rows at the bottom of the table, then select DataRefresh All or press Ctrl+Alt+F5. Because the source is a table, the new rows join the pivot table on refresh, and the month grouping extends to them.

Run the ISNUMBER check on the new rows first. A single text date in next month's export brings "Cannot group that selection" back.

When each month arrives as a separate file, combine the files with Power Query and build the pivot table on the result. Then a refresh picks up the new file without any pasting.

Where does Glow Reports fit?

A pivot table answers "how much per month" well. The monthly report around it is still yours to build: charts, comments, comparisons and the PDF. When that report comes back every month, read how to replace manual Excel reports.

Glow Reports starts from the same exports. You upload the month's CSV or Excel files and ask for the report, with your definitions. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can read how each month was counted and edit the query when a definition is wrong. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

Why does my pivot table put January 2025 and January 2026 together?

The dates are grouped by Months only. Right-click a date, select Group, and select both Months and Years.

Can I group by week instead of month?

Yes. In the Group dialog, select Days only and set the number of days to 7. Use Starting at to make each week begin on the day you want.

Why doesn't my pivot table show the new rows?

Either the pivot table hasn't been refreshed, or its source is a fixed range instead of a table. Refresh first. If the rows still don't appear, turn the data into a table and point the pivot table at it.

Do I need SQL to use Glow Reports?

No. The AI writes the SQL. You can read it under each figure when you want to check how a number was counted.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel files to Glow Reports and get a report with the SQL behind each figure.

Keep reading