How to Use the FILTER Function in Excel: Syntax, Multiple Criteria and the #CALC! Fix

If you want the dropdown arrows that hide rows in place, that's a different tool, and my guide on how to filter in Excel covers it. This page is about the FILTER function, which leaves your data alone and builds a live, filtered copy wherever you put the formula.

FILTER syntax

The syntax is =FILTER(array, include, if_empty), and the third argument is optional.

Argument	What it holds
array	The range you want to filter
include	A TRUE/FALSE test with the same height or width as array
if_empty (optional)	What to return when nothing matches

In Microsoft's first example, =FILTER(A5:D20,C5:C20=H2,""), array is the whole table, the include test checks column C against the product in H2 (Apple, in Microsoft's sheet), and the empty quotes tell Excel to return an empty string if there are no matches.

Always set the third argument, or you'll get #CALC!

This is the catch. If your filter can ever come back empty, use the if_empty argument. Without it, Excel returns a #CALC! error, because, as Microsoft puts it, Excel doesn't currently support empty arrays. That might not happen today, but change the product in H2 to one with no sales and the error appears. A short message such as "No results" is friendlier than empty quotes if other people use the sheet.

FILTER can also return an error if the include test itself contains one, such as #N/A or #VALUE, or contains something that can't be converted to TRUE or FALSE.

How results spill

FILTER returns an array. When it's the final result of a formula, it spills: Excel creates the right-sized range of cells for the results when you press Enter. So pick a cell with plenty of empty space below and to the right.

Two practical consequences, both from Microsoft. You don't need absolute references in these formulas, because each one lives in a single cell and spills into the cells next to it. And dynamic arrays only partly work between workbooks: linking to another file works only while both are open, and if you close the source, linked dynamic array formulas return #REF!.

FILTER and the other functions on this page are dynamic array functions, which Microsoft lists for Microsoft 365 and Excel 2021 onwards.

Filtering on more than one condition

This is where FILTER gets clever. You combine conditions with maths rather than with AND and OR functions:

Logic	Operator	Example
AND (both must be true)	Multiply with *	=FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"")
OR (either can be true)	Add with +	Put + between the bracketed conditions instead of *

Microsoft's AND example returns rows that have the chosen product and are in the chosen region. Each condition sits in its own brackets, and the multiplication means a row only survives if both tests are TRUE.

Sorting the results with SORT and SORTBY

Wrap FILTER in SORT and the results come out in order. Microsoft's example sorts the filtered rows by units in descending order.

SORT's syntax is =SORT(array, sort_index, sort_order, by_col), with everything after array optional. A sort_order of 1 is ascending, which is the default, and -1 is descending.

SORTBY works differently: =SORTBY(array, by_array1, sort_order1, ...) sorts by another range rather than by a column number. Microsoft's example sorts a table by region ascending and then by age descending. Every by_array must be one row high or one column wide and the same size as array, and a sort order other than 1 or -1 returns #VALUE!.

Microsoft's advice is to prefer SORTBY when sorting data in the grid, because it references columns rather than a column index and so it copes when you add or delete columns. That matches my experience: a SORT that relies on column 3 breaks the day someone inserts a new column 2.

Getting distinct values with UNIQUE

UNIQUE is FILTER's natural partner when you want a clean list. The syntax is =UNIQUE(array, by_col, exactly_once). by_col set to TRUE compares columns; FALSE or left out compares rows.

The third argument is the one people miss. Set exactly_once to TRUE and UNIQUE returns only the values that occur exactly once, which is the database meaning of unique. Microsoft's example uses it to list only the customers who had service one time. Leave it out and you get every distinct value instead.

Function	Does	Watch for
FILTER	Returns rows that meet a condition	#CALC! if no match and no if_empty
UNIQUE	Returns distinct values	exactly_once TRUE keeps only values that appear once
SORT	Sorts an array	sort_order 1 or -1
SORTBY	Sorts by another range	by_array same size as array

If you only need to strip repeats out of the data itself, rather than build a live list, removing duplicates is the quicker job.

How to write your first FILTER formula

1. Click an empty cell with space below and to the right, because the results spill.
2. Type =FILTER( and select the full data range.
3. Type a comma and build the test, for example C5:C20=H2
4. Type a comma and "" or a short message for no matches, close the bracket and press Enter.
5. Wrap it in SORT or SORTBY if the result needs ordering.

FILTER in Google Sheets is built differently

Don't copy an Excel FILTER straight into Sheets and expect it to behave. Google's version is FILTER(range, condition1, condition2, ...), with each extra condition as its own argument rather than an if_empty slot. The conditions must be exactly the same length as the range. When nothing matches, Sheets returns #N/A rather than #CALC!. And Sheets FILTER works on rows or on columns, not both at once; to do both, nest one FILTER inside another.

My verdict

FILTER replaces the old routine of filtering, copying and pasting with one live formula, and once you've used it you won't go back. Always set the third argument so an empty result never breaks your sheet, leave plenty of room for the spill, and wrap it in SORTBY rather than SORT if anyone else will be adding columns.

People who read this usually go on to the guide on how to find and replace in Excel.

Frequently asked questions

What does the FILTER function do in Excel?

It returns the rows of a range that meet criteria you define, and spills them into neighbouring cells as a live result.

Why does my FILTER formula show #CALC!?

Nothing matched and you didn't set the third argument, if_empty. Microsoft says Excel doesn't currently support empty arrays.

How do I filter by two conditions?

Multiply the bracketed conditions for AND, or add them for OR, inside the include argument.

Is the FILTER function the same as the Filter button?

No. The button hides rows in place; the FILTER function builds a separate, live copy of the matching rows.

Does FILTER work the same in Google Sheets?

No. Sheets takes each condition as its own argument, returns #N/A when nothing matches, and filters rows or columns, not both at once.