How to Use Excel Filter: Step-by-Step Guide (2026)

To turn on Excel filter, click any cell inside your data, press Ctrl+Shift+L, then click the small arrow that appears in any column header. This is the shortcut for Filtering in Excel. Pick values, a text rule, a number rule, or a colour. To clear everything, press Ctrl+Shift+L again. That is the whole shortcut. The rest of this guide walks you through every filter Excel has — the everyday AutoFilter dropdown, the Advanced Filter dialog for OR logic, the new FILTER() formula in Microsoft 365, and Slicers for tables and PivotTables — so you have one reference instead of four. If you want a full, hands-on walkthrough on real datasets, Excel training in Singapore covers every filter pattern below in a structured workshop.

A filter in Excel is like a torchlight in a dark room. The room is still full of things. You just stop looking at the parts you don’t need right now. After 24 years of training in Singapore, I see the same thing trip people up: they confuse the torchlight with rearranging the furniture, and that costs them either time or a wrong number in a report. By the end of this article, you will know which tool to reach for, and you will know the one formula (SUBTOTAL) that stops your filtered totals from quietly lying to your boss.

What “filter” actually means in Excel (and when not to use it)

A filter hides rows that do not match your rule. It does not delete them. It does not move them. When you remove the filter, every hidden row comes back exactly where it was. Think of it as a temporary view, not a permanent change.

This matters because filter is often confused with two other tools that look similar:

• Sort reorders rows. A sort actually changes the order of your data (until you sort again). A filter does not change the order — it just hides some of the rows.
• Group collapses adjacent rows under a summary row, using an outline level. Grouping helps when your data already has subtotals or sections. Filtering is what you want when you need to isolate any subset of rows by a rule.

Use a filter when you want to look at a slice of a dataset, do work on the slice, and then go back to the full picture. Do not use a filter when you want to permanently remove unwanted rows. For that, filter to find them first, then delete the visible rows, then clear the filter.

One more constraint to know. AutoFilter only works on a single contiguous range per worksheet. If you need filters on two separate ranges on the same sheet, convert at least one of them to an Excel Table (Insert > Table, or Ctrl+T). Tables carry their own filter row independent of any range-level filter.

How to turn on Excel filter — three ways

There are three ways to turn the filter on. They all do the same thing — small filter arrows appear in the header row of your data — but each path suits a different muscle memory.

The fastest path:

1. Click any cell inside your data. The cell does not need to be in the header row. Anywhere inside the range works.
2. Press Ctrl+Shift+L. Filter arrows appear in every column header.
3. Click an arrow to open the dropdown.
4. Choose values, a text rule, a number rule, a date rule, or a colour rule.
5. Press Ctrl+Shift+L again to remove all filters and clear the arrows.

Through the Data tab:

Data > Filter. Same result. Useful when your hands are already on the mouse.

Through the Home tab:

Home > Editing group > Sort & Filter > Filter. Slowest of the three, but it sits next to Sort, which is where many people look first.

One gotcha. For the filter to work properly, your data must have a header row. One row of labels at the top — Name, Date, Amount, and so on. If row 1 is data and not a header, Excel will treat the first data row as headers, which is almost never what you want. Add a header row first, then turn the filter on.

Filter by text, number, and date — the dropdown menus explained

Once the filter arrows are on, the real work happens in the dropdown menu. Click any arrow and you will see, top to bottom: Sort options, “Sort by Colour”, “Clear Filter”, “Filter by Colour”, a Text / Number / Date Filters submenu (the label changes based on the column’s data type), a search box, and a checkbox list of every unique value in that column.

Text filters

For columns that hold text — names, regions, product codes — the submenu is called Text Filters. It gives you:

• Equals / Does Not Equal — exact match (or anti-match)
• Begins With / Ends With — partial match anchored to one end
• Contains / Does Not Contain — substring match anywhere in the cell
• Custom Filter — combine two rules with AND or OR

The fastest text filter is the search box at the bottom of the dropdown. Type a few characters. The checkbox list filters in real time. Press Enter, and only matching rows remain. Extremely easy once you’ve done it once.

Number filters

For numeric columns — amounts, counts, scores — the submenu becomes Number Filters:

• Equals / Does Not Equal / Greater Than / Less Than / Between
• Top 10 (which is misleadingly named — it lets you pick the top or bottom N rows, by count or by percent, so “Top 5 by percent” and “Bottom 20 by count” are both valid)
• Above Average / Below Average
• Custom Filter for compound AND/OR rules

