Dropdown lists from validation in Excel - Dashboard Guide

Dashboard Mode - Dropdown lists from validation

Goal

Create a dashboard that uses dropdown lists to filter sales data by region and product category. This helps users quickly see sales totals for their chosen filters.

Sample Data

Order ID	Region	Category	Sales
1001	East	Furniture	250
1002	West	Technology	450
1003	East	Office Supplies	150
1004	South	Furniture	300
1005	West	Office Supplies	200
1006	South	Technology	500
1007	East	Technology	400

Dashboard Components

• Dropdown List 1 (Region): Cell G2 uses Data Validation with list source: East, West, South
• Dropdown List 2 (Category): Cell G3 uses Data Validation with list source: Furniture, Technology, Office Supplies
• Total Sales (Filtered): Cell G5 shows total sales based on selected Region and Category using formula:
  =SUMIFS(D2:D8, B2:B8, G2, C2:C8, G3)
  This sums sales where Region matches G2 and Category matches G3.
• Filtered Data Table: Starting at I2, shows rows matching selected Region and Category using FILTER formula:
  =FILTER(A2:D8, (B2:B8=G2)*(C2:C8=G3), "No data")

Dashboard Layout

+----------------------+---------------------+
| Region: [Dropdown]   | Category: [Dropdown]|
+----------------------+---------------------+
| Total Sales: [G5]                        |
+-----------------------------------------+
| Filtered Data Table (I2:L8)              |
|                                         |
|                                         |
+-----------------------------------------+

Interactivity

When the user selects a Region in the dropdown at G2 and a Category in G3, the Total Sales cell (G5) updates automatically to show the sum of sales matching those choices. The Filtered Data Table below also updates to list only the orders matching the selected Region and Category. This lets users explore sales data easily by changing dropdown selections.

Self Check

If you change the Region dropdown to "West" and the Category dropdown to "Technology", what is the Total Sales value shown? Which rows appear in the Filtered Data Table?

Key Result

Dashboard with dropdown filters for Region and Category showing filtered total sales and matching data rows.