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.
Estimated reading time: 2 minutes
Table of contents
YouTube Remove Date Groupings
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 date 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.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Comparing years in Excel Pivot chart
Safest way to enter a date in Excel
Pivot Table Dropdown Field List Missing
MS Excel axis labels overlap in charts (what can you do about it)