How to Filter by Color in Excel


On the Data tab, click Filter. in the column that contains the content that you want to filter. Under Filter, in the By color pop-up menu, select Cell Color, Font Color, or Cell Icon, and then click the criteria.

Sort by cell color in Excel

Sorting Excel cells by color is the easiest task compared to counting, summing and even filtering. Neither VBA code nor formulas are needed. We are simply going to use the Custom Sort feature available in all versions of Excel 365 through Excel 2007.

  1. Select your table or a range of cells.

  2. In the Sort dialog window, specify the following settings from left to right.

    • The column that you want to sort by (the Delivery column in our example)
    • To sort by Cell Color
    • Choose the color of the cells that you want to be on top
    • Choose On Top position

    Sort cells by colour using Excel's Custom Sort feature.

  3. Click the Copy Level button to add one more level with the same settings as the first one. Then, under Order, select the color second in priority. In the same way, add as many levels as many different colors are in your table.

  4. Click OK and verify if your rows have been sorted by color correctly.

    In our table, the "Past Due" orders are on top, then come "Due in" rows, and finally the "Delivered" orders, exactly as we wanted them.Row sorted by color

  5. Tip: If your cells are colored with many different colors, it is not necessary to create a formatting rule for each and every one of them. You can create rules only for those colors that really matter to you, e.g. "Past due" items in our example and leave all other rows in the current order.Sort cells by one color

If sorting cells by only one color is what you are looking for, then there's even a quicker way. Simply click on the AutoFilter arrow next to the column heading you want to sort by, choose Sort by color from the drop-down menu, and then select the color of cells that you want to be on top or at the bottom. By the way, you can also access the "Custom Sort" dialog from here, as you can see in the right-hand part of the screenshot below.

Sorting by font color in Excel

If you want to sort by just one font color, then Excel's AutoFilter option will work for you too:Use Excel AutoFilter to sort by one color.

Apart from arranging your cells by background color and font color, there may be a few more scenarios when sorting by color comes in very handy.

Sort by cell icons

For example, we can apply conditional formatting icons based on the number in the Qty. column, as shown in the screenshot below.

Original data formatted with the conditional formatting icon set

As you see, big orders with quantity more than 6 are labeled with red icons, medium-size orders have yellow icons and small orders have green icons. If you want the most important orders to be on top of the list, use the Custom Sort feature in the same way as described earlier and choose to sort by Cell Icon.Sorting by cell icon

It is enough to specify the order of two icons out of 3, and all the rows with green icons will get moved to the bottom of the table anyway.

Excel data sorted by cell icon

How to filter cells by color in Excel

If you want to filter the rows in your worksheet by colors in a particular column, you can use the Filter by Color option available in Excel 365 - Excel 2016.

The Filter by Color feature

The limitation of this feature is that it allows filtering by one color at a time. If you want to filter your data by two or more colors, perform the following steps:

  1. Create an additional column at the end of the table or next to the column that you want to filter by, let's name it "Filter by color".

  2. Enter the formula =GetCellColor(F2) in cell 2 of the newly added "Filter by color" column, where F is the column congaing your colored cells that you want to filter by.

  3. Copy the formula across the entire "Filter by color" column.
    Add the formula to get the color code.

    1. Apply Excel's AutoFilter in the usual way and then select the needed colors in the drop-down list.Select the needed colors from the drop-down list.

As a result, you will get the following table that displays only the rows with the two colors that you selected in the "Filter by color" column.

Rows filtered by several colors.


Filtering by color is a powerful technique that can help you to manage and analyze your data more effectively in Excel. By using the various tools and techniques described in this article, you can filter and sort your data by color, create custom color schemes, and even automate the filtering process using VBA macros or Power Query.