The “Between” option is the one most people underuse. Picking “Between 10,000 and 20,000” is the cleanest way to ask “show me mid-range invoices” without setting up two separate filter rules.

Date filters

For columns Excel recognises as dates, the submenu becomes Date Filters. This one is more powerful than people expect:

• Equals / Before / After / Between specific dates
• Today / Yesterday / Tomorrow
• This Week / Last Week / Next Week, and the same for Month, Quarter, and Year
• Year to Date, All Dates in the Period (any month, any quarter)
• Custom Filter for “before X and after Y”

Date filters only work if Excel actually recognises the column as a date. If your dates are stored as text (which is common when you paste from a system export), the submenu will say “Text Filters” instead. Convert them to dates first (Data > Text to Columns is the usual fix) and the date submenu appears. After 24 years of training Singapore professionals, this is the single most common reason a “date filter” doesn’t work the way someone expects.

Filter by colour, icon, and blanks

If you have conditionally formatted a column — coloured cells, coloured text, or icon sets like up/down arrows or traffic-light circles — Excel can filter by the format itself, not just by the underlying values.

To filter by colour:

1. Click the filter arrow on the column.
2. Choose Filter by Colour from the dropdown.
3. Pick the cell colour, font colour, or icon you want to keep visible.

This is the right tool when someone has used colour to mark exceptions — flagged rows for review, late deliveries, overspends — and you want to look at only those. It also works in reverse. Filter by the “non-flagged” colour to focus on the clean rows. Your boss will appreciate the speed.

Filtering blanks:

The checkbox list at the bottom of every filter dropdown includes a (Blanks) entry, if the column has any empty cells. Tick it on its own to see only the rows where that column is empty. A fast way to find missing data. Untick it to hide blank rows. If (Blanks) does not appear, the column has no empty cells.

Wildcards: filter for “starts with”, “contains”, and partial matches

The AutoFilter search box and the Custom Filter dialog both accept two wildcard characters that make pattern matching much faster:

• ? — matches any single character. Searching b?t finds bat, bit, but, and bot.
• * — matches any number of characters (including zero). Searching inv* finds invoice, invest, invader, and inventory.
• ~ — escapes a literal ? or *. Searching Q3~? finds “Q3?” exactly (with the literal question mark).

A practical example. In a list of invoice references like INV-2026-001, INV-2025-099, REF-2026-014, searching INV-2026-* returns only 2026 invoices that start with INV. Searching *-2026-* returns every reference from 2026 regardless of prefix. These are not full regular expressions. Wildcards only work at the level Excel supports. But for the everyday “starts with / contains / ends with” patterns, they are enough — and they save you from setting up a Custom Filter dialog every time.

Excel Advanced Filter — when the dropdown isn’t enough

AutoFilter has one structural limit. Every rule in the dropdown joins with AND. You can say “Region = APAC AND Amount > 10,000”. You cannot say “Region = APAC OR Region = EMEA”. For that, you need Advanced Filter.

Advanced Filter takes a separate criteria range — a small block of cells, somewhere on the sheet, that describes the rule. Set it up like this:

1. Above or below your data, create a copy of the header row.
2. Under that copy, write the criteria. Values on the same row are joined with AND. Values on different rows are joined with OR.
3. Select any cell in your data, then choose Data > Advanced (in the Sort & Filter group).
4. In the dialog, set the List range (your data) and the Criteria range (the block you just built).
5. Choose Filter the list, in-place to hide non-matching rows, or Copy to another location to write the result into a fresh range.

A worked example. Suppose your headers are Region, Amount, Status. You want everything in APAC over $10,000, plus everything in EMEA over $50,000. Your criteria range looks like this:

Two rows, joined by OR. Exactly what AutoFilter cannot do.

Advanced Filter also has a Unique records only checkbox, which gives you a deduplicated copy of the data. Useful when you need a clean lookup list. And the “Copy to another location” option is the secret weapon. It lets you keep the original data untouched while pulling a filtered subset into a fresh range for a report. If you want to take this further, the VLOOKUP and XLOOKUP guide shows how to feed a filtered list into lookups without breaking the source.

If you want hands-on practice with Advanced Filter criteria ranges, copy-to-location, and the rest of the data-handling stack on real datasets, structured Excel training walks through it step by step. The course is WSQ-funded and SkillsFuture-eligible, which keeps the out-of-pocket cost low.

The FILTER() formula — dynamic, spill-range filtering

Microsoft 365 and Excel 2021 introduced a function called FILTER() that does what AutoFilter does, but as a formula. The result is dynamic. Change the underlying data or the criteria, and the filtered output updates by itself.

