Most valuable professional
  • Excel Book Excerpt

Excel Sort All Red Cells to the Top of a Report

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Sort All Red Cells
to the Top of a Report

Problem: I’ve read through a 20-page report and marked a dozen cells in red. I need to audit those records and would like to sort the red cells to the top of the report.


Figure 606 Sort the red cells to the top.

Strategy: Starting in Excel 2007, you can sort by color. Follow these steps:

1. Right-click on one of the red cells.

2. From the context menu, choose Sort, Put Selected Cell Color on Top.


Figure 607 Choose to sort by color.

Results: Excel will sort the red cells to the top of the report.


Figure 608 The red cells come to the top.

Additional Details: Using the context menu as described here works fine if you need to sort by only one color. If you used cells of several different colors and want to sort them in a particular order, you need to select Data, Sort to open the Sort dialog. Then, for the first sort level, you choose Quantity in the Sort By dropdown, Cell Color from the Sort On dropdown, and green from the Order dropdown.

You set the next sort level by clicking the Copy Level button and then choosing yellow from the Order dropdown. You click Copy Level for each additional color you need to specify.


Figure 609 Four levels for one column.

If you have many colors in a column, you might use several sort levels to specify how to sort the first column.

Additional Details: You can also sort by font color or cell icon. Amazingly, sorting by color will even work if your colors have been assigned through conditional formatting.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: