Referencing other worksheets in Excel - Dashboard Guide

Dashboard Mode - Referencing other worksheets

Dashboard Goal

See total sales by product category using data from another worksheet.

Sample Data (Sheet: SalesData)

Order ID	Product	Category	Amount
1001	Notebook	Stationery	15
1002	Pen	Stationery	5
1003	Chair	Furniture	120
1004	Desk	Furniture	250
1005	Stapler	Stationery	8
1006	Lamp	Furniture	45

Dashboard Components

• KPI Card: Total Stationery Sales
  Formula: =SUMIF(SalesData!C2:C7, "Stationery", SalesData!D2:D7)
  Result: 28
• KPI Card: Total Furniture Sales
  Formula: =SUMIF(SalesData!C2:C7, "Furniture", SalesData!D2:D7)
  Result: 415
• Summary Table: Sales by Category
  Formula for Stationery: =SUMIF(SalesData!C2:C7, A2, SalesData!D2:D7) (where A2 contains "Stationery")
  Formula for Furniture: =SUMIF(SalesData!C2:C7, A3, SalesData!D2:D7) (where A3 contains "Furniture")
  Results:
  Stationery: 28
  Furniture: 415

Dashboard Layout

+----------------------+----------------------+
| Total Stationery      | Total Furniture      |
| Sales: 28            | Sales: 415           |
+----------------------+----------------------+
|        Sales by Category Table             |
|  Category   |   Sales                       |
|  Stationery |   28                          |
|  Furniture  |   415                         |
+--------------------------------------------+

Interactivity

Add a dropdown filter for Category. When you select a category, the KPI cards and summary table update to show sales only for that category.

Self Check

If you add a filter to show only Furniture category, which components update?

• The Total Furniture Sales KPI card shows 415.
• The Total Stationery Sales KPI card shows 0 or hides.
• The Sales by Category table shows only Furniture with sales 415.

Key Result

Dashboard shows total sales by product category using data referenced from another worksheet.