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

In this article, I will discuss how you can filter multiple columns by color in Microsoft Excel. Usually, when we filter a certain column, other columns present in the dataset are filtered according to the first filter applied. Filtering multiple columns is a bit tricky. However, I will show you how you can filter multiple columns by color. Besides, I will filter data by multiple colors too.

How to Filter Multiple Columns by Color in Excel: 2 Suitable Methods

Suppose we have the below dataset containing several people’s names and their residing cities in columns B and C.

Now, I will apply different colors in the above dataset based on the data type and filter them by color using two methods.

1. Use Excel Filter Feature to Filter Multiple Columns by Color

In this method, I will filter multiple columns by color only using the regular Filter option of Excel. In the below dataset, names are starting with the letters A, J, and M. So I highlighted cells in column B with different colors according to the first letter of the name. For instance, all the names starting with A are of green color. Later, I highlighted duplicate cities in similar colors. To illustrate, the city: Phoenix is present twice, so both of the cells are blue. Now I will filter both column B by green color and column C by blue.

Steps:

• First, select any cell in the dataset, then from Excel Ribbon, go to Data > Filter. Or you can use Ctrl + Shift + L to apply the Filter.

• As a result, the drop-down arrow for the filter appears.

• Next, click on the dropdown arrow for column B, select Filter by Color, and choose green from the Filter by Cell Color (see screenshot).

• Consequently, we will see column B is filtered by green color.

• Now I will filter column C by another color. To do that, select the drop-down arrow, click on the Filter by Color,and choose the blue color.

• Then we can see that column C is filtered by the blue color. So we have become successful in filtering multiple columns by color which was our requirement.

2. Filter Multiple Columns by Color Using Excel VBA

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 columns B and C. Then I will filter column B for names starting with J (peach color) and column C for the city: Portland (light orange color). Follow the below steps to perform the task.

Steps:

• Firstly, I will add two helper columns to my original dataset.

• Secondly, I will create a UDF. To do that, go to Developer > Visual Basic to bring the VBA window. Or you can press Alt + F11 too.

• When the VBA window appears, right-click on the VBAProject, and go to Insert > Module.

• Now type the below code in the Module.

Function FindColor(n As Range) As Integer
FindColor = n.Interior.ColorIndex
End Function

• Then come to the worksheet where you want to apply the newly created function, type the below formula in Cell D5, and hit Enter.

• After entering the above formula we will get the color index of Cell B5. Later, I used the Fill Handle (+) tool to get the color index for the rest of the colored cells of column B.

• Similarly, use the same function to get the color index of cities in column C.

• Now, we will apply the Filter option to the dataset using Ctrl + Shift + L. Initially, click on the filter drop-down arrow of column D put a checkmark only for the color index 19, and press OK.

• As a consequence, we will get the below output. As we have filtered column C only for peach color (index: 19), column B is filtered for the same color which contains names starting with the letter J.

• Similarly, I will filter column E for color index 36.

• Finally, when column E is filtered for the color index 36, column C is filtered for the light orange color which contains the city of Portland. It is clearly seen that we successfully applied a filter to multiple columns (columns B & C) by the below colors.

Filter Data by Multiple Colors in Excel

Usually, in Excel, we can filter data by one color only. But you can filter rows or a column by multiple colors too. We will use VBA UDF to perform the task. Suppose we have highlighted the rows of the below dataset in different colors. Now I will filter the below rows by the color green and blue.

Steps:

• First, create the VBA UDF to find the color index. To do that, press Alt + F11 to bring up the VBA window. Next, Insert a Module from VBAProject. Then, in the new Module, type the similar VBA code that I have used for Method 2. I have shown the creation of the UDF in the previous section (see Method 2).
• Then, insert a helper column to the original data and type the below formula in Cell D5. After that press Enter.

• As a result, we will get the color index for all colors present in the below rows.

• Now apply the Filter to the below dataset. Then, click on the drop-down icon of column D, and put a checkmark only on the index: 15 and 24. After that press OK. Consequently, my dataset is filtered for rows colored in blue and green.

• At last, you will see that we are successful in filtering rows by multiple colors (blue and green) at once.

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods to filter multiple columns by color in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

<< Go Back to Color Filter | Filter in Excel | Learn Excel