How to Filter Data in Excel — step-by-step

If you need to view or extract specific rows from a spreadsheet, use Excel's filtering tools. For quick column drop-downs use AutoFilter, for complex extraction use Advanced Filter, and to return live, dynamic sets use the FILTER worksheet function; each approach is shown step-by-step below with a worked example and troubleshooting tips.

Choose the right filtering method

Which method you pick depends on the goal. AutoFilter is the fastest for on-the-spot row hiding and ad hoc queries. Advanced Filter is suited to extract a subset to another location or to apply multiple criteria ranges. The FILTER function (dynamic arrays) returns a live array you can shape with formulas.

AutoFilter - quick, interactive, works in place.
Advanced Filter - copy filtered results to another range, use criteria ranges.
FILTER function - returns a dynamic range, updates as data changes (requires Excel with dynamic array support such as Microsoft 365).

If you also need to reorder data before or after filtering, see How to Sort Data in Excel. If you plan to build dashboards or slice results visually, consider Build Interactive Dashboards with Slicers or using PivotTables; a good primer is How to Summarize Data with PivotTables. For dynamically returning ranges with formulas, read Use FILTER to return dynamic ranges in Excel.

How to apply AutoFilter - step-by-step

Select any cell inside your data table or select the entire header row.
On the Data tab choose Filter. Small drop-down arrows appear in each header cell.
Click a column arrow to see checked values, Text Filters, Number Filters, or Date Filters depending on the data type.
Pick built-in filters (for example Top 10, Between, Before/After) or use Custom Filter to set criteria like "contains", "does not equal", "greater than".
Clear a column filter by opening the arrow and choosing Select All, or remove all filters by choosing Filter on the Data tab again.

Using text, number and date filters

Text filters include options such as Contains, Begins With, and Custom. Number filters offer comparisons like Greater Than and Between. Date filters let you filter by relative periods such as This Month or by specific dates. Those tools match typical inspection and cleanup tasks in spreadsheets.

How to use Advanced Filter - step-by-step

Create a small criteria range separate from your data. Use the exact header names and write criteria under them. For OR logic, use separate rows; for AND logic, put criteria on the same row across columns.
Select your entire data range (including headers).
On the Data tab, choose Advanced in the Sort & Filter group.
In the Advanced Filter dialog pick Filter the list, in-place or Copy to another location. Specify the List range and the Criteria range. If copying, specify the destination cell for output.
Click OK to run the filter. Results will be hidden in-place or copied to the chosen location.

Advanced Filter is useful when you need a static snapshot of filtered rows in a separate area, or when your criteria use multiple fields with a mix of AND and OR conditions.

How to use the FILTER function (dynamic arrays)

The FILTER function returns an array of rows that match a logical test. It is live: if source data changes the output updates. A basic form is:

=FILTER(range, include, [if_empty])

Example: to return rows from A2:D100 where column B equals "East" and column C equals "Widget":

=FILTER(A2:D100, (B2:B100="East")*(C2:C100="Widget"), "No matches")

Use multiplication for AND logic and addition for OR logic. FILTER is best when you need formulas to drive dashboards or feed a PivotTable source that stays current.

Worked example: filter sales data for Region and Product

Suppose you have headers Date, Region, Product, Amount in A1:D1 and rows below.

Instant view with AutoFilter: Click any cell, Data > Filter. Open the Region arrow, choose East. Open Product arrow, choose Widget. You now see only matching rows in place.
Copy results with Advanced Filter: Create criteria cells with Region in F1 and Product in G1. Put East in F2 and Widget in G2. Select A1:D100, Data > Advanced, choose Copy to another location, set Criteria range to F1:G2 and Copy to H1. Click OK to paste matching rows starting in H1.
Dynamic results with FILTER: In another sheet enter =FILTER(A2:D100, (B2:B100="East")*(C2:C100="Widget"), "No matches"). The output spills and updates automatically when the source changes.

Common mistakes and troubleshooting

Not selecting the header row before applying AutoFilter - Excel may treat the first row as data and place filters incorrectly. Always ensure headers are present and selected.
Mixing data types in a column - numbers stored as text will not match number filters. Use VALUE or Text to Columns to fix types.
Using Advanced Filter without absolute ranges - when copying criteria or ranges, confirm the List range includes headers and rows you intend to evaluate.
Assuming FILTER exists in every Excel - FILTER requires Excel versions with dynamic arrays (for example Microsoft 365). If FILTER returns a #NAME error, use AutoFilter or Advanced Filter instead.
Forgetting to clear filters before sorting - apply or clear filters deliberately; filtered views can hide rows that still participate in sorts if not handled correctly.

Quick checklist before filtering

Confirm the dataset has a single header row with unique field names.
Check data types in each column and correct mixed entries.
Decide if you want in-place visibility, a copied snapshot, or a dynamic formula output.
Ensure any criteria ranges for Advanced Filter use exact header text and the correct AND/OR layout.
Back up the worksheet when copying filtered results over existing data.

Closing

Filtering data in Excel is a routine but powerful way to focus on the rows you need. Use AutoFilter for quick inspection, Advanced Filter when you need a copied subset, and the FILTER function for live formula-driven results. Pair filtering with sorting, PivotTables or slicers depending on the task: see How to Sort Data in Excel, How to Summarize Data with PivotTables, and Build Interactive Dashboards with Slicers for next steps.