Excel Filters: How to Sort and Show the Rows You Need

To filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data > Filter, then use the dropdown in a column heading to choose which rows to show. Filtering hides rows that do not match your choices; it does not delete them. For a formula-driven result in a separate area, Excel also has the FILTER function.

What filtering does in Excel

A filter temporarily narrows a list to records that meet conditions you choose. For example, a sales table can be filtered to show only the North region, only sales above a particular amount, or dates within a selected period. The headings and matching records remain visible, while the other rows are hidden from view.

That distinction matters: a filter is not the same as deleting unwanted records or making a permanent copy of a subset. Clear the filter and the hidden rows return. Filtering also differs from sorting. Sorting changes the order of records; filtering controls which records are displayed. You can use both together—for instance, show only North region sales and sort those rows from highest to lowest.

Excel’s column dropdowns are often called AutoFilter. They are useful for exploring a list without changing its underlying values. Filtering can affect what you see and what you select, so check whether a filter is active before copying, printing, or drawing conclusions from a total.

Turn on a filter for a range or table

For an ordinary range, click any cell within the data and choose Data > Filter. Excel usually identifies the contiguous data range and adds a dropdown to each header. If the selected data has blank rows or columns splitting it into separate blocks, select the complete range first so Excel includes the intended records. A good source range has one header row, one field per column, and no completely blank rows within the records.

You can also make the range an Excel table. Select a cell in the data, choose Insert > Table, confirm the range, and indicate whether it has headers. A table automatically provides filter dropdowns in its header row, expands as you add adjacent records, and carries formatting and formulas into new rows. Tables are often a convenient choice for data that will grow, while a regular filtered range can suit a small, fixed list.

On Windows, Ctrl+Shift+L toggles filters for a selected range. You can also use Data > Filter on Windows, Mac, and Excel for the web; ribbon layout and some controls can vary by platform and window size. On Mac, use the ribbon command if the Windows shortcut does not match your keyboard setup. If dropdowns are already present, filtering is on; applying the command again can turn it off.

Choose values, text, numbers, or dates

Open a column’s dropdown to see the values Excel has found in that field. Select or clear checkboxes to include the values you want, and use the search box to find a particular item in a long list. For a Region column, selecting North leaves records with North visible. Clearing the selection for a value does not remove that value from the workbook.

When a column contains appropriate data, its dropdown also offers condition-based choices. Text Filters can include options such as Begins With or Contains; Number Filters can find values greater than, less than, or between limits; and Date Filters can select periods or date conditions. The available choices depend on the data type Excel recognizes. If numbers or dates are stored as text, Excel may offer text-oriented choices or treat visually similar values inconsistently.

Filters on different columns work together: a row must meet the active condition in each filtered column to remain visible. Within one column, selecting several checkbox values shows rows matching any of those selected values. For example, choosing North and South in Region, then selecting Tea in Product, shows Tea records from either region. This combination lets you narrow a list without creating a separate copy for each condition.

Use the FILTER formula for a separate result

AutoFilter hides rows in the original list. The FILTER function instead returns matching values as a dynamic array in another part of the sheet, which is useful for a live report or a smaller view that updates when source data changes. FILTER is available in Microsoft 365 and Excel 2021 or later. The output area must have enough empty cells for the results to spill; otherwise, Excel displays a spill error.

With headers in A1:C1 and records in A2:C6, where column A contains Region, column B contains Product, and column C contains Sales, enter this formula in E2 to return North records:

=FILTER(A2:C6,A2:A6="North","No matches")

The result is two rows: North, Tea, 120 and North, Coffee, 150. Their sales total 270 (120 + 150). The third argument displays No matches if no row meets the condition. The formula returns the selected columns in their original order; it does not hide the source rows or change the source data. If the source range grows, the references may need to be extended, or the data can be placed in a table and the formula written with structured references.

Use AutoFilter when you want to inspect or work with the original list in place. Use FILTER when you need a separate, formula-generated result that responds to changes in the source. They solve related but different problems, and a worksheet can use both.

Filtering compared with sorting and Advanced Filter

Sorting reorders records without excluding them. For example, sorting Sales from largest to smallest brings high values to the top, but all records remain in the list. Filtering for Sales greater than 100 leaves only records above that threshold visible. Applying a sort after a filter sorts the visible subset; clearing the filter restores the other records, while the sort order may remain.

