How to Filter Data by Multiple Cell Colors in A List in Excel

As we all know we can only filter data in a list by one color due to limit of ‘Filter by Cell Color’ function. If we want to filter data by multiple cell colors, for example we mark cells in red, yellow, blue and green four color but we only want filter data marked in red and yellow, how can we do in this situation? Actually, we can achieve filtering by several cell colors via adding another column of color parameter. If you are confused about the method, please read this free tutorial, we will show you all steps to help you implement filtering multiple cell colors in your list.

Precondition:

We make products’ status in Broken, Missing, Good and Locked. And we mark different cell colors for different status. Now we want to filter status marked in yellow (broken) and red (missing), how can we filter by colors? (Though we can directly select Broken and Missing in status filter list, below sample is used to illustrate the way to select multiple cell colors.)

How to Filter Data by Multiple Cell Colors in A List 1

Method: Filter Data by Multiple Cell Colors Via Add Color Parameters Column


Actually, in excel each color has its own color index. We can get all index firstly, then though filter one or two index to filter matched color cells. See details below.

Step 1: Enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

How to Filter Data by Multiple Cell Colors in A List 2

Step 2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1. This step we define a function called ShowColorIndex.

Function ShowColorIndex(x As Range) As Integer

ShowColorIndex = x.Interior.ColorIndex

End Function

Step 3: Save code and then quit Microsoft Visual Basic for Applications.

Step 4: In cell C2 enter the formula =ShowColorIndex(B2). After typing ‘=Show’, defined function How to Filter Data by Multiple Cell Colors in A List 3

is displayed.

How to Filter Data by Multiple Cell Colors in A List 4

Step 5: Drag fill handle down till the last cell in the list. Then we get all color index. We can see that yellow=6, red=3, blue=33, green=43.

How to Filter Data by Multiple Cell Colors in A List 5

Step 6: Click Data->Filter to create filter for color index.

How to Filter Data by Multiple Cell Colors in A List 6

Step 7: On column C, click the small triangle to load filter criteria, as we want to filter yellow cell and red cell, just check on number 3 and 6. Then click OK.

How to Filter Data by Multiple Cell Colors in A List 7

Step 8: Check the result. Verify that two colors are filtered properly. Thus, we can filter two colors by the help of color index column.

How to Filter Data by Multiple Cell Colors in A List 8

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar