Summary  
This chapter covers using the FILTER function to extract subsets of data based on dynamic criteria, combining multiple logical tests with multiplication for AND and addition for OR, and supplying a fallback value when no rows match.  

General domain of usage  
Interactive reporting in spreadsheets


Instead of filtering raw data manually, use `FILTER` to build a dynamic report that updates automatically based on user inputs.

## FILTER Structure

```excel id="1"
=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:

```excel id="2"
=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:

```excel id="3"
(Condition1) * (Condition2)
```

Both conditions must return `TRUE`.

OR logic uses addition:

```excel id="4"
(Condition1) + (Condition2)
```

At least one condition must return `TRUE`.





Inside `Dynamic_Reports`, add:

| Cell | Value           |
| ---- | --------------- |
| J1   | Selected Region |
| J2   | North           |



Step 1 Create Input Cells


Inside `K2`, type:

```excel id="5"
=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.



Step 2 Build the First FILTER Report



Change the value inside `J2`.

Examples:

```text id="6"
South
East
```

Notice that the report updates automatically.



Step 3 Test the Report



Add:

| Cell | Value           |
| ---- | --------------- |
| J3   | Minimum Revenue |
| J4   | 3000            |



Step 4 Add a Second Input



Update the formula inside `K2`:

```excel id="7"
=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.



Step 5 Add an AND Condition


Change the minimum revenue value inside `J4`.

Example:

```text id="8"
5000
```

Notice that the report updates automatically and displays fewer rows.



Step 6 Test Dynamic Filtering



Replace the formula with:

```excel id="9"
=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.



Step 7 Test OR Logic


Enter a region that does not exist inside `J2`.

Confirm that Excel returns:

```text id="10"
No results found
```





Step 8 Test Fallback Behavior

Why is `FILTER` preferred over Excel's built-in filter dropdowns for reporting?

What is the role of `Sales_Data!E2:E52=J2`?


What is the effect of replacing `*` with `+` in a FILTER condition?

Build a complete Excel business analysis system using modern formulas, dynamic reporting, financial modeling, and scenario-based analysis. Throughout the course, you will transform raw transactional data into interactive dashboards, KPI engines, and scalable analytical models that reflect real-world business workflows.

Build the core Excel skills used in real business reporting workflows. Work with formulas, lookups, logical functions, conditional aggregation, error handling, and data cleaning while developing a structured sales analysis model.


Build dynamic Excel reporting systems using modern array functions and formula-driven workflows. Work with FILTER, SORT, SORTBY, UNIQUE, TRANSPOSE, spill ranges, interactive inputs, ranking systems, validation lists, and chart-ready data structures that update automatically as the dataset grows.


Build scalable relational Excel models using advanced lookup patterns and parameter-driven logic. Work with XLOOKUP, INDEX, MATCH, XMATCH, approximate matching, dynamic table relationships, scenario-based calculations, category-level aggregation, and flexible lookup architectures used in real analytical workflows.


Build advanced analytical and financial models that respond dynamically to changing data and assumptions. Work with time-based calculations, dynamic ranges, Named Ranges, installment modeling, investment analysis, amortization schedules, weighted aggregations, and scenario-driven financial metrics used in real business reporting systems.

Transform the workbook into a fully interactive business simulation system driven by scenarios, sensitivity analysis, and dynamic KPI logic. Build scenario engines, parameter-driven dashboards, sensitivity grids, advanced text-processing formulas, and centralized reporting layers that respond instantly to user inputs and business assumptions.

Interactive Reports with FILTER

FILTER Structure

AND and OR Logic in FILTER

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?

Interactive Reports with FILTER

FILTER Structure

AND and OR Logic in FILTER

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?

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?