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

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.

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

is displayed.

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.

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

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.

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.