Interactive Reports with FILTER
Swipe to show menu
Instead of filtering raw data manually, use FILTER to build a dynamic report that updates automatically based on user inputs.
FILTER Structure
=FILTER(array, include, [if_empty])
array: dataset being returned;include: logical test controlling which rows are included;[if_empty]: fallback value if no rows match.
Formula used in this chapter:
=FILTER(Sales_Data!A2:H52, Sales_Data!E2:E52=J2, "No results found")
Sales_Data!A2:H52: dataset being returned;Sales_Data!E2:E52=J2: filtering condition;"No results found": fallback output.
AND and OR Logic in FILTER
AND logic uses multiplication:
(Condition1) * (Condition2)
Both conditions must return TRUE.
OR logic uses addition:
(Condition1) + (Condition2)
At least one condition must return TRUE.
Inside Dynamic_Reports, add:
| Cell | Value |
|---|---|
| J1 | Selected Region |
| J2 | North |
Inside K2, type:
=FILTER(Sales_Data!A2:H52, Sales_Data!E2:E52=J2, "No results found")
Press Enter. The report now displays only rows matching the selected region.
Change the value inside J2.
Examples:
South
East
Notice that the report updates automatically.
Add:
| Cell | Value |
|---|---|
| J3 | Minimum Revenue |
| J4 | 3000 |
Update the formula inside K2:
=FILTER(Sales_Data!A2:H52, (Sales_Data!E2:E52=J2)*(Sales_Data!H2:H52>J4), "No results found")
(Sales_Data!E2:E52=J2): region condition;(Sales_Data!H2:H52>J4): revenue condition;*: AND logic.
Change the minimum revenue value inside J4.
Example:
5000
Notice that the report updates automatically and displays fewer rows.
Replace the formula with:
=FILTER(Sales_Data!A2:H52, (Sales_Data!E2:E52=J2)+(Sales_Data!H2:H52>J4), "No results found")
+: OR logic;- Rows are returned if at least one condition is true.
Enter a region that does not exist inside J2.
Confirm that Excel returns:
No results found
1. Why is FILTER preferred over Excel's built-in filter dropdowns for reporting?
2. What is the role of Sales_Data!E2:E52=J2?
3. What is the effect of replacing * with + in a FILTER condition?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
Interactive Reports with FILTER
Instead of filtering raw data manually, use FILTER to build a dynamic report that updates automatically based on user inputs.
FILTER Structure
=FILTER(array, include, [if_empty])
array: dataset being returned;include: logical test controlling which rows are included;[if_empty]: fallback value if no rows match.
Formula used in this chapter:
=FILTER(Sales_Data!A2:H52, Sales_Data!E2:E52=J2, "No results found")
Sales_Data!A2:H52: dataset being returned;Sales_Data!E2:E52=J2: filtering condition;"No results found": fallback output.
AND and OR Logic in FILTER
AND logic uses multiplication:
(Condition1) * (Condition2)
Both conditions must return TRUE.
OR logic uses addition:
(Condition1) + (Condition2)
At least one condition must return TRUE.
Inside Dynamic_Reports, add:
| Cell | Value |
|---|---|
| J1 | Selected Region |
| J2 | North |
Inside K2, type:
=FILTER(Sales_Data!A2:H52, Sales_Data!E2:E52=J2, "No results found")
Press Enter. The report now displays only rows matching the selected region.
Change the value inside J2.
Examples:
South
East
Notice that the report updates automatically.
Add:
| Cell | Value |
|---|---|
| J3 | Minimum Revenue |
| J4 | 3000 |
Update the formula inside K2:
=FILTER(Sales_Data!A2:H52, (Sales_Data!E2:E52=J2)*(Sales_Data!H2:H52>J4), "No results found")
(Sales_Data!E2:E52=J2): region condition;(Sales_Data!H2:H52>J4): revenue condition;*: AND logic.
Change the minimum revenue value inside J4.
Example:
5000
Notice that the report updates automatically and displays fewer rows.
Replace the formula with:
=FILTER(Sales_Data!A2:H52, (Sales_Data!E2:E52=J2)+(Sales_Data!H2:H52>J4), "No results found")
+: OR logic;- Rows are returned if at least one condition is true.
Enter a region that does not exist inside J2.
Confirm that Excel returns:
No results found
Thanks for your feedback!
