Change Calculated Field in a Pivot Table

To change a calculated field in a pivot table, open the field’s formula settings, edit its formula, and confirm the change; the pivot table then recalculates using the revised formula. The exact commands depend on the spreadsheet program, and a calculated field is different from a formula entered in a worksheet cell.

Formula

This simple calculator illustrates the arithmetic a calculated field might perform; it does not edit a pivot table. A calculated value equals the original value multiplied by the multiplier.

Formula: calculated value = original value × multiplier

• Original value is the starting number, with no unit specified.
• Multiplier is the factor applied to that number.
• Calculated value is the product, rounded to two decimal places in the calculator.

Worked Example

Using the calculator defaults:

1. Original value = 100.
2. Multiplier = 1.1.
3. Calculated value = 100 × 1.1 = 110.
4. Rounded to two decimal places, the result is 110.00.

In a pivot table, the equivalent formula might multiply a revenue field by 1.1. The table’s field formula—not this standalone example—must be changed in the pivot table’s settings.

Change the Field in Excel

In desktop Excel, select a cell in the pivot table and open the calculated-field controls. Depending on the version and ribbon layout, the command is generally under PivotTable Analyze (sometimes called Options), then Fields, Items, & Sets and Calculated Field.

1. Click inside the pivot table you want to update.
2. Open the calculated-field dialog from the PivotTable Analyze ribbon.
3. Use the Name list to select the existing calculated field.
4. Review and edit the formula in the Formula box. Insert source fields using the dialog’s field list where possible, rather than relying on guessed spelling.
5. Choose Modify or the equivalent update command, then confirm with OK.
6. Check the resulting values and refresh the pivot table if the underlying source data has changed.

Changing the formula modifies that calculated field wherever it appears in the pivot table; it is not a separate formula for each row or column position. If you want to preserve the old calculation, create a second calculated field with a different name instead of overwriting the original.

How a Pivot-Table Calculated Field Works

A standard calculated field in an Excel PivotTable performs arithmetic using other fields in the source data. For instance, if the source has Revenue and Cost, a calculated field named Profit can use = Revenue - Cost. The pivot table applies the formula to its summarized field values.

This behavior is an important distinction: a calculated field generally works from aggregated field values in the pivot table, rather than calculating a new result independently for every visible row of the finished report. Consequently, a formula such as profit margin may not equal the average of the individual rows’ margins. If the desired metric is a ratio of totals, use a formula such as total profit divided by total revenue; if it is an average of row-level ratios, calculate those ratios in the source data and summarize that field appropriately.

Calculated fields are also not the same as calculated items. A calculated field creates a measure from source fields. A calculated item creates a synthetic item within a particular row or column field, combining or comparing items in that field. If the formula dialog does not show the result you expect, verify that you are editing the right kind of calculation.

What to Check Before Editing

• Identify the calculation type. Confirm that the value is a calculated field, not a source-data formula, a calculated item, or a worksheet formula placed beside the pivot table.
• Check the field names. Formulas refer to source fields, not necessarily the labels displayed in the report. Field names containing spaces or symbols may need to be inserted from the dialog’s list.
• Confirm the intended aggregation. Adding or subtracting totals often works as expected, but averages and ratios can produce results that differ from calculations performed record by record.
• Know the source and pivot type. Calculated-field options and behavior vary by spreadsheet application and by how the pivot table was created. PivotTables based on the Data Model, for example, use measures rather than the standard calculated-field workflow.
• Keep a record of the old formula. Save a copy or note the original expression if the change needs to be reversed or compared.

Common Problems and Alternatives

If the formula is accepted but the numbers seem wrong, check whether the pivot table is summarizing the inputs as sums, counts, or averages. Also inspect blanks, text values, and zero denominators in the source data. Formatting a result as a percentage or currency changes how it is displayed, not the underlying calculation.

If the calculated-field command is unavailable, the pivot table may use a data source or model that does not support that kind of field. A source-data helper column is often a practical alternative: calculate the value for each record, include that column in the source range, then refresh and summarize it. For a Data Model pivot table, create an appropriate measure instead. These alternatives can also be preferable when the calculation must occur per record before aggregation.

After changing a formula, verify it with a small, independently calculated example and check several groups in the pivot table. Refreshing updates the pivot table from its source; it does not fix a mistaken formula. Conversely, editing a formula will not necessarily update the underlying source data.

Frequently Asked Questions

Can I change a calculated field formula without deleting it?
Yes. Select the existing field by name in the calculated-field dialog, edit its formula, and use the modify or update command. Deleting and recreating it is generally unnecessary.

Why is the calculated field option greyed out?
The pivot table may be based on the Data Model or another source that does not support standard calculated fields, or you may not have selected a cell inside the pivot table. Check its source and use a measure or source-data calculation if appropriate.

Can a calculated field use a value in a worksheet cell?
A standard calculated field is designed around source fields and its formula controls, not arbitrary worksheet-cell references. Put the needed value into the source data or use a suitable worksheet formula or model measure instead.

Why does a calculated percentage differ from the average of row percentages?
A calculated field commonly evaluates summarized inputs. Dividing total profit by total revenue is not generally the same as averaging each record’s profit percentage. Decide which definition you need and calculate at the corresponding level.

Does changing the formula update every pivot table?
It updates the calculated field in the pivot table you edited. Other pivot tables may use separate definitions or sources, so inspect them individually rather than assuming they changed too.

Related

• Calculate Gross Domestic Product: Formula and Example
• Calculate GPA After This Semester: Formula and Example
• Calculate Decrease: Formula and Percentage Change

in value | Original value | - | 100 | 0
in rate | Multiplier | - | 1.1 | 0
out result | Calculated value | - | value * rate | 2