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.
Table of Contents
- 1 Sort by color in Excel – The Basics
- 2 VBA solutions for sorting data by color
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.
2. Click the Data Tab on the ribbon. Click the Sort button.
3. The Sort dialog box will appear:
4. Your data has headers, so check it on the top-right corner of the dialogue box.
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.
9. Click OK, and you’ll get the result below:
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.
3. A small drop-down menu style arrow will appear. You can find it on the top-left corner of the first cell.
4. Click the arrow.
5. A new window will appear. Choose the ‘Filter by color’ option and select your preferred color.
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.
1. Right-click on the icon range.
2. Choose ‘Sort’ then click ‘Sort to largest to smallest’ option
3. That’s all! You’ll get the same result as shown below.
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:
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.
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.
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
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?
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 Application.Volatile 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.
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.
The result as same as below:
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.