Pivot Table: How to Group Dates by Month (and Fix 'Cannot Group')

What you will build

Brightline Trading Co. invoices six customers, and the invoicing system exports one row per invoice. The practice file covers 42 invoices from September 2025 to October 2026, so it spans two calendar years on purpose:

Step 1: Start from clean source data

1. Put the data in a list with one header per column (Date, Invoice, Customer, Amount) and one invoice per row.
2. Check that every cell in the Date column is a real date. Real dates are right-aligned by default; text is left-aligned.
3. Click any cell in the list and choose Insert > PivotTable. Keep the suggested range, choose New Worksheet and click OK. Leave "Add this data to the Data Model" unticked.
4. Drag Date to Rows and Amount to Values. Values should read Sum of Amount.

Step 2: Group the dates by Months and Years

1. Right-click any date in the PivotTable (a row label, not a value) and choose Group.
2. In the Grouping dialog, Excel fills in the first and last dates. Leave Starting at and Ending at as they are.
3. Under By, click Months, then Ctrl+click Years so both are selected. Make sure Days is not selected.
4. Click OK.

Step 3: Read the grouped PivotTable

Years become the outer label, with each month underneath and a subtotal for each year. Excel adds a new Years field to the field list; the original Date field now holds the months.

The Pivot Check sheet gets the same numbers with SUMIFS between two dates: =SUMIFS(Sales!$D$4:$D$45,Sales!$A$4:$A$45,">="&E5,Sales!$A$4:$A$45,"<="&EOMONTH(E5,0)), where E5 is the first day of the month. A second method that agrees with the PivotTable is the quickest way to trust a report.

Step 4: Avoid the Months-only trap

If you group by Months alone, Excel creates twelve buckets, Jan to Dec, and puts every September in the same bucket regardless of year. With data that spans more than twelve months, that is a silent error:

Step 5: Why Excel says 'Cannot group that selection'

Grouping by month only works when every item in the field is a real date. One exception blocks it. The usual causes:

• Dates stored as text, for example 2025-11-17 typed or exported as text, often with a trailing space.
• Blank cells in the Date column, or a source range that runs past the last row of data (whole columns such as A:D include thousands of blanks).
• Error values such as #N/A or #VALUE! in the Date column.
• The PivotTable uses the Data Model (the "Add this data to the Data Model" box was ticked). Group is not available there; add a month column to the source instead (see the FAQ).
• The wrong cell is selected: right-click a date row label, not a value or the grand total.

On the Raw Export sheet, column F tests each date. In F4, copied down:

=IF(ISNUMBER(A4),"Yes","No")

Excel stores a real date as a number, so ISNUMBER returns TRUE for real dates and FALSE for text and blanks. J4:J8 summarize the column with COUNTA, COUNT, =SUMPRODUCT(--ISTEXT(A4:A45)) and COUNTBLANK. You can also find text quickly with Go To Special > Constants > Text, or read how to find inconsistent data types in a column.

Step 6: Fix the dates, refresh and group

Convert the text dates to real dates. In G4, copied down:

=IF(A4="","MISSING",IF(ISNUMBER(A4),A4,DATEVALUE(TRIM(A4))))

TRIM removes the trailing space and DATEVALUE turns text that looks like a date into a real one. Copy column G and use Paste Special > Values over column A, then format it as a date. For a whole column of mixed formats, standardize mixed date formats and remove extra spaces and hidden characters cover the harder cases. A blank date cannot be guessed: look it up on the invoice (INV-5116 is dated Feb 3, 2026) and type it in.

Then go back to the PivotTable and choose PivotTable Analyze > Refresh (Alt+F5). The PivotTable keeps a copy of the source data, so it does not see the fix until you refresh. If the source range was too long, use Change Data Source to cover only the real rows, or turn the data into a Table with Ctrl+T. To refresh every PivotTable in the file at once, see refresh all queries and PivotTables with VBA. Now right-click a date and group again.

That test is also why accountants care about dates: an invoice dated a few days late moves revenue from one month to the next. Month-end cut-off is a classic audit check, and it is exactly what a monthly PivotTable will show you on a monthly profit and loss statement.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• Every cell in the Date column is a real date (ISNUMBER returns TRUE for all rows).
• No blank rows or error values inside the source range.
• The PivotTable is not in the Data Model, or a month helper column is used instead.
• Months and Years are both selected when the data spans more than twelve months.
• The PivotTable was refreshed after fixing the source.
• The grand total agrees with the source data total.

With dates grouped by month, a PivotTable becomes a quick monthly report. Turn it into a KPI dashboard with a PivotTable, or build the formula version in the sales report dashboard.