The syntax is:

=FILTER(array, include, [if_empty])

• array — the data to filter (a range, like A2:D500).
• include — a logical test that returns TRUE/FALSE for each row of the array.
• if_empty — optional. What to show if nothing matches (a message like “No records found”, or just "" for blank).

A simple example. To pull every invoice from APAC out of A2:D500, where column B is Region:

=FILTER(A2:D500, B2:B500="APAC", "No records found")

The result spills into the cells below the formula automatically. No copy-paste. No AutoFilter. Just live, dynamic output.

Multiple criteria. Boolean algebra joins conditions:

• AND — multiply with *: =FILTER(A2:D500, (B2:B500="APAC") * (C2:C500>10000))
• OR — add with +: =FILTER(A2:D500, (B2:B500="APAC") + (B2:B500="EMEA"))

Sorting the output. Wrap FILTER inside SORT to order the spill range:

=SORT(FILTER(A2:D500, B2:B500="APAC"), 3, -1)

That sorts the filtered result by the third column, descending. How good is that?

When to use FILTER() instead of AutoFilter. Use the formula when the filtered output has to feed another formula, a chart, or a dashboard cell that updates on its own. Use AutoFilter when you just want to look at a slice in place. For dashboard and reporting work where filters drive downstream visuals, advanced data analytics and visualisation with Excel covers FILTER, SORT, UNIQUE, and the whole dynamic-array family.

Slicers: the click-button filter for Tables and PivotTables

Slicers are filter buttons that float on the worksheet instead of sitting hidden inside a dropdown. They are the cleanest way to filter a Table or a PivotTable when other people will be using the sheet — non-Excel users find slicer buttons obvious in a way that little dropdown arrows are not. If you have not yet built one, the pivot table guide walks through the underlying structure that slicers attach to.

To add a slicer:

1. Click anywhere inside an Excel Table or PivotTable.
2. Go to Insert > Slicer (or PivotTable Analyze > Insert Slicer if you started from a Pivot).
3. Tick the column(s) you want as slicers. Each column becomes a separate slicer panel.
4. Click a button to filter to that value. Ctrl+click for multi-select. Click the “Clear Filter” button at the top right of the slicer to reset.

Connecting slicers to multiple PivotTables. If you have two or three Pivots driven by the same dataset, one slicer can filter all of them at once. Right-click the slicer, choose Report Connections, and tick every Pivot you want it to control. This is how interactive dashboards work — one slicer changes every chart on the page in one click. Fantastic for management reports.

Timeline slicers are a date-only variant. Insert > Timeline gives you a draggable slider for filtering Pivot data by date range. Useful when “this month versus last month” is the kind of question you ask every week.

For Pivot-driven dashboards, slicers, and the Power Query / Power Pivot stack that sits behind them, Power Query and Power Pivot for deeper analysis is the natural next step up.

Sort + Filter together — and why SUBTOTAL is the missing piece

Sorting inside a filter dropdown is the most common workflow people use without realising it. Every filter dropdown has Sort A→Z (or 1→9 for numbers) and Sort Z→A (or 9→1) at the very top of the menu. Click either, and the entire dataset reorders by that column — keeping all the other columns in lockstep, which is the part that matters.

Sort 1 → 9 inside a filter. For a numeric column, the labels in the dropdown change from “Sort A to Z” to “Sort Smallest to Largest” (which Excel writes as 1→9). Same option, smarter label.

The trap with sort + filter. Sort reorders every row, including the ones currently hidden by another filter. If you filter to APAC only and then sort by amount, then clear the filter, all your data is now sorted by amount — including the rows that were hidden when you sorted. This is sometimes what you want, and sometimes not. If you only want to reorder the visible rows, copy them to a new sheet first.

Why SUM gives the wrong number on a filtered list

Here is the failure mode that catches almost everyone the first time. You filter a 12,000-row register down to the rows you care about. You type =SUM(B:B) at the bottom. You get a number that includes every hidden row. SUM ignores filters entirely. And of course your boss will then be unhappy, because the total you reported doesn’t tie to the filtered list on screen.

The fix is SUBTOTAL — a function specifically designed to respect filtered rows. SUBTOTAL is like a torchlight that only counts what the torchlight can see. The syntax is:

=SUBTOTAL(function_num, range)

Where function_num picks the operation: 9 for SUM, 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX, 5 for MIN, and so on. To total only the visible rows after a filter:

=SUBTOTAL(9, B2:B12000)

