[Solved]: Filter by Color Not Working in Excel (7 Quick Fixes)

To demonstrate the solutions, we have a sample dataset of employee salaries. In the dataset, three cells with values below $3500 are filled yellow.

Solution 1 – Selecting Entire Dataset

Steps:

• Go to the Data tab and unclick the Filter command.
• Select the range of cells B4:C14.
• Go to the Data tab and click on the Filter option from the Sort & Filter group.

• Click on the drop-down arrow of the January column and select the Yellow color from the Filter by Color option.

• The filtered data will be displayed as shown below:

Solution 2 – Avoid Keeping Blank Rows

The presence blank rows creates problems in getting an accurate result from the Filter by Color option. In the sample dataset, we have a blank row at row number 12. When we apply the Filter by Color option, it will not show the required result.

Steps:

• Select the entire row 12.
• In the Home tab, click on the Delete option from the Cells group.

• The blank row will be deleted.
• Click on the drop-down arrow of the January column and from the Filter by Color option, choose the Yellow color.

• It will display only the required filtered data.

Solution 3 – Use of Unmerge Cell

If the selected range contains any vertically merged cell, the Filter by Color command will not display the desired result. You can see a similar type of issue in row 7:8.

Unmerged any merged cells in the column where you have to apply the command to get an accurate result.

Solution 4 – Remove All Types of Errors

If the dataset contains any type of errors caused by certain calculations, the Filter by Color may not work accurately. To get the correct result, the Filter by Color command will eliminate all types of errors from the dataset.

Solution 5 – Unhide All Rows

Hidden rows sometimes cause some unexpected results when we use the Filter by Color option. You can see that in our dataset, we have only two colored cells.

When we apply the Filter by Color command, it shows three values.

This is because of the hidden rows. You may notice in the first image, our rows 12 and 13 are hidden and one of them was a colored row. Show all rows of your dataset before applying any type of filtration.

Solution 6 – Selecting Single Worksheet to Apply Filter

You cannot do any type of filtration if you select multiple sheets. You can see that the Filter key in the Data is disabled when we select two sheets of a workbook.

Steps:

• Click on the name of any sheet to break down the group.
• Select the range of cell B4:C14 in the Problem sheet.
• Go to the Data tab and select Filter.

• Click on the drop-down arrow of the January column and from the Filter by Color option, choose the Yellow color.

• The data will be filtered accurately.

Solution 7: Installing an Update Version of Excel

The most recent versions of Microsoft Excel can operate the filter command even if the column contains merge cells, errors, and other unwanted issues. Use the updated version of Microsoft Excel or Office 365 to avoid such types of problems.

Download Practice Workbook

<< Go Back to Color Filter | Filter in Excel | Learn Excel