Sort by color

This article explains how to sort by color in Excel using conditional formatting, filter function, or VBA automation.

How to sort by color in Excel

Here are the steps to sort by color:

  1. Select a cell inside the range that you want to sort.
  2. Use the Ctrl + Shift + L shortcut to turn the Filter on.
  3. A small arrow will appear in the header.
  4. Click the arrow.
  5. Choose the Filter by Cell color option and select the preferred color.
  6. Now you have a sorted list.
sort by color using filter

#1: Use the custom sort

1. Select your data range, which contains three columns. If you prefer using the shortcuts instead of the mouse, select the top-left corner of the data range and press CTRL+A.

2. Click the Data Tab on the ribbon. Click the Sort button.

sort function

3. The Sort dialog box will appear.

4. Your data has headers, so check it on the top-right corner of the dialogue box.

sort dialog box

5. In the Sort by row, select Color

6. Navigate to the Sort On label and select the Cell Color option from the drop-down list.

7. Select green as the fill color and set the Order value to On Top.

8. To add a new row, click the Add level button.

9. Repeat the last step twice for the yellow and red fill colors.

10. Click OK to sort by color


ordered list based on cell color

Additional resources:

#2: Sort by color using filters

You can use filters instead of sorting. We’ll show you how to filter the data if you want to learn a faster method.

1. Select the column which contains colors.

2. Under the Data Tab, locate the Sort & Filter group, then click the Filter button.

select data tab then click filter button

3. A small arrow will appear; you can find it in the header.

4. Click the arrow.

5. A new window will appear. Choose the Filter by color option and select your preferred color.

select sort by cell color

The function will sort the list by cell color.

#3: Sort a Conditionally Formatted Range based on colors (value-based)

If you have a list with conditional formatting icons (in this example: flags), you apply the classic scheme: the higher third values are green, and the lower third is red. Then, use the typical value-based sorting.

1. Right-click on the icon range.

2. Choose ‘Sort’, then click ‘Sort to largest to smallest’ option

Conditionally Formatted Range based on colors

3. That’s all! You’ll get the same result as shown below.

conditional sort by color

#4: Sort by color using Conditional Formatting Rules

Let us assume that your list was formatted based on a custom rule.

You want to highlight the top 20% and the bottom 20% of the range using green to find the peaks (highest and lowest values in the array)

In the next example, we want to sort data by color, but we cannot apply value-based sorting.

sort by color based on custom conditional formatting rules

1. Select the entire data set, in this case, ‘C3:C12’. Right-click in the range and choose the Sort, Custom Sort option from the menu.

custom sort excel

2. A new window will appear. First, select the column which contains colors.

3. Choose the ‘Conditional Formatting’ icon.

4. Select your preferred color in the Order section.

5. Finally, apply the ‘On Top’ parameter for proper sorting.

sort using conditional formatting icons

Click OK, and you will get a sorted list based on the selected color.

#5: How to sort by color using User Defined Function

1. Install the DataFX add-in that contains a custom function library

2. Enter the formula: =DxCellColorIndex(cell)

3. As a result of the formula, you’ll get unique numbers, so from now, it’s super easy to apply a custom sort based on values.

using DataFX function library

#6: Sorting rows by color using DataXL add-in

We love add-ins; with their help, we can boost productivity.

First, install DataXL Productivity Add-in.

1. Select the data range, which contains values and colors.

2. Go to the DataXL tab on the ribbon and choose Ranges. Select the color-based sorting option from the drop-down list.

using DataXL
3. Now, you can sort data by fill color, font color, or font style. If your range has a header, check the “Headers in a first-row” checkbox. Then, click OK to apply a quick sort.

Additional resources

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.