How to Filter in Excel: A Complete Guide to Sorting and Displaying the Data You Need

Filtering in Excel is one of the most practical skills you can have when working with spreadsheets. Whether you're managing a sales report, tracking inventory, or analyzing survey results, filters let you instantly narrow down thousands of rows to show only what's relevant — without deleting or rearranging anything.

What Does Filtering in Excel Actually Do?

When you apply a filter, Excel temporarily hides rows that don't match your criteria. The underlying data stays completely intact. You're not removing anything — you're just telling Excel what you want to see right now.

This makes filtering non-destructive and reversible. Turn a filter off, and all your original rows come back exactly as they were.

How to Turn On AutoFilter

The fastest way to start filtering is with AutoFilter, Excel's built-in tool.

1. Click anywhere inside your data range
2. Go to the Data tab in the ribbon
3. Click Filter

Small dropdown arrows will appear at the top of each column. These are your filter controls. Click any arrow to start filtering that column.

You can also use the keyboard shortcut Ctrl + Shift + L (Windows) or Command + Shift + F (Mac) to toggle AutoFilter on and off.

📋 Tip: Your data works best with AutoFilter when it has a clear header row at the top. Excel uses that row to label the filter dropdowns.

Filtering by Specific Values

Once the dropdown arrows are visible:

1. Click the arrow on the column you want to filter
2. A checklist of all unique values in that column appears
3. Uncheck "Select All" to deselect everything
4. Check only the values you want to see
5. Click OK

Excel immediately hides any row that doesn't match your selection. The row numbers on the left side turn blue — a visual indicator that a filter is active and some rows are hidden.

To see multiple values at once, simply check more than one item in the list.

Using Text, Number, and Date Filters

Beyond selecting individual values, Excel offers more precise filtering options depending on the type of data in your column.

To access these:

1. Click the column's filter dropdown
2. Hover over "Text Filters," "Number Filters," or "Date Filters" depending on your data
3. Choose your condition from the submenu
4. Enter your criteria in the dialog box that appears

For example, if you have a sales column and want to see only orders above $500, you'd use Number Filters → Greater Than → 500.

Filtering Multiple Columns at Once

You can apply filters to more than one column simultaneously. Each filter stacks on top of the previous one, narrowing results further.

For example:

• Filter Column A to show only "Electronics"
• Then filter Column B to show only entries from "January"

The result shows only rows that satisfy both conditions at the same time.

How to Use the Search Box in Filters

When a column has hundreds of unique values, scrolling the checklist is impractical. Excel's filter dropdown includes a search box at the top. Type part of a word or value, and the list narrows to matching items only — then check the ones you want.

This is especially useful for product names, customer names, or any long text-based list.

Filtering with Custom Criteria: Advanced Filter

🔍 For more complex scenarios, Advanced Filter gives you control that AutoFilter can't match:

• Filter based on criteria written in a separate cell range
• Use AND and OR logic across multiple columns simultaneously
• Copy filtered results to a different location on the sheet

To use it:

1. Go to Data → Advanced (in the Sort & Filter group)
2. Define your list range and criteria range
3. Choose whether to filter in place or copy results elsewhere

This is particularly useful when building reusable reporting templates or working with data that needs multi-condition logic that would be awkward to set up through the standard dropdowns.

Clearing and Removing Filters

To clear a filter on one column: click its dropdown arrow and select "Clear Filter From [Column Name]."

To remove all filters at once and show every row again: go to Data → Clear or press Ctrl + Shift + L to toggle AutoFilter off entirely.

What Affects How Filtering Behaves

Not all filtering situations are identical. A few variables shape what works smoothly and what gets complicated:

• Data structure: Filters work cleanest on contiguous data tables with no blank rows or merged cells. Merged cells in particular can interfere with how Excel identifies rows.
• Table format vs. plain range: Data formatted as an official Excel Table (Insert → Table) keeps filters persistent and extends them automatically as new rows are added — a meaningful difference from a plain data range.
• Excel version: Features like dynamic array filtering (using the FILTER function) are only available in Excel 365 and Excel 2019 and later. Older versions rely entirely on AutoFilter and Advanced Filter.
• File format: Files saved as .csv or opened in compatibility mode may behave differently than native .xlsx files.
• Dataset size: Filtering across hundreds of thousands of rows on an older or lower-spec machine can introduce noticeable lag, especially with complex criteria or volatile formulas nearby.

The FILTER Function: A Different Approach

In newer versions of Excel, there's also a FILTER function — a formula-based approach that extracts matching data into a separate range dynamically.

=FILTER(A2:C100, B2:B100="Electronics", "No results") 

This doesn't hide rows — it outputs matching results to a new location. It updates automatically when source data changes, which makes it powerful for dashboards and live reports. But it requires Excel 365 or Excel 2019+, and it behaves differently enough from AutoFilter that it's worth treating as a separate tool.

Whether AutoFilter, Advanced Filter, or the FILTER function fits best depends heavily on how your data is structured, what version of Excel you're working in, and whether you need results that update automatically or just a quick on-screen view of a subset of your data.