How to Filter by Color in Excel?

Filtering data is a common task in Excel, and in most cases, you would need to filter the data based on the values in the cells.

But sometimes, you may want to filter the database on the cell color. for example, you may want to filter all the cells that have a red color or green color in it

Thankfully, Excel has the ‘filter by color’ option inbuilt into Excel, and it only takes a few clicks to filter the data based on the cell color quickly.

Let’s start with the dataset below, in which we have a list of stores and their sales. 

In the dataset, we have the sales of each store highlighted with 3 colors – Yellow, Orange, and Green. 

In this article, we will discuss 3 methods to filter by color in Excel. The methods are quick and easy to follow and can be used in any dataset. 

Method 1: Filter Based on Cell Color Using the Filter Option in the Ribbon

In this method, we will use the Filter to the column, which will allow us to filter values based on the font color.

This method is already available as part of the various options to Filter data by various conditions. 

Let’s suppose we want to filter the dataset using the “Green” color in Sales column, so that we can see only the rows which have the Green color and other rows having Orange and Yellow will be hidden.

So, let’s understand the steps involved in using the Filter option to filter by color in Excel.

1. Select the dataset that has the cells with colors based on which you want to filter the data

2. Click on the Data Tab

3. In the Data tab options, click on the ‘Filter’ option (in the Sort & Filter group)

4. When you click the Filter icon in the ribbon, you will notice that a drop-down icon appears in all column headers  (as shown in the below screenshot).

Shortcut Option: You can use the Shortcut option to apply filter and directly move to Step 5. Select the data (A1:B12) and press CTRL + SHIFT + L (press the CTRL key, followed by the SHIFT and the L key)

5. Click on the Filter drop-down icon of the column header with the colored cells (column B in this example).

6. Clicking on the Drop-down button will open the various options by which we can filter the data

7. Now, Select the Filter by Color option. This will open another tab that will have all the colors present in the column data – Orange, Yellow, and Green.

8. From this tab, select the color we want to filter the data. In this case, we will select the green color 

9. Clicking on the green color will now show only rows which have the green color in the sales column. 

You can see in the result above that only 4 rows are visible, and rows containing orange and yellow color cells are hidden. 

10. To filter by another color, again click on the drop-down button and select a different color (for example – Yellow) and see rows containing yellow color cells only

So, these were steps to filter cells by color using the Filter option.

Note: This method will allow you to filter your data based on on color only. In case you want to filter by multiple cell colors (say green as well as red), you can use the fourth method covered in this tutorial later

Also read: How to Create a QR Code in Excel

Method 2: Filter by Color from Right Click Options

Another quick way to your data set based on the cell color is by using the filter option that appears when you right-click on the cell that contains the color.

Below I have a dataset where I have colored cells in column B, and I want to filter all the records where the cell in column B is green.

1. Select any cell which has green color in column B. In this case, we are selecting Cell B4, which has sales for ABC Store.

2. Right Click on the cell (in this case, B4), and another menu will open. Click on the Filter in this submenu

3. Once you click on the Filter option, another submenu will open, which will have multiple options to Filter basis Cell’s Value or Cell’s Color or Font Color or Cell’s Icon.

4. Now select the option of “Filter by Selected Cell’s Color”

5. This will filter the sales data to show only rows with green color in column B as below

As you can see in the output above, only 4 rows are visible, and rows containing orange and yellow color cells are hidden. 

Personally, I find this method to be slightly faster as it only takes a few clicks to quickly filter the entire data set based on the color of the selected cell.

Note: With this method, you can only filter your data set based on one color. in case you want the data set to be filtered based on multiple colors, you will have to use the 4th method in this tutorial, which uses VBA 

Also read: How To Highlight Blank Cells In Excel

Method 3: Filter by Color in Excel Using VBA

We can filter data by the color of the cell in Excel by using a VBA script along with the AutoFilter function.  

We will again use the earlier data set, which has sales of retail stores. We will filter sales data whose cells are highlighted in green using the steps mentioned below.

