To create a filter in Excel for Microsoft 365 on Windows, click a cell in your data range, then choose Data > Filter. Excel adds a drop-down arrow to each header; use an arrow to show only rows that match your chosen values or conditions. Filtering hides rows temporarily rather than deleting them, so you can focus on a subset and restore the full list later.
What You’ll Need
- An Excel workbook with a list of data arranged in columns.
- A header row with a clear label for each column, such as Date, Region, or Status.
- A continuous block of data is easiest to filter. Remove completely blank rows or columns inside the list before you begin.
You do not need a special template or formula. If the list is important, save a copy before reorganizing it. A filter does not remove records, but sorting or editing while only some rows are visible can make the results confusing if you are not sure which rows are shown.
Step-by-Step
- Check and prepare the list. Put each record on its own row and each type of information in its own column. For example, a sales list might have Order Date, Customer, Region, and Amount across the first row, with one order per row beneath. Make sure the headings are in a single row, are not blank, and are different from one another. Remove merged cells from the list; merged headers and data can interfere with filtering. Avoid placing a total row in the middle of the records.
- Select a cell in the data. Click any populated cell inside the list. You usually do not need to select the whole range: Excel detects the connected block around the active cell. If the worksheet has other data immediately beside or below your list, select just the intended range, including its header row, so Excel does not treat unrelated cells as part of the same list. Check the selected range before turning on the filter.
- Turn on the filter controls. On the ribbon, select Data > Filter. Excel places a drop-down arrow in each header cell. You can also use the Windows shortcut Ctrl+Shift+L to turn AutoFilter on or off. On Excel for Mac, use Data > Filter; keyboard shortcuts can vary with the Mac keyboard and settings. In Excel for the web, use Data > Filter when the command is available on the ribbon.
- Open a column’s drop-down. Click the arrow in the header for the field you want to narrow down. The menu usually shows a search box, a list of distinct values with checkboxes, and relevant filter commands. The options depend on the kind of data in that column. For example, a column of dates offers date-related filters, while a column of numbers offers number filters. If the list of values is long, type part of a value in the search box to find it.
- Choose specific values. To show only selected entries, clear the Select All checkbox, select the values you want to keep visible, and click OK. For example, in a Region column, selecting East and West displays records for those regions and hides the other rows. To include several values, leave each wanted checkbox selected. The checkboxes control which records remain visible; they do not change the underlying cell contents.
- Filter using a condition when an exact value is not enough. Open the arrow for a text column and choose Text Filters, then select a condition such as Contains, Begins With, or Equals. Enter the text and confirm. For a numeric column, choose Number Filters and a condition such as Greater Than or Between. For dates, use the date filter options to select a period or a date condition. Read the condition and entered value before confirming; Greater Than 100, for instance, excludes values equal to 100.
- Apply filters to more than one column. Choose a filter in the first column, then open another header and set its filter too. Excel shows rows that meet the chosen criteria in all filtered columns. For example, filtering Region to East and Amount to greater than 500 shows East records whose amounts are above 500, not every East record plus every high-value record from other regions. This lets you narrow a large list in stages.
- Check the visible results. Look at the row numbers and the filter arrows. Excel hides rows that do not meet the criteria; it does not move the matching records to a separate sheet. A filtered header usually shows a filter indicator. Verify a few visible rows against the conditions you selected, especially if the column contains inconsistent entries such as extra spaces, different spellings, or numbers stored as text. If results look unexpected, clear the filters and inspect the source values.
- Clear or remove the filter when finished. To show every value in one column again, open that column’s drop-down and choose Clear Filter From [column name], where Excel supplies the relevant name. To clear filters across the list, select Data > Sort & Filter > Clear. This restores hidden rows but leaves the filter arrows in place. To remove the arrows entirely, select Data > Filter again, or press Ctrl+Shift+L on Windows. Clearing a filter and turning off the filter controls are different actions.
- Consider converting a growing list to a table. If the data will expand, click inside it and choose Insert > Table, or press Ctrl+T on Windows. Confirm the range and check My table has headers if the first row contains labels. Click OK. Excel creates a table with filter arrows and generally extends it as you add rows or columns. You can still use the same filtering menus. To use ordinary AutoFilter rather than a table, the Data > Filter steps above are sufficient.
Common Mistakes
- Leaving blank rows or columns inside the list. Excel may detect only part of the data, so some records do not get filter arrows or are not included. Keep the records in one continuous block, or select the full intended range before choosing Data > Filter.
- Using unclear or empty headers. Each column should have one descriptive heading. A blank heading makes it difficult to identify the field in a filter menu, and duplicate headings can make the list harder to understand. Give each field a distinct label.
- Assuming a filter deletes unwanted records. Filtered-out rows are hidden, not erased. They return when you clear the filter. If you need a permanent shorter list, copy the visible results to another location and verify the copied range before deleting anything.
- Forgetting that multiple filters combine. Filters on different columns apply together. A result that looks too small may be caused by a condition left active in another column. Look for filter indicators and clear the other conditions before deciding the data is missing.
- Mixing data types in one column. A column that contains some numbers stored as text, inconsistent date formats, or extra spaces may offer confusing options or produce unexpected matches. Standardize the values first when possible, then reapply the filter.
Tips
- Use a header row that describes the contents precisely. Labels such as Invoice Date and Payment Status are more useful than Date and Status when the workbook has several similar fields.
- Use the drop-down search box for long lists of distinct values. Search can help locate an entry quickly, but review the checked values before clicking OK so you know what the filter will retain.
- To display blank or nonblank cells, open the relevant column menu and use the checkbox list or the available Blanks option. The exact option can depend on the values and data type in that column.
- Keep a visible reminder of active criteria if you share a filtered workbook. A reader may open it and see only a subset of rows without realizing that other records are hidden.
- For lists that are routinely expanded, use an Excel table. Tables make it easier to keep headers and filter controls connected to the records as the list grows.
- Filtering and sorting are separate. Filtering hides rows that do not match; sorting changes the order of the visible records. If you sort while a filter is active, clear the filter afterward if you want to review all records in their new order.
Troubleshooting
No filter arrows appear: Click inside the intended range and select Data > Filter. If the command is unavailable or the range behaves unexpectedly, check whether the sheet is protected or the cells are part of an existing table. Also check that the data has a header row and that the intended range is selected. If a filter is already on, selecting Data > Filter may turn it off rather than add another set of arrows.
Some records are not included: Look for a completely blank row or column that separates those records from the main block. Remove the gap or select the complete range, including the header row, before turning on filtering. If the list is a table, confirm that the records were added inside the table or immediately adjacent to it so the table can expand.
A value you expect is missing from the menu: Clear the search box and check whether a filter is active in another column. Inspect the source cells for leading or trailing spaces, inconsistent spelling, or mixed data types. For example, a number stored as text may not behave like the other numeric values. Correct the source data if needed, then reopen the filter menu.
The filter shows no rows: The selected conditions may not match any record, or conditions in separate columns may be too restrictive when combined. Clear one condition at a time using its drop-down, then check the results. Re-enter the criteria carefully, paying attention to operators such as Equals, Greater Than, and Contains.
Rows seem to be missing after filtering: Clear the active filters using Data > Sort & Filter > Clear. If the rows still are not visible, check whether the worksheet has manually hidden rows or whether the data is on another sheet. A filter only controls which records in its range are displayed; it does not restore rows hidden by other methods.
The menu path looks different: These steps describe Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web can arrange ribbon controls differently, and some options may appear in a compact or simplified ribbon. Look for the Data tab and its Filter command; if needed, expand the ribbon. The core process—select the list, enable filtering, and use a header menu—is the same.
Frequently Asked Questions
Does filtering delete rows in Excel?
No. A filter hides rows that do not match the criteria. Clear the filter to display them again. It does not remove or rewrite the values in those rows.
Can I filter by more than one value in the same column?
Yes. Open that column’s drop-down, select each value you want to keep, and click OK. You can also use a condition such as Contains or Between where that option is available.
Can I apply filters to multiple columns at once?
Yes. Set a condition in each column’s menu. Excel displays records that meet the chosen conditions together, so a row must satisfy the active filters across the columns to remain visible.
How do I show all rows again without removing the arrows?
Open a filtered column’s drop-down and choose Clear Filter From its column name. To clear all active filters at once, choose Data > Sort & Filter > Clear. The filter arrows stay available.
Why does Excel filter only part of my list?
A blank row or column can make Excel treat the surrounding data as separate blocks. Remove gaps within the list or select the complete range before enabling the filter. Converting a continuous list to a table can also help when it grows.
Can I filter a list without converting it to a table?
Yes. Click in the range and choose Data > Filter. A table is optional; it is useful when you want the range to expand more naturally as records are added.
Related
- How to Copy Selected Cells in Excel Without Errors
- How to Copy a Sum From Excel’s Status Bar to a Cell
- How to Copy and Paste Only Filtered Data in Excel Safely
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- OFFSET in Excel: Syntax, Examples, and UsesIn Excel, OFFSET returns a reference to a cell or range located a specified number of rows and…
- Excel XLOOKUP Practice Examples: Formulas and TipsExcel XLOOKUP practice examples are easiest to learn by matching a lookup value to a column and returning…
- SUMIFS Excel Criteria Range: How to Set It UpIn Excel, a SUMIFS criteria range is the range of cells that SUMIFS checks against a condition before…
- Excel Formula to Check Whether a Cell Matches a ListTo check whether a cell’s value appears in a list, use =COUNTIF($D$2:$D$10,A2)>0, assuming the value to check is…
- Excel VLOOKUP Column Index Number ExplainedIn Excel VLOOKUP, the column index number tells Excel which column to return a value from, counting from…
- Excel Timesheet Formulas for Overtime, Breaks, and PayFor a basic Excel timesheet, put a shift’s start time in B2, end time in C2, and unpaid…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.