What Advanced Filter Does and When to Use It

Advanced Filter in Excel lets you pull rows from a spreadsheet that match conditions you set — without deleting anything or changing the original data. If you have a list of 500 transactions and need to see only the ones over $1,000 from a specific vendor, Advanced Filter shows you those rows in seconds. It works on the data you already have, so you can run it again with different conditions whenever you need to.

Advanced Filter is faster than sorting when you want to see a subset of data temporarily, and safer than manually deleting rows. It also handles multiple conditions at once — for example, "show me rent payments over $500 that happened in March" — which would take several steps with sorting alone.

Key Takeaways

  • Advanced Filter hides rows that don't match your conditions, leaving your original data untouched.
  • You set up conditions in a separate area of the spreadsheet called a criteria range, then tell Excel where to look and what to match.
  • The two main modes are "filter in place" (hides non-matching rows) and "copy to another location" (puts matching rows in a new spot).
  • Multiple conditions on the same row mean AND (all must be true); conditions on different rows mean OR (any can be true).

Setting Up Your Criteria Range

Before you run Advanced Filter, you need to tell Excel what you're looking for. Create a criteria range — a small table that holds your search conditions — somewhere empty on the same sheet, usually above or to the right of your data.

Copy the column headers from your data table into the first row of the criteria range. Under each header, type the value or condition you want to match. If your data has columns for Name, Amount, and Date, and you want rows where Amount is greater than 500, copy those three headers into your criteria range, then type >500 under the Amount column. Leave the Name and Date cells blank — blank means "any value is fine for this column."

For text, type the exact text or a partial match using wildcards: *rent* finds any cell containing the word "rent" anywhere in it. For numbers, use comparison symbols: > (greater than), < (less than), = (equal to), >= (greater than or equal to), <= (less than or equal to), <> (not equal to).

Running Advanced Filter in Place

Open the Data menu and click Advanced Filter (in some Excel versions, it's under Data > Filter > Advanced Filter). A dialog box appears with three fields: List Range, Criteria Range, and Copy To.

In List Range, select your entire data table including headers. Click the small box icon next to the field, then drag to select all your data, or type the range manually — for example, A1:D500. Press Enter or click the box icon again to confirm.

In Criteria Range, select the criteria table you just built, including headers and conditions. Again, use the box icon or type the range.

Leave Copy To empty. Check the box for Filter the list, in-place if it's not already checked. Click OK. Excel hides all rows that don't match your conditions. The row numbers turn blue to show that filtering is active. Your original data is still there — you're just not seeing it.

Copying Matching Rows to a New Location

If you want to keep the filtered results separate from your original data, use the "copy to another location" mode instead. Set up your List Range and Criteria Range the same way, but this time click in the Copy To field and select an empty area of the spreadsheet where you want the results to appear — for example, cell F1.

Do not check "Filter the list, in-place." Click OK. Excel copies all matching rows (including headers) to the location you chose, leaving your original data untouched and visible. This is useful when you want to save the results, send them to someone else, or compare filtered data side by side with the original.

Using Multiple Conditions with AND and OR Logic

When you put conditions on the same row of your criteria range, Excel treats them as AND — all conditions must be true for a row to match. If you type >500 under Amount and March under Month on the same row, you get only rows where the amount is over 500 AND the month is March.

When you put conditions on different rows, Excel treats them as OR — a row matches if any condition is true. If you type >500 under Amount on row 2 of your criteria range, and <100 under Amount on row 3, you get rows where the amount is over 500 OR under 100 (everything except amounts between 100 and 500).

You can combine AND and OR: put one set of conditions on row 2 and a different set on row 3. Excel returns rows that match all conditions on row 2, plus rows that match all conditions on row 3.

Clearing a Filter to See All Data Again

When you've finished looking at filtered results, open the Data menu and click Reset Filter or Clear Filter (the exact name depends on your Excel version). All hidden rows reappear. If you used "copy to another location," the copied results stay where you put them — resetting the filter doesn't delete them.

If you want to run Advanced Filter again with different conditions, just change the values in your criteria range and repeat the steps. You can reuse the same criteria range over and over.

Common Mistakes and How to Avoid Them

The most common mistake is forgetting to include headers in your criteria range. Advanced Filter looks at the header row to know which columns your conditions explore to. If your criteria range has no headers, Excel won't know what you're filtering for.

Another mistake is leaving extra blank rows or columns in your data table. Advanced Filter stops at the first blank row or column it finds, so if your data has a gap in the middle, Advanced Filter treats everything after the gap as a separate table. Clean up any blanks before you start, or select only the continuous block of data you want to filter.

If your text conditions aren't working, check your spelling and capitalization. Excel's text matching is usually case-insensitive, but wildcard patterns like *rent* must be exact. If you're looking for "Rent Payment" and type *rent* in lowercase, it should still find it — but *rentals* won't find "Rent Payment" because the word "rentals" doesn't appear in the cell.

Frequently Asked Questions

Can I use Advanced Filter on a table that has blank cells?

Yes, but blank cells can cause problems. If a column has blank cells scattered throughout, Advanced Filter treats them as values. If you want to find rows where a column is blank, type nothing in that column of your criteria range. If you want to exclude blank rows, use a criteria like <> (not equal to) with nothing after it, though this doesn't work in all Excel versions.

What's the difference between Advanced Filter and AutoFilter?

AutoFilter adds dropdown arrows to your headers and lets you click to choose which values to show. Advanced Filter requires you to set up a criteria range and is better for complex conditions, especially when you need AND and OR logic together. AutoFilter is simpler for quick, one-off filters.

Can I filter by date ranges?

Yes. In your criteria range, use comparison symbols: type >=1/1/2024 to find dates on or after January 1, 2024. For a range, use two rows: one with >=1/1/2024 and another with <=3/31/2024, which gives you all dates in the first quarter. Make sure your date column is formatted as a date, not text.

Does Advanced Filter work on filtered data?

If you've already applied AutoFilter or another Advanced Filter, running a new Advanced Filter works on the visible rows only, not the hidden ones. Clear any existing filters first if you want to search the entire dataset.

Can I save my criteria range and reuse it later?

Yes. Keep your criteria range on the same sheet as your data, or on a separate sheet. As long as you reference it correctly in the Criteria Range field, you can run the same filter again. Change the values in the criteria range when you want different results.