How to sort by color in Excel

We frequently ask: How to sort by color in Excel? The answer is easy. Be smart! We will show you the most used tricks. In this tutorial, we’ll start from the ground up. We hope you may find helpful when you want to use time-saving solutions to sort data by color.

As first, we’ll use the classic sorting tools of Excel. After that, we’ll use conditional formatting and apply a built-in filter. Finally, you will learn how to sort data by color using custom functions and VBA automation.

Sort by color in Excel – The Basics

Example 1: Use the custom sorting method

If you are using custom sorting, the range will be sorted based on colors, not by values.

Here are the steps of how to do this:

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.

unsorted data range with colors

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

data tab sort menu

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 by color settings

5. Let us see the ‘Sort by’ row. Select the Column field and choose the column name, which contains colors.

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

7. Select red as fill color and keep the Order value as On Top.

8. Repeat the last step twice for the yellow and the green color.

multi level sort by color in excel

9. Click OK, and you’ll get the result below:

color sorting result

Example 2: Sorting data by color using filters

You can use filters instead of sort. If you want to learn a faster procedure, we’ll show you how to filter the data using a better way.

1. Select the column which contains colors.

2. Click on the Sort & Filter group, then click the Filter button.

color sorting using filters

3. A small drop-down menu style arrow will appear. You can find it on the top-left corner of the first cell.

arrow on top-rigt corner

4. Click the arrow.

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

select the top color order

6. Click ok to apply the color filter.

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

Conditional formatting is an essential tool in Excel. We built a sample data set using our random data generator. You can use the classic randbetween() function also.

If you have a list with conditional formatting icons (in this example: flags), and you apply the classic scheme: the higher third values are green, the lower third is in red. Use the typical value-based sorting! It’s not rocket science.

conditional formatting base table

1. Right-click on the icon range.

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

Sort to largest to smallest

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

value based color sorting cf

Example 4: Sort a Range by color in case of special 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)

Check this example below:

color sorting by complex formatting rules

Okay, how to sort data by color in this case? You cannot apply the value-based sorting.

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

using custom sort

2. A known window will appear. As first, select the Column, which contains colors.

3. After that, choose the ‘Conditional Formatting’ icon.

4. Select your preferred color in the Order section.

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

steps to create conditional sorting by color

The result:


VBA solutions for sorting data by color

In this example, we’ll use a user-defined function. After that, you’ll see how to use automated solutions!

Example 1: Sort by color using User Defined Function

1. As first, Install DataXL free excel add-in

2.Enter formula:

user defined function

3. You’ll get unique numbers, so from now, it’s super easy to apply a custom sort based on values. So quick, is it not?

how to use UDF to boost your productivity

If you want to use this function without the installed add-in, please insert the code below into your Workbook.

Public Function DxCellColorIndex(cell As Range) As Single
DxCellColorIndex = cell(1).Interior.ColorIndex
End Function

Example 2: Sorting rows using DataXL add-in

We love add-ins and VBA! With its help, we can boost productivity from day by day.

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

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

excel productivity add-in 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. Click OK to apply a quick sort.

userform color setup

The result as same as below:

sorted list by color using vba

Conclusion and Additional resources

Sorting is an essential function in Excel. In some cases, we’ll get an unsorted sheet with colors and have to create an organized list.