If you use Office 365 Pro Plus and Excel 2016, you should see a new tab under File – Options. The Data tab. This has many useful features which help improve Pivot performance. In addition, it also helps you with incorporating Data Models more easily into your Excel data management process.
If you do not see the Data tab in File – Options, you are not using the the right version of Excel. Escalate to your IT team to set it right.
Here is a set of Practical usage guidelines for some of these new options.
Contents
Pivot Default Settings (no 1)
Edit Default Layout allows us to save defaults for all Pivot Table options. This is already covered in a previous article and video.
Pivot performance improvement options (no 2)
These options disable the ability to UNDO for refresh and pivot manipulation for large raw data sizes. You can control what Large data means. Default is 300,000 rows.
However, this is where you have an opportunity to improve your life. If Pivot performance is slow for much smaller data in your case, just reduce that number.
Performance depends on data size as well as the PC configuration. For slow machines, which are still very prevalent (Aug 2017), even 50,000 rows of raw data can make Pivot performance degrade.
Disabling Undo is not a big problem for Pivot tables because everything is a read-only option. It should increase performance significantly for large data.
Data model related options (no 3) we will cover in a separate article.
Disable automatic grouping of Date/Time columns in Pivot tables
This option will also improve Pivot performance. For decades, Pivot was always capable of grouping on all types of date related items. Unfortunately, very few human beings noticed and used these options. Therefore, Microsoft recently started grouping dates to Year, Month, Quarter and Time to Hours, Min, Sec – automatically.
While it is a good way to educate the humanity about grouping features, it has two disadvantages. Firstly, nobody knew how to get it back to individual dates – which is easy – just right click and Ungroup. Secondly, Grouping of dates / time takes a long time for large amount of raw data. For all you know, your analytical needs do not require the usage of data time column at all.
That is why this option allows you to change the new default behavior. No more automatic grouping if you enable this check box.
