To filter Excel cells, select a cell in your data, choose Data > Filter, then use the arrow in a column heading to choose which rows to show. Filtering hides rows that do not meet your choices; it does not delete them. Before you begin, make sure your list has a clear header row and that each column contains one kind of information, such as dates, names, or amounts.
What You’ll Need
- A workbook open in Microsoft Excel for Microsoft 365 on Windows. The Data tab and filter controls are also available in Excel for Mac and Excel for the web, though some keyboard shortcuts differ.
- A rectangular list of data with a single header row. Keep each record on one row and each field in its own column.
- Optional: a copy of the workbook if the data is important. Filtering itself does not change cell values, but sorting or editing while a filter is active can affect what you see and select.
Step-by-Step
- Check and prepare the data. Look over the top row and confirm that every column has a distinct, descriptive heading, such as Date, Region, or Status. Remove completely blank rows and columns from inside the list because they can cause Excel to treat part of the data as a separate range. Avoid merged cells in the header or data area. Also check that values in a column use consistent formats: dates should be dates, and numbers should not be mixed with text versions of numbers. You do not need to select the entire worksheet; clicking one cell inside a continuous data list is usually enough.
- Turn on the filter controls. Click a cell within the list. On the Windows ribbon, choose Data > Filter. Excel adds a drop-down arrow to each detected header. The Windows shortcut is Ctrl+Shift+L. On Mac, use Data > Filter; the shortcut is commonly Command+Shift+F. If Excel adds arrows to only part of your list, undo the filter or turn it off, fix gaps or blank headers, and apply it again after selecting the full range.
- Open the column you want to filter. Click the arrow beside a heading, such as Region. The menu normally shows a search box, a list of distinct values, and commands relevant to the column’s data type. The exact choices can vary depending on whether Excel recognizes the column as text, numbers, or dates. The checkboxes control which values remain visible; checked values are included, and unchecked values are hidden.
- Show selected values only. In the value list, clear Select All, then check the values you want to keep. For example, in a Region column, select West and Central to show records from those regions while hiding the rest. Click OK to apply the filter. If the list is long, type a word or part of a word in the menu’s search box to narrow the displayed choices, select the matching values, and then click OK. Search narrows the menu; it does not alter the underlying cells.
- Filter by a condition when choosing individual values is not practical. Open a text column’s arrow and choose Text Filters to find entries that equal, contain, begin with, or meet another listed text condition. In a numeric column, choose Number Filters for options such as greater than, less than, or between. In a date column, use Date Filters to select a date range or a period such as a month. Follow the dialog prompts, enter the value or values, and confirm. Use the displayed operator choices carefully: a condition such as greater than includes values above the threshold, not the threshold itself.
- Add another column filter if needed. Apply the next filter from a different heading without clearing the first one. Excel displays rows that satisfy both column filters. For example, filter Region to West and then Status to Open to show only open records from the West. To include multiple alternatives in one column, select several values in that column’s checkbox list, or use the available custom filter options. Read the resulting rows as a combined subset, not as a replacement for the source data.
- Review the result and clear filters when finished. Check the filter icons in the headings and confirm that the visible rows match the conditions you intended. To remove one column’s condition, open that column’s arrow and choose Clear Filter From followed by its heading. To clear all active conditions while keeping the arrows, use Data > Clear. To remove the arrows entirely, choose Data > Filter again. Clearing or switching off a filter makes hidden rows visible again; it does not restore a previous sort order.
You can also format a list as an Excel table with Home > Format as Table or Insert > Table. Confirm that My table has headers is selected when appropriate. Tables add filter arrows automatically and expand more reliably when you add new rows. This is useful for lists that change regularly, but it is not required for a basic filter.
Common Mistakes
- Selecting only one column. If you select a single column and apply a filter, Excel may filter that column separately from the related records. Click inside the full list and let Excel detect the contiguous range, or select the complete range before choosing Data > Filter. Keep each row’s related fields together.
- Leaving a blank row in the middle of the data. A fully blank row can split a list, so records below it might not be included. Remove the gap or select the entire intended range before enabling filters. Blank cells within a column are different: they are valid data entries and can be filtered using the (Blanks) option.
- Assuming filtering deletes records. It only hides rows that do not match. A filtered row remains in the workbook and can reappear when you clear the filter. Be cautious when copying, deleting, or editing filtered results; check the selected range and visible rows before making changes.
- Expecting a text value to match a number or date automatically. A column may contain values that look alike but have different underlying types, such as a real date and text that resembles a date. Check the number format and the way the values were imported. Convert inconsistent entries to a consistent type before relying on numeric or date conditions.
- Forgetting that multiple filters work together. A filter in one column can make it appear that a value is missing from another column’s menu, because Excel may list only values represented in the current filtered results. Clear existing filters if you need to inspect the full set of choices.
Tips
- Use meaningful, unique headings. They make filter menus easier to identify, especially when the list has many columns.
- For a quick check, use the filter menu’s search box to locate a value rather than scrolling through a long checkbox list. Clear the search text afterward if you need to see all available values again.
- Filter by color when the cells already have a fill color or font color applied. Open the column arrow, choose Filter by Color, and select the color. This is useful for reviewing marked records, but color is not a substitute for a clear status value when the workbook will be shared or updated.
- Use Data > Reapply if you change values while a filter is active and want Excel to update the displayed results. Excel may update some filters automatically, but Reapply is a useful way to refresh the view.
- Keep an eye on the status bar and row numbers: gaps in row numbering indicate that some rows are hidden by the filter. The row numbers help confirm that you are looking at a subset.
- To make filtered data easier to review, freeze the header row with View > Freeze Panes > Freeze Top Row. This keeps headings visible while you scroll; it does not change which records the filter shows.
- Filtering and sorting are different. Filtering hides rows that do not meet a condition; sorting rearranges the order of the visible data. If you need the original order back, clear the filter alone will not undo a sort, so consider adding an ID or sequence column before sorting.
Troubleshooting
No arrows appear after choosing Filter: Click inside the data range and check that the header row is not blank or merged. If necessary, select the complete rectangular range, including headers, then choose Data > Filter again. If the worksheet is protected, filtering may be unavailable unless the sheet’s protection settings permit it.
Some records are not included: Look for a blank row or column separating the records from the rest of the list. Also check whether the data was pasted outside the filtered range. Turn off the filter, select the full range, and apply it again, or use an Excel table so new adjacent records are included more predictably.
A value you expect is missing: Clear filters in other columns and inspect the relevant column for extra spaces, spelling differences, or mixed data types. In the filter menu, clear any search text and check whether the desired value is unchecked. If the column contains many distinct entries, a condition such as Contains may be more practical than selecting values one by one.
A number or date filter gives surprising results: Verify that the cells are stored as numbers or dates rather than text. Imported data can look correctly formatted while remaining text. Test a few entries by changing their number format or examining how Excel aligns and recognizes them, then convert the column consistently if necessary. Reapply the filter after correcting the data.
Rows remain hidden after you are done: Open each column menu and check for an active condition, or choose Data > Clear to remove all filter criteria. If rows are still hidden, they may have been hidden manually; select the affected row numbers, right-click, and choose Unhide.
The filter arrow is disabled or the results seem unusual in a shared workbook: Check whether the sheet is protected, the range is part of a table, or another filter is already active. In Excel for the web or a shared workbook, another user’s view or editing activity may also affect what you see. Save or refresh as appropriate, and ask collaborators before changing shared data.
Frequently Asked Questions
Does filtering delete or change Excel cells?
No. A filter hides rows that do not meet the selected criteria; it does not remove their contents. Clear the filter to show those rows again. Deleting visible rows is a separate action and can permanently remove data.
Can I filter more than one column at once?
Yes. Apply a condition from each column’s heading. Excel shows rows that satisfy all the active column filters. To show several alternatives within one column, select multiple checkbox values or use that column’s filter conditions.
How do I filter for blank cells?
Open the column’s filter arrow and select (Blanks) in the value list. To show populated cells instead, select the nonblank values or use the available nonblank condition. A cell containing a formula that returns an empty string may not behave exactly like a truly empty cell.
Can I filter by cell color?
Yes, if cells in the column have a fill or font color. Open the column filter menu, choose Filter by Color, and select the color to display. If that command is not available, check that the selected column has colored cells and that you are filtering the intended range.
What is the difference between Clear and turning Filter off?
Data > Clear removes active filter criteria but leaves the drop-down arrows available. Choosing Data > Filter again turns off the filter controls as well as the filtered view. Neither command deletes the data, and neither necessarily reverses a sort.
Do filters work the same way on Mac and in Excel for the web?
The basic process is similar: select the list, use the Data tab’s Filter command, and choose criteria from a header arrow. Ribbon details and shortcut keys can vary by platform and keyboard settings. If a shortcut does not work, use the ribbon command; Excel for the web also has its own browser and keyboard behavior.
Related
- How to Enable an Audit Trail in Excel: Track Changes
- Freeze Panes in Excel: How to Edit Rows and Columns
- How to Edit Custom Number Formats in Excel Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- XLOOKUP to Compare Two Sheets in Excel Step by StepIf Sheet1 has IDs in A2:A100 and Sheet2 has IDs in A2:A100, enter this in Sheet1 cell B2…
- Excel COUNTIFS Multiple Criteria in the Same ColumnYes. In Excel, use COUNTIFS with the same range repeated for each condition; the conditions are combined with…
- Excel UPPER Function: How to Make Text All CapsExcel’s uppercase text function is UPPER. In Microsoft Excel for Microsoft 365, enter =UPPER(A2) to convert the text…
- Excel AVERAGEIF Formula Examples: Criteria, Ranges, FixesUse Excel’s AVERAGEIF function to average values that meet one condition: =AVERAGEIF(A2:A6,"North",B2:B6). If A2:A6 contains regions and B2:B6…
- How to Delete an Array Formula in Excel Without ErrorsTo delete an array in Excel, remove its formula: select the entire output range for a legacy array…
- Excel Filter Not Showing All Items? Find the CauseIf an Excel filter is not showing all items, first clear filters on every column, then check that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.