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

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

Excel/Google Sheets: Expanding Reference

An expanding reference is a reference type in Excel and Google worksheets that extends the range of a cell when a formula is copied to the cell below or to the right. In this article, we will explain how to ...

Excel/Google Sheets: Mixed Reference

In Microsoft Excel Spreadsheet or Google sheets, there is another cell reference, mixed references, where part of the reference is absolute, part of the relative. This article will describe how to use mixed references through specific examples. Mixed Reference When ...

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute ...

Sidebar