How to Sort By Color in Excel (in less than 10 seconds)

Excel has a lot of options when it comes to sorting data.

And one of those options allows you to sort your data based on the color of the cell.

For example, in the below dataset, you can sort by color to get the names of all the students who scored above 80 together at the top and all the students who scored less than 35 together at the bottom.

With the sorting feature in Excel, you can sort based on the color in the cell.

In this tutorial, I will show you different scenarios where you can sort by color and the exact steps you need to do this.

Note that in this tutorial, I have taken examples where I am sorting based on numeric values. However, these methods work perfectly well even if you have text or dates instead of numbers.

Sort Based on a Single Color

If you only have a single color in your dataset, you can easily sort it based on it.

Below is a dataset where all the students who have scored more than 80 have been highlighted in green.

Here are the steps to sort by the color of the cells:

1. Select the entire dataset (A1:B11 in this example)
2. Click the Data tab
3. Click on the ‘Sort’ option. This will open the Sort dialog box.
4. In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.
5. Click the ‘Sort By’ drop down and select ‘Marks’. This is the column based on which we want the data to be sorted.
6. In the ‘Sort On’ drop down, click on Cell Color.
7. In the ‘Order’ drop-down, select the color based on which you want to sort the data. Since there is only one color in our dataset, it only shows one color (green)
8. In the second drop-down in Order, select On-top. This is the place where you specify whether you want all the colored cells at the top of the dataset or at the bottom.
9. Click OK.

The above steps would give you a dataset as shown below.

Note that sorting based on color only rearranges the cells to bring together all the cells with the same color together. Rest of the cells remain as is.

Sort Based on Multiple Colors

In the above example, we only had cells with one color that needed to be sorted.

And you can use the same methodology to sort when you have cells with multiple colors.

For example, suppose you have a dataset as shown below where all the cell where the marks are more than 80 are in green color and the ones where marks are less than 35 are in red color.

And you want to sort this data so that you have all the cells with green at the top and all the ones with red at the bottom.

Below are the steps to sort by multiple colors in Excel:

1. Select the entire dataset (A1:B11 in this example)
2. Click the Data tab.
3. Click on the Sort option. This will open the Sort dialog box.
4. In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.
5. Click the ‘Sort By’ drop down and select ‘Marks’. This is the column based on which we want the data to be sorted.
6. In the ‘Sort On’ drop down, click on Cell Color.
7. In the ‘Order’ drop-down, select the first color based on which you want to sort the data. It will show all the colors that are there in the dataset. Select green.
8. In the second drop-down in Order, select On-top.
9. Click on Add Level button. This is done to add another condition of sorting (as we want to sort based on two colors). Clicking this button would add another set of sorting options.
10. Click the ‘Then by’ drop-down and select ‘Marks’
11. In the ‘Sort On’ drop down, click on Cell Color.
12. In the ‘Order’ drop-down, select the second color based on which you want to sort the data. It will show all the colors that are there in the dataset. Select Red.
13. In the second drop-down in Order, select On-Bottom.
14. Click OK

The above steps would sort the data with all the green at the top and all the reds at the bottom.

Sort Based on Font Color

Another amazing thing about sorting in Excel is that you can also sort by font color in the cells.

While this is not as common as getting data where cells are colored, this is still something a lot of people do. After all, it only takes one click to change the font color.

Suppose you have a dataset as shown below and you want to sort this data to get all the cells with the red color together.

Below are the steps to sort by font color in Excel:

1. Select the entire dataset (A1:B11 in this example)
2. Click the Data tab
3. Click on the Sort option. This will open the Sort dialog box.
4. In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.
5. Click the Sort By drop down and select ‘Marks’. This is the column based on which we want the data to be sorted.
6. In the ‘Sort On’ drop down, click on Font Color.
7. In the ‘Order’ drop-down, select the color based on which you want to sort the data. Since there is only one color in our dataset, it only shows one color (red)
8. In the second drop-down in Order, select On-top. This is the place where you specify whether you want all the colored cells at the top of the dataset or at the bottom.
9. Click OK.

The above steps would sort the data with all the cells with the font in red color at the top.

Sort Based on Conditional Formatting Icons

Conditional formatting allows you to add a layer of visual icons that can make your data or your reports/dashboards look a lot better and easy to read.

If you have such data with conditional formatting icons, you can also sort this data based on the icons

Suppose you have a dataset as shown below:

Below are the steps to sort by conditional formatting icons:

1. Select the entire dataset (A1:B11 in this example)
2. Click the Data tab
3. Click on the Sort option. This will open the Sort dialog box.
4. In the Sort dialog box, make sure ‘My Data has headers’ is selected. In case your data doesn’t have headers, you can keep this option unchecked.
5. Click the ‘Sort By’ drop down and select ‘Marks’. This is the column based on which we want the data to be sorted.
6. In the ‘Sort On’ drop down, click on ‘Conditional Formatting Icon’.
7. In the ‘Order’ drop-down, select the icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select the green one first.
8. In the second drop-down in Order, select On-top.
9. Click on Add Level button. This is done to add another condition of sorting (as we want to sort based on three icons). Clicking this button would add another set of sorting options.
10. Click the ‘Then by’ drop-down and select ‘Marks’
11. In the ‘Sort On’ drop down, click on ‘Conditional Formatting Icons’.
12. In the ‘Order’ drop-down, select the second icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select yellow.
13. In the second drop-down in Order, select On-Top.
14. Click on Add Level button. This is done to add another condition of sorting. Clicking this button would add another set of sorting options.
15. Click the ‘Then by’ drop-down and select ‘Marks’
16. In the ‘Sort On’ drop down, click on ‘Conditional Formatting Icons’.
17. In the ‘Order’ drop-down, select the third icon based on which you want to sort the data. It will show all the icons that are there in the dataset. Select Red.
18. In the second drop-down in Order, select On-Top.
19. Click OK.

The above steps would sort the data set and give you all the similar icons together.

Not Losing the Original Order of the Data

When you sort the data, you lose the original order of the dataset.

In case you want to keep the original dataset as well, it’s best to create a copy of the data and then perform the sorting on the copied data.

Another technique to make sure you can get back the original data is to insert a column with row numbers.

Once you have this column added, use this when sorting the data.

In case you need the original data order back at a later stage, you can easily sort this data based in the columns with the numbers.

You May Also Like the Following Excel Tutorials:

• Highlight Rows Based on a Cell Value in Excel
• How to Sort by Length in Excel?
• How to Sum by Color in Excel (Formula & VBA)
• How to Count Colored Cells in Excel
• Search and Highlight Data Using Conditional Formatting
• Multiple Level Data Sorting in Excel
• Sort Data in Excel using VBA
• How to Move Rows and Columns in Excel
• Highlight the Active Row and Column in Excel
• How to Undo Sort in Excel
• Filter By Color in Excel