How to Filter in Microsoft Excel: A Practical Guide

Formulas & functions · Updated November 20, 2026

To filter in Microsoft Excel, select a cell in your data, then choose Data > Filter or press Ctrl+Shift+L on Windows. Use the arrow 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 reliable results, make sure your data has one clear header row and no completely blank rows or columns inside the range.

What You’ll Need

  • An Excel workbook containing a list or table of data.
  • A header in the first row of the range, with a distinct label for each column, such as Date, Region, or Status.
  • Access to Excel for Microsoft 365 on Windows. The main steps are similar in Excel for Mac and Excel for the web, with the interface and some shortcuts differing as noted below.

You do not need to convert the data to an Excel table to filter it, but a table is often convenient. If you already have a table, its header row normally has filter arrows ready to use.

Step-by-Step

  1. Prepare and check the data. Put one record on each row and one type of information in each column. For example, a sales list might have columns for Date, Customer, Region, and Amount. Give every column a meaningful, unique heading. Avoid merged cells in the list, blank header cells, and completely empty rows or columns splitting one part of the list from another. Formatting, such as bold text or a background color, is optional; the headings and consistent rows are what matter most.
  2. Select the data range or click inside it. Click any cell in the list. Excel usually detects the surrounding data and applies the filter to its connected block. If detection could include unrelated data nearby, select the exact range first, including the header row—for example, A1:D250. Check that the selection includes all the records you want to filter and does not include an unrelated note or second table.
  3. Turn on the filter arrows. On the Windows ribbon, go to Data > Filter, in the Sort & Filter group. The same command switches filters off if you use it again. On Windows, Ctrl+Shift+L also toggles AutoFilter. On a Mac, use Data > Filter; the keyboard shortcut can vary by Excel version and keyboard settings, so the ribbon command is the dependable option. Excel for the web also offers the Filter command on the Data tab. Each heading in the filtered range should now have a drop-down arrow.
  4. Open a column’s filter menu. Click the arrow beside the heading for the field you want to narrow. The menu normally includes a search box, a list of values with checkboxes, and options for sorting or applying a type-specific condition. The choices depend on the contents Excel recognizes in that column. If there is no arrow beside a heading, check that filters are on and that the heading is part of the selected range.
  5. Choose specific values from the checklist. To show only selected categories, clear Select All, then tick the values you want to keep and select OK. For example, in a Region column, selecting North and West hides rows for other regions. You can use the search box to find a value in a long list, select it, and then confirm. Be sure to inspect the checkboxes after searching: filtering the checklist with search is not the same as applying the filter until you confirm the selection.
  6. Use a text, number, or date condition when appropriate. In a text column, choose Text Filters and a condition such as Contains, Begins With, or Equals. In a numeric column, choose Number Filters to show values greater than, less than, between, or equal to a number. In a date column, choose Date Filters to select a date period or a condition. Enter the value or values in the dialog and confirm. The available options vary with the data type Excel detects; mixed text and numbers can prevent the expected condition menu from appearing.
  7. Apply filters in more than one column if needed. Filter one column, then open another column’s menu and make a second selection. Excel shows rows that satisfy both columns’ filters. For example, choose Region = North and Status = Open to see open records in the North region. Within one column, selecting several listed values usually shows rows matching any selected value; across separate columns, the conditions work together. The filter arrows indicate active filters, typically with a filter icon replacing or appearing on the arrow.
  8. Review the filtered results. Look at the visible rows and the status bar, and check that the results make sense. Excel hides rows that fail the conditions; it does not remove or rearrange the underlying records permanently. A filtered list can be sorted as well, but sorting changes the order of the visible records. If you copy filtered data, verify what is selected before pasting elsewhere, especially if you intend to copy visible rows only.
  9. Clear one filter or remove all filters. To undo a filter in just one column, open that column’s menu and choose Clear Filter From followed by the column name, or select all values again. To clear filtering across the range, use Data > Clear in the Sort & Filter group. This restores hidden rows but leaves the filter arrows in place. To remove the arrows entirely, choose Data > Filter again. In a table, you can also use a header menu to clear the relevant filter while keeping the table format.

Mac and web note: Ribbon labels and menu placement can vary slightly by platform or update. Look for Filter on the Data tab. In Excel for the web, some desktop dialogs and advanced controls may be simplified, but basic checklist, text, number, and date filtering is available for ordinary ranges and tables.

