Sort by color 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)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: