How to Filter Rows with the FILTER Function in Excel

Pull matching rows into a live list with FILTER — by category, by comparing two columns, and with a message when nothing matches.

You will learn to

• Return the rows that match a value
• Filter by comparing two columns row by row
• Show a message when nothing matches

How it works

FILTER(array, include, [if_empty]) returns the rows of array where include is TRUE. =FILTER(A2:A11,B2:B11="Beverages") lists every beverage, and the result spills down as far as needed. Unlike the Filter button, the formula updates as soon as the data changes and leaves the source untouched.

The include argument can compare two columns, such as C2:C11<D2:D11 for items below their reorder level, and the array can be several columns wide to return whole rows. If nothing matches, FILTER returns #CALC! — add a third argument like "All in stock" to show a friendly message instead.

=FILTER(A2:C11,C2:C11<D2:D11)