HowTos

Excel Error – Pivot Table Does Not Group Dates Properly or Give Errors

I work with large data in spreadsheet and pivot table comes very handy in summarizing  the data. However, often I encounter with an error  when I try to group the multiple sets of date.

Types of Pivot Table Date Grouping errors

  • Pivot Table does not Group dates properly – It gets grouped into just one Group namely ‘Group 1.’
  • Sometimes it gives an error that that data set can’t be grouped.

Various reason for not grouping dates

  • Dates are in text format or non-date format
  • There are extra characters hidden in some of the cells of date column
  • Date locale issue – your locale (PC) uses DMY date format and the text date is using MDY date format (or the other way around)
  • Selection Issue

How to fix this error?

a) Convert Text Formatted Dates into Real Date Format

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • Type / (forward slash) in ‘Find what’ and in ‘Replace with’
  • Hit Replace All
  • All Text formatted date will be converted into Real date format

Type / (forward slash) in ‘Find what’ and in ‘Replace with’

b) Remove extra characters in some of the cells of date column

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • In Find what space, Type Alt key, and then type 0160. Nothing will type in space and it will remain empty. Also, Keep Replace with space blank.
  • Hit Replace All
  • All Text formatted date will be converted into Real date format
  • Also, make sure to manually remove any visible funny characters, words, alphabets.

1) Type Alt key 2) Type 0160, Nothing will type in space 

C) Fix Date Locale Issue

  • Select the date column
  • Go to menu >  Data > Text to Columns, select Delimited options and hit next
  • Keep everything blank in this window and hit next
  • Select date and in drop down menu select the date type that your system is using. You can use trial and error method by selecting different format and see what works for you.
  • Hit finish
Go to Data> Text to Columns, select Delimited and hit next.
Keep everything blank in this window and hit next
Select date and in drop down select the date type that your system use. You can do trial error by selecting different format and see what works for you.

D) Fix Selection Issue

After creating Pivot table if you do not select date properly then also it may not group as per month and year.

Sometimes, if you select dates not properly then it will get grouped into just ‘Group1’

To fix this selection issue. All you need to do is select any cell and take your cursor to lower part of selected cell until a downward arrow appears and then click to select all other cells.

1) Select any cell 2) Take your cursor at lower part of the cell until downward arrow forms 3) Click to select rest of the cells
All cells will selected like this then right click and hit Group

Please let me know if all these tricks solves your Pivot table date grouping issue or not.

Leave a Reply