For more involved criteria, Excel for Windows also includes Advanced Filter, available at Data > Advanced. It can filter a list in place or copy matching records to another location, and its criteria range supports conditions that ordinary dropdown selections do not express as conveniently. The criteria range needs headings that match the source field names, so set it up carefully. For many everyday tasks, AutoFilter or the FILTER function is simpler.

Choose based on the outcome you need:

Use column dropdowns to temporarily narrow an existing list.
Use a table when the list will be extended and you want built-in headers and filters.
Use FILTER to create a separate result that recalculates as its source changes.
Use Advanced Filter when you need its criteria-range options or want to copy matching records using that tool.

Common filter problems and fixes

If a dropdown is missing, check that filtering is enabled and that the data has a recognizable header row. If Excel selected only part of a list, turn off the filter, select the full range, and apply it again. Blank rows or columns inside the data can interrupt Excel’s detection of a continuous block. Converting a well-structured range to a table can help avoid that issue as new records are added.

If a value seems absent from the dropdown, clear other active filters first: a filter in one column can make the visible list smaller, and the available choices can reflect the remaining records. Also check for extra spaces, inconsistent spelling, or values stored in different formats. For instance, 100 stored as text may not behave like the number 100 in number-based conditions. Correcting the source values is usually better than relying on a filter to compensate for inconsistent data.

If filtering by dates or numbers gives unexpected results, inspect the source cells and their formats. A cell that looks like a date may actually contain text, and a number formatted with decimals may still have a more precise underlying value. If a FILTER formula returns #SPILL!, remove content from cells in its intended output area. If it returns No matches, verify the condition and whether the source values contain hidden spaces or different spellings.

To show all records again, open a column dropdown and choose Clear Filter From [field], or use Data > Clear to clear filter criteria. The exact wording and placement can vary slightly across platforms. Turning off the filter removes the dropdowns; it is different from clearing conditions while leaving filtering available.

Good practices for reliable results

Before filtering, make the source easy to interpret: use a single row of distinct headings, keep each field in its own column, and avoid merged cells in the data area. Keep dates in a consistent date format and numeric fields as numbers. These habits help Excel recognize field types and make the dropdown choices more predictable.

When reviewing results, note which filters are active. Excel indicates filtered columns with a filter symbol in the header dropdown, and row numbers may skip hidden records. A total typed into a cell does not necessarily change just because rows are hidden; its formula determines what it counts. In particular, SUM includes values in filtered-out rows, whereas SUBTOTAL can calculate over visible rows depending on its function number and the type of hidden rows. Check the formula before treating a displayed total as a filtered subtotal.

For sharing, explain the criteria used or provide a clearly labeled report. Recipients may open the workbook with the saved filter state, and hidden rows can be easy to overlook. If the goal is to provide only a subset, create a separate output or workbook deliberately rather than assuming a filter permanently removes other records.

Frequently Asked Questions

Does filtering in Excel delete rows?
No. A filter hides rows that do not match the current criteria. Clear the filter or show all values to display them again. To remove records permanently, you would need to delete them separately, which is a different action.

How do I filter by more than one column?
Apply a condition from each column’s dropdown. Excel combines conditions across columns, so a row must meet each active column’s criteria to remain visible. Within one column, selecting multiple checkbox values includes rows matching any selected value.

Why is the Filter button greyed out or missing?
Check that you have selected a cell in a normal data range and that the worksheet is not protected in a way that prevents filtering. Merged cells or a poorly structured range can also cause trouble. Select the intended range and use Data > Filter; if protection is involved, the sheet owner may need to allow filtering or unprotect the sheet.

Can Excel filter by cell color?
Yes. When the column contains cell or font colors, open its filter dropdown and look for Filter by Color. The option appears when Excel has relevant color formatting to filter. Color filtering is a way to display matching cells, not a replacement for a consistent category field.

What is the difference between Excel’s Filter command and the FILTER function?
The Filter command adds dropdowns that hide nonmatching rows in the existing list. The FILTER function is a worksheet formula that returns matching values into another area and updates as its inputs change. The formula requires a compatible version of Excel, while the dropdown command is the standard way to filter a list directly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.