3 Ways to Sort by Color in Excel

Sort by color in Excel

In addition to sorting by values such as text or numbers, Microsoft Excel offers custom options that permit you to sort your cells by order of color.

Note The instructions in this article apply to Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 for Mac, and Excel 2016 for Mac.

Before Sorting

Before data can be sorted, Excel needs to know the exact range to sort. Excel can select areas of related data, as long as:

Excel determines if the data area has field names and excludes these rows from the records to be sorted.

Allowing Excel to select the range to be sorted is fine for small amounts of data that can be checked visually to ensure:

  • That the correct range is selected.
  • That there are no blanks in the range.
  • That, after the sort, the data records are intact and correct.

For large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before starting the sort.

If the same range is to be sorted repeatedly, the best approach is to give it a Name.

If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list and Excel automatically highlights the correct range of data in the worksheet.

Sort by Cell Background Color in Excel

A screenshot showing how to sort data by cell color in Excel

Any sorting requires the use of a sort order. When sorting by values, there are two possible sort orders: ascending and descending. When sorting by colors, however, no such order exists, so you'll define the color sort order in the Sort dialog box.

In the example, for the range of cells A2 to D11, conditional formatting was used to change the cell background color of records based on the age of the students.

Instead of changing the cell color of all student records, only those 20 years of age or younger were affected by conditional formatting, with the rest remaining unaffected.

These records were then sorted by cell color to group the records of interest at the top of the range for easy comparison and analysis.

The following steps show how to sort the data by cell background color:

  1. Highlight the range of cells to be sorted (in the example, cells A2 to D11).
  2. Select Home.
  3. Select Sort & Filter to open a drop-down list.
  4. Select Custom Sort in the drop-down list to open the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose ​Cell Color from the drop-down list.
  6. When Excel finds different cell background colors in the selected data, it adds those colors to the options listed under the Order heading in the dialog box.
  7. Under the Order heading, select the color red from the drop-down list.
  8. If necessary, choose On Top under the sort order so that the red-colored data will be at the top of the list.
  9. Select OK to sort the data and close the dialog box.
  10. The four records with the red cell color are grouped together at the top of the data range.

Sort Data by Font Color in Excel

A screenshot showing how to sort data by text color in Excel

Very similar to sorting by cell color, sorting by font color quickly organizes data with different colored text.

You can change font color by using conditional formatting or as a result of number formatting, such as when displaying negative numbers in red to make them easier to find.

In the image, for the range of cells A2 to D11, conditional formatting was used to change the font color of student records based on their programs of study:

  • Red font: students enrolled in the nursing program
  • Blue font: students enrolled in the science program

These records were then sorted by font color to group the records of interest at the top of the range for easy comparison and analysis.

The sort order for font color was red followed by blue. Records with the default black font color were not sorted.

Here's how to sort the data by font color:

  1. Highlight the range of cells to be sorted (in the example, cells A2 to D11).
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort in the drop-down list to open the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose Font Color from the drop-down list.
  6. When Excel finds different font colors in the selected data, it adds those colors to the options listed under the Order heading in the dialog box.
  7. Under the Order heading, select the color red from the drop-down list.
  8. If necessary, chose On Top under the sort order so that the red-colored data will be at the top of the list.
  9. At the top of the dialog box, select Add Level to add the second sort level.
  10. For the second level, under the Order heading, select the color blue from the drop-down list.
  11. Chose On Top under the sort order so that the blue-colored data will be above those records with the default black font.
  12. Select OK to sort the data and close the dialog box.
  13. The two records with the red font color are grouped together at the top of the data range, followed by the two blue records.

Sort Data by Conditional Formatting Icons in Excel

A screenshot showing how to sort data using an icon set in Excel

Another option for sorting by color is to use conditional formatting icon sets for the sort order. These icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes.

As with sorting by cell color, when sorting by icon color, the sort order is set in the Sort dialog box.

In the example above, the range of cells containing temperature data for Paris, France, is conditionally formatted with the stop light icon set based on the daily maximum temperatures for July 2014. These icons are used to sort the data, with records displaying the green icons grouped first, followed by the amber icons, and then red.

Follow these steps to sort the data by icon color:

  1. Highlight the range of cells to be sorted (in the example, cells A3 to B31).
  2. Select Home.
  3. Select Sort & Filter to open the drop-down list.
  4. Select Custom Sort in the drop-down list to open the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose Cell Icon from the drop-down list.
  6. When Excel finds cell icons in the selected data, it adds those icons to the options listed under the Order heading in the dialog box.
  7. Under the Order heading, select the green icon from the drop-down list.
  8. If necessary, chose On Top under the sort order so that the data with green icons will be at the top of the list.
  9. At the top of the dialog box, click on select Add Level to add the second sort level.
  10. For the second level, under the Order heading, select the amber or yellow icon from the drop-down list.
  11. Chose On Top under the sort order, if necessary. This places the second group of records below those with green icons, but above all other records being sorted.
  12. Since there are only three icon choices in this set, there is no need to add a third level to sort the records with red icons. They are the only records left and will be located at the bottom of the range.
  13. Select OK to sort the data and close the dialog box.
  14. The records with the green icon are grouped together at the top of the data range, followed by the records with the amber icon, and then those with a red icon.