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

Get FREE Advanced Excel Exercises with Solutions!

The Filter is an amazing tool of Microsoft Excel. We can get a particular set of data on the data or the cell colors easily. But, sometimes, the filter by color feature doesn’t work properly. In this article, we are going to demonstrate to you 7 possible solutions to fix the problem if the  Filter by Color is not working in Excel. If you are also curious about it, follow us.


Filter by Color Is Not Working in Excel: 7 Possible Solutions

To demonstrate the solutions, we consider a dataset of the 10 employee’s salaries for January. In this dataset, three cells have different cell colors, and those values are below $3500.

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


Solution 1: Selecting Entire Dataset

Below applying the Filter command, we have to select the entire range of datasets. Otherwise, you will not get an accurate result. We have a similar type of problem due to the presence of a blank row. When we apply the Filter by Color option, it will show more data instead of those three cells.

Excel filter by color not working

The steps to fix this problem are given below:

📌 Steps:

  • First of all, go to the Data tab and unclick the Filter command.
  • Now, select the range of cells B4:C14.
  • Then, again, go to the Data tab and click on the Filter option from the Sort & Filter group.

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

Applying Filter by Color Command to Filter Data

  • You will get those filtered data.

Final result after using Filter by Color option

Thus, we can say that our procedure works perfectly, and the problem that is not working in Excel is solved.


Solution 2: Avoid Keeping Blank Rows

Sometimes, the presence of a blank row also creates problems in getting an accurate result from the Filter by Color option. In our dataset, we have a blank row at row number 12. Hence, when we applied the Filter by Color option, we didn’t get the exact result.

Excel filter by color not working

The steps to fix this issue are given below:

📌 Steps:

  • First, select the entire row 12.
  • After that, in the Home tab, click on the Delete option from the Cells group.

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

Applying Filter by Color Command to Filter Data

  • You will notice only three filtered data.

Final result after using Filter by Color option

Hence, we can say that our working process works effectively, and the trouble is fixed.


Solution 3: Use of Unmerge Cell

Due to the presence of the vertical merge cell, the Filter by Color command cannot work properly and show the result. You can see a similar type of issue in row 7:8.

Dataset with merged cell causing Excel Filter by Color not working

Always try to keep an unmerged cell in that column where we have to apply the command. You will get an accurate result and fix the problem.


Solution 4: Remove All Types of Errors

When the dataset contains any type of errors that come after some certain calculation, the Filter by Color may not work accurately, and we did not get an accurate result. To get the right result, the Filter by Color command eliminates all types of errors from the dataset. It will help you to fix the problem.


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.

Dataset with hidden rows

But, when we apply the Filter by Color command, it returns three values.

Final result after using Filter by Color option

This dilemma is caused 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. So, try to show all rows of your dataset before applying any type of filtration. You will be able to resolve the problem.


Solution 6: Selecting Single Worksheet to Apply Filter

You cannot do any type of filtration if you keep selecting multiple sheets at the same time. You can see that the Filter key in the Data is disabled when we select two sheets of a workbook on our device.

Filter by Color not working to selecting multiple sheets

The steps to enable the Filter button is given as follows:

📌 Steps:

  • At first, click on the name of any sheet to break down the group.
  • Then, select the range of cell B4:C14 in the Problem sheet.
  • Now, go to the Data tab, and you will find the Filter key enabled in the Sort & Filter group.
  • Click on the Filter option.

Enabling Filter command

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

Applying Filter by Color Command to Filter Data

  • You will get the filtered data accurately.

Final result after using Filter by Color option

Therefore, we can say that our procedure works successfully, and you can overcome the Issue.


Solution 7: Installing an Update Version of Excel

It is the ultimate way to fix the problem. The most recent versions of Microsoft Excel can operate the filtration work even if the column contains merge cells, errors, and other unwanted issues. So, we recommend you install and use the updated version of Microsoft Excel or Office 365 to avoid such types of problems.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to resolve the problem, Filter by Color option is not working in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo