To enable filters in Excel for Microsoft 365 on Windows, click a cell in your data range, then select Data > Filter. Excel adds a drop-down arrow to each header so you can show only the rows that match criteria you choose. Filters hide rows temporarily; they do not delete or change the underlying data.
What You’ll Need
- An Excel workbook containing a list or table, such as a contact list, inventory, or sales log.
- A single, clearly labeled header row, with one column heading per field.
- A continuous block of data. Blank cells within the data are fine, but avoid completely blank rows or columns that split the list into separate sections.
You do not need to convert the range into an Excel table before filtering. Filters work on ordinary cell ranges as well as tables. If your data has multiple title rows, merged cells, or explanatory notes mixed into the list, tidy or separate those items first so Excel can identify the actual header and data range.
Step-by-Step
- Check the layout of your list. Look for one header row at the top, followed by the records. Give each column a distinct label, such as Date, Region, Product, or Amount. Remove merged cells from the header area, and check that there is not a fully blank row or column dividing the list. You can leave ordinary blank cells in individual records; those can be filtered too.
- Select a cell inside the data. Click any populated cell in the list. In most cases, you do not need to drag across the entire range: Excel detects the connected block of data around the active cell. If your worksheet has nearby notes or another table, select the full intended range manually, including its header row, so Excel does not include unrelated content or leave out part of the list.
- Open the Data tab. On the Windows ribbon, choose Data. Find the Sort & Filter group. If the ribbon is simplified or the Excel window is narrow, some buttons may be compressed; widen the window or expand the ribbon to locate the Filter command.
- Turn on the filter arrows. Select Data > Filter. A drop-down arrow should appear at the right side of each heading in the detected range. You can also use the Windows keyboard shortcut Ctrl+Shift+L to toggle filters on or off. If the arrows appear only on some columns, check whether the selected range includes all of the intended headers and records.
- Open a column’s filter menu. Click the arrow beside the heading you want to filter. The menu usually includes a search box, a list of values, sorting options, and commands such as Text Filters, Number Filters, or Date Filters. The choices depend on the type of values in that column. For instance, a column of dates offers date-specific criteria, while a column of text offers text criteria.
- Choose the values or rule to display. To show selected values, clear (Select All), then tick the values you want to keep visible. You can search for a value first if the list is long. To apply a rule instead, choose the relevant filter category—for example, Number Filters > Greater Than—enter the condition, and confirm it. The menu wording can vary slightly with the data type and Excel version.
- Apply the selection and review the result. Select OK if the menu has an OK button. Excel hides rows that do not meet the condition and leaves matching rows visible. A filtered column’s arrow changes to a filter indicator, so you can tell that a condition is active. Check a few visible records to confirm the result makes sense before relying on it.
- Add another condition if needed. Open a second column’s arrow and choose another value or rule. Excel combines filters across different columns: a row must meet the active condition in each filtered column to remain visible. For example, filtering Region to West and Status to Open shows records that are both in the West and still open.
- Clear a filter or turn filtering off. To show all records again while keeping the arrows, open the filtered column’s menu and choose Clear Filter From followed by the column name. Repeat for other filtered columns, or use Data > Clear to clear the current filters for the range. To remove the arrows themselves, select Data > Filter again, or press Ctrl+Shift+L. Turning the feature off does not erase the data.
On Mac: The ribbon route is also Data > Filter. Menu names and ribbon layout may differ slightly by Excel for Mac release. A commonly available toggle shortcut is Command+Shift+F; if it does not work on your Mac, use the ribbon command. On Excel for the web, select a cell in the range and use Data > Filter. Browser shortcuts can be intercepted by the browser or operating system, so the ribbon is the reliable option there.
Common Mistakes
Including more than one header row. If a report title or subtitle sits directly above the column headings, Excel may interpret the wrong row as the header. Select the actual data block beginning with the column labels, or move the report title above the list with a blank row separating it.
Leaving merged cells in the range. Merged headings can interfere with sorting and filtering because a filter expects one heading per column. Unmerge those cells and give each column its own label before enabling the filter.
Assuming hidden rows were deleted. Filtering hides nonmatching rows from view; the records remain in the workbook. This is useful for analysis, but remember that a copied selection or a printout may not include hidden records. Clear the filter to display the full list again.
Filtering one column and forgetting another is active. Several filters can be applied at once, which can leave very few visible rows. If a record you expect is missing, inspect every header for a filter indicator and clear conditions one at a time.
Mixing data types in one column. Dates entered as real Excel dates may behave differently from dates stored as text. Similarly, numbers stored as text can appear in unexpected groups or offer different filtering choices. Keep each column consistent where possible, and verify the underlying cell values if a filter menu looks inconsistent.
Clicking Filter without checking the detected range. Nearby content, blank rows, or separate blocks can cause Excel to identify only part of a list. If arrows stop before the last column or row, undo the action if necessary, select the complete range explicitly, and enable the filter again.
Tips
Use precise, unique column headings. A heading such as Order Date or Customer Name is easier to recognize than a vague label such as Details. Consistent labels also make it simpler for someone else to understand what each filter controls.
Use the search box in a filter menu when a column has many distinct entries. Search narrows the checklist; it does not change the values in the worksheet. After searching, verify which checkboxes are selected before confirming, especially if you want to include several similar terms.
Choose a rule when selecting individual values would be cumbersome. Text filters can help locate entries that contain or begin with particular text; number filters can show values above, below, or between limits; and date filters can group or narrow dates. Use the menu that corresponds to the type of information in the column.
For a reusable data list, consider converting the range to a table: select a cell in the range and choose Insert > Table, confirm the range, and ensure My table has headers is selected when appropriate. Excel tables normally display filter arrows in their header row. If those arrows are hidden, select a table cell and look for the Table Design tab and its Filter Button option. Table features can make it easier to expand a list while keeping its formatting and filtering behavior together.
Before sharing a filtered workbook, decide whether recipients should see the current filtered view or the full list. If the view might be confusing, clear filters first or add a note explaining which criteria are active. For important work, save a copy before making large changes to the data; filtering itself is reversible, but sorting or editing the visible rows can affect the workbook.
Troubleshooting
No arrows appear after selecting Data > Filter: Check that the worksheet is not protected in a way that prevents filtering, and confirm that a valid cell range is selected. Make sure the ribbon command was activated rather than a similarly named option. If the selected cells include a merged header, unmerge it and try again. You can also select the entire list, including its headings, and repeat the command.
Arrows appear, but some records or columns are missing: Excel may have detected a smaller block than you intended. Turn the filter off, select the full rectangular range, including every heading and record, and turn it back on. Check for a completely blank row or column inside the list; if it separates records, remove it or select the range explicitly.
A value you expect is not listed: Clear other active filters first, then reopen the relevant menu. Confirm that the value is actually in the range and that spelling, spaces, punctuation, or date formatting do not differ. If the column contains mixed types, inspect a few cells and standardize them if appropriate. The filter list may also need refreshing after edits; close and reopen the menu or toggle the filter off and on.
The filter returns too few or no rows: Check whether multiple columns have active conditions. Each condition across columns narrows the results further. Clear one condition at a time to see which one excludes the records, then adjust the selected values or rule. For a text or number rule, review the comparison choice and any entered criteria.
The Filter command is unavailable: The sheet may be protected, or the workbook may be in a state that restricts editing. If you have permission, use Review > Unprotect Sheet and provide the password if prompted. In a shared or read-only workbook, request editing access or make an authorized copy. Do not try to bypass protection you are not permitted to remove.
The shortcut does something else: Use Data > Filter instead. Shortcuts differ across Windows and Mac, and browser or system settings can take priority. In Excel for the web, the ribbon command is usually the simplest way to enable filtering.
Frequently Asked Questions
Does enabling a filter change or delete my data?
No. Filtering hides rows that do not match the selected criteria. Clear the filter to display them again. It does not remove records from the worksheet.
Can I filter more than one column at the same time?
Yes. Apply a condition from each column’s drop-down menu. Excel displays rows that satisfy all the active column filters, so each additional condition narrows the visible results.
How do I remove the filter arrows but keep the data?
Select a cell in the filtered range and choose Data > Filter to toggle the arrows off. The rows may become visible again, and the data remains in the worksheet.
Can I filter a list that has blank cells?
Yes, individual blank cells do not prevent filtering. A blank entry can appear as a selectable value in the menu. A completely blank row or column, however, may split the list and cause Excel to detect only part of it.
Why does my filter menu show Text Filters instead of Number Filters?
Excel may be interpreting the column values as text, or the column may contain a mixture of text and numbers. Check the cell contents and number formats, then make the values consistent if you need numeric comparisons.
Can I use filters in Excel for the web or on a Mac?
Yes. In both versions, use the Data > Filter ribbon command. The interface and keyboard shortcuts can vary, but the basic process—select the data, enable the arrows, then choose criteria—works similarly.
Related
- Delete a Hidden Row in Excel: Safe Steps That Work
- How to Delete Gridlines in Excel for a Cleaner Sheet
- How to Delete Grouping in Excel: Rows, Columns & Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel UNIQUE Sort: List Distinct Values in OrderTo sort unique values in Excel, combine the UNIQUE and SORT functions: if the source values are in…
- How to Find Standard Deviation in Excel With FormulasTo find standard deviation in Excel, enter =STDEV.S(B2:B9) for a sample or =STDEV.P(B2:B9) for an entire population, replacing…
- Excel Table Filter Button Greyed Out? Fixes That WorkIf Excel’s filter button is greyed out, first press Esc to leave cell-edit mode, ungroup any selected worksheets,…
- Excel Power Query Filter Rows: Practical Steps and ExamplesTo filter rows in Excel Power Query, open a query in the Power Query Editor, select the filter…
- SUMIFS in Excel: Count or Sum Matching Values?SUMIFS in Excel adds numeric values that meet one or more conditions; it does not normally count matching…
- Excel SUMIF for Text: Match and Join Text Values EasilyExcel has no SUMIF function that adds text strings, but in Microsoft 365 you can use TEXTJOIN with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.