How to Filter in Excel (Sort, Filter, and Slicers)

To filter in Excel, select your data and go to Data → Filter (or press Ctrl + Shift + L). Small arrows appear in the header row — click one to choose which values to show. Filtering hides rows, it doesn’t delete them, so nothing is lost.

Turn AutoFilter on and off

1. Click any cell inside your data.
2. Go to the Data tab → Filter (or press Ctrl + Shift + L).
3. Dropdown arrows appear on each header.
4. Press Ctrl + Shift + L again to remove them.

Make sure your data has a single header row with no blank rows underneath. Excel guesses the range from the cells around your cursor — blank rows cut the range short and leave some data unfiltered.

Filtering by ticking values

1. Click the arrow in the column header.
2. Untick (Select All).
3. Tick the values you want to see.
4. Click OK.

Use the Search box inside the dropdown when the list is long — type a few letters and tick Add current selection to filter to keep adding.

Tip: if you can’t tick anything, or the list looks odd, your data probably has blanks or mixed types in that column.

Filtering by condition (number, text, date)

The dropdown also has Number Filters, Text Filters, or Date Filters depending on the column. Those give you proper conditions:

Custom Filter lets you combine two conditions with AND/OR — e.g. Sales greater than 500 AND Region contains “West.”

The classic trap: filtering for “contains north” also matches Northampton and Northwind. Use Equals for an exact match when the wording matters.

Reapplying and clearing filters

• Reapply after editing data: Data → Reapply (or Ctrl + Alt + L).
• Clear a single column: click its arrow → Clear Filter from [column].
• Clear everything: Data → Clear.
• Remove the arrows entirely: Ctrl + Shift + L.

Filtered-out rows are hidden, not gone. Select all and Delete while a filter is active and you’ll only delete the visible rows — a very common way to lose data by accident.

Filtering vs sorting

These get confused, and they do different jobs:

See how to sort in Excel for the sorting side. You can filter and sort the same column.

Filters and formulas

Hidden rows are still calculated. SUM, AVERAGE and friends ignore the filter and total everything.

To total only what’s visible:

=SUBTOTAL(109, B2:B100)     // 109 = SUM, ignoring hidden rows
=SUBTOTAL(103, B2:B100)     // 103 = COUNTA, ignoring hidden rows

SUBTOTAL is the function that respects filters. That’s usually the right answer for a filtered total.

Slicers (the better filter)

A slicer is a floating set of buttons that filters the data with one click — much better than opening dropdowns.

1. Click inside your data. It must be a Table (Ctrl + T) or a PivotTable — slicers don’t work on a plain cell range.
2. Go to Insert → Slicer.
3. Pick the column, click OK.
4. Click buttons to filter. Multi-select with Ctrl or the multi-select toggle.

Slicers work on Tables and PivotTables. They make a filtered sheet feel like a dashboard — see how to make a dashboard in Excel.

Advanced filtering

For anything more complex, use Data → Advanced:

• Filter the list in place — hides rows like a normal filter, but with a criteria range.
• Copy to another location — extracts matching rows to a new area, leaving the original untouched.
• Unique records only — a quick way to produce a de-duplicated list. (For a full cleanup, see how to remove duplicates in Excel.)

Advanced Filter needs a small criteria range on the sheet: copy your header row somewhere, then type your conditions underneath. Conditions on the same row are AND; on separate rows they’re OR.

Common problems

FAQ

How do I filter in Excel quickly?

Click inside your data and press Ctrl + Shift + L. Arrows appear in the header row — click one to choose which values to show.

Does filtering in Excel delete rows?

No — it hides them. The data is still there. Be careful though: if you delete while a filter is active, only the visible rows are deleted.

How do I filter for multiple values in one column?

Click the column arrow, untick (Select All), then tick every value you want. Use the search box and Add current selection to filter for long lists.

Why won’t my Excel filter count correctly?

Because SUM and AVERAGE include hidden rows. Use SUBTOTAL instead — =SUBTOTAL(109, range) sums only the visible rows.

How do I filter and sort at the same time?

Both work on the same column: sort first or filter first, then use the other dropdown. You can sort within a filtered view — see how to sort in Excel.