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.
But, additionally, there may be a cell value involved with the identification, as illustrated by the Status column below.
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.
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.
Apply the sort and your list should now appear in the desired order, as shown below.
When you have a cell value, we can optionally use a custom list. Let’s check that out.
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.
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.
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.
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.
Apply the sort and bam…custom sort order applied!
If you have any other sorting tips, please share by posting a comment below…thanks!
Sample Excel file: SortByColor
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.