In this article, I will discuss how you can filter by multiple colors in Microsoft Excel. Usually, when we filter a certain column by multiple colors, other columns present in the dataset are filtered according to the first filter applied. Filtering data by multiple colors is a bit tricky. However, I will show you two suitable ways to filter by multiple colors in Excel effectively with appropriate illustrations.
How to Filter by Multiple Colors in Excel: 2 Suitable Ways
Suppose we have the below dataset containing several sales representatives’ names and their region of sales, and revenue earned by the sales representative is given in columns B, C, and E respectively. We will create a User Defined Function using VBA code and manually using the Color code to filter data by multiple colors. Here’s the overview of our dataset.
1. Use Color Code to Filter by Multiple Colors in Excel
In this method, I will filter our data by multiple colors only using the color code option of Excel. To do that, you have to know the cell’s color code. The code for Red is 3, for Yellow 6, for Green 14, and so on. Let’s follow the instructions below to filter by multiple colors!
Step 1:
- First, select any cell in the dataset, then from Excel Ribbon, select the Filter command from the Data tab to apply the Filter.
- As a result, the drop-down arrow for the filter appears.
- Next, click on the drop-down arrow for column D, and check the 3, 6, and 14 options to Filter by Color code. At last, press the OK option.
- As a result, you will be able to filter by multiple colors using the color code that has been given in the below screenshot.
Step 2:
- You also can do that using the Sort To do that, click on the drop-down arrow for column C, select Sort by Color, and then choose Custom Sort.
- Hence, a dialog box named Sort will appear in front of you. From the Sort dialog box, first, select Region under the Sort by drop-down list. After that, select Cell Color under the Sort On drop-down list. Hence, select the Red color under the Order drop-down list. Further, press the Add Level to add several colors.
- After that, do like in the below screenshot using Add Level At last, press the OK option.
- As a result, you will be able to filter by multiple colors using the Custom Sort command which is given in the below screenshot.
2. Run VBA Code to Filter by Multiple Colors in Excel
This time, I will use a simple VBA code to create a User Defined function and thus find the color index of each color I have applied to cells of column D. Then I will filter column D for multiple colors. Let’s follow the instructions below to filter by multiple colors using a simple VBA code!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Filter by Multiple Colors will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
- Hence, the Filter by Multiple Colors module pops up. In the Filter by Multiple Colors module, write down the below VBA
Function FindColor(n As Range) As Integer
FindColor = n.Interior.ColorIndex
End Function
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
Step 2:
- Then come to the worksheet where you want to apply the newly created function, type the below formula in Cell E5, and hit Enter on your keyboard. The User-Defined Function is,
=FindColor(D5)
- After entering the above formula we will get the color index of Cell D5. Later, I used the AutoFill Handle (+) tool to get the color index for the rest of the colored cells of column D.
- Now, we will apply the Filter option to the dataset using Ctrl + Shift + L. Initially, click on the filter drop-down arrow of column E and put a checkmark only for the color index 3, 6, 14, and At last, press OK.
- As a result, you will be able to filter by multiple colors using the VBA code which has been given in the below screenshot.
Read More: How to Filter Multiple Columns by Color in ExcelÂ
Things to Remember
➜ You can use Ctrl + Shift + L as the keyboard shortcut for filtering data.
➜ If your Developer tab is not visible, you will press Alt + F11 to enable macros.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to filter by multiple colors will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