Common Mistakes

  • Leaving a blank row in the middle of the list. Excel may treat the data above and below it as separate blocks and apply the filter to only one block. Remove the spacer row or turn the complete range into a table before filtering.
  • Using blank or duplicate headings. A missing heading makes it difficult to identify the field, and duplicate headings can make a list confusing. Give each column a clear, distinct label before enabling filters.
  • Expecting a filter to delete unwanted records. A filter only hides rows that do not match. If you want to remove records, first make a backup and carefully verify the rows; filtering alone is not a deletion operation.
  • Filtering inconsistent entries as though they were one value. Entries such as North, north, and North with an extra space may appear as separate checklist items. Standardize values if they should be treated alike.
  • Assuming every number or date is stored as that type. Text that looks like a number or date can be treated differently, particularly when pasted from another source. Mixed types may make number or date conditions unavailable or produce unexpected results.
  • Forgetting an earlier filter. If you apply a second condition, the first remains active. A small set of results may be caused by the combination, not by the latest column alone.

Tips

  • Convert a changing list into a table. Select a cell in the data and choose Insert > Table, confirm that the table has headers, and select OK. Tables include filter arrows by default and usually extend formatting and formulas as you add rows. If you already turned on filters, converting to a table is optional rather than necessary.
  • Use search for long value lists. Searching the filter menu is quicker than scrolling through hundreds of categories. After finding the value, check the selection and confirm it so the filter is actually applied.
  • Use the most useful condition. A checklist is best when you know the exact categories. A condition such as Contains or Greater Than is more practical when you want a range or partial text match, or when new values may be added later.
  • Check blanks deliberately. If you need rows with no entry in a field, look for (Blanks) in the value checklist. If you need only completed rows, deselect it. A cell containing spaces is not necessarily treated as blank.
  • Keep headers visible while browsing. If the list is long, freeze the header row using View > Freeze Panes > Freeze Top Row. This does not change the filter; it simply keeps column names visible as you scroll.
  • Clear filters before sharing or printing. Someone opening the workbook may not notice that some rows are hidden. Clear filters, or make the filtered state obvious and explain which conditions are active. For printing, review Print Preview to confirm that the intended records and headings appear.

Troubleshooting

No filter arrows appear: Click inside the data and select Data > Filter. Confirm that the range includes its header row and that the sheet is not protected in a way that prevents filtering. If you selected only a single cell, Excel may have detected a different block than intended; select the complete range, then apply Filter again.

Only part of the list is filtered: Look for fully blank rows or columns that split the records, and check whether new rows were added outside the original filtered range. Remove the break and reapply the filter to the full range, or use an Excel table so added records are included more reliably.

The results seem wrong or too limited: Open each column menu and inspect its checked values. Clear filters one at a time to identify an old condition. Also check for leading or trailing spaces, inconsistent spelling, mixed data types, and dates stored as text. If a number or date condition is missing, correct the source data’s type and then reopen the menu.

Some rows seem to have disappeared: They are likely hidden by the active filter, not deleted. Use Data > Clear or clear the relevant column’s filter to show them again. If rows remain hidden, check for manually hidden rows separately; clearing a filter does not necessarily unhide rows hidden through the row-header menu.

The Filter command is unavailable: Check whether the worksheet is protected or whether you are editing a cell. Press Enter or Esc to finish editing, then try again. If protection is enabled, ask the workbook owner whether filtering is permitted or whether the sheet can be unprotected.

Frequently Asked Questions

Does filtering in Excel delete rows?
No. Filtering hides rows that do not meet the selected conditions. Clear the filter to show them again. To delete records, use a separate, deliberate editing action and verify the target rows first.

Can I filter by more than one column?
Yes. Apply a condition in one column and then another in a second column. Excel displays records that meet the conditions across both columns, which is useful for combinations such as a particular region and status.

Can I filter by cell color or font color?
In supported Excel desktop versions, open the column’s filter menu, choose Filter by Color, and select the available cell or font color. The option appears only when Excel finds color formatting in that column. Excel for the web may have a different or more limited menu depending on the current feature set.

How do I show blank cells only?
Open the relevant column’s filter menu, clear the existing selections, select (Blanks), and confirm. If expected blank rows do not appear, check whether the cells contain spaces or formulas returning an empty string rather than truly empty cells.

Why does my filter miss new rows?
The new records may be outside the range to which AutoFilter was applied, or a blank row may separate them from it. Extend the filtered range or add the records directly below the existing range. Converting the list to a table helps it expand when you add adjacent rows.

Can I filter without changing the source data?
Yes. A standard filter changes which rows are visible, not the values stored in the cells. If you need a separate extracted results list rather than hidden rows, use a separate copy of the data or an appropriate formula-based approach supported by your Excel version.

Related

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.