How to Sort by Color in Excel

Steps to Sort Data by Color in Excel (Cell Color)

Sorting data based on cell colors requires you to follow a few steps. You need to use the same sorting option but create multiple sorting levels based on the cell colors. So, for this, you need to follow the steps that I have mentioned below, one by one.

1. In the first step, select the range or at least a cell within the range you want to sort based on the cell colors. And make sure that none of the cells in the range are merged. Now, go to the Data tab and click the Sort button to open the Sort dialog box.
   
2. After that, in the Sort dialog box, click on the “Sort On” drop down and select “Cell Color”.
3. When you select Cell Color from the drop-down menu, you will get another drop-down menu to select the cell color in the Order option. And this is the drop-down where you can tell Excel to sort cells based on that color.
   
4. Next, click Add Level to add one more level for sorting. Here, you need to specify the second color you want to set to sort the cells. So here, I would select the yellow color from the drop-down to use as the second order for sorting.
   
5. Now again, we need one more level in the sorting order, where I will select the third color to sort. I want to choose the light color from the drop-down for this.
   
6. In the end, click OK to apply the sort.

When you click OK, it will sort the cells with the color order you created in the sorting dialog box.

In the same way, you can use the font color instead of the cell color from the “Sort On” drop-down menu.

Put the Selected Color on Top (Rest in the Same Order)

There is also a quick way to help you put a single-color cell at the top.

1. Select a cell with the color you want to put on the top for the sort.
2. After that, right-click.
3. Now, go to the “Sort” option.
4. Click “Put Selected Cell Color On Top” from here.

And the moment you click it, it puts the cells with the “Yellow” color at the top. The rest of the rows keep their current order below them.

Sort by Font Color in Excel

You can also sort data based on font color. The steps are the same; you just need to select “Font Color” in the “Sort On” drop-down instead of “Cell Color”.

1. First, select the range of cells you want to sort. You can also select a single cell from the range, but make sure that the range has continuous data and no merged cells.
2. After that, go to the Data tab on the ribbon and click on the SORT button to open the Sort dialog box. Or you can also use the keyboard shortcut Alt > A > S > S.
3. Now, in the sort dialog box, you need to select the “Font Color” from the “Sort On” option and then select the font color from the “Order” drop-down.
4. Next, you need to create the second level by clicking “Add Level” and then again, select “Font Color” and select the color from the “Order” drop-down.
5. From here, you can also add the third level of sorting using the same steps that you have used above. This will give you three levels of sorting the data based on the font color. Here in this example, I have used “Automatic”, which is the default (usually black) font color.
6. In the end, click OK to sort the data.

Sort Cells Based on Icons in Excel

In Excel, we can use conditional formatting to present data based on a color theme. And conditional formatting also allows you to add icons to the cells based on the values in them.

Excel allows you to use those icons to create sorting levels. When you open the sorting dialog box, the “Sort On” drop-down also has an option to sort by icons (Cell Icon).

Using the exact same steps that we have used in the above methods you need to create a sorting level for each icon. So, let’s suppose you want to put the red icon cells on the top; you need to add that level first.

And then, if you want the yellow icons in the second position, you need to create that as the second level. In the end, if you want the green icons in the third position, you need to add them as the third level of sorting. Below are the simple steps you need to follow:

1. For this, go to the “Data” tab and click the “Sort” button to open the sort dialog box.
2. In the sort dialog box, click “Add Level” to create a sort order. Under the “Sort by” dropdown, select the column with the icons.
3. Then, under “Sort On,” choose “Cell Icon”, and under “Order,” pick the icon to put on top. Add one level for each icon.
4. In the end, click “OK” to apply the sort.

When you open the sorting dialog box, and select cell color, font color, or icons to sort by, Excel is smart enough to show you only the colors and icons that you have in the selected range, instead of showing you all the colors.

Sort by Conditional Formatting Colors

Sorting by color also works when the colors come from conditional formatting, so you can sort the cells that a rule has highlighted to the top. But if the colors were added by hand, Excel doesn’t know why a cell is red. In that case, it’s often better to add a helper column with the status (like “Overdue”) and sort by that column, so the order stays right even when someone forgets to color a cell.

Note that formula functions like SORT and SORTBY can’t sort by color, as a color is not a value. Use the Sort dialog box, or filter by color with Data ➜ Filter ➜ Filter by Color if you only want to see one color.

Frequently Asked Questions