Sort by Color

Sort by color

In this post, I’ll answer a question submitted by reader Chérie about sorting by color. The basic question is this. “I have created a color coded list, where yes=green, no=red, maybe=orange, and other is any other color. How can I sort the list so that all the yes rows are first, then no, then maybe, and then other?”  Thanks for your question! Time to get to work.


Let’s be clear about our objective. There are two ways to identify rows. One is by using a cell value and the other is with cell formatting. For example, if the rows are identified by color only, and there is no corresponding cell value, the list may look like this.

Color List by Jeff Lenning

But, additionally, there may be a cell value involved with the identification, as illustrated by the Status column below.

Value List by jeff Lenning

Now, let’s sort both types of lists in a specific order: yes, no, maybe, and other.

Sort by Color

First, we’ll sort by color, which works on both types of lists. To sort the list by color, add Sort and Filter controls by selecting any cell in the range and then the following Ribbon command:

  • Data > Filter

This will provide little drop-downs on the header row as shown below.

Filter Controls by Jeff Lenning

Expand any of the drop-downs and select the following:

  • Sort by Color > Custom Sort

This opens the Sort dialog. In the Sort by field, pick any column that includes the colors. Then, choose to Sort On Cell Color. Pick the color you want to appear at the top of the list. Then click the Add Level button, and repeat using the next color, and so on. When you are done, your dialog should look something like this.

Excel Sort Dialog by Jeff Lenning

Apply the sort and your list should now appear in the desired order, as shown below.

Excel list sorted by Jeff Lenning

When you have a cell value, we can optionally use a custom list. Let’s check that out.

Custom List

When you have a column that contains cell values, but the desired sort order is other than ascending or descending, you can create a custom list. For example, consider the data below.

Status column by Jeff Lenning

Let’s say we tried to sort by the Status column values. If we used an ascending sort, we’d end up with Maybe, No, Other, Yes. If we used descending, we’d end up with Yes, Other, No, Maybe. Neither of these built-in sort orders are what we want, which is Yes, No, Maybe, Other. This is when a custom list can come in handy. To create a custom list, open the Options dialog with the following command.

  • File > Options

Click the Advanced category, and then scroll way down until you see a button named Edit Custom Lists as shown below.

Excel Options by Jeff Lenning

Click it to open the Custom Lists dialog. Simply enter your list entries in order, Yes, No, Maybe, Other, and then click Add. This will create a new custom list, as shown below.

Custom Excel List by Jeff Lenning

Now, this custom list will be available in other workbooks and various places within Excel, including PivotTables, Fill Series, and in the Sort dialog.

To sort our data using the order of the custom list, simply select any data cell and then the following Ribbon command:

  • Data > Sort

This opens the familiar Sort dialog, and this time, we’ll ask Excel to Sort by the Status column. In the Order drop-down, select Custom List, and then pick your fancy new custom list. The Sort dialog will look something like this.

Sort by Jeff Lenning

Apply the sort and bam…custom sort order applied!

Sorted by Jeff Lenning

Objective accomplished!

If you have any other sorting tips, please share by posting a comment below…thanks!

Additional Resources

Sample Excel file: SortByColor


This article was written by jefflenning


  1. Liz

    Hi. I sometimes use colors a similar way.
    Each time you sort by color it brings the color by which you are sorting to the top with the other rows below with their original order intact. What I do is sort first by the color I want to appear at the bottom, then by the color I want to appear above that… finally by the color I want to appear at the top.

    1. jefflenning Post author

      Thanks :-)

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.