Here’s a pivot table in which I added the Category and OrderDate fields to the Rows area. Since that version, dates in Pivot tables have been automatically grouped too. Pivot Tables dates weren’t grouped in earlier versions of Excel, but that behaviour changed in Excel 2016. You can change an Excel option to turn that automatic grouping on or off, and there are instructions and a video on my Contextures website. Starting in Excel 2007, dates have been automatically grouped into a hierarchy, such as years and months, when you turn on an AutoFilter or create a named Excel table. Remember to make a backup of the registry first, and read the warning at the top of that page.
To do that, follow the instructions in this article by Microsoft’s Excel team. The only way to turn off pivot table date grouping in Excel 2016, if you’re brave enough, is by making a change to the Windows Registry. Then, in the PivotTable Fields list, drag the date field into the Rows or Columns area.Dates are not automatically grouped there Add the date field to the pivot table Report Filter area first.However, to prevent dates from automatically grouping in Excel 2016, you can use this 2-step workaround: In Excel 2016, there isn’t an easy way turn off pivot table date grouping. In the “Data options” section, add a check mark to “Disable automatic grouping of Date/Time columns in PivotTables”.Click Options, then click the Data category.At the top of Excel, click the File tab.NOTE: This is an application-level setting, not a setting for individual workbooks. Now, you can simply change one of the Excel options, to stop pivot table date grouping. You don’t have to use workarounds or make changes to the registry. If you’re using Excel 2019 or Excel for Office 365, there’s good news. See the simple steps below.įor Excel 2016, it takes more work - use either the Excel 2016 Workaround, or the Excel 2016 Registry tweak, described below.įurther down in this blog post, you can read more about date grouping in Filters, and the benefit of pivot table date grouping, which started in Excel 2016. It’s easy to prevent automatic date grouping for pivot tables in Excel 2019/365, by changing a setting. You can also read about why this feature was added, and watch a date grouping video. See how to stop pivot table date grouping in the latest versions of Excel, and a couple of workarounds for Excel 2016. When you add a date field to your Pivot Table, Excel automatically groups the dates into a hierarchy, such as years and months.
0 kommentar(er)
