Ravindra BagaleCourses & study guides Track your progress

Guides

How to Use the FILTER Function in Excel 365/2021

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

The FILTER function (Excel 365 / 2021) spills every row that matches a condition — a dynamic array, not a one-cell answer.

Chala mitrano! AutoFilter hides rows on the sheet. FILTER formula spills matching rows somewhere else. Why? Live Mumbai-only view that updates. How? One formula, empty space below. Stay with me.

Quick answer

Vertical steps:

  1. Confirm Microsoft 365 or Excel 2021.
  2. Keep a clean City / Channel / Amount table.
  3. Click an empty cell with free space below.
  4. =FILTER(A2:C4, A2:A4="Mumbai") (adjust ranges).
  5. Press Enter — matching rows spill.
  6. Point the city at a criteria cell to switch cities.
=FILTER(A2:C4, A2:A4="Mumbai", "No rows")

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Before — full table

City Channel Amount
Mumbai Online 100
Pune Online 40
Mumbai Store 50

After — FILTER City = Mumbai (spilled)

City Channel Amount
Mumbai Online 100
Mumbai Store 50
Before FILTER Full table before FILTER.

Before

After FILTER Mumbai rows spilled by FILTER.

After

Why FILTER?

  1. Results sit in a separate spill block — source stays visible.
  2. Change criteria → spill updates.
  3. Works with other dynamic arrays (SORT, UNIQUE) later.

What happens

  1. Excel tests each row’s City.
  2. Keeps Mumbai Online 100 and Mumbai Store 50.
  3. Drops Pune 40.
  4. Spills two rows starting at your formula cell.

Errors you will meet

Error Meaning Fix
#SPILL! Blocked cells in the spill range Clear cells below/right
#CALC! Often no match Add if_empty message; check spelling
#NAME? Excel too old for FILTER Use 365/2021 or AutoFilter

Vertical practice steps

  1. Type the three-row table.
  2. In E2: =FILTER(A2:C4,A2:A4="Mumbai","No rows").
  3. Confirm two spilled rows.
  4. Change to "Pune" → one row (40).
  5. Put city name in G1; use A2:A4=G1.
  6. Block the spill with a random value under E2 — see #SPILL!, then clear it.

FILTER vs AutoFilter

AutoFilter FILTER function
Hides rows on the same sheet Spills a copy elsewhere
Click UI Formula-driven
All Excel versions 365 / 2021

Ravindra Bagale's Tip

Leave a blank landing zone under the formula — FILTER is hungry for space. If #SPILL! appears, look for one stubborn value in the way, not a “broken” FILTER.

Practice task

  1. Ten sales rows, three cities.
  2. FILTER one city into a spill block.
  3. Add if_empty = "No sales for this city".
  4. Try FILTER with Channel = "Online" on the same data.

Learn it properly

Course lessons:

Related guides: SUMIF and SUMIFS · Create PivotTable · XLOOKUP for beginners

Samajla ka? FILTER spills matching rows on Excel 365/2021. Keep spill space clear. Aata practice sheets var try kara — Excel basics solid hotat.

Frequently asked questions

What is FILTER?

A dynamic array function that returns all rows (or columns) that match a condition, spilling into neighbouring cells.

Which Excel versions?

Microsoft 365 and Excel 2021. Older versions need AutoFilter or Advanced Filter.

What is #SPILL!?

Something blocks the spill range (a value in the way). Clear the cells below/right of the formula.

What is #CALC!?

Often no rows matched. Use the optional if_empty argument for a friendly message.

Multiple conditions?

Multiply conditions for AND, or use addition patterns for OR — practise AND first.

Course lesson?

Chapter 9 Dynamic Arrays — FILTER.