1. To use VBA in excel, you need first open the Visual Basic Editor. To do this, click the Visual Basic button on the Developer tab.

If the Developer tab is not present, go to File -> Options -> customize ribbon and tick Developer.

Alternatively, you can open the VBA editor using the shortcut – Alt + F11 (hold the ALT key and press the F11 key). This will also open the Visual Basic editor in a separate window.

3. Click on Insert Tab and then click on the Module option that appears in the drop-down.

4. Dong this inserts a new Module, which is where we are going to add the VBA code to filter the dataset based on the color

5. Below is the VBA macro code that allows you to filter based on the specified cell color

Sub FilterByCellColor()
DataRange = "A1"
SelCell = "B4"
SelColumn = 2
SheetName = "Sheet1"

Dim ws As Worksheet
Set ws = Worksheets(SheetName)

ws.Range(DataRange).AutoFilter field:=SelColumn, Criteria1:=Range(SelCell).DisplayFormat.Interior.Color, Operator:=xlFilterCellColor
End Sub

In the above VBA code, we have hard-coded the cell from which you want to pick the color, and then use that color as the criteria to filter the Dataset.

You can customize this code based on your data set.

In the above VBA code, we have assigned values to a few variables first. 

For example, 

DataRange is assigned the reference A1, which is then used as the first cell in the dataset (which helps VBA determine the starting point of the dataset).

SelCell is the variable that is going to hold the reference to the cell that has the color that we want to use to filter our entire data set.

SelColumn has been set to 2 as we want to filter the data set based on the color in column B

SheetName variable has been assigned the name of the worksheet, which is Sheet1 in this case.

Once we have assigned all the variables, use the auto filter method to get the color from cell B4 and then give us the filtered data

6. You can Copy and Paste the VBA code into the module window.
7. After saving the code, click on the green icon in the toolbar to run the code. Alternatively, Press the F5 key to run the code.

8. This will filter the dataset with only the rows with green color visible, as shown below.

Also read: How to Remove Dashes in Excel?

Method 4: Filter the Dataset Based on Multiple Colors

While there is no inbuilt functionality to filter your data set based on multiple colors, This can be done using a helper column and extracting the color index of each cell in this helper column.

Once you have the color index values, you can use a regular filter option to filter your dataset based on multiple color index values

Let’s see how to get this done!

1. To start, open the Visual Basic Editor by clicking on the develop tab followed by the Visual Basic Button as below.

2. Now click the  Insert option and then select the Module option. 

3. Copy the VBA code below into the module code window

Function ObtainColor(x As Range) As Integer
ObtainColor = x.Interior.ColorIndex
End Function

4. Once you have the VBA function code in the module code window, return to the Excel worksheet and enter the formula =ObtainColor(B2) in cell C2. This will give you the color index value of the cell in column B

5. Now copy and paste the formula down to the cells in the helper column C to extract the color index for all the required cells, as shown below.

6. Now that you have the color index values of all cells in column B, you can filter your dataset using the values in Column C. To do this, select the dataset, click the Data tab and then click on the Filter icon 

Alternatively, you can use the Shortcut to apply the filter. Select the data (A1:C12) and press CTRL + SHIFT + L (press the CTRL key, followed by the SHIFT and the L key)

7. Then click on the drop-down button in the C1 cell, and select the color index values that you want to be filtered, as below:

8. Click the OK button. This will give you the filtered dataset. In this example, we have selected the numbers 43 and 44, to filter the rows containing green- and orange-colored cells. The result is shown below in the screenshot

This article shows four ways to filter data based on cell color. 

The first method uses the Filter option, but it can only filter data using one color at a time.

The second method uses the Filter option using the Right-click menu, but it can also be used to filter one color at a time.

The third and fourth method uses VBA scripts and is more dynamic.

For people who know how to use VBA scripts, the fourth method is the best because it gives you more control and you can filter based on one or more colors.

Other Excel articles you may also like:

• Show Negative Numbers in Parenthesis in Excel
• Apply Remove Filter in Excel (Keyboard Shortcut)
• Fill Color Shortcut in Excel