Excel: Sort All Red Cells to the Top of a Report

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

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.

Strategy: 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.

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

  1. Sort the red cells to the top.

    1. Choose to sort by color.

    1. 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.

  2. 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.