Recalculating Formulas
When you are working with data in a PowerPivot for Excel workbook, from time to time you might need to refresh the data from the source, recalculate the formulas that you have created in calculated columns, or make sure that the data that is presented in a PivotTable is up-to date.
This topic explains the difference between refreshing data vs. recalculating data, provides an overview of how recalculation is triggered, and describes your options for controlling recalculation.
Understanding Data Refresh vs. Recalculation
PowerPivot uses both data refresh and recalculation:
Data refresh means obtaining up-to-date data from external data sources. PowerPivot does not automatically detect changes in external data sources, but data can be refreshed manually from the PowerPivot workbook or automatically if the workbook is shared on SharePoint. For more information, see Refreshing or Changing Imported Data.
Recalculation means updating all the columns, tables, charts, and PivotTables in your workbook that contain formulas. Because recalculation of a formula incurs a performance cost, it is important to understand the dependencies associated with each calculation.
This topic explains how recalculation works. For more information about the potential performance impacts of recalculation, see Troubleshoot Recalculation.
Important
You should never save or publish the workbook until the formulas in it have been recalculated.
Manual vs. Automatic Recalculation
By default, PowerPivot automatically recalculates as required while optimizing the time required for processing. Although recalculation can take time, it is an important task, because during recalculation, column dependencies are checked, and you will be notified if a column has changed, if the data is invalid, or if an error has appeared in a formula that used to work. However, you can choose to forego validation and only update calculations manually, especially if you are working with complex formulas or very large data sets and want to control the timing of updates.
Both manual and automatic modes have advantages; however, we strongly recommend that you use automatic recalculation mode. This mode keeps the PowerPivot metadata in synch, and prevents problems caused by deletion of data, changes in names or data types, or missing dependencies.
Using Automatic Recalculation
When you use automatic recalculation mode, any changes to data in the workbook that would cause the result of any formula to change will trigger recalculation of the entire column that contains a formula. The following changes always require recalculation of formulas:
Values from an external data source have been refreshed.
The definition of the formula changed.
Names of tables or columns that are referenced in a formula have been changed.
Relationships between tables have been added, modified, or deleted.
New measures or calculated columns have been added.
Changes have been made to other formulas within the PowerPivot workbook, so columns or calculations that depend on that calculation should be refreshed.
Rows have been inserted or deleted.
You applied a filter that requires execution of a query to update the data set. The filter could have been applied either in a formula or as part of a PivotTable or PivotChart.
Using Manual Recalculation
You can use manual recalculation to avoid incurring the cost of computing formula results until you are ready. Manual mode is particularly useful in these situations:
You are designing a formula by using a template and want to change the names of the columns and tables used in the formula before you validate it.
You know that some data in the workbook has changed but you are working with a different column that has not changed so you want to postpone a recalculation.
You are working in a workbook that has many dependencies and want to defer recalculation till you are sure all the necessary changes have been made.
Note that, as long as the workbook is set to manual calculation mode, PowerPivot for Excel does not perform any validation or checking of formulas, with the following results:
Any new formulas that you add to the workbook will be flagged as containing an error.
No results will appear in new calculated columns.
For instruction on how to change the calculation mode or trigger manual calculation of formulas, see Manually Recalculate Formulas in PowerPivot.
See Also
Other Resources
Creating and Working with Calculations
Manually Recalculate Formulas in PowerPivot