Advanced filtering criteria in Excel - Dashboard Guide

Dashboard Mode - Advanced filtering criteria

Dashboard Goal

Find and analyze sales records that meet multiple complex conditions, such as sales above a certain amount, specific regions, and product categories, to help focus on key business areas.

Sample Data

Order ID	Product	Category	Region	Sales	Order Date
1001	Desk Chair	Furniture	East	250	2024-01-15
1002	Pen Set	Office Supplies	West	45	2024-02-10
1003	Monitor	Technology	East	300	2024-03-05
1004	Notebook	Office Supplies	South	60	2024-01-20
1005	Desk Lamp	Furniture	West	120	2024-02-25
1006	Keyboard	Technology	East	80	2024-03-15
1007	Paper Clips	Office Supplies	North	15	2024-01-30

Dashboard Components

• KPI Card: Total Sales (Filtered)
  Formula: =SUMPRODUCT((D2:D8="East")*(C2:C8="Technology")*(E2:E8>100), E2:E8)
  Explanation: Adds sales where Region is East, Category is Technology, and Sales are greater than 100.
  Result: 300
• Filtered Sales Table
  Formula (helper column F): =AND(D2="East", C2="Technology", E2>100)
  Filter applied on column F = TRUE to show only matching rows.
  Shows rows with Order ID 1003 only.
• Count of Filtered Orders
  Formula: =COUNTIFS(D2:D8,"East", C2:C8,"Technology", E2:E8,">100")
  Result: 1
• Average Sales of Filtered Orders
  Formula: =AVERAGEIFS(E2:E8, D2:D8, "East", C2:C8, "Technology", E2:E8, ">100")
  Result: 300

Dashboard Layout

+----------------------+-----------------------+
| Total Sales (Filtered) | Count of Filtered Orders |
+----------------------+-----------------------+
| Average Sales (Filtered)                      |
+----------------------------------------------+
| Filtered Sales Table (shows matching rows)   |
|                                              |
+----------------------------------------------+

Interactivity

User can change filter criteria by editing the formulas or helper column conditions, for example changing Region or Category or Sales threshold. When criteria change, the KPI cards and filtered table update automatically to reflect the new filtered data.

Self Check

If you add a filter to show only Region = West and Sales > 100, which components update and what are their new values?

• Total Sales (Filtered): Updates to 120 (Desk Lamp)
• Count of Filtered Orders: Updates to 1
• Average Sales of Filtered Orders: Updates to 120
• Filtered Sales Table: Shows only the row with Order ID 1005

Key Result

Dashboard shows sales filtered by multiple conditions like region, category, and sales amount with KPIs and filtered table.