Excel Tutorials: How to Sort Data by Cell Color in Excel 2007

Sort Data by Cell Color in Excel 2007

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.

Sort By Cell Color Excel 2007

© Ted French

Excel sort options include a Custom Sort feature that allows you to sort data by the cell background color or by font color.
Sorting by color can be useful if you are using conditional formatting, which allows you to change the cell color or font color of data that meets certain conditions.
Sorting by color can then be used to group this data together for easy comparison and analysis.

Example: Sort by Cell Color in Excel

Note: For help with this example, see the image above.

1. Enter the following data into cells D1 to D6: 40, 20, 30, 60, 5, 25.
   
   
2. Use the mouse and CTRL key on the keyboard to select cells D1, D3, D5.
   
   
3. Click on the Home tab of the ribbon.
   
   
4. Click on the down arrow next to the Fill Color icon on the ribbon to open the Fill Color drop down list.
   
   
5. Choose red from the colors available to change the cell color in cells D1, D3, D5 to red.
   
   
6. Drag select cells D1 to D6 to highlight them.
   
   
7. Click on the Sort & Filter icon on the ribbon to open the drop down list.
   
   
8. Click on Custom Sort in the drop down list to bring up the Sort dialog box.
   
   
9. Since our data does not have headings, if necessary, click on My data has headers at the top right of the dialog box to remove the check mark.
   
   
10. Under the Sort On heading in the dialog box, choose Cell Color from the drop down list.
    
    
11. When Excel finds different cell colors in the selected data it adds those colors to the options listed under the Order heading in the dialog box.
    
    
12. Under the Order heading, select the color red from the drop down list.
    
    
13. Chose On Top under the sort order so that the data in red-colored cells will be at the top of the list.
    
    
14. Click OK.
    
    
15. The data in cells D1 to D6 should now be sorted so that the cells with the red background are at the top:
    
    40, 30, 5, 20, 60, 25 .