If you are unable to filter in Excel, select the full data range, confirm it has one clear header row, then choose Data > Filter. If the filter arrows are present but the results look wrong, use Data > Clear to remove the current criteria and check for blank rows, merged cells, hidden rows, or worksheet protection.
What Excel filtering does
Excel’s AutoFilter hides rows that do not match the conditions you choose; it does not delete or change the underlying records. For example, a sales list can be filtered to show only one region or dates after a certain day. The rows that do not match remain in the worksheet and reappear when you clear the filter.
When AutoFilter is on, Excel displays a drop-down arrow in each header cell. The arrow opens options for that column, such as selecting specific values, searching the list, filtering by date or number, and filtering by cell color. A missing arrow usually points to a range or display issue. An arrow that works but produces unexpected results more often points to the data or to criteria already applied.
Turn on a filter for the right range
For a standard list, click a cell inside the data and choose Data > Filter. Excel usually detects the surrounding contiguous data automatically. If it does not, or if the worksheet contains nearby information that should not be included, select the exact range first, including its header row, and then choose Data > Filter.
- Check that the first row of the selected range contains field names such as Date, Customer, and Amount.
- Click inside the list or select the complete range, including all relevant columns.
- Choose Data > Filter. Confirm that a drop-down arrow appears in each header cell.
- Open an arrow and select a value, search for a value, or choose a relevant text, number, or date filter.
On Windows, Ctrl+Shift+L toggles AutoFilter for the current range. Pressing it again removes the filter arrows, so it can appear as though filtering has stopped working. On Mac, use Data > Filter if you are unsure of the shortcut available in your Excel version. The command is also available from the Data tab in Excel for the web, although the interface may differ slightly.
Check the data range and headers
A filter applies to a rectangular block of data, not necessarily to every value that looks like part of the same list. A completely blank row or column can make Excel treat data below or beside it as a separate block. If you turn on a filter in the upper block, records beyond the gap will not be included. Select the intended full range explicitly, or remove the separating blank row or column if it is not meaningful.
Keep one header row at the top of the list. Avoid a title row above the headers, multiple header rows, and blank header cells. If a heading is missing, Excel may use a generic name in a table, or the range may be interpreted in an unexpected way. Give every column a distinct, nonblank label, then apply the filter again. A heading such as “Status” is more useful than a merged heading spanning several columns.
Also inspect merged cells. Merging cells inside a list can interfere with selecting, sorting, and filtering a consistent rectangular range. Unmerge cells in the data area and repeat the filter using a simple header row. If you need a visual heading across columns, place it above the list rather than merging cells within the records.
Clear existing criteria without removing the arrows
A filter may be working correctly while hiding the records you expect to see. A funnel symbol on a header arrow indicates that a criterion is active. Open that column’s menu and choose Clear Filter From [column name], or use Data > Clear to clear filter criteria across the current filtered range. The arrows should remain available. By contrast, choosing Data > Filter toggles AutoFilter itself and can remove the arrows.
Check every column, not just the one you are trying to filter. A selection in one column combines with selections in other columns, so the visible rows must satisfy all active criteria. For instance, filtering Region to West and Status to Open will show only records matching both conditions. Clearing just the Region condition will still leave the Open-status restriction in place.
Filters can also hide rows because of criteria that are easy to overlook, such as a date range, a text condition, or a color selection. Open the relevant drop-down and review the checked values and filter options. If you want to see the complete list again, clear all the criteria rather than repeatedly changing one dropdown.
Use an Excel Table for a growing list
If records are added regularly, converting the list to a Table can make its boundaries easier to manage. Select a cell in the data and choose Insert > Table, or use Ctrl+T on Windows. Confirm that the selection is correct and that My table has headers is checked when the first row contains column names. Tables include filter arrows by default and usually extend their structured range as new rows or columns are added directly beside them.
A Table is helpful when new records are appended, but it does not repair inconsistent data or include unrelated cells separated from the table by blank rows or columns. If an existing filter seems to omit new records, check whether those records are actually inside the table. Click in the table and look for the Table Design tab, then check its visible boundary. You can resize the table if necessary, or place new records directly below it so Excel can expand it.
Look for protection, hidden rows, and display issues
A protected worksheet can prevent users from changing filter settings. If you can view the arrows but cannot use them, the sheet may be protected. Check Review > Unprotect Sheet if you have permission and the password, if one is required. When protecting a sheet, the person setting up protection can allow filtering by selecting Use AutoFilter in the Protect Sheet options. That permission must be configured as part of the protection settings; do not try to bypass protection you are not authorized to change.
Rows hidden manually are a separate issue from rows excluded by a filter. Clearing filter criteria does not necessarily reveal rows that someone hid with Hide, grouped rows, or an outline. Select the rows around the gap, right-click, and choose Unhide, or inspect the row numbers for skipped values. Also check whether worksheet zoom or a narrow window makes the arrows difficult to see; scrolling horizontally may reveal columns outside the current view.
Fix values that Excel reads inconsistently
Excel decides which filter choices to show based on the values in a column. Numbers stored as text can behave differently from numeric values, and dates stored as text may not appear in date-filter groups. For example, a column that looks like dates may contain a mixture of real Excel dates and text strings. Check a few individual cells and compare their alignment or number format, but remember that formatting alone does not prove the stored value is the right type.
Use consistent data in each column. Convert text numbers or text dates to the appropriate values when they need numeric or date filtering, and check for leading or trailing spaces when entries that look identical appear as separate choices. A value such as “Open” and “Open ” can be treated as different text. The exact cleanup method depends on the source and should be checked before changing important records.
For a quick diagnostic, remove the criteria and filter one column at a time. If a value is missing from the dropdown, verify that it exists within the selected range and is not below a blank row. If a filter returns too few records, inspect whether the column mixes data types or whether another column still has an active condition. This separates a range problem from a content problem.
When filtering is unavailable in a shared or web workbook
In a shared workbook, another person’s view or worksheet settings can affect what you see, depending on how the file is shared and which Excel features are being used. Excel’s Sheet View feature can let users sort and filter a worksheet without changing the view for everyone else. If a coworker says the list is filtered differently, check the View tab and whether you are in a particular Sheet View before changing shared data.
Excel for the web supports ordinary filtering, but some workbook features and controls can differ from desktop Excel. If a command is unavailable or the workbook behaves differently, open it in the desktop app if you have access, and check whether the sheet is protected or the file is in a restricted editing state. Avoid making a separate copy as a first fix if other people are actively editing the shared source; that can lead to conflicting versions.
When to use another approach
AutoFilter is designed for exploring and temporarily narrowing a list. If you need a separate, automatically updated result area while keeping the original records visible, Excel for Microsoft 365 and Excel 2021 also have the FILTER function. That is a different feature from the Data tab’s filter: it returns matching values in another area and can spill results into neighboring cells. It is not a fix for a broken AutoFilter, and it requires an appropriate formula setup.
For routine lists, a Table with consistent headers is generally the simplest option. For a one-time inspection, ordinary AutoFilter is usually enough. If the same complex criteria must be applied repeatedly, consider documenting the steps or using an appropriate analysis tool rather than relying on a long sequence of manual dropdown selections. Choose based on whether you want to hide rows temporarily, create a separate result, or repeat a defined data-cleaning process.
Frequently Asked Questions
Why can’t I see the filter arrows in Excel?
Click inside the intended data range and choose Data > Filter. If arrows still do not appear, select the complete range including its header row, then check for blank rows, merged cells, or an unexpected selection boundary.
Why does Excel filter only part of my data?
The selected range may stop before all the records, or a fully blank row or column may split the list. Select the entire intended range before applying the filter, and consider converting a continuous list to a Table.
How do I remove a filter but keep the dropdown arrows?
Choose Data > Clear to remove active criteria, or clear the filter from the relevant column’s dropdown. Do not toggle Data > Filter if you want to keep the arrows, because that command turns AutoFilter off or on.
Why is my filter hiding rows I need?
Review criteria in every column; multiple filters work together. Also check manually hidden or grouped rows, since clearing a filter does not necessarily unhide rows hidden separately.
Why can’t I filter a protected worksheet?
Worksheet protection may restrict filter use. Ask the workbook owner to adjust the protection settings or unprotect the sheet if you are authorized. When protection is set up, Use AutoFilter must be allowed for users to filter the protected sheet.
Why are some dates or numbers missing from the filter list?
The column may contain inconsistent types, such as real dates mixed with date-looking text, or numbers stored as text. Check that the values are inside the filtered range and convert inconsistent entries carefully if the filtering needs to treat them as dates or numbers.
Related
- Excel Strikethrough Shortcut: Fast Ways to Cross Out Text
- Compare Two Excel Spreadsheets Quickly: Best Methods
- Shade Every Other Row in Excel: Quick, Reliable Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Why Is My LEFT Formula Not Working in Excel?Most LEFT formulas fail because Excel is reading the entry as text, the formula syntax or argument separator…
- Excel Copy Formula Down a Column Not Working? FixesIf copying a formula down an Excel column is not working, select the formula cell and the cells…
- EDATE Not Working in Excel? Fix Common ErrorsIf EDATE is not working in Excel, first check that its start date is a real Excel date,…
- Excel COUNTIF Not Working? Fixes & ExamplesThe direct answer: if your Excel COUNTIF formula is returning 0, the wrong count, or a #VALUE! error,…
- Excel Repair Not Responding? Fix the Hang and RecoverIf Excel’s repair process is not responding, first give it time to finish, especially if the workbook is…
- Excel Solver Not Converging? Fix the ModelIf Excel Solver is not converging, first check that the selected solving method matches your model, then verify…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.