How to fix filter errors in Excel spreadsheets

Most users often have the habit of using built-in Excel filters to filter the data in their spreadsheets. However, many users have reported that Excel spreadsheets filter data incorrectly, causing a lot of annoyances during use.

So if you also encounter the same problem as above and need to fix Excel filter error, then take a look at some of the best fixes that I will guide in the article below.

1. Select the entire column to apply to the filter

The steps are as follows:

Step 1: Select the entire column in the worksheet by clicking on the letter of the column> clicking the Data tab .

Step 2: Then click the Filter button to apply the filter to the entire selected column.

Step 3: Click the arrow button of the cell filter to open the filter options shown below.

Step 4 : Now you can cancel the check mark in Blanks box to remove the blanks.

2. Delete blank cells from the column in the worksheet

In addition, you can delete blank rows from column in worksheet including values ​​below blank cells in filter.

To do this, select all rows of empty cells by holding down the Ctrl key . Then right-click and select the Delete option to delete them.

3. Ungroup the spreadsheet

The Filter options appear greyed out when the worksheet is grouped together. Therefore, you cannot filter data against that group of worksheets.

To fix this, right-click the grouped pages at the bottom of Excel and choose Ungroup sheets.

4. Use the Unprotect feature

The steps are as follows:

Step 1: Click the Excel Review tab , then click the Unprotect Sheet button .

Step 2: If the Unprotect Sheet asks for a password, you'll need to enter it in the login window that opens. Then click the OK button .

5. Merge cells in the worksheet

Step 1: Press the shortcut Ctrl + F, then select the Format button on the Find and Replace window .

Step 2: Click Merge cells on the Alignment tab shown right below and click the OK button .

Step 3: Click the Find All button . Then the Find and Replace window will list all cell references to the merged cells.

Step 4: To unmerge merged cells , select a merged cell, click Merge & Center option in Home tab > select Unmerge cells option .

6. Set up a new filter

If the rows in your Excel spreadsheet can't be filtered, try setting up a new filter with the following steps:

Step 1: Click the Data tab > click the Clear button in Sort & Filter.

Step 2: Then select the full column range of the workbook with the cursor.

Step 3: Click the Filter button on the Data tab of Excel and you're done.

Above are some methods that will be able to fix the filter-related errors of Excel workbook. In most cases, reapplying filters or removing them to set up a new filter will be the best ways to resolve these problems in Excel.

So I have instructed the easiest ways to fix filter errors in Excel spreadsheets. Hope this article will be useful for you. Wish you all success!

Samuel Daniel

Update 21 August 2020