Excel 2007: Sort by Cell Color
Marlene, of Phoenix asks, "I have recently upgraded to Excel 2007 and found
Conditional Formatting allows me to color cells based on results. But, am I
able to sort the results based on the Conditional Formatting.
For those not familiar with Excel 2007 Conditional Formatting, there are a
lot of powerful things now available. See my previous article for details on
working with Conditional Formatting in Excel 2007.
To answer Marlene's question, yes, Excel 2007 allows cells to be sorted by
color once Conditional Formatting has been completed. Sorting by color helps to
visually group like-data.
To sort by cell color, follow these instructions.
Place the mouse in a cell within the column you want to sort.
Click on the “Sort" button in the "Sort & Filter" area of the “Data" tab. In the "Sort" window, select the options you want.
For our example, we'll use a previous list that contains test scores in Column C. Test scores have been colored based on Conditional Formatting, which applied different colors for a score of 91-100, 81-90, 71-80, and anything 70 and lower.
Click on the “Sort By" drop down and select Column C, "Grade.”
Click on the "Sort On" drop down and select the "Cell Color" option.
Click on the "Order" drop down and select the first color you want to appear "On Top."
Continue adding levels by clicking on the “Add Level" button at the top of the window and repeating the steps above for each of the colors you want to sort. If you only want one color to appear "On Top," then you do not need to continue with more levels.
HINT: If you want to setup the formatting to place each color on
the bottom of the list first, select the "On Bottom" option in the last drop
down list just to the right of the "Order" drop down field.
Once all sorting has been setup, click on the "OK" button to
apply the sorting rules and see that the column has now been sorted according to
Use these simple instructions and you'll become very comfortable with sorting
by cell color in Excel 2007.