Sort by color with ASAP Utilities

(Update October 18, 2007: If you want an easier way to do this, see this post: Video: How to sort by color in Excel with ASAP Utilities)

In Excel you can only sort by value, not by formatting such as cell colors. With ASAP Utilities you can! Here's a short example how to do sort by color. For this I've built a custom worksheet function into ASAP Utilities (added October 2004) that extracts the color of a cell: `=asapcellcolorindex(range)`

In order to sort by cell color we need to create an extra column that will list all colors as numbers (cell color index) which we can then use to sort by.
I've created an example workbook to show it.

This is what your data might look like at the start (excuse me for the girly colors):

Enter the formula: =asapcellcolorindex(B2) to sort by cell color. In case you want to sort by font color use the function =asapfontcolorindex(B2)

Copy the formula down to the last cell (shortcuts: Controls+C » Control+Alt+L » Control+V)

In the menu choose Data » Sort:

The result:

Important
This formula does not recalculate itself if you change a color. Only if you change a cell's value and a normal calculation is performed, so if you only change a color, you need to press F9 or Control+Alt+F9 te recalculate the cell-colors.

If you want to know more about this technique I recommend an article Chip Pearson wrote: www.cpearson.com/excel/SortByColor.htm

You don't have ASAP Utilities?
Download it at www.asap-utilities.com/p_dl.php (free).

4 Responses to “Sort by color with ASAP Utilities”

1. Bastien
Thank your for very much for the help.
Aqib Rizvi

2. Simple and Excellent solution.

3. In the mean time I have built this into ASAP Utilities:

Range Â» Advanced sorting…

Extend the sorting capabilities of Excel.
You can now sort on 5 columns and we have extended the characteristics to sort on.

You can sort by:
– Value
– Number Format
– Cell Color
– Cell Color Indexnr.
– Font Color
– Font Color Indexnr.
– Font Size
– Font Name
– Bold
– Italic
– Underline
– Strikethrough
– Text length
– Shuffle (randomize order)
– Formula
– Email address host (@…)
– Year-Month-Day
– Year-Day-Month
– Month-Day-Year
– Month-Year-Day
– Day-Year-Month
– Day-Month-Year
– Day of the week (start on Sunday)
– Day of the week (start on Monday)

In the Excel menu choose ASAP Utilities Â» Range Â» Advanced sorting… to start this utility.

//www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=230&utilities=Range

4. What a life saver. Thank you.