PivotTable in Excel

Samir abdelmalek 21 Reputation points
2021-05-03T21:26:19.957+00:00

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.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,343 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,000 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Emily Hua-MSFT 21,686 Reputation points Microsoft Employee
    2021-05-04T08:59:39.413+00:00

    @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.

    93582-capture10.png

    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.