How to Filter by Color in Excel (2 Examples)

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.


Download Practice Workbook

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


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


Similar Readings


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:

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: Filter Multiple Criteria in Excel with VBA


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.

Your questions will be answered as soon as possible by the Exceldemy professionals.


Further Readings

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo