How to Clear All Filters in Excel (and Show Hidden Rows)
Half of your rows are gone, but you did not delete anything. Most likely a filter is on. Your data is safe. Here is how to clear the filter and see all rows again. Start with Fix 1.
Clear all filters in one click
- Click any cell in your list.
- Go to the Data tab.
- Click Clear in the Sort and Filter group.
All filters on this sheet are gone and all rows are back. The filter arrows stay in the header row, so you can filter again.
Clear one column or remove the filter arrows
To clear only one column:
- Click the small arrow in the header of that column.
- Choose Clear Filter from and the name of the column.
To remove the filter arrows completely, click Filter on the Data tab. The shortcut is Ctrl+Shift+L. Click it again to turn the filter back on.
Show rows that are still hidden
Sometimes the filter is off, but rows are still missing. Someone hid them by hand. Row numbers that jump, like 4 and then 9, are the sign.
- Click the small triangle in the top left corner of the sheet to select everything.
- Right click a row number.
- Choose Unhide.
Do the same for columns if letters are missing. Check other sheets too. A filter works only on its own sheet.
Filter shows the same value twice? Clean hidden spaces
You open the filter list and see Boston two times. One of them has a space at the end. Excel treats them as two values, so your filter misses rows. Run the Clean Whitespace tool first, then filter again. The same problem breaks lookups, see our guide on VLOOKUP #N/A.
Clear filters in Excel: before and after
| Rows you see | What is on | |
|---|---|---|
| Before | 3 of 500 | Filter on column City |
| After Clear | 500 of 500 | No filter |
The bottom bar shows how many rows match. After Clear it shows all rows again.
Make your filters work on clean data
The Clean Whitespace tool runs on your own computer. Your file is never uploaded.
Questions about clearing filters in Excel
Do I lose data when I clear a filter?
No. A filter only hides rows. Clear removes the filter and shows them again.
Why can I still not see all my rows?
Some rows may be hidden by hand. Select the whole sheet, right click a row number and choose Unhide.
What is the shortcut to turn a filter on or off?
Ctrl+Shift+L turns the filter arrows on and off on Windows.
Does Clear remove the filter on all sheets?
No. It works on the sheet you are on. Check your other sheets too.
Why does my filter list show duplicates?
Hidden spaces or different spelling. Clean the spaces first with the Clean Whitespace tool.
More Excel guides
Remove Duplicates in Excel Without Losing Data (3 Ways)
Find double rows and remove them, without deleting the wrong ones.
Excel Keyboard Shortcuts: 30 You Need (Windows and Mac)
30 shortcuts for Windows and Mac that make list cleaning much faster.
VLOOKUP Returns #N/A: How to Fix It (Easy Steps)
The value is in your list, but VLOOKUP does not find it. Here is the fix.