Calculated fields in Excel - Dashboard Guide

Dashboard Mode - Calculated fields

Dashboard Goal

Understand total sales, average sales per order, and profit margin using calculated fields.

Sample Data

Order ID	Product	Quantity	Price per Unit	Cost per Unit
1001	Notebook	10	5	3
1002	Pen	20	2	1
1003	Backpack	5	20	12
1004	Calculator	3	15	10
1005	Folder	12	3	1.5

Dashboard Components

• Total Sales: Sum of Quantity * Price per Unit
  Formula: =SUMPRODUCT(C2:C6,D2:D6)
  Result: 10*5 + 20*2 + 5*20 + 3*15 + 12*3 = 50 + 40 + 100 + 45 + 36 = 271
• Average Sales per Order: Average of Quantity * Price per Unit per order
  Formula: =SUMPRODUCT(C2:C6,D2:D6)/COUNTA(C2:C6)
  Result: 271 / 5 = 54.2
• Profit Margin (%): Total Profit / Total Sales * 100
  Total Profit = Sum of Quantity * (Price per Unit - Cost per Unit)
  Formula for Total Profit: =SUMPRODUCT(C2:C6,D2:D6-E2:E6)
  Profit Margin formula: =SUMPRODUCT(C2:C6,D2:D6-E2:E6)/SUMPRODUCT(C2:C6,D2:D6)*100
  Calculate:
  Profit per order:
  1001: (5-3)*10=20
  1002: (2-1)*20=20
  1003: (20-12)*5=40
  1004: (15-10)*3=15
  1005: (3-1.5)*12=18
  Total Profit=20+20+40+15+18=113
  Total Sales=271
  Profit Margin=113/271*100=41.7%

Dashboard Layout

+----------------+-----------------------+
| Total Sales    | Average Sales per Order |
|     271        |          54.2          |
+----------------+-----------------------+
|          Profit Margin: 41.7%           |
+-----------------------------------------+
|               Sales Data Table           |
| (Order ID, Product, Quantity, Price, Cost)|
+-----------------------------------------+

Interactivity

Add a filter for Product type. When you select a product (e.g., "Pen"), the Total Sales, Average Sales per Order, and Profit Margin update to show only data for that product. The Sales Data Table also filters to show only matching rows.

Self Check

If you add a filter to show only orders where Quantity is greater than 10, which components update and what are their new values?

Answer: Orders with Quantity > 10 are 1002 (20 pens) and 1005 (12 folders).
Total Sales = (20*2) + (12*3) = 40 + 36 = 76
Average Sales per Order = 76 / 2 = 38
Profit per order:
1002: (2-1)*20=20
1005: (3-1.5)*12=18
Total Profit = 38
Profit Margin = 38 / 76 * 100 = 50%
All KPI cards and the data table update accordingly.

Key Result

Dashboard shows total sales, average sales per order, and profit margin using calculated fields from sales data.

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