How to Easily Fix Filtering by Color in Excel if it is Greyed Out but Color-Filled Cells Exist

There are so many ways that Excel can quickly calculate the data entered into it. Some examples of what Excel can do are: generating averages, locating trends, creating charts, processing calculations, auto-filling data, conditionally formatting data, summarizing large data sets and more. One of the reasons these tools are worth learning is because they can process large amounts of data very quickly, and where doing it manually would not be feasible.

This post discusses filtering by color-filled cells as well as how to fix this feature in Excel when it is greyed out, but where color-filled cells exist.

How to Easily Fix Filtering by Color in Excel if it is Greyed Out but Color Filled Cells Exist

First it helps to understand how filtering works and why you would have color filled cells that you want to filter by. You can fill cells with a background color at any time and for any reason. However, you can also use conditional formatting to more efficiently located and fill cells within a specific criteria you set with color so they are easily identified.

To access conditional formatting, locate it on the Home tab of Excel:

Conditional formatting is located on the Home tab in Excel closer to the right edge.

Conditional formatting can be used to color-fill cells based on all kinds of criteria which includes several quick rules already created that can be applied at any time. These embedded rules include:

Highlighting rules based on greater than, less than, between, equal to, text that contains, a date occurring and duplicate values.
Top/bottom rules based on top 10 items, top 10%, bottom 10 items, bottom 10%, above average and below average.

There are several types of already created conditional formatting rules you can apply to your data at any time.

For a walk through on how to apply conditional formatting, check out this former post Using Conditional Formatting to Identify Date-Based Patterns in Excel which is also available as a video. Once you have applied a conditional formatting rule, which uses color-filling and/or text colors to identify the cells that meet the specific criteria, you can filter for these cells by their filled color which is an efficient way to filter.

In my spreadsheet, I have created a conditional formatting rule that will color fill all cells where the total cost is greater than $100.

A conditional formatting rule to color fill all cells in the column where the amount is greater than $100.

Next you need to enable filtering so you can filter by color-filled cells. To enable filtering, select the row that has your data headings and click on "Filter" in the Data tab.

To enable filtering, click on "Filter" in the Data tab. The drop-down arrows next to your headings verify filters are available.

With the conditional formatting rule applied, you should be able to filter the column by the color-filled cells by clicking the drop-down arrow for the column the formatting rule was applied to. Assuming some data fit into the conditional formatting rule, the option to "Filter by Color" option should be available.

However, sometimes the option to filter by color is greyed out even if you have color-filled cells that should be available to filter by.

Example of the "Filter by Color" option being greyed out even after a conditional formatting rule has been applied and the appropriate cells have been filled with color.

If this happens, the most likely cause is a break in your data. In this case, a break means the column you are selecting to filter by has an empty cell somewhere and the cells affected by the conditional formatting rule are located beyond the blank cell. Unfortunately, one thing Excel does when filtering or applying functions is stop looking for data once it runs into a single cell in the selection that is empty of data.

This means if you have results that fall below a blank cell, those will never be read or seen by Excel until you remove the blank cell or fill it with data. If you have ever worked with functions in Excel, you have probably run into this issue before. The way Excel handles blank cells, meaning to stop looking for additional data, also affects filtering results of conditional formatting.

There are two ways to fix this issue so that you can filter by color:

Sort the data by the column with the color-filled cells to remove any blank lines between the data in those cells. Note: Depending upon how you sort, this may not work if the entire row is blank.
Filter the column with the color-filled cells and select "blanks" if it is an option. Remove the empty row or fill in the row where appropriate so all of the data in the column is read by Excel.

Once you have removed the empty cell interrupting the filtering function, you can filter by color.

Filter by color will be available after removing the empty cell.

Once you have applied the filter, you will see the results you were looking for.

The results of filtering by color to see the results of conditional formatting now that you can filter this way.

The most important thing to understand about Excel when it comes to not being able to filter by color, when you are sure there are color-filled cells, is that Excel stops reading data once it locates an empty cell. This can happen with results in rows or columns. If you find you are getting odd results when filtering, or the ability to filter by color is greyed out even though you have color-filled cells, you probably have a blank cell somewhere in with the data you are trying to filter. If you remove the blank cell, you will be able to filter and your results should be complete.

As always, understanding the why behind how something works helps you save time and better use the software!