How to Filter by Color in Excel (2 Examples)

Get FREE Advanced Excel Exercises with Solutions!

In financial big data analysis, you could find that applying a filter to your analysis helps you examine your data more effectively. When data is filtered, only the rows that match the filter criteria are shown; the remainder is hidden. Filtered data may be copied, formatted, printed, and so on without having to sort or move it beforehand. This tutorial will explain to you how to filter by color in excel using both the conventional method and the VBA code.


2 Different Ways to Filter by Color in Excel

The following two parts will explain how to apply a color filter. The first is the usual approach, which is well-known, and the second is to use VBA code. It’s a good idea to learn how to use VBA to expand your skillset.

For example, we have a sample data set in which we use two distinct colors to differentiate between two criteria. The first criterion we have set is that the purchase amount in January must be greater than 20 and the other requirements must be less than 20. In addition, you could wish to filter the color by a specific criterion to examine the value all at once.

Apply the Basic Method

1. Apply the Basic Method to Filter by Color in Excel

To establish comparisons between particular criteria, you may need to distinguish between the data. While working, you might wish to look up the values under the same criteria. Simply follow the instructions below to filter the dataset to differentiate by color.

Step 1:

  • First of all, select the data table in the range.

Apply the Basic Method to Filter by Color in Excel

Step 2:

  • Click the Home

Apply the Basic Method to Filter by Color in Excel

Step 3:

  • After Selecting the Home Tab, click on Sort & Filter
  • Choose the Filter option from the menu.

Apply the Basic Method to Filter by Color in Excel

As a result, a drop-down button will emerge in the table header as shown in the below screenshot.

Apply the Basic Method to Filter by Color in Excel

Step 4:

  • Click on the drop-down button to open options for filtering.
  • Select the Filter by Color
  • Then, show any of the colors you want to filter. Here we have selected the first color RGB (248, 203, 173).

Apply the Basic Method to Filter by Color in Excel

Therefore, you will get the filtered data with a certain color as shown in the below image.

Apply the Basic Method to Filter by Color in Excel

Step 5:

  • To filter by another color, again click on the drop-down button.
  • Select the new color (RGB = 217,225,242) to filter by.

Apply the Basic Method to Filter by Color in Excel

Consequently, the filtered value by a certain color will show up as shown in the below image.

Apply the Basic Method to Filter by Color in Excel

 Notes  In addition to filtering by color, if you now want to remove the filter just click on the drop-down menu and click on the Clear Filter Form option from the list.

Apply the Basic Method

Therefore, you can restore the prior data set.

Apply the Basic Method

Read More: How to Filter Multiple Columns by Color in Excel


2. Run a VBA Code to Filter by Color in Excel

In addition to the standard technique, you may also use VBA code to filter. Though it is not widely used, it is necessary to learn it in order to broaden one’s skill set. To have it done, follow the outlined steps.

Step 1:

  • Press Alt + F11 to activate VBA Macro-Enabled Worksheet.
  • Click on Insert Tab
  • Select Module from the menu.

Run a VBA Code

Step 2:

  • Paste the following VBA codes.
Sub Filter_by_color()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
ws.Range("B4:D11").AutoFilter field:=3, Criteria1:=RGB(248, 203, 173), Operator:=xlFilterCellColor
End Sub

Here,

Dim ws As Worksheet is declaring ws as a worksheet.

Worksheets(“Sheet2”) is the current worksheet name.

ws.Range(“B4:D11”) is the range of the table.

AutoFilter field:=3 is the column number (3) for which we assign the filter

Criteria1:=RGB(248, 203, 173) is the color code of the filtering color.

Run a VBA Code

Step 3:

  • Finally, save the program and press F5 to run it.

As a result, you get the filtered result in your current worksheet.

Run a VBA Code

 Notes  Sometimes, your excel filtering option may not work. There could be some possible reasons for that. Try to fix these problems.
  • Make sure you have selected all the data.
  • It won’t work in merged cells. Unmerge the cells and try again.
  • Make sure your data table has just one column heading. Look for hidden rows or errors in your data table.
  • If the filter button is greyed out, ungroup the data and now your filter option will be available.

Read More: How to Filter by Color Using Conditional Formatting in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

To summarize, I hope this post has illustrated how to use Excel’s color filtering feature to distinguish values based on several criteria. These methods should all be taught and used on your data. Examine the practice book and put your newfound knowledge to use. We are able to sponsor programs like this because of your generosity.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.


Further Readings


<< Go Back to 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.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo