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.

Practice

1. What is a calculated field in Excel?

easy

A. A filter to hide rows

B. A cell that contains only text

C. A chart that shows data trends

D. A formula that creates new values from existing data

Solution

1. Step 1: Understand the definition of calculated fields

   Calculated fields use formulas to create new values based on existing data in cells.

2. Step 2: Identify the correct description

   Only A formula that creates new values from existing data describes a formula creating new values from data, which matches the definition.

3. Final Answer:

   A formula that creates new values from existing data -> Option D

4. Quick Check:

   Calculated field = formula creating new values [OK]

Hint: Calculated fields always involve formulas and existing data [OK]

Common Mistakes:

• Confusing calculated fields with charts
• Thinking calculated fields are just text
• Mixing calculated fields with filters

2. Which of these is the correct formula to calculate total price if quantity is in A2 and price per item is in B2?

easy

A. =A2*B2

B. =A2+B2

C. =A2-B2

D. =SUM(A2,B2)

Solution

1. Step 1: Understand the calculation needed

   Total price equals quantity multiplied by price per item, so multiply A2 by B2.

2. Step 2: Identify the correct formula syntax

   =A2*B2 correctly multiplies the two cells. Other options add, subtract, or sum incorrectly.

3. Final Answer:

   =A2*B2 -> Option A

4. Quick Check:

   Multiply quantity by price = =A2*B2 [OK]

Hint: Use * to multiply quantities and prices in formulas [OK]

Common Mistakes:

• Using + instead of * for multiplication
• Using SUM for multiplication
• Subtracting instead of multiplying

3. Given the formula =C2*D2+E2 where C2=5, D2=10, and E2=3, what is the result?

medium

A. 53

B. 50

C. 80

D. 18

Solution

1. Step 1: Calculate multiplication part

   Multiply C2 and D2: 5 * 10 = 50.

2. Step 2: Add E2 value

   Add E2 (3) to 50: 50 + 3 = 53.

3. Final Answer:

   53 -> Option A

4. Quick Check:

   5*10+3 = 53 [OK]

Hint: Multiply first, then add according to order of operations [OK]

Common Mistakes:

• Adding before multiplying
• Ignoring E2 value
• Misreading cell values

4. You wrote the formula =A2*B2+ but Excel shows an error. What is the fix?

medium

A. Add a closing parenthesis

B. Add a cell reference after the plus sign

C. Change * to +

D. Remove the plus sign at the end

Solution

1. Step 1: Identify the formula error

   The formula ends with a plus sign (+) but no value or cell after it, causing a syntax error.

2. Step 2: Fix the formula by completing it

   Add a cell reference or number after the plus sign to complete the formula, e.g., =A2*B2+C2.

3. Final Answer:

   Add a cell reference after the plus sign -> Option B

4. Quick Check:

   Formula must be complete, no trailing operators [OK]

Hint: Never end formulas with an operator without a value [OK]

Common Mistakes:

• Removing operator without replacement
• Adding parentheses without matching pairs
• Changing operators incorrectly

5. You have a table with columns Quantity (A), Price (B), and Discount % (C). Which formula in D2 calculates the final price after discount?

hard

A. =A2*B2/C2

B. =A2*B2-C2

C. =A2*B2*(1-C2)

D. =A2*B2+C2

Solution

1. Step 1: Understand the calculation for discount

   Final price = quantity * price * (1 - discount rate). Discount % is in decimal form (e.g., 0.2 for 20%).

2. Step 2: Choose the formula matching this logic

   =A2*B2*(1-C2) correctly applies discount by multiplying total price by (1 - discount).

3. Final Answer:

   =A2*B2*(1-C2) -> Option C

4. Quick Check:

   Apply discount by multiplying total by (1 - discount) [OK]

Hint: Use (1 - discount) to reduce price by discount percent [OK]

Common Mistakes:

• Subtracting discount directly without multiplying
• Adding discount instead of subtracting
• Dividing by discount incorrectly