The following step-by-step example shows how to filter rows by multiple colors in Excel.
Let’s jump in!
Step 1: Enter the Data
First, let’s enter the following list of tasks in Excel that are color-coded based on whether they have been completed or not:
Step 2: Use VBA to Extract Color Code
Next, we will write a simple function in VBA to extract the color from each cell as an integer value.
To do so, click Alt + F11 to open the VB Editor. Then click the Insert tab and then click Module.
In the module window that appear, type the following code:
Function FindColor(CellColor As Range) FindColor = CellColor.Interior.ColorIndex End Function
This will create a custom function that we can use in Excel to extract the background color of any cell as an integer value.
The following screenshot shows how to do so in practice:
Once you’ve entered the code, feel free to close out of the VB Editor. The code will automatically be saved.
Step 3: Extract Colors from Cells
Next, let’s type the following formula into cell B2 to extract the background color from cell A2:
Then click and drag this formula down to each remaining cell in column B:
Column B now shows the background color (as an integer value) of each corresponding cell in column A.
Step 4: Filter by Multiple Colors
Now that we have the background color of each cell as an integer value, we can simply filter the rows based on the values in the Color column.
To do so, highlight the cell range A1:B11, then click the Data tab along the top ribbon, then click the Filter icon within the Sort & Filter group.
Then click the dropdown arrow next to Color and uncheck the box next to 35:
Once you click OK, the rows will be filtered to only show the ones where the color of the task is either yellow or red:
Feel free to uncheck whichever color codes you’d like to filter by a different set of colors.
The following tutorials explain how to perform other common operations in Excel: