Sometimes when you use a filter in Excel, you may find that it is not doing what you expect it to do. This could end up affecting other functions in your document and leave you confused and frustrated.
Below we will discuss possible reasons why the filter has failed—and how to address the issue.
1. Check that you have selected all the data.
The first step is to confirm if you have any empty rows or columns in your data. If so, you should correct this. The solution is quite simple.
1. Select the data with empty rows or columns.
2. Go to the Home tab.
3. Click “Sort & Filter.”
4. Choose the “Filter” option.
5. Click the arrow in the column header to reveal the filter options.
6. Remove the check mark from the “(Blanks)” option to deselect it.
7. Press “OK.”
That’s it! The blank cells have been filtered out.
2. Check for errors.
One of the reasons why a filter does not work is the presence of errors in your worksheet. You need to use the following guide to remove possible errors:
1. Select the cells where you want to find errors.
2. Navigate to the Home tab.
3. Choose “Sort & Filter.”
4. Select the Filter option.
5. Press the arrow in the column header to reveal your filter options.
6. Remove the check mark where you see the mistake. In the case of this example, the word “PROFIT” is out of place among the numbers and should be removed.
7. Click “OK.”
There you go! The misplaced data has been filtered out.
3. Check for hidden rows.
Is your filter still not working? Make sure there are no hidden rows confusing the filter. To reveal any hidden rows, follow these steps:
1. Click on the column heading.
2. Go to the Home tab.
3. Select “Format.”
4. Navigate to the Hide & Unhide option.
5. Choose “Unhide Rows.”
Any hidden rows have now been revealed.
4. Check your column headings.
Check to make sure your data has only one row of column headings. Multiple rows for headings can confuse the filter. If your heading title takes up more than one line, you can address that in just two steps:
1. Enter the text you need.
2. Press the Alt + Enter key combination to move text to a second line in the heading cell.
Another way to allow multiple lines of text in one cell is to use a function in Excel called Wrap Text. It’s quite simple.
1. Type the text you need.
2. Click the Home tab.
3. Select the “Wrap Text” button in the Toolbar.
Easy as pie!
5. Check for merged cells.
Another common reason why an Excel filter doesn’t work is the presence of merged cells. You will need to unmerge Excel cells in order to properly use the filter.
1. Select the cells that are merged.
2. Go to the Home tab.
3. Click the “Merge & Center” button in the toolbar.
4. Choose the “Unmerge Cells” option.
It’s that simple!
6. Check for grouped sheets.
If you have grouped sheets, this could cause trouble when filtering data. Your best option is to ungroup the sheets—a fairly simple process.
1. Right-click on the grouped sheets.
2. Choose “Ungroup Sheets.”
There’s nothing to it! Your sheets have now been ungrouped.
7. Check for password-protected sheets.
Another reason why your filter may not work is due to the protection of your Excel worksheet. To keep your filter working, you will need to remove the protection. Here’s how to do that:
1. Go to the Review tab.
2. Select the “Unprotect Sheet” option.
3. Enter the password that is currently protecting the sheet.
And that’s it! With a few clicks, you have successfully removed the protection that is blocking your filter.
Excel Filter Troubleshooting FAQ
The Filter button isn’t working.
You may find yourself confused about why the Filter button is not working. The Filter will not work when your worksheets are grouped. To activate the Filter, review the name of your Excel book. If you see “[GROUP]” in the title, you will need to ungroup your Excel sheets. Read the article to find out how.
![“[GROUP]” in the title](https://spreadsheetdaddy.com/wp-content/uploads/2022/01/GROUP-in-the-title.png)
The “Equals” filter isn’t working.
To avoid confusing the Excel Filter option as it works with the “Equals” function, make sure the numbers in all rows and columns reflect the same format. If you have one format in one column or row (such as currency) and a different format in the other column or row (such as date), the filter will not work. Сheck that you have entered the correct number format and try activating the “Equals” button again.
Check for other filters.
Sometimes having many active filters in one worksheet can suspend Excel. In order to have your file work properly, we recommend reducing the number of activated filters. If you no longer use some filters, turn them off and leave only the ones you need. This will help you keep working without interruption.
In this tutorial, we show you how to make sure your Excel filter does its job.
