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.

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.

Step 2:

• Click the Home

Step 3:

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

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

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).

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

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.

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

Therefore, you can restore the prior data set.

Read More: How to Filter Multiple Columns by Color in Excel (2 Methods)

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.

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.

Step 3:

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

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

Read more: How to Filter by Multiple Colors in Excel (2 Easy Methods)

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

• Filter by Text Color in Excel (5 Easy Methods)
• Add Filter in Excel (4 Methods)
• How to Filter by Color Using Conditional Formatting in Excel
• Remove Filter in Excel (5 Easy & Quick Ways)
• How to Filter by Cell Color in Excel (2 Easy Ways)
• Remove Filter by Color in Excel (5 Methods)
• How to Apply Multiple Filters in Excel [Methods + VBA]