Turn off automatic date and time grouping in Excel Pivot Tables

You may have noticed that in the newer versions of Excel, if you drag a date or time into the  columns or rows, it automatically groups them into months or hours respectively. You can stop this happening either with a quick (but repetitive fix) or a permanent fix. Below how to turn off automatic date and time grouping in Excel Pivot Tables

As shown below, whether you want it or not, the dates are grouped in months.

Turn off automatic date and time grouping in Excel Pivot Tables

Quick Fix to switch off the date grouping

The quick fix is to undo (CTRL + Z). This works because when you drag it in, Excel puts it in and groups it in 2 steps. You can Undo even though you didn’t do it.

Turn off automatic date and time grouping in Excel Pivot Tables

You can turn off automatic date and time grouping in Excel Pivot Tables by changing the default behaviour in the Options settings. Click on your FILE menu item and choose Options.

As shown below, in the DATA section choose to ‘Disable automatic grouping of Date/ Time columns in Pivot Tables.

Turn off automatic date and time grouping in Excel Pivot Tables