# How to filter by a dropdown with an "All" option in Excel

Let each test pass when the dropdown says All: `=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))`.

**Excel:** `=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Department | Location |  | Department | All |
| Ana Ruiz | Finance | Remote |  | Location | Remote |
| Raj Patel | Sales | Chicago |  |  |  |
| Lee Chen | IT | Remote |  |  |  |
| Kim Ortiz | Sales | Remote |  |  |  |
| Mo Diaz | Finance | Chicago |  |  |  |
| Sam Cole | IT | Denver |  |  |  |
| Eve Lund | Marketing | Remote |  |  |  |
| Noor Ali | Sales | Denver |  |  |  |

Formula in E4: `=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))`

Result (spills 4 × 3): Ana Ruiz, Finance, Remote; Lee Chen, IT, Remote; Kim Ortiz, Sales, Remote; Eve Lund, Marketing, Remote

## How it works

Each filter is an OR of two tests: the row matches the dropdown, or the dropdown says All. `F1="All"` is a single TRUE, so it adds 1 to every row and the department test can't fail. The two pairs are multiplied, which is AND: a row needs to pass both filters.

Here the department is All and the location is Remote, so every remote employee comes back, whatever their department.

## Other ways

- `=FILTER(A2:C9,IF(F1="All",TRUE,B2:B9=F1)*IF(F2="All",TRUE,C2:C9=F2))`: IF spells out the All case. Same result; easier to read if you add more filters.
- `=FILTER(A2:C9,((B2:B9=F1)+(F1="All")+(F1=""))*((C2:C9=F2)+(F2="All")+(F2="")))`: Also treats an empty dropdown as All.

## Common mistakes

- Every OR pair needs its own brackets before you multiply. Without them, AND and OR mix in the wrong order.
- The word must be exactly what the dropdown offers. If the list says "(All)", test for "(All)".

## Questions

**How do I put All at the top of the dropdown list?**

Build the list in a spare cell with `=VSTACK("All",SORT(UNIQUE(B2:B9)))` and point Data Validation at it with #, such as `=$H$1#`.

**How do I add a third dropdown?**

Multiply another pair: `*((D2:D9=F3)+(F3="All"))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/filter-by-dropdown-with-all-option