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: 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: 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: 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: 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.
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
