If Excel filter is not working after a certain row, for merged cells, on large files, or on a protected Sheet, you can follow these solutions to resolve the issue. These tips and work on Microsoft Excel 365 as well as Office 2021/19 as well as older versions.
Fix Excel filter not working properly
If the Excel filter is not working properly, follow these suggestions:
- Check for error
- Select entire data
- Unhide hidden rows and columns
- Unmerge cells
- Ungroup sheets
- Unlock protected sheet
To learn more about these steps, continue reading.
1] Check for error
Checking the error is the very first thing you need to do to fix this issue. Filters do not work properly when you have one or multiple errors in your spreadsheet. To get rid of this issue, you need to find the specific cell containing the error and fix it accordingly.
2] Select the entire data
To use the Filter functionality, you need to select the entire data first. If you skip one row or column, it may not work all the time. On the other hand, if you have a large sheet and multiple blank rows in the data, Excel will choose the row up to the first blank row.
That is why you can either use Ctrl+A or your mouse to select the entire data in your spreadsheet. Following that, you can click on the Sort & Filter menu to apply the filter.
3] Unhide hidden rows and columns
Excel allows users to hide rows and columns according to their requirements. If you have hidden a row or column, you cannot see the expected result even if everything is in line. That is why it is recommended to unhide all hidden rows and columns before using the filter. For that, do the following:
- Find the hidden row or column.
- Right-click on the hidden row or column.
- Select the Unhide option.
Next, you can use the usual option to apply filters.
Related: Excel is slow to respond or stops working
4] Unmerge cells
If you have merged two or more cells in your spreadsheet, the excepted result will be different from the shown values. That is why it is recommended to unmerge cells before you include filters. To unmerge cells in Excel, follow these steps:
- Select the merger cell in your spreadsheet.
- Click on the Merge & Center option.
- Select the Unmerge Cells option.
After that, check if it resolves your issue or not.
5] Ungroup sheets
If you have multiple sheets in a spreadsheet, you might have consolidated them in a group. If the filter option is not working properly, you can ungroup those sheets and check if it resolves your issue or not. To ungroup sheets, follow these steps:
- Select one sheet first.
- Press and hold the Ctrl button.
- Click on another sheet in the same group.
- Right-click on either of them.
- Select the Ungroup Sheets option.
Whether you have two or more sheets, you can apply the same technique to ungroup sheets and use the Sort & Filter option.
Read: Cannot create List in Excel: The file does not exist
6] Unlock protected sheet
If you have a password-protected sheet, Filter won’t work as per your requirements. Therefore, follow these steps to unlock the protected sheet:
- Go to the Unprotect Sheet option.
- Enter the password.
- Click the OK button.
Then, check if you can use the option normally or not.
Read: How to delete text vertically in Word or Excel
Why is my Excel not filtering correctly?
There could be several reasons why Excel is not filtering correctly. For example, if you have a protected sheet, filters won’t work. On the other hand, you need to choose the entire data or all rows and columns in order to make the Filter work. Apart from that, you can try unhiding hidden rows and columns as well.
Why is Sort and Filter not working in Excel?
If you have hidden rows or columns, protected sheet, or an error in various cells, Sort & Filter option might not be working at all. To troubleshoot the issue, you can go through the solutions mentioned above one after one. You can unlock protected sheet, unhide hidden rows and columns, etc.
Why is Excel not including all rows in filter?
This problem mainly occurs when you have a large Excel file with multiple blank rows. Excel automatically selects data up to the first blank. That is why Excel is not including all rows in filter. To get rid of this issue, you need to choose the rows and columns manually.
