SWITCH function in Excel - Dashboard Guide

Dashboard Mode - SWITCH function

Dashboard Goal

Help a small store quickly categorize products by type using the SWITCH function to assign product categories based on product codes.

Sample Data

Product Code	Product Name	Price
A1	Apple	1.20
B2	Bread	2.50
C3	Carrot	0.80
D4	Detergent	5.00
A2	Avocado	1.50
B3	Butter	3.00
X9	Unknown Item	4.00

Dashboard Components

• Category Column: Adds a new column to categorize products by code using SWITCH.
  Formula in D2:
  =SWITCH(A2, "A1", "Fruit", "A2", "Fruit", "B2", "Bakery", "B3", "Bakery", "C3", "Vegetable", "D4", "Cleaning", "Other")
  This formula checks the product code and assigns a category. If no match, it returns "Other".
• Total Price by Category: Calculates total price for each category using SUMIF.
  Example formula for Fruit total:
  =SUMIF(D2:D8, "Fruit", C2:C8)
• KPI Cards: Show total sales for each category:
  • Fruit Total: 2.70
  • Bakery Total: 5.50
  • Vegetable Total: 0.80
  • Cleaning Total: 5.00
  • Other Total: 4.00

Dashboard Layout

+----------------------+---------------------+
|      Product List     |   Category Totals   |
| +------------------+ | +-----------------+ |
| | Product Code     | | | Fruit: 2.70     | |
| | Product Name     | | | Bakery: 5.50    | |
| | Price           | | | Vegetable: 0.80 | |
| | Category (SWITCH)| | | Cleaning: 5.00  | |
| +------------------+ | | Other: 4.00     | |
+----------------------+ +-----------------+ +

Interactivity

Add a filter dropdown for Category. When a category is selected, the product list and total sales update to show only that category's products and totals.

This uses the Category column created by SWITCH as the filter basis.

Self Check

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

• The product list will show only Bread and Butter.
• The total sales KPI cards will update to show Bakery total as 5.50 and others as zero or hidden.

Key Result

Dashboard categorizes products by code using SWITCH and shows total sales by category.