skip to Main Content

Guest Post by Scott at Tiny Lizard

Power Pivot 2013 is a bit, shall we say, aggressive with its desire to keep your reports up-to-date.

Renaming a measure that isn’t even used yet?
Please wait, while I refresh your reports.

Adding a brand new measure that can’t possibly be used yet? 
Please wait, while I refresh your reports.

You tilted your head to the side?
Please wait, while I refresh your reports.

I had a customer with 16 pivot tables per sheet, and about 16 sheets.  Making edits was getting very non-fun, so I finally took the time to look into this fantastically stupendously wonderfully awesome solution to the problem.Untitled_thumb[4]

It worked ok. Nyah-Nyah New entries get added to your pivot table context menu, as seen on right.  Simply disable when you intend to make a bunch of edits at once, and use the extra time to read Rob’s new book.

Highly recommended for those annoyed by this in 2013.

This Post Has 12 Comments
  1. I need this feature. This is by far the biggest drawback I’ve seen to upgrading to 2013. Every time I make a change or try to update….”Reading Data…..”

    I’m a little confused how to get this. Is this just an update I need to apply to excel or an add-in? The download on the link says 32 bit, what if I have 64?

    Please help

      1. Thanks for the feedback. It used to be that 32-bit was way more common. But that is changing. I modified the OLAP PivotTable Extensions release so it lets you choose between 32-bit and 64-bit more clearly.

  2. I think the ‘Defer Layout Update’ checkbox at the bottom of the pivot table field list does the same thing…or at least close to it.

    I was having issues with my pivots and charts “breaking” when I renamed measures or columns. Sometimes I would create a slicer based off a particular column and then realize that some banding in a calculated column would reduce the number of options and make the slicer easier to use.

    My 2010 method for this would be to simply create a new column, rename the original column, name the new column after the original, then update my excel worksheets. The slicers never noticed the underlying change.

    In Excel 2013, all copies of the slicer would disappear as soon as the original column was renamed, then I had to manually add the slicers again…until I tried ‘Defer Layout Update’

    Just check the box before editing the data model, click the Update button next to it when you’re done, then uncheck the box.

    Also useful when adding/removing rows, columns, and values from your pivot. You don’t have to wait on a recalc for each field move.

    1. Good trick Mike. I will try that or the OLAP PivotTables Extensions. I like the part that the Extension disables it for all Pivottables connected to the model. I have to say my pet peeve is when I make a mistake in a Power Pivot measure, Power Pivot freezes while the Excel window flashes with the error message. I often do not realize that the error is being shown in Excel.

  3. Does anyone have a similar solution for a cube?
    I would like to manipulate a Pivot table (add attributes and metrics) connected to a cube and then refresh it.
    Thanks

    1. Hg, consider checking the “Defer Layout Update” option for the pivot you are attempting to manipulate. You can check that box, make as many changes you need to make, then click “Update”. You can uncheck that option to get back to usual behavior (Pivot would update as you make changes to it). Hope that helps.

      1. Avichal,
        the only problem with this solution is the interaction with filter that are also disabled.
        Even so, it is better than nothing.
        Thank you once again.

  4. This feature of 2013 had been making me a bit crazy. I did download the OLAP Pivot Tables extension and it did the trick. However, I had issues when trying to view the data model in relationship view so I removed the extension.

    The following VBA will do the trick. You can turn the refresh capability of each pivot table on or off with this code, just replace the “=false” with “=true” accordingly.

    Sub PreventRefresh()
    Dim MySheet As Worksheet
    Dim MyPivot As PivotTable

    For Each MySheet In ActiveWorkbook.Worksheets
    For Each MyPivot In MySheet.PivotTables
    MyPivot.PivotCache.EnableRefresh = False

    Next MyPivot
    Next MySheet

    End Sub

    If anyone knows of something that Microsoft has changed to prevent this issue from happening, please let me know.

    1. Hi Michael,

      In my case, I’m creating a dashboard with some slicers and I’m adding and removing pivots to the slicers to be able to manipulate the filtering and avoiding the long wait (many pivots)

      now that I try this line of code of yours:
      MyPivot.PivotCache.EnableRefresh = False
      and then follow my code:
      slCache.PivotTables.AddPivotTable MyPivot

      Excel gives me an error: Refresh has been disabled by a Visual Basic Macro, so it seems that the Slicer is forcing to refresh and when cannot do it, stops everything.

      Any ideas?

      Thx

      BR/ AM

Leave a Comment or Question