Recalculate 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 Different Ways to Update Data in PowerPivot.
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 the Troubleshoot Recalculations section below.
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 below.
Manually Recalculate Formulas
This topic describes how to temporarily change the settings of a PowerPivot workbook so that the results of formulas are no longer updated automatically. We recommend that you use the Automatic option whenever possible; however, manual recalculation of formulas might be necessary to mitigate the impact on workbook performance while you are designing formulas.
After you change this setting, you must manually trigger an update of any calculations that are based on formulas.
Important
Before you publish the workbook you should always change the calculation mode back to automatic. This will help prevent problems when designing formulas.
Configure Manual Recalculation
Recalculating means updating the results of any formulas that use changed data. You need to recalculate whenever the formula changes, or when data that affects the results of calculations has changed or when the data has been refreshed. For more information about data refresh, see Different Ways to Update Data in PowerPivot.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
To configure the workbook for manual recalculation
In the PowerPivot window, click the Design tab, and in the Calculations group, click Calculation Options.
Click Manual Calculation Mode.
To recalculate all tables, click Calculation Options again, and then click Calculate Now.
Formulas in the workbook are checked for errors, and tables are updated with results, if any. Depending on the amount of data and the number of calculations, the workbook may become unresponsive for some time.
Troubleshoot Recalculations
This section provides additional technical tips for consideration when planning when to recalculate a workbook. For general information about recalculating and refreshing data in your workbook, see the following topics:
Different Ways to Update Data in PowerPivot
Dependencies
When a column depends on another column, and the contents of that other column change in any way, all related columns might need to be recalculated. Whenever changes are made to the PowerPivot workbook, PowerPivot for Excel performs an analysis of the existing PowerPivot data to determine whether recalculation is required, and performs the update in the most efficient way possible.
For example, suppose you have a table, Sales, that is related to the tables, Product and ProductCategory; and formulas in the Sales table depend on both of the other tables. Any change to either the Product or ProductCategory tables will cause all calculated columns in the Sales table to be recalculated. This makes sense when you consider that you might have formulas that roll up sales by category or by product. Therefore, to be sure the results are correct, the formulas based on the data must be recalculated.
PowerPivot always performs a complete recalculation for a table, because a complete recalculation is more efficient than checking for changed values. The changes that trigger recalculation might include such major changes as deleting a column, changing the numeric data type of a column, or adding a new column. However, seemingly trivial changes, such as changing the name of a column, might trigger recalculation as well. This is because the names of columns are used as identifiers in formulas.
In some cases, PowerPivot for Excel may determine that columns can be excluded from recalculation. For example, if you have a formula that looks up a value such as [Product Color] from the Products table, and the column that is altered is [Quantity] in the Sales table, the formula does not need to be recalculated even though the tables Sales and Products are related. However, if you have any formulas that rely on Sales[Quantity], recalculation is required.
Sequence of Recalculation for Dependent Columns
Dependencies are calculated prior to any recalculation. If there are multiple columns that depend on each other, PowerPivot follows the sequence of dependencies. This ensures that the columns are processed in the right order at the maximum speed.
Transactions
Operations that recalculate or refresh data take place as a transaction. This means that if any part of the refresh operation fails, the remaining operations are rolled back. This is to ensure that data is not left in a partially processed state. You cannot manage the transactions as you do in a relational database, or create checkpoints.
Recalculation of Volatile Functions
Some functions such as NOW, RAND, or TODAY, do not have fixed values. To avoid performance problems, execution of a query or filtering will usually not cause such functions to be re-evaluated if they are used in a calculated column. The results for these functions are only recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of formulas that contain these functions. However, volatile functions such as NOW, RAND, or TODAY will always be recalculated if the function is used in the definition of a measure.