Recalculate formulas in Power Pivot

When you work with data in Power Pivot, you might need to refresh data from its source, recalculate formulas in calculated columns, or update PivotTables that use the Data Model.

This article explains the difference between data refresh and recalculation, provides an overview of how recalculation is triggered, and describes your options for controlling recalculation.

Understanding data refresh vs. recalculation

Power Pivot uses both data refresh and recalculation:

Data refresh means getting up-to-date data from external data sources. Power Pivot doesn't automatically detect changes in external data sources. To bring in updated data, refresh the data model.

Recalculation means updating all the columns, tables, charts, and PivotTables in your workbook that contain formulas. Because recalculating a formula affects performance, it's important to understand the dependencies associated with each calculation.

Manual vs. automatic recalculation

By default, Power Pivot automatically recalculates as required while optimizing the time needed for processing. Although recalculation can take time, it's an important task. During recalculation, Power Pivot checks column dependencies and notifies you if a column changed, if the data is invalid, or if an error appears in a formula that used to work. However, you can choose to forego validation and only update calculations manually, especially if you're 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, use automatic recalculation mode. This mode keeps the Power Pivot metadata in sync, 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 that would cause the result of any formula to change trigger recalculation of the entire column that contains a formula. The following changes always require recalculation of formulas:

• Values from an external data source are refreshed.
• The definition of the formula changed.
• Names of tables or columns that are referenced in a formula are changed.
• Relationships between tables are added, modified, or deleted.
• New measures or calculated columns are added.
• Changes are made to other formulas within the workbook, so columns or calculations that depend on that calculation should be refreshed.
• Rows are inserted or deleted.
• You applied a filter that requires execution of a query to update the data set. The filter could be applied either in a formula or as part of a PivotTable or PivotChart.

Using manual recalculation

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 changed but you are working with a different column that didn't change 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 are made.

If you set the workbook to manual calculation mode, Power Pivot in Excel doesn't perform any validation or checking of formulas. The following results occur:

• Any new formulas that you add to the workbook are flagged as containing an error.
• No results appear in new calculated columns.

To configure the workbook for manual recalculation

1. In Power Pivot, select Design > Calculations > Calculation Options > Manual Calculation Mode.
2. To recalculate all tables, select Calculation Options > Calculate Now.
   The workbook checks formulas for errors and updates tables with results. Depending on the amount of data and the number of calculations, the workbook might become unresponsive temporarily.

Troubleshooting recalculation

Dependencies

When a column depends on another column, and the contents of that other column change in any way, you might need to recalculate all related columns. Whenever you make changes to the Power Pivot workbook, Power Pivot in Excel analyzes the existing Power Pivot data to determine whether recalculation is required, and it 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 causes all calculated columns in the Sales table to be recalculated. This behavior makes sense when you consider that you might have formulas that roll up sales by category or by product. To ensure the results are correct, you must recalculate the formulas based on the data.

Power Pivot 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 major changes such 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 behavior occurs because the names of columns are used as identifiers in formulas.

In some cases, Power Pivot determines that it can exclude columns 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 you altered is [Quantity] in the Sales table, the formula doesn't 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

Power Pivot calculates dependencies before it recalculates any columns. If multiple columns depend on each other, Power Pivot follows the sequence of dependencies. This sequence ensures that Power Pivot processes the columns in the right order and at the maximum speed.

Transactions

Operations that recalculate or refresh data take place as a transaction. If any part of the refresh operation fails, the remaining operations roll back. This behavior ensures that data isn't left in a partially processed state. You can't manage the transactions like you do in a relational database, and you can't create checkpoints.

Recalculation of volatile functions

Some functions, such as NOW, RAND, or TODAY, don't have fixed values. To avoid performance problems, executing a query or filtering usually doesn't cause Power Pivot to re-evaluate these functions if you use them in a calculated column. Power Pivot only recalculates the results for these functions when it recalculates the entire column. These situations include refreshing from an external data source or manually editing data that causes re-evaluation of formulas that contain these functions. However, if you use volatile functions such as NOW, RAND, or TODAY in the definition of a calculated field, Power Pivot always recalculates them.