An Excel filter is any tool that hides rows you do not currently want to see while leaving the underlying data intact. The simple version is the AutoFilter — the dropdown arrows that appear at the top of each column when you press Ctrl+Shift+L on a data range.
The more powerful version is the FILTER dynamic array function in Microsoft 365 and Excel 2021, which returns a filtered subset of data into a new location without altering the source. Between those two ends sits a useful middle ground: Advanced Filter, slicers attached to Excel Tables, and Power Query for filtering before the data ever lands in a worksheet.
Knowing which filter to reach for matters more than people expect. AutoFilter is the right answer when you want to hide rows temporarily and you are working in the same sheet as the data. FILTER is the right answer when you want a clean filtered list to feed into another formula or chart.
Slicers are the right answer when non-technical colleagues will be clicking through filters in a shared dashboard. Power Query is the right answer when the filter logic is complex and needs to run every time the workbook refreshes. This guide walks through each in enough depth that you can confidently pick the right tool for the job.
One easily-missed point about Excel filters is that they are non-destructive. Whatever filter is applied, the underlying data remains untouched. Saving the workbook with active filters preserves the filter state — the next person opening the file will see exactly the same filtered view, with the same dropdown arrows and the same hidden rows. Re-saving without filters is as simple as toggling AutoFilter off before saving, but most workbooks benefit from preserving the active filter so the recipient sees the curated view the author intended.
AutoFilter shortcut: Ctrl+Shift+L. Clear AutoFilter: Alt+A+C. FILTER syntax: =FILTER(array, include, [if_empty]). Copy visible cells only: select range, Alt+; (semicolon), Ctrl+C. Advanced Filter: Data → Sort & Filter → Advanced. Slicers: Insert tab → Slicer when inside an Excel Table. FILTER returns a spilled array; AutoFilter hides rows in place.
AutoFilter is the most common Excel filter and the one nearly every user encounters first. Click anywhere inside a data range with column headers, then press Ctrl+Shift+L or click Data → Filter on the ribbon. Excel adds a small dropdown arrow to the top of each column.
Click any arrow and you get a panel offering several filtering options: a simple checkbox list of the unique values in the column, sort by ascending or descending, search to find specific entries, and a submenu of more advanced filters specific to the column's data type — Number Filters for numeric columns, Text Filters for text columns, Date Filters for date columns and Color Filters for any column with conditional formatting or manual colour fills.
AutoFilter hides rows that do not match the criteria rather than removing them. The row numbers on the left turn blue to indicate hidden rows, and the column dropdown arrows that have an active filter applied display a small filter icon to make active filters visible at a glance. Multiple columns can hold filters simultaneously, with the conditions joined by AND — the visible rows are the ones meeting every active criterion across every filtered column. Clearing all filters at once is done with Alt+A+C, a shortcut that more Excel users would benefit from memorising than they realise.
AutoFilter has a small but useful integration with Excel Tables. When data has been formally converted to an Excel Table via Ctrl+T, AutoFilter is enabled automatically and survives sorting, restructuring and pasting in a way that the looser AutoFilter on a plain range does not. The dropdown arrows live on the structured Table headers rather than on a worksheet row, which means rearranging the columns of a Table preserves the filter state cleanly. For any data range that grows over time, converting it to a Table first is the smallest step that makes filters more durable.
Equals, Does Not Equal, Greater Than, Less Than, Between, Top 10, Above Average, Below Average. The Top 10 filter accepts any number, including percentages — useful for top-quartile selections.
Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain. Wildcards work — * for any sequence, ? for a single character. Useful for code-pattern matching like SKU prefixes.
Equals, Before, After, Between, Tomorrow, Today, Yesterday, This Week, Last Week, Next Week, This Month, Year to Date, All Dates in the Period (e.g. Q1 across all years). One of the most useful built-in filter sets.
Filter by font colour or fill colour. Works with both manual cell formatting and conditional formatting outputs. Most useful when a workflow flags rows by colour and the user later needs to extract or count them.
Type to filter the value list as you type. Selecting Add Current Selection To Filter combines search results with previous selections, supporting cumulative inclusion of multiple filtered values.
Right-click a cell → Filter → Filter by Selected Cell's Value. Quick way to filter without opening the dropdown menu when you already know the row you want to match against.
The FILTER function arrived with the Microsoft 365 dynamic arrays update and changed how many users approach filtered output. Unlike AutoFilter, which hides rows in place, FILTER returns a new array of values to a different range — leaving the source data untouched. Syntax is =FILTER(array, include, [if_empty]). The first argument is the source range, the second is a TRUE/FALSE array that says which rows to include, and the optional third argument is the value to display if no rows match.
A simple example: =FILTER(A2:C100, B2:B100>1000) returns every row where column B is greater than 1000. Because dynamic arrays spill, the function fills as many rows as the result demands. Multiple criteria are combined with the multiplication operator for AND or the addition operator for OR — =FILTER(A2:C100, (B2:B100>1000)*(C2:C100="East")) returns rows where B is over 1000 AND C equals "East". The same logic with addition would be inclusive — rows where B is over 1000 OR C equals "East".
One pattern that emerged with FILTER is the live count of filtered rows. =ROWS(FILTER(A2:A100, B2:B100>1000)) returns the number of rows that pass the criterion, updating automatically as source data changes. Pairing this with conditional formatting on the count cell — turn the cell red if the count drops below a threshold — produces a useful at-a-glance signal in dashboard layouts without any custom VBA or refresh button.
=FILTER(A2:C100, B2:B100>1000) returns every row where the value in column B exceeds 1000. The output spills into adjacent cells automatically.
=FILTER(A2:C100, (B2:B100>1000)*(C2:C100="East")) returns rows meeting both conditions. The multiplication operator combines TRUE/FALSE arrays as AND. Wrap each condition in parentheses.
=FILTER(A2:C100, (B2:B100>1000)+(C2:C100="East")) returns rows meeting either condition. Addition combines TRUE/FALSE arrays as OR. Useful for inclusive selections that AutoFilter struggles to express in a single click.
=FILTER(A2:C100, B2:B100>1000, "No matches") returns the text "No matches" instead of #CALC! when no rows meet the criterion. Always include the third argument when output may legitimately be empty.
=SORT(UNIQUE(FILTER(A2:A100, B2:B100>1000))) returns a unique, sorted list of values from column A where B exceeds 1000. Dynamic arrays compose cleanly — each function passes its output to the next.
=FILTER(A2:C100, ISNUMBER(SEARCH("acme", A2:A100))) returns rows where column A contains the word "acme" anywhere in the cell. SEARCH is case-insensitive; FIND would be case-sensitive. Common pattern for free-text filtering.
Excel's Advanced Filter, available from Data → Sort & Filter → Advanced, fills the gap between AutoFilter and the FILTER function. It supports multiple criteria across multiple columns with explicit OR logic between criteria sets, and it can copy filtered results to a new location while leaving the source data intact. The cost is more setup — Advanced Filter requires a separate criteria range that mirrors the source column headers, with each row of the criteria range representing an OR condition and each column representing an AND condition within that row.
Advanced Filter is the right tool when you need OR logic across columns and you do not have access to the FILTER dynamic array function. It is also the right tool when you want a one-click extract of unique values from a column — checking the "Unique records only" box at the bottom of the Advanced Filter dialog produces a deduplicated output to a new range. Many Excel veterans still use Advanced Filter for these specific tasks even on modern Excel installations because the workflow is fast once memorised.
The criteria range for Advanced Filter follows a specific structure that trips up first-time users. Column headers in the criteria range must match the source data column headers exactly, including any trailing spaces or capitalisation. Conditions appear below the header — text values match exactly, comparison operators like =>100 work in numeric columns, and wildcards work in text columns. Each row of the criteria range adds an OR condition; multiple cells in one row form an AND condition. Setting up the criteria range correctly the first time is the only real friction; after that, Advanced Filter is fast.
Slicers are visual filter controls that attach to Excel Tables and PivotTables. Insert a slicer from the Insert tab when you have an active cell inside an Excel Table or PivotTable, choose the column to filter, and a small floating control panel appears with a button for each unique value in that column. Clicking a button filters the table; Shift-click adds to the selection; Ctrl-click selects multiple non-adjacent values. Slicers live as sheet objects and can be positioned anywhere on the sheet, sized to fit a dashboard layout, and styled to match a brand colour palette.
The reason slicers matter is that they make filtering accessible to non-technical users. A reader who would never click into the AutoFilter dropdown menu will happily click slicer buttons because the visual affordance is obvious. Dashboard designers building reports for executives, sales teams or operations colleagues regularly use slicers as the primary interaction layer over a hidden Excel Table. Multiple slicers on the same table compose with AND logic by default — clicking East in a region slicer and Q1 in a quarter slicer shows only East-region Q1 data.
One advanced slicer technique is the connected slicer that filters multiple PivotTables at once. After inserting a slicer, right-click it and choose Report Connections to attach the slicer to additional PivotTables built from the same data source. Clicking a slicer button then filters every connected PivotTable simultaneously, which is the trick behind most dashboards where one selection updates four or five panels in unison. Slicer styles, sizes and column counts can also be customised through the Slicer tab on the ribbon.
The most common use of an Excel filter is locating a small number of rows inside a large dataset — finding all transactions from a specific customer in a sales log, or every employee in a department in a payroll register. The second most common is producing a dynamic top-N list, where AutoFilter's Top 10 setting and the FILTER function's combination with SORT both shine. The third common case is deduplicating a list, where Advanced Filter's Unique Records option or the dynamic array UNIQUE function produces a clean output without affecting the original.
Less obvious but equally useful is the audit case: filtering for blanks to find missing data, filtering for outliers to spot data-entry errors, filtering for specific colour codes to extract rows that someone has visually marked. Each of these tasks is several clicks of pointing-and-clicking shorter through filters than through any formula approach. Filtering by cell colour in particular saves enormous time when a workbook has been annotated by hand and someone needs to extract the flagged rows quickly.
Filters also pair naturally with subtotals. With a filter active, functions like SUBTOTAL ignore hidden rows by default — meaning a SUBTOTAL above a filtered table shows the sum of only visible rows, updating live as the filter changes. SUBTOTAL takes a function code as its first argument: 9 for SUM, 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX. The 100-series codes (109, 101) ignore manually hidden rows in addition to filter-hidden rows. This is the cleanest way to display dynamic totals that respect the active filter.
Once a filter has hidden some rows, copying the visible range produces a small surprise: by default, Excel copies all the cells including the hidden ones. The fix is to select the filtered range, press Alt+; (semicolon) to limit the selection to visible cells only, then Ctrl+C to copy and paste into the destination. Excel for the web does not support the Alt+; shortcut directly, but the same outcome is reached by using the Go To Special dialog (F5 → Special → Visible cells only) before copying.
This visible-only behaviour is a common source of confusion when users paste filtered Excel data into another application like email or Word. The receiving application has no way of knowing some rows were hidden in the source, so it shows everything. Limiting the selection to visible cells before copying solves the problem cleanly. The same shortcut also works for hidden columns and for outline groups, making it useful well beyond the strict filtering case.
Sorting a filtered range also has a quirk worth knowing. Sorting respects the filter — only visible rows are sorted, and hidden rows retain their original positions. This is rarely what users actually want. The fix is to clear the filter before sorting, then reapply the filter afterwards. The Excel team has discussed automatic temporary unfilter behaviour during sort but the current behaviour persists across recent versions and is unlikely to change.
Quick in-place filtering of a single range. Fast, familiar, requires no formula. Best for ad-hoc analysis where you can see and modify the source data directly.
Multiple criteria across columns with OR logic, or unique value extraction. Useful when AutoFilter dropdowns are too constrained and FILTER is unavailable. Setup heavier but more powerful for one-off extractions.
Filtered output that feeds other formulas, charts or PivotTables. Updates automatically when source data changes. Best for dashboards and reports where the filtered range needs to be a live downstream input.
Visual filter controls for non-technical users. Attach to Excel Tables and PivotTables. Best for shared dashboards where the audience clicks rather than typing.
Filter applied before data lands in the worksheet, runs on every refresh. Best for complex multi-step transformations that need to be repeatable on changing source data.
Visual highlighting rather than filtering, but useful when you want to keep all rows visible while drawing attention to specific ones. Pairs well with Color Filters when later extraction is needed.
The most common AutoFilter problem is the disappearing dropdown arrows. This usually happens when the data range has a blank row in the middle that breaks the contiguous range Excel detected when you applied the filter. Removing the blank row and reapplying restores the full filter behaviour. Another common issue is dropdowns that appear but show only some unique values. AutoFilter's dropdown shows up to 10,000 unique values; ranges with more variety force users to use the search box instead of scrolling.
For the FILTER function, the most common error is #SPILL! caused by another value blocking the spill range. The fix is to clear the destination cells. The next most common is #CALC!, which appears when the filter returns no matching rows and no if_empty argument is supplied. Adding the third argument with a fallback string solves the issue. Filters on protected sheets are also a frequent gotcha — protection blocks AutoFilter unless the sheet protection settings explicitly allow filtering. Setting that permission at the time of protection is the cleanest fix.
Power Query is the right next step when filtering becomes a recurring task on changing data. Power Query records the filter steps as a script that runs every time the workbook refreshes, including across data sources like CSV files, databases and SharePoint lists. The filter dialog inside Power Query looks similar to AutoFilter but produces a transformation step rather than a temporary view. Many advanced Excel users now reach for Power Query first when their data refreshes regularly, leaving AutoFilter for ad-hoc exploration of static datasets.
Recording a Power Query script once and reusing it across many similar workbooks scales filter logic in ways no AutoFilter or formula approach can match.