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.