Calculated fields in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing total sales, total cost, and profit for each product category.

📊 Data: You have sales data including Product Category, Units Sold, Unit Price, and Unit Cost.

🎯 Deliverable: Create a table with calculated fields for Total Sales, Total Cost, and Profit for each product category.

Sample Data

Product Category	Units Sold	Unit Price	Unit Cost
Electronics	100	50	30
Clothing	200	20	10
Electronics	150	50	30
Furniture	50	100	70
Clothing	100	20	10
Furniture	30	100	70

1

Step 1: Create a pivot table from the sales data.

Insert > PivotTable > Select the entire data range including headers > Place pivot table in a new worksheet.

Expected Result

A blank pivot table ready for configuration.

2

Step 2: Add 'Product Category' to the Rows area of the pivot table.

Drag 'Product Category' field to Rows area.

Expected Result

Pivot table rows show Electronics, Clothing, Furniture.

3

Step 3: Add a calculated field for Total Sales: Units Sold * Unit Price.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Total Sales > Formula: ='Units Sold' * 'Unit Price' > Add.

Expected Result

Calculated field 'Total Sales' appears in the pivot table.

4

Step 4: Add a calculated field for Total Cost: Units Sold * Unit Cost.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Total Cost > Formula: ='Units Sold' * 'Unit Cost' > Add.

Expected Result

Calculated field 'Total Cost' appears in the pivot table.

5

Step 5: Add a calculated field for Profit: Total Sales - Total Cost.

PivotTable Analyze > Fields, Items & Sets > Calculated Field > Name: Profit > Formula: ='Total Sales' - 'Total Cost' > Add.

Expected Result

Calculated field 'Profit' appears in the pivot table.

6

Step 6: Format the calculated fields as currency for better readability.

Select the calculated fields in the pivot table > Right-click > Number Format > Currency > OK.

Expected Result

Total Sales, Total Cost, and Profit values show with currency formatting.

Final Result

Product Category | Total Sales | Total Cost | Profit
---------------------------------------------------
Electronics      | $12,500     | $7,500     | $5,000
Clothing         | $6,000      | $3,000     | $3,000
Furniture        | $8,000      | $5,600     | $2,400

✓Electronics category has the highest total sales and profit.

✓Clothing has moderate sales and profit with lower unit prices.

✓Furniture has fewer units sold but higher unit prices, resulting in good profit.

Bonus Challenge

Add a calculated field to show Profit Margin as a percentage (Profit divided by Total Sales).

Show Hint

Create a calculated field with formula = Profit / Total Sales and format it as Percentage.

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