PivotTable in Excel

Question

Hi All,

I'm using a tabular model in Excel, everytime i add a filter, row or column in the PivotTable, the excel tries to execute the query to refresh data. Since our tabular model is full of data and dax, it takes time every time the excel refresh data.

Is there some way to disactivate this on Excel ? and after the creation of my PivotTable i can click on "Refresh" or somewhere in Excel to execute the whole query when we want ?

If it is possible, i will be grateful if you share it with me.

Thanks.

Answer 1

@Samir abdelmalek

Everytime you add or remove a row or column in the PivotTable via a field, you may tick the box of "Defer Layout Update" below the Pivot Table fields first.

When you’re finished changing the fields, you can click on the “Update” button which is towards the right of the Defer Layout Update box, then the Pivot Table would be updated or recalculated. You can remove the checkmark from the Defer Layout Tab as well.

More information, please refer to "PivotTables VIII: “Defer Layout Update”, or adding multiple fields in one fell swoop".

If needed, you could also go to PivotTable Analyze > Options > Data to uncheck “Refresh data when opening the file”.

Hope the information could be helpful.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can deactivate auto-refresh in Excel pivot tables. Here's how:

1. Right-click on the pivot table and select "PivotTable Options".
2. In the "PivotTable Options" dialog box, go to the "Data" tab.
3. Uncheck the "Refresh data when opening the file" checkbox.
4. Click "OK" to close the dialog box.

This will deactivate the auto-refresh feature for that pivot table.

You can also turn off auto-refresh for specific pivot tables or all pivot tables in a workbook using VBA macros. Here's an example of a VBA macro that will turn off auto-refresh for all pivot tables in the active workbook:

Sub TurnOffAutoRefresh()

Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        pt.ManualUpdate = True
    Next pt
Next ws

End Sub

To use this macro, follow these steps:

1. Open the workbook containing the pivot tables.
2. Press "Alt + F11" to open the VBA editor.
3. Insert a new module by right-clicking on "Modules" in the "Project Explorer" on the left side of the screen and selecting "Insert" > "Module".
4. Copy and paste the macro into the new module.
5. Close the VBA editor and return to the workbook.
6. Press "Alt + F8" to open the "Macros" dialog box.
7. Select the "TurnOffAutoRefresh" macro and click "Run".

This will turn off auto-refresh for all pivot tables in the workbook. To turn off auto-refresh for specific pivot tables, you can modify the macro to loop through only the pivot tables that you want to change.