Power Pivot

Wait, Power Pivot! Don’t Refresh Yet! I’ve Got More Changes!

My Tuesday morning was an exercise in patience waiting on Excel 2013. I was working on a complex workbook with a complex Power Pivot model with half a dozen complex PivotTables. After every minor change in Power Pivot, all the reports refresh. Hide a column… wait 2 minutes… change a calculation… wait 2 minutes. I finally got fed up with it, had a brain spark, and then my Tuesday afternoon was very productive.

Excel 2013 in most ways is a great leap over Excel 2010. Baking Power View directly into Excel was such a smart move for Microsoft to have made. Including Power Pivot in Excel in the form of the Data Model was clever because a user can leverage all that power without thinking about modeling or add-ins. However, in one way Excel 2013 was a major step backwards. Unlike Power Pivot in Excel 2010 where it assumed the user was intelligent enough to make a batch of changes to the model and then manually refresh the PivotTable reports, Excel 2013 tries to make this process more automatic. With every minor change to the model, all PivotTable reports refresh. What’s worse is that if I want to move a calculated measure from one table to another, Excel 2013 decides that I want to remove the measure from all reports.

Fortunately, we can do something about that…

Let’s say you are about to make a large batch of Power Pivot changes and don’t want PivotTables to refresh after each small change.

Install the latest version of OLAP PivotTable Extensions. Then use the Disable Auto Refresh feature by right clicking on a PivotTable connected to your Power Pivot model:

That command will disable auto refresh on all PivotTables connected to your Power Pivot model.

Now proceed with your Power Pivot changes and none of the PivotTables will refresh after every change. What’s even better is that you can make major changes like moving a calculated measure from one table to another without that measure being deleted from all the PivotTables.

Right click on a PivotTable connected to your model and Enable Auto Refresh:

On the topic of refresh, don’t forget that you can also speed up model changes by changing the calculation mode within Power Pivot under the Design tab and the Calculation Options dropdown to Manual Calculation Mode and choosing Calculate Now only as necessary:

The real question now is this… what are you going to do with all that time you got back!?