Trending September 2023 # Filter By Color In Excel (3 Easy Ways + Vba) # Suggested October 2023 # Top 15 Popular

You are reading the article Filter By Color In Excel (3 Easy Ways + Vba) updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Filter By Color In Excel (3 Easy Ways + Vba)

Watch Video – Filter by Color in Excel (Cell Color or Font Color)

Filtering your data is a common task for many Excel users as a part of their daily work.

Excel already has a well-developed filter functionality that allows you to filter based on many criteria, such as text or number, or dates.

Not many people know that Excel also has an inbuilt filter by color functionality, where you can easily filter your data set based on any pre-existing cell color or font color in your data set.

In this tutorial, I will show you how to quickly filter by color in Excel using the inbuilt filter functionality. I will also cover how to filter based on multiple colors using a simple VBA trick.

Note: Excel allows you to filter your data set based on the cell color as well as the font color of the text/number in the cells. I will cover both of these aspects in this tutorial.

The best way to filter your data set by color is by applying a filter to your data and then using the drop-down in the headers to filter the cells by color.

Below I have a data set where I have some rows that are highlighted in green color, and I want to filter these records.

Here are the steps to do this:

Select the data (or any cell in the dataset)

You can also use the keyboard shortcut Control + Shift + L to apply the filters.

Hover the cursor over the ‘Filter by Color’ option. This will further show a sub-menu of the ‘Filter by Cell Color’ options.

Select the color based on which you want to filter this data set. In this example, since we only have one color, only one color is shown in the options. In case you have multiple colors, all would be shown here.

The above steps would instantly filter your data set, and you would only see the records where the specified color is filled in the cell.

One limitation of this method is that you can only filter based on one color. Unlike the text or number filter, the filter-by-color functionality does not allow you to filter based on multiple colors

You can also follow the same steps to filter your data set based on the font color instead of the cell color.

Below I have a data set where I have some records that are in red font color, and I want to filter all these records

Here are the steps to do this:

Select the data (or any cell in the dataset)

Hover the cursor over the ‘Filter by Color’ option. This will further show a sub-menu of the ‘Filter by Font color’ options.

Select the font color based on which you want to filter this data set. In this example, we only have one font color. In case your dataset has more, all the colors would show up.

The above steps will filter the data, and we’ll keep only those records visible that have the selected font color.

If you want to remove the color filter, follow the below steps:

Select the ‘Clear Filter from…’ option.

Let me show you how it works.

Below I have the same data set where I have some colored cells that I want to filter.

Hover the cursor over the Filter option.

This method has the same limitation, where you cannot filter based on multiple colors. You can only filter based on one color.

Below I have a data set where I have some cells that are in red font color, and I want to filter these records.

Here are the steps to do this:

Hover the cursor over the Filter option.

Below are the steps to remove the cell color or font color filter:

Hover the cursor over the Filter option

Select the ‘Clear Filter from…’ option.

While the above two methods are fast and easy, a big limitation is that you can only filter your data set based on one single color.

But what if you have multiple colors in your data set and you want to filter your data to get records that are highlighted in two or more colors?

Let me show you a very simple VBA code and a smart technique to do this.

Below, I have a data set where I have cells highlighted in two colors (green and orange), and I want to filter all the records that have green and orange colors.

Since I cannot do this using the regular Filter by Color functionality in Excel, I will add a helper column and then extract the color index value of each cell color in that helper column.

Once I have these values in the helper column, I can easily filter based on multiple colors using multiple color index values.

The first step for this would be to create a custom function in VBA and then use that function in the worksheet to get the color index for each cell.

Below are the steps to create the custom function in Excel:

Copy and paste the below code to the module code window.

Function GetCellColor(cell As Range) As Integer GetCellColor = cell.Interior.ColorIndex End Function

Function GetCellFontColor(cell As Range) As Integer GetCellFontColor = cell.Font.ColorIndex End Function

Close the VB Editor

With the above steps, we have created two functions using VBA that can now be used in the worksheet as regular functions.

The GetCellColor function will take the cell reference as the input and give us the numerical value that represents the color index of that cell.

And the GetCellFontColor function takes the cell reference as input and gives us the cell font color index value.

Now let’s see how to use these functions in the worksheet to filter our data by color.

In cell C1, enter the text ‘ColorIndex”. We are doing this as we need a header for our helper column. You can write any text you want.

Enter the below text in cell C2, and then copy it for all the cells in the column.

The numerical value that you see in the helper column represents the color index value of each cell on the left. So 15 represents the green color in our dataset, 40 represents orange, and -4142 represents no color.

Now that we have all the data, we will see how to filter this data set based on multiple colors.

Select the entire data set.

Uncheck the options you don’t want to filter for and keep the numbers for the colors for which you want to filter the data. In our example, we will keep 15 and 40 checked (and uncheck all other options).

The above steps would filter our data set based on the selected colors.

Once done, you can hide or remove the helper column if you don’t want it.

Note: In case you want to filter the dataset based on the cell font color instead, use the GetCellFontColor function in step 8.

And if you need to filter based on multiple colors, you will have to create a User-defined function using VBA and then use that function in the worksheet to fetch the color index, which can then be used to apply the filter.