Calculated fields in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a calculated field in Excel?

A calculated field is a new field in a PivotTable or table that uses a formula to calculate values based on other fields. It lets you create custom calculations without changing the original data.

beginner

How do you add a calculated field in a PivotTable?

Go to the PivotTable Analyze tab, click on 'Fields, Items & Sets', then choose 'Calculated Field'. Enter a name and formula, then click OK to add it.

beginner

True or False: Calculated fields change the original data in your worksheet.

False. Calculated fields only add new calculations in the PivotTable or report. They do not alter the original data source.

beginner

Example formula for a calculated field to find profit if you have 'Sales' and 'Cost' fields?

The formula would be: =Sales - Cost. This subtracts the Cost from Sales to show profit.

intermediate

Why use calculated fields instead of adding formulas directly in the worksheet?

Calculated fields update automatically when you refresh the PivotTable and keep calculations inside the report, making it easier to manage and analyze data.

Where do you add a calculated field in Excel PivotTables?

A PivotTable Analyze > Fields, Items & Sets > Calculated Field

B Home > Insert > Calculated Field

C Data > Sort & Filter > Calculated Field

D Formulas > Name Manager

Calculated fields are added from the PivotTable Analyze tab under Fields, Items & Sets.

What does a calculated field do?

A Deletes data from the PivotTable

B Changes the original data in the worksheet

C Creates a new field with a formula in a PivotTable

D Formats cells automatically

Calculated fields create new fields with formulas inside PivotTables without changing original data.

If you want to calculate profit as Sales minus Cost, what formula would you use in a calculated field?

A = Sales + Cost

B = Sales - Cost

C = Cost - Sales

D = Sales * Cost

Profit is Sales minus Cost, so the formula is = Sales - Cost.

Can calculated fields be used outside PivotTables?

A Yes, anywhere in the worksheet

B Only in charts

C Only in tables

D No, only inside PivotTables

Calculated fields are a feature specific to PivotTables.

What happens to calculated fields when you refresh the PivotTable?

A They update automatically

B They cause errors

C They need to be re-created

D They disappear

Calculated fields update automatically when the PivotTable is refreshed.

Explain what a calculated field is and how it is used in Excel PivotTables.

Think about how you can add new calculations inside a PivotTable without changing your data.

Describe the steps to add a calculated field to a PivotTable and give an example formula.

Remember the menu path and a simple subtraction formula.