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


Download Practice Workbook

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


2 Methods to Filter Multiple Columns by Color in Excel

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

2 Methods to Filter Multiple Columns by Color in Excel

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.

Use Excel Filter Feature to Filter Multiple Columns by Color

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.

Use Excel Filter Feature to Filter Multiple Columns by Color

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

Use Excel Filter Feature to Filter Multiple Columns by Color

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

Use Excel Filter Feature to Filter Multiple Columns by Color

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

Use Excel Filter Feature to Filter Multiple Columns by 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 to filter 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.

Filter Multiple Columns by Color Using Excel VBA

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

Filter Multiple Columns by Color Using Excel VBA

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

Filter Multiple Columns by Color Using Excel VBA

  • Now type the below code in the Module.
Function FindColor(n As Range) As Integer
FindColor = n.Interior.ColorIndex
End Function

Filter Multiple Columns by Color Using Excel VBA

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

Filter Multiple Columns by Color Using Excel VBA

  • 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 and put a checkmark only for the color index 19, and press OK.

Filter Multiple Columns by Color Using Excel VBA

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

Filter Multiple Columns by Color Using Excel VBA

  • 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: 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.

Filter Data by Multiple Colors in Excel

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.
=FindColor(B5)

Filter Data by Multiple Colors in Excel

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

Filter Data by Multiple Colors in Excel

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

Filter Data by Multiple Colors in Excel

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


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.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo