How to Sort by Color in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you’re managing a sales report, and you’ve used colors to highlight different statuses: red for overdue payments, yellow for pending, and green for completed. Sorting by color will help you quickly see which payments need urgent attention.

And yes, you can sort data by color in Excel. There’s an option for that. With this option, you need to create a custom sorting order using the cells or cell color. Below is a list of names; we need names with the yellow cell color on the top.

sort-by-color

So let’s learn it in simple steps:

Steps 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 is merged. And now, go to the data tab and click the sort button to open the sort dialog box.
    2-sort-data-by-color
  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.
    3-select-color-from-color
  4. Next, you need 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.
    4-click-add-level-button
  5. Now again, we need you at one more level of 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.
    5-create-thrid-order
  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.

data-sorted-by-color-sequence

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

choose-font-color

Put Selected Color on the Top Rest Random

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 the Top” from here.
put-selected-color-on-top

And the moment you click it, it puts cells with the “Yellow” color at the top of the sorting.

sorted-color-on-top

Sort by Font Color in Excel

You can also sort data based on cell color. The steps are the same, and you need to select font color from the “Sort By” dialog box 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 ensure that the range has continuous data and no merge cell.
  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 order by clicking of the “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 to using the same steps that you have used above. This will give you three level of sorting the data based on the cell color. Here in this example, I have used the “Automatic” which is the default blank color of font.
  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 use icons to the cells based on the values in them.

Now Excel allows you use those icons to create order level for sorting. When you open the sorting dialog box, from there in the “Sort by” option you also have an option to sort by icons.

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 if you want to put the red icon cells on the top you need to add that level first.

And then if you want to add the yellow color icons on the second position you need to create that order on the second number In the end, if you want to add the green icons on the third number you need to add it to 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” to specify what sort you want based on the icons.
  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 which you have in the selected range, instead of showing you all the colors.

Last Updated: May 27, 2024