Filtering data with AutoFilter in Excel - Dashboard Guide

Dashboard Mode - Filtering data with AutoFilter

Dashboard Goal

See sales data for specific regions and products easily by filtering the table using AutoFilter.

Sample Data

Order ID	Region	Product	Sales
1001	East	Apples	120
1002	West	Oranges	150
1003	East	Bananas	90
1004	South	Apples	200
1005	West	Bananas	130
1006	North	Oranges	170
1007	South	Bananas	110

Dashboard Components

• Sales Data Table: The main table with all sales records. Use AutoFilter on headers to filter by Region or Product.
• Total Sales: A KPI card showing total sales of visible rows.
  Formula: =SUBTOTAL(9, D2:D8)
  This sums only the visible sales after filtering.
• Count of Orders: A KPI card showing count of visible orders.
  Formula: =SUBTOTAL(3, A2:A8)
  This counts only visible rows after filtering.

Dashboard Layout

+----------------------+------------------+
| Sales Data Table     | Total Sales (KPI) |
| (with AutoFilter)    | Count of Orders   |
|                      | (KPI)            |
+----------------------+------------------+

Interactivity

Use the AutoFilter dropdown arrows on the Region or Product columns in the Sales Data Table to select which rows to show.

When you filter the table, the Total Sales and Count of Orders KPIs update automatically to reflect only the visible rows.

Self Check

If you filter the Region column to show only East, what will the Total Sales and Count of Orders show?

Answer: Total Sales = 120 + 90 = 210; Count of Orders = 2

Key Result

Dashboard shows sales data with AutoFilter to filter by Region or Product and updates total sales and order count accordingly.