A very frustrating aspect of PivotTables is where you receive a message that Pivot Table group dates by month cannot group that selection. This also happens when you try other groupings.
The reason for this seems to be how Microsoft Excel sees the data.
For example if you have 999 cells that contain a valid Excel date, and only 1 cell that is a blank or a date that excel doesn’t recognise you will get the message that ‘Pivot Table group dates by month cannot group that selection’.
To get the PivotTable to perform the grouping you will need to change the problem cells to more acceptable entries. Change blanks to a zero or dates into Excel acceptable dates.
Dates are often a problem in PivotTables so we often recommend that clients create a new column in the underlying data that does any of the grouping they need and then pull through these grouped columns into the Pivot Table.
Please fill in your details below to receive the free monthly Audit Excel newsletter which covers free Excel tips tricks and shortcuts.
Please fill in your details below to get regular Excel tips and tricks and a free Quick Reference Download!
New Online Data Cleanup Course, Repair Corrupt Files, tables slowing Excel down, Excel and BI In this issue: Next Advanced Excel course dates 12-16 Oct, 9-13 Nov Beginner Excel Course Dates- Next 12 Feb 2016 Data Cleanup online Course In … Continue reading
Data table slowing down Excel Data tables are a great feature in Excel but it can be the cause of Excel slowing down during operations and resulting in the closing of a file taking lots of time. Data table slowing … Continue reading
Adrian was really good. He explained everything really professionally- Bookkeeper.– Excel Testimonials
It dealt with my daily challenges at work- IS Management Accountant.
There was nothing I did not like about the workshop. Adrian was fantastic- Accountant.