If you are unable to put a filter in Excel, select a cell in your data and choose Data > Filter; on Windows, Ctrl+Shift+L also toggles filters. If the command is unavailable or the dropdown arrows do not appear, check whether the sheet is protected, multiple sheets are selected, or Excel has identified the wrong data range.
What Excel filters do
An Excel filter hides rows that do not meet criteria you choose; it does not delete those rows. When you filter a list by a column, such as Region, Excel displays matching records and temporarily hides the others. You can clear the filter to show the full list again. Sorting changes row order, while filtering changes which rows are visible, so the two commands are related but not interchangeable.
In a normal range, AutoFilter adds a dropdown to each column heading. The dropdown lets you select specific values, use text, number, or date conditions, and sort the visible records. An Excel table has its own header dropdowns and usually shows them as soon as the table is created. A PivotTable has separate field filters; it is not filtered by applying ordinary AutoFilter to its report area.
Filters work best when the data is arranged as one rectangular list: one row of headings, with each following row representing a record. For example, a sales list might have headings for Date, Product, Region, and Amount. Each sale should occupy one row, and each value should appear under the appropriate heading.
Apply a filter to a normal data range
In Excel for Microsoft 365 on Windows, click a cell inside the list, then select Data > Filter. Excel normally detects the connected block of data and adds dropdown arrows to its heading row. If it selects the wrong area, select the full range yourself before choosing Filter. For example, select A1:D200 if the headings are in row 1 and the records run through row 200.
- Make sure the list has one clear heading row and no blank row splitting the records.
- Click within the list, or select the exact range including its headings.
- Choose Data > Filter, or press Ctrl+Shift+L on Windows.
- Click a heading arrow and choose values or a condition to display.
- To show all rows again, open the relevant dropdown and choose Clear Filter, or use Data > Clear.
On Excel for Mac, use Data > Filter; Command+Shift+F is the common keyboard shortcut for toggling the filter. In Excel for the web, select the data and use Data > Filter as well. Ribbon layouts and shortcut behavior can vary by platform and settings, so the menu command is the dependable option if the shortcut does not work.
Turn on filters for an Excel table
If your data is already an Excel table, click inside it and look for dropdown arrows in the header row. Tables normally include filter buttons automatically. If those arrows are missing, select a cell in the table and check the Table Design tab. In Windows Excel, the Filter Button option appears in the Table Style Options group. Turn it on if it has been cleared.
You can also create a table from a well-formed range by selecting a cell in the data and choosing Insert > Table. Check that the selected range includes the headings and confirm that My table has headers is selected when appropriate. Table headers must be usable as distinct column names; Excel may change blank or duplicate headings when it creates a table. Do not turn a range into a table just to troubleshoot if your workbook depends on its current layout or formulas—first try applying a normal filter to the selected range.
A table is useful when records will grow over time: new rows added directly below it are generally incorporated into the table, and its header controls remain attached to the data. A normal filtered range is often sufficient for a short, static list. Neither option changes the underlying records, and both let you remove a filter without deleting data.
Check the range and heading row
A frequent cause is that Excel has not recognized the full list. A completely blank row or column can make Excel treat the records on either side as separate blocks. If you click inside one block and choose Filter, the arrows may appear only for that block. Select the whole intended range explicitly, including the header row, before applying the command. Also look for stray values below or beside the list: they can make Excel extend the detected range farther than expected.
Give every column a heading in the first row of the selected range. Avoid merged cells in the heading row, multi-row headings, and decorative title rows within the data block. A title such as “Quarterly sales report” should sit above the list, not replace the individual column names. If it is part of the selection, Excel may treat it as a heading and make the dropdowns confusing.
Check that records are consistently arranged in columns. A blank cell inside a record is allowed, but a blank separator row can interrupt the block. If your list has notes or totals immediately beside it, move them outside the range or select only the actual records. In a table, use a distinct heading for each field and keep the same type of information in each column where possible.
When the Filter command is unavailable
If Data > Filter is dimmed, first press Enter or Esc to finish editing a cell. Excel may disable ribbon commands while the insertion point is active inside a cell or the formula bar. Then click an ordinary cell in the worksheet and check the command again.
Next, check whether the worksheet is protected. A protected sheet can restrict changes to filtering controls. On Windows, look at Review > Unprotect Sheet; if the sheet is protected, you may need the password or the workbook owner’s help. When protecting a sheet, the owner can set permissions that allow users to work with existing AutoFilters, but protection settings can prevent users from changing the filter setup. Do not try to bypass protection; ask the owner to adjust the permissions if you need access.
Look at the sheet tabs, too. If more than one worksheet is selected as a group, some worksheet commands may be unavailable or behave differently. Click a single sheet tab to return to one worksheet, then try again. If the ribbon itself is collapsed, expand it or use the Microsoft Search box to search for Filter. A customized ribbon or a narrow window can hide commands without removing the feature.
Finally, confirm that you are on a worksheet rather than editing a chart, shape, or other object, and that the workbook is not in a mode that prevents normal editing. If a command is missing only in one file, compare it with a new blank workbook; that can help distinguish a workbook-specific restriction from a general ribbon or installation issue.
Filter arrows appear, but filtering does not work
If the arrows are visible but choosing a filter seems to do nothing, check whether the condition actually matches any records. For example, a filter for the text “North” will not match “Northwest” if you chose an exact value rather than a text condition. Use the dropdown’s search box or the relevant Text Filters, Number Filters, or Date Filters options to make the intended condition explicit.
Mixed data can also make a filter list surprising. A column that looks numeric may contain numbers stored as text, spaces, or inconsistent date entries. Excel may list values in ways that reveal these differences, and a date filter may not treat text that resembles a date as a real date. Inspect a few cells and standardize the column if necessary. Filtering is not a reliable way to repair inconsistent source data.
Another possibility is that the matching rows are hidden for a separate reason, such as manually hidden rows or another filter already applied. Open each relevant column dropdown and check for an active filter indicator. Use Data > Clear to clear filter criteria on the sheet, then apply the criteria again. Clearing criteria displays records again; it does not erase values.
For a shared workbook, a colleague may be viewing a different filter state. In Microsoft 365, Sheet View can let people sort or filter a shared worksheet without changing the view for everyone, where the feature is available and the workbook is stored in a supported shared location. If the goal is to avoid disrupting another person, check the View tab for Sheet View rather than assuming the filter command is broken.
Common misconceptions and useful distinctions
A filter does not permanently remove rows, and copying only visible rows is a separate action. If you need to share a filtered result, verify what will be copied or printed rather than assuming hidden rows are deleted. Clearing a filter restores the view of the existing records; it does not undo edits made to them.
Another common misunderstanding is that every arrow is an AutoFilter dropdown. A table’s header buttons, a PivotTable’s field controls, and data-validation dropdowns serve different purposes. If you are working with a PivotTable, use its field filter controls or the PivotTable Analyze options. If a cell has a small selection arrow because of data validation, that arrow does not filter worksheet rows.
Filters apply to rows in a list, not to individual cells independently. Filtering the Region column hides entire records, including their Date, Product, and Amount cells. This is why a clean, rectangular dataset matters: Excel needs to know which values belong together as one record.
Frequently Asked Questions
Why is Filter greyed out in Excel?
Finish editing the active cell, select only one worksheet instead of a group, and check whether the sheet is protected. A protected sheet may restrict changes to AutoFilter settings; the owner may need to change permissions or unprotect it.
How do I add filter arrows to a specific range?
Select the entire range, including its heading row, then choose Data > Filter. On Windows, Ctrl+Shift+L toggles AutoFilter. Make sure the selection does not include unrelated notes or omit rows in the list.
Why are filter arrows missing from my Excel table?
Click inside the table and check Table Design > Table Style Options > Filter Button on Windows. If the data is not actually a table, select the range and choose Data > Filter instead.
Will applying a filter delete rows from my spreadsheet?
No. A filter hides rows that do not match the criteria; it does not delete them. Use Clear or remove the filter to display the full list again.
Why does my filter show unexpected values?
The column may contain inconsistent entries, such as numbers stored as text, extra spaces, or dates stored as text. Check the source cells and use the appropriate text, number, or date filter condition.
When to use a different approach
For an interactive view of a regular list, AutoFilter or a table is usually the simplest choice. For summaries by category, use a PivotTable and its own field filters. If you need a separate, formula-driven list of matching records rather than hiding rows in the original list, the FILTER worksheet function is available in Microsoft 365 and Excel 2021 or later. These tools solve different problems: a worksheet filter changes the visible rows in place, while a PivotTable summarizes data and a formula can return results elsewhere.
If none of the checks restore the command, save a copy of the workbook, close and reopen it, and test whether the problem occurs in another workbook. If only one workbook is affected, inspect its protection and data layout or ask its owner about restrictions. If the command is missing across workbooks, check for ribbon customization, updates, or organization-managed settings. This sequence helps narrow the cause without changing or deleting the source data.
Related
- Real Estate Excel Template for Cash Flow: Build One
- Real Estate Excel Budget Template: Setup and Costs
- Randomize Numbers in Excel Without Duplicates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Remove Spaces After Text in Excel Cells with TRIMTo remove only ordinary spaces at the end of text while preserving spaces inside words, put this formula…
- Paste a Formula in Excel Down a Column: Step-by-StepTo paste a formula down a column in Excel, enter it in the first cell, select that cell…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- How to Filter Dates in Excel: A Step-by-Step GuideTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the dropdown…
- Excel IFS Formula with Dates: Examples, Rules, and TipsTo use IFS with dates in Excel, compare a date cell with date boundaries created by DATE. For…
- How to Find XLOOKUP in Excel and Use It in a FormulaTo find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup &…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.