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.
- First of all, select the data table in the range.
- Click the Home
- 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.
- 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.
- 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.
- How to Filter Data in Excel using Formula
- Shortcut for Excel Filter (3 Quick Uses with Examples)
- Filter by Date in Excel (4 Quick Methods)
- How to Filter Excel Pivot Table (8 Effective Ways)
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.
- Press Alt + F11 to activate VBA Macro-Enabled Worksheet.
- Click on Insert Tab
- Select Module from the menu.
- 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
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.
- Finally, save the program and press F5 to run it.
As a result, you get the filtered result in your current worksheet.
- 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
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.