Skip to main content
Excel Cookbook
ExcelFILTERDynamic Arraydata extractionreportingdata analysis

The Problem: Drowning in Data?

Your manager needs a list of all sales from the "North" region for products exceeding $500 — by EOD. You're staring at thousands of rows. Manually applying filters, copying and pasting? That's like peeling potatoes by hand when you have a machine.

The FILTER function is your automated data slicer. It pulls exactly the rows you need, dynamically, without touching your original data.

The Ingredients: Understanding FILTER's Setup

The FILTER function acts as a smart sieve that selects rows matching your criteria.

=FILTER(array, include, [if_empty])

Parameter Description
array The range of data you want to filter (e.g., A2:D100).
include A Boolean array (TRUE/FALSE) that determines which rows to include.
[if_empty] Optional. What to display if no results match (e.g., "No results found").

The Recipe (Step-by-Step): Filtering Sales Data

Imagine this dataset in A1:D6:

Region Product Sales Rep
North Widget A $800 Alice
South Gadget B $200 Bob
North Doodad C $150 Charlie
East Widget A $600 Diana
North Thingy D $950 Eve

Goal: Show only "North" region sales.

  1. Select your destination cell (e.g., F2).
  2. Type the formula:
    =FILTER(A2:D6, A2:A6="North", "No results")
  3. Press Enter. The result spills automatically:
Region Product Sales Rep
North Widget A $800 Alice
North Doodad C $150 Charlie
North Thingy D $950 Eve

Advanced Recipe: Multiple Criteria

Want North region sales over $500? Combine conditions with * (AND logic):

=FILTER(A2:D6, (A2:A6="North") * (C2:C6>500), "No results")

This returns only Alice ($800) and Eve ($950).

For OR logic, use + instead of *:

=FILTER(A2:D6, (A2:A6="North") + (A2:A6="East"), "No results")

Pro Tips: Sharpen Your Skills

  • Always include [if_empty]: Without it, you'll get an ugly #CALC! error when no rows match. Use something like "No matching results".
  • Dynamic with cell references: Instead of hardcoding "North", reference a dropdown cell: =FILTER(A2:D6, A2:A6=H1) where H1 contains the region.
  • Combine with SORT: Wrap your FILTER in SORT for organized output: =SORT(FILTER(A2:D6, A2:A6="North"), 3, -1) sorts by Sales descending.

Troubleshooting: Common Errors

  • #CALC! Error: No matching results found. Add the [if_empty] parameter to handle gracefully.
  • #SPILL! Error: The output area is blocked by existing data. Clear the cells where results will spill.
  • #VALUE! Error: The include array size doesn't match the number of rows in array. Ensure both ranges have the same number of rows.
  • Wrong results with multiple criteria: Remember * = AND, + = OR. Wrap each condition in parentheses.

Related Recipes