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:
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:
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)