array - Required. The range or array to filter (can be single or multiple columns).
include - Required. Boolean array (TRUE/FALSE) indicating which rows to include. Must match array row count.
if_empty - Optional. Optional. Value to return when no matches found. Prevents #CALC! error.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Region | Sales |
| 2 | Laptop | West | $5,200 |
| 3 | Mouse | East | $1,800 |
| 4 | Laptop | West | $6,100 |
| 5 | Formula: | =FILTER(A2:C4, B2:B4="West") Laptop | West | $5,200
Laptop | West | $6,100 |
Filter all West region records
Returns rows where column B = "West"
West region AND sales > $5,000
Use * for AND (both conditions must be TRUE)
West OR East region records
Use + for OR (either condition can be TRUE)
Show message when no results
Displays "No matches" instead of #CALC! error
Create a live sales dashboard where users select a region from a dropdown, and the Excel FILTER function automatically displays only that region's sales records. The filtered data updates instantly when the dropdown changes, showing salesperson names, products sold, and revenue amounts. This FILTER formula pattern is essential for sales managers who need regional performance visibility, executive dashboards that segment data by territory, and automated reporting systems that eliminate manual filtering. Works perfectly with data validation lists to create interactive Excel dashboards without VBA or pivot tables.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Salesperson | Product | Region | Sales |
| 2 | Alice | Laptop | West | $5,200 |
| 3 | Bob | Mouse | East | $1,800 |
| 4 | Carol | Keyboard | West | $2,400 |
| 5 | Filter Region: | West | =FILTER(A2:D4, C2:C4=E1, "No records") |
Filter inventory to find products that meet multiple urgent conditions: low stock levels AND high reorder costs. The FILTER function in Excel uses multiplication for AND logic, showing only items where stock is below threshold AND unit cost exceeds budget limits. Critical for warehouse managers monitoring critical inventory, purchasing teams prioritizing high-value reorders, and supply chain analysts preventing stockouts on expensive items. This Excel FILTER formula automatically flags urgent procurement needs without manual sorting or conditional formatting.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Stock | Unit Cost | Status |
| 2 | Widget A | 45 | $120 | |
| 3 | Widget B | 8 | $250 | |
| 4 | Widget C | 150 | $85 | |
| 5 | Filter: | =FILTER(A2:C4, (B2:B4<20)*(C2:C4>100)) URGENT |
❌ The Problem:
✅ Solution:
=FILTER(A2:C100, B2:B100="West")The include range (B2:B100) must have exactly the same number of rows as the array being filtered (A2:C100). Both have 99 rows in this corrected example. The Excel FILTER function requires this alignment to evaluate each row's criteria properly.
❌ The Problem:
✅ Solution:
=FILTER(A2:C100, (B2:B100="West")*(C2:C100>5000))Use multiplication (*) for AND logic and addition (+) for OR logic when filtering with multiple criteria. The Excel FILTER formula evaluates each row independently, so (condition1)*(condition2) creates an array where both must be TRUE (1×1=1), while any FALSE makes result 0.
❌ The Problem:
✅ Solution:
=FILTER(A2:C100, B2:B100="West", "No records found")Always include the if_empty parameter with a user-friendly message like "No matches" or "No records found". This provides clear feedback when filter criteria return zero results, making the Excel FILTER function more professional and easier to troubleshoot in shared workbooks.
Master these FILTER Function variants:
From basics to advanced - AI generates perfect formulas instantly.