To filter data in Excel, select a cell inside your data, then on the Data tab select Filter (the full ribbon path is Data > Sort & Filter > Filter). A dropdown arrow appears on every column header. Select the arrow on the column you want to narrow, then choose which values or condition to show. Filtering hides the rows that do not match and keeps the ones that do, so nothing is deleted, and clearing the filter brings every row back.
AutoFilter is the built-in way to show part of a range and hide the rest without touching the data underneath. The sections here cover turning filters on, filtering by checking values off a list, filtering by a text, number, or date condition, filtering by color, stacking filters across several columns, and clearing or reapplying them, plus the point that trips people up: a filter hides rows, it does not remove them.
How do I turn on filters in Excel?
Select any cell inside the range, then on the Data tab select Filter. That button lives in the ribbon’s Sort & Filter group, so you may see the path written as Data > Sort & Filter > Filter. A small dropdown arrow appears on each column header, and each arrow opens that column’s filter menu.
The same command sits on the Home tab as well: Home > Editing > Sort & Filter opens a menu that holds Filter alongside the sort and Reapply commands. Either route toggles the arrows on.
If your data is already formatted as an Excel table, you do not need the toggle at all. Filter controls are added to the table’s header row automatically the moment the table is created.
How do I filter by a specific value?
Choosing values off the list is the quickest filter. Select the header arrow for the column you want to narrow, and Excel lists every value in that column with a check box. Clear the (Select All) box to uncheck them all at once, then check only the values you want on screen and select OK. To find a value in a long list, type it into the Search box above the check boxes.
Once a filter is applied, the column’s dropdown arrow changes to a small filter icon, the sign that this column is narrowing the range. Hover over it and Excel shows a screen tip describing the filter applied to that column.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Q1 sales |
| 3 | West | Ramirez | 4,200 |
| 5 | West | Osei | 3,800 |
| 7 | West | Nowak | 5,100 |
How do I filter by a text, number, or date condition?
When checking values off a list is not enough, filter by a condition instead. Open the column’s arrow and, depending on what the column holds, Excel offers Text Filters, Number Filters, or Date Filters. Point to one and pick a comparison:
- Text Filters cover options like Contains, Does Not Contain, Begins With, and Ends With, for narrowing to rows whose text matches a pattern.
- Number Filters cover Greater Than, Less Than, Between, Top 10, and Above Average. Enter the number the comparison needs and select OK.
- Date Filters work the same way, with period options such as Today, This Week, and Year to Date.
You can combine two conditions on the same column with the And button, so both must be true, or the Or button, so either one qualifies. Excel offers one filter type per column, chosen by whichever data type appears most in it, so keeping a column to a single kind of data gives you predictable options.
Two of the number filters behave unlike the rest. Top 10 and Above Average are measured against the original range, not the rows an earlier filter left behind, so the top ten always means the top ten of the whole column rather than of the current subset.
On a Mac the same conditions are reached a little differently: open the column’s Filter menu, select Choose One, and pick the comparison there, rather than through separate Text Filters and Number Filters submenus.
How do I filter by cell or font color?
If a column carries color, whether you applied it by hand or through conditional formatting, you can filter on it. Click a cell that has the color you want, open that column’s Filter arrow, and under Filter open the By color menu. Choose Cell Color, Font Color, or Cell Icon, then pick the color to keep. Excel shows only the rows whose cell in that column carries it.
How do I filter more than one column at once?
Filter one column, then open a second column’s arrow and filter it too. Excel keeps both. Microsoft’s rule for stacked filters is that “Filters are additive, which means that each additional filter is based on the current filter and further reduces the subset of data.” Each column you filter narrows what is already on screen, so the columns combine with AND: a row has to pass every active filter to stay visible.
One effect of that stacking: once a column is filtered, the choices offered in another column’s list are only the values still visible in the current subset, not every value the column originally held. Clear the filters if you need the full set of choices back.
How do I clear a filter or show all rows again?
There are three levels, depending on how much you want to undo.
- Clear one column. Open that column’s filter icon and select Clear Filter From the column’s name. The other columns keep their filters; only this one shows all its values again. You cannot leave the arrows on for one column and off for another, so this clears the filter without removing the arrow.
- Clear every filter at once. On Windows, the Data tab has a Clear button (Data > Sort & Filter > Clear) that drops all filters and redisplays every row while leaving the dropdown arrows in place.
- Turn AutoFilter off. Select Filter again on the Data tab and the arrows disappear along with the filtering, which is also what the keyboard shortcut Alt+D+F+F does. This is all or nothing: AutoFilter is on for the whole range or off for all of it.
To refresh a filter after the data behind it changed, use Reapply: on the Home tab, select Editing > Sort & Filter > Reapply. Reapplying matters when rows were added or edited, when a formula recalculated, or when the filter is a moving date range like This Week, because the visible set can be out of date until you reapply it.
Does filtering delete the hidden rows?
No. A filter only hides rows; the data is all still there. Microsoft’s description is direct: “When you filter data, entire rows are hidden if values in one or more columns don’t meet the filtering criteria.” Hidden means hidden, not gone, and clearing the filter restores every row exactly as it was.
That has a consequence worth keeping in mind. Actions you take on a filtered range apply to the rows you can see: you can copy, edit, format, chart, or print just the visible subset. Find searches only the displayed rows too, so a value sitting in a hidden row will not turn up until you clear the filter. Because the hidden rows are still in the file and untouched by what you do to the visible ones, it is safest to clear filters before you delete a block of rows or fill a column downward, so you are working on the whole range rather than the slice on screen.
Filtering also leaves the row order alone. It hides rows in place instead of moving them, which is the difference from sorting: a filter changes which rows you see, a sort changes the order they sit in. To rearrange rows instead of hiding any, see how to sort data in Excel.
If you would rather pull the matching rows out to a new location than hide the rest in place, the =FILTER() function is the formula version: it returns the rows that meet a condition into a spill range you point it at. The guide to array formulas covers it. And to copy just the distinct values out of a column, Excel’s Advanced Filter has a unique-values option, covered in how to remove duplicates.
The short version
- Turn filters on: select a cell in your data, then Data > Filter (the button is in Data > Sort & Filter, and also on Home > Editing > Sort & Filter). A dropdown arrow lands on each column header.
- Filter by value: open a column’s arrow, clear (Select All), check the values to show, and select OK. Use the Search box for long lists.
- Filter by condition: open the arrow and pick Text Filters, Number Filters, or Date Filters, then a comparison like Contains, Greater Than, Between, or Top 10.
- Filter by color: open the arrow, then By color, and choose Cell Color, Font Color, or Cell Icon.
- Stack columns: filters are additive, so each column you filter narrows what is already shown, combining with AND.
- Clear or reapply: Clear Filter From a column clears one; Data > Clear clears all and keeps the arrows; selecting Filter again removes the arrows; Home > Editing > Sort & Filter > Reapply refreshes after the data changes.
- Total only what shows: a plain SUM still adds the rows a filter hid, so for a total that follows the filter use SUBTOTAL, the filter-aware aggregate.
- The rows are hidden, not deleted: clearing the filter brings them all back, and when you need to know which rows changed while a filter was on, compare the two files so the hidden rows are checked too.
