This page has been replaced. Please go to the new Sort By Color page.
Pearson Software Consulting Services
Sorting By Color
If you have color-code cells in your worksheet, you find
that at times it is useful to sort rows by the colors of the cells.
That is, sort all the reds at the top, followed by the blues, followed by
the yellows, and so on.
provides no such tool. You have to do it manually. This page describes
how to do it.
Next, you need a VBA function to return the ColorIndex value of the cell. Put the following code in a standard code module in your workbook.
ColorIndexOfCell(Rng As Range, _
Then, in the newly created column, enter either of the following formulas:
If you want to sort by the Background color of the cell, use the formula
If you want to sort by the Font color of the cell, use the formula
Of course, change the reference A1 to the first cell in the range. Use Edit, Fill, Down to fill this formula down to the entire range of data you want to sort.
In these cells, you'll see numbers between 1 and 56. Each of the values indicates the ColorIndex of the cell.
Now, you can sort your data in the normal way, but choose the new column as the primary or first sort key. The cells will be sorted in ascending (or descending) order of the ColorIndex values.
So far, this is all well and good if you are happy with the default order of ColorIndex values. For example, by default, Red = 2, Blue= 5, and Yellow = 6. Therefore, when sorting by ColorIndex values, the data will list all the reds first, followed by the blues, then the yellows.
If you want to modify this order, you will need to create a "custom list" and tell Excel to use this list as the sort order. First, create a custom list by going to the Tools menu, Options item, Custom Lists tab, and selecting NEW LIST in the Custom Lists box. Then, enter the ColorIndex values in the order you want them to appear in ascending sorts, in the List Entries box. You can enter the numeric values (between 1 and 56) on separate lines in the List Entries box (create a new line by pressing ALT+ENTER) or by separating the entries with a comma all on the same line. (NOTE: Non-USA English users may have to use a semicolon rather than a comma.) For example, to sort in order Blue, Yellow, Red, the custom list would be (without the quotes) "5,6,2".
Then, in the Sort dialog box, click the Sort By drop down box, click the Options button, and choose this new list from the lists displayed.
Yes, sorting by color is a bit tricky, and something that we all would like to see built in to Excel. However, until Microsoft provides this feature as a built in tool, we must make the best of what is available.
NOTE: This method sorts by the color specified by the cell's properties. It does NOT work with colors that are displayed as a result of Conditional Formatting.
See also Colors In Excel and Conditional Formatting Colors.
Created By Chip Pearson and
Pearson Software Consulting, LLC
This Page: Updated: November 06, 2013
MAIN PAGE About This Site Consulting Downloads
Page Index Search Topic Index What's New Links Legalese And Disclaimers
© Copyright 1997-2007 Charles H. Pearson