Better still, use =SUBTOTAL(109, B2:B12000). The 100-series codes also ignore manually hidden rows, not just filter-hidden ones. For any reconciliation, audit roll-up, or “total of what I’m currently looking at” formula, SUBTOTAL is what you want. Not SUM. The SUMIF and SUMIFS guide covers the other half of conditional totals — when you want a criterion-based sum that ignores the visible filter entirely.

This matters more than it sounds. A reconciliation that uses SUM on a filtered list will quietly report the total of the entire dataset, not the filtered slice — and you will not notice until someone in the audit team does. For finance and HR teams who work filtered ranges every week, Excel for HR professionals covers the SUBTOTAL pattern alongside the rest of the filtering and reporting workflow.

Frequently asked questions

What’s the keyboard shortcut to turn filter on or off in Excel?

Ctrl + Shift + L toggles filter arrows on and off in Windows. On a Mac it is Cmd + Shift + F. The shortcut works from any cell inside your data — you do not need to click the header row first. There is also Alt + D + F + F, an older sequence inherited from Excel’s menu-key days, which does the same thing. If the shortcut does nothing, check that your cursor is actually inside a data range. Pressing it from an empty cell does not turn filters on.

How do I sort numbers 1 to 9 inside a filter?

Click the filter arrow on the numeric column you want to sort. The two options at the top of the dropdown will say “Sort Smallest to Largest” and “Sort Largest to Smallest” (which Excel labels 1→9 and 9→1 in older versions). Click the first one to sort ascending, the second to sort descending. The whole dataset reorders, and every row stays aligned with its other columns. If the labels say “Sort A to Z” instead, Excel is treating the column as text. Convert it to numbers first (Data > Text to Columns, then choose General) and the labels switch.

How do I count or sum only the visible filtered rows?

Use the SUBTOTAL function, not SUM or COUNT. The formula is =SUBTOTAL(function_num, range). Set function_num to 9 for SUM, 1 for AVERAGE, 2 for COUNT (numbers only), 3 for COUNTA (all non-blank), 4 for MAX, 5 for MIN. So =SUBTOTAL(9, B2:B12000) sums only the visible cells in B2:B12000 after a filter. The 100-series codes (109 instead of 9, 102 instead of 2, and so on) also ignore manually hidden rows. Plain SUM and COUNT include every row, hidden or not. That is the most common reason filtered totals look wrong.

How do I filter to show only duplicates (or remove duplicates)?

To find duplicates first, select the column, then Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values. Excel will colour every duplicate. Now turn the filter on and use Filter by Colour to keep only the highlighted (duplicate) rows. To remove duplicates permanently instead, select the data range, then Data > Remove Duplicates and tick the columns Excel should match on. Use Conditional Formatting plus filter when you want to review duplicates before deleting. Use Remove Duplicates when you trust the rule and want them gone in one step.

Why is the filter dropdown greyed out, or showing the wrong list of values?

The most common cause is mixed data types in the column. Some cells are numbers, some are text that looks like numbers. Excel can only run one filter type per column (text or number, not both), so it picks one — and the other type’s values either disappear or behave oddly. Select the column, run Data > Text to Columns > Finish to force all cells to the same type. Two other causes worth checking: the cursor is sitting outside the filtered range, or another sheet has the filter range scoped to a name that no longer matches your current data. Re-applying the filter (Ctrl+Shift+L twice) usually resets it.

What’s the difference between Filter, Advanced Filter, the FILTER() formula, and Slicers?

Four tools, four jobs. AutoFilter (the dropdown arrows) is for quick, in-place filtering of one range with AND rules. The everyday tool. Advanced Filter is for OR logic across criteria rows, and for copying the filtered output to a new location without touching the source. FILTER() is a formula (Microsoft 365 / Excel 2021+) that produces a live, dynamic filtered result. Use it when downstream formulas, charts, or dashboards need to update automatically. Slicers are big visual filter buttons that sit on the worksheet. Use them on Tables and PivotTables when other people will be reading or driving the file. Pick AutoFilter for personal work, Advanced Filter for OR rules and one-off extracts, FILTER() for dashboards, Slicers for shared files.

I hope this clears up which filter to reach for, and when. Do try the SUBTOTAL formula on your next filtered report — it’s the one tip from this guide most likely to save you from a quietly wrong number. Open a workbook now, turn on a filter with Ctrl+Shift+L, and try one of the techniques above. Give it a try this week and you will not go back to typing SUM on a filtered list. When you are ready to put this into a structured course, Excel training is the place to start.