-Posted by Rob Collie
UPDATE: It turns out that all of this is unnecessary, there is no longer a need to disable the addin to get these benefits. (I was still stuck on what was possible in the Betas). See the latest post on this topic for details.
If you’re like me and you work with large data sets and/or complex measures all the time, you may be annoyed by the time it takes to layout 6-10 fields on a pivot – a handful of measures, a field or two on rows, a couple of slicers… even with the performance of PowerPivot, you can be burning a reasonable amount of time in the click-and-wait game.
I have a trick for you.
First, disable the PowerPivot addin. Yep, you heard right. On the File tab, go to Options:
Then select Addins. At the bottom of that screen, open the dropdown for Manage, and pick COM Addins from the list:
And then in the dialog that comes up, uncheck the PowerPivot addin:
Now go to the Options tab on the PivotTable ribbon and enable the Field List:
OK, now you see the native, built-in Excel field list. If you are familiar with normal Excel pivottables, this will appear foreign to you, because this is the “OLAP” field list – designed strictly for use with OLAP db’s like Analysis Services, which PowerPivot happens to be under the hood.
Anyway, at the bottom of that field list, you will see the “Defer Layout Update” checkbox. Check that, then you can make a bunch of changes to the layout of the pivot WITHOUT them refreshing the pivot. Click Update and all of your changes will run in a single batch while you go get a fresh cup of coffee:
Cool huh? A few things to note:
- You can’t create measures without the addin enabled.
- You also don’t get the fancy slicer auto-layout.
- The field checkboxes are organized very differently, split into measures and non-measures AND by table – you will see what I mean
- There are a few “dummy” measures visible in this field list that are hidden in the addin
OK… I should also mention that we might have had this feature already built-in to the PowerPivot v1 field list if it weren’t for me :) It kept coming up during development – “should we build that defer layout feature in?” And I kept saying “no, not yet.”
Why would I do such a thing, you ask? Two good reasons. One, we were running WAY low on time and resources. By NOT doing that feature, the team was able to do several other important things that today we’d be complaining about instead.
Two, I knew that if we added that feature, as we worked on PowerPivot internally, we would all turn it on whenever we hit something that was slow. As a result, we’d not feel the pain of performance issues as acutely as we should, and not as many things would be identified and fixed. So, like Cortez burning his ships, I wanted to deprive us internally of this crutch.
Of course, now that I no longer work at MS, and I find myself in a job where I crunch hundreds of millions of rows daily, irony has come home to roost :) Just kidding really – I still think it was a good v1 decision. I’m positive that engine query performance is much better for it, and it will be easy for the PowerPivot team to add this feature in v2.