# Using the Advanced Filter in Excel spreadsheets

Learn how the Advanced Filter in Excel works, where its criteria range goes, and how to copy matching rows to a new spot for a clean shortlist.

Using the Advanced Filter in Excel spreadsheets means filtering a list with criteria you write into cells instead of clicking dropdown arrows. The tool reads a criteria range you set up, compares it with your list range, and can either hide non-matching rows in place or copy the matching rows somewhere else. This page explains what the feature does, how to set the criteria range, and where it differs from the filter buttons on the ribbon. 

## What the advanced filter does 

The Advanced Filter in Excel compares every row of a list range against conditions you type into separate cells. Rows that meet the conditions stay visible or get copied out, and rows that fail are hidden or left behind. Because the conditions live in cells rather than in a menu, the same setup can be reused and edited without rebuilding the filter. The feature belongs to rule-based conditional filtering, where the rule is written down rather than clicked. 

## Setting up the criteria range 

A criteria range needs a header row that copies the column names of your list exactly, plus at least one row of conditions below it. Conditions written on the same row must all be true for a record to match. Conditions written on separate rows work as alternatives, so a record matches if it satisfies any one row. Keep a blank row between the criteria range and the list range so Excel reads the two areas as separate blocks. 

## Filter in place or copy results 

The Advanced Filter dialog offers two outcomes for the rows that match your criteria. Filter the list in place hides non-matching rows inside the original table, which suits a quick look at the data. Copy to another location places the matching rows in a destination range you name, which keeps the source intact and builds a separate shortlist. Choose the copy option when you need the result to sit beside other information. 

## Questions about the Excel Advanced Filter 

### What is the Advanced Filter in Excel? 

The Advanced Filter is an Excel command that filters a list using conditions stored in worksheet cells. You define a criteria range with matching column headers, and Excel compares each row of the list range against those conditions. It can hide the rows that do not match or copy the rows that do match to another area. The result is a filtered view driven by written rules rather than dropdown selections. 

### How do I use the Advanced Filter in Excel? 

To use the Advanced Filter, first type your column headers and conditions into an empty area, leaving a blank row before the list. Select a cell inside the list range, open the Data tab, and choose Advanced. Set the list range, the criteria range, and either filter in place or copy to another location, then confirm. Check the output and widen the criteria range if you add more condition rows. 

### Does the Advanced Filter work in Google Sheets? 

The Advanced Filter as a named command belongs to Excel, and Google Sheets does not offer the same dialog. Google Sheets handles conditional filtering through its filter views and formula functions that test each row against a rule. You can rebuild the same logic by writing conditions in cells and referring to them in a formula. The idea of rule-based filtering carries over, while the menu steps differ. 

### Can the Advanced Filter copy matching rows to another sheet? 

The Advanced Filter can copy matching rows, but the copy destination must sit on the sheet where you run the command. To place results on another sheet, copy them there after filtering, or point the destination at a range and move the output afterward. Keep the destination clear of existing data so the copied rows are not overwritten. Rebuilding the list range after a copy keeps later runs predictable.
