FILTER function in Google Sheets - Dashboard Guide

Dashboard Mode - FILTER function

Dashboard Goal

Find and display sales records for a specific product category and region to quickly analyze targeted sales data.

Sample Data

Order ID	Product	Category	Region	Sales
1001	Notebook	Stationery	East	120
1002	Pen	Stationery	West	80
1003	Chair	Furniture	East	300
1004	Desk	Furniture	South	450
1005	Marker	Stationery	East	60
1006	Table	Furniture	West	200
1007	Folder	Stationery	South	90

Dashboard Components

• Filter Input Cells:
  B10 for Category (e.g., "Stationery")
  B11 for Region (e.g., "East")
• Filtered Sales Table:
  Formula in B14:
  =FILTER(A2:E8, C2:C8=B10, D2:D8=B11)
  This shows all rows matching the chosen category and region.
• Total Sales for Filtered Data:
  Formula in B22:
  =SUM(FILTER(E2:E8, C2:C8=B10, D2:D8=B11))
  Calculates total sales for the filtered records.

Dashboard Layout

+----------------------+---------------------+
| Filter Inputs        | Filtered Sales Table |
| Category: [B10]      | +-----------------+  |
| Region:   [B11]      | | Order ID | ...   |  |
+----------------------+ +-----------------+  |
| Total Sales: [B22]                         |
+-------------------------------------------+

Interactivity

When you change the values in the filter input cells B10 (Category) or B11 (Region), the filtered sales table and total sales update automatically to show only matching records.

Self Check

If you set B10 to "Furniture" and B11 to "East", which rows appear in the filtered sales table and what is the total sales?

Answer: Only the row with Order ID 1003 (Chair, Furniture, East, 300) appears. Total sales is 300.

Key Result

Dashboard filters sales data by category and region using FILTER function and shows total sales.