How to filter by a dropdown with an "All" option in Excel
Formula (Excel and Google Sheets)
=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))Let each test pass when the dropdown says All: =FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"))).
- Result on the example
- a 4-row list that spills from E4 in
E4 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E4. Change any input and the result updates, or edit the formula to experiment.
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.
=FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All")))Result{4 rows × 3 cols} "Ana Ruiz", "Finance", "Remote", "Lee Chen", …
A2:C98 rows × 3 cols: "Ana Ruiz", "Finance", "Remote", "Raj Patel", …A range: 8 rows × 3 columns.
B2:B98 rows × 1 col: "Finance", "Sales", "IT", "Sales", …A range: 8 cells in one column.
F1"All"The value in F1.
B2:B9=F1{8 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
F1="All"TRUEThe = operator tests whether the two sides are equal.
B2:B9=F1)+(F1="All"{8 rows × 1 col} 1, 1, 1, 1, …The + operator adds.
C2:C98 rows × 1 col: "Remote", "Chicago", "Remote", "Remote", …A range: 8 cells in one column.
F2"Remote"The value in F2.
C2:C9=F2{8 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
F2="All"FALSEThe = operator tests whether the two sides are equal.
C2:C9=F2)+(F2="All"{8 rows × 1 col} 1, 0, 1, 1, …The + operator adds.
B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"{8 rows × 1 col} 1, 0, 1, 1, …The * operator multiplies.
FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"))){4 rows × 3 cols} "Ana Ruiz", "Finance", "Remote", "Lee Chen", …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
A2:C98 rows × 3 cols: "Ana Ruiz", "Finance", "Remote", "Raj Patel", … - condition1
B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"{8 rows × 1 col} 1, 0, 1, 1, …
- range
In Google Sheets
The same formula works in Google Sheets: =FILTER(A2:C9,((B2:B9=F1)+(F1="All"))*((C2:C9=F2)+(F2="All"))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 4 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =FILTER(A2:C9,IF(F1="All",TRUE,B2:B9=F1)*IF(F2="All",TRUE,C2:C9=F2)) | spills 4 × 3 | 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=""))) | spills 4 × 3 | 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)".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Filter by a dropdown with an "All" option
E4, return the employees that match the department in F1 and the location in F2. When a cell says All, that filter should let every row through.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")).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.