3 Ways to Sort by Color in Excel

Sort data by conditional formatting

There are a number of ways you can sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.

The instructions in this article apply to Excel for Microsoft Office 365, Excel 2019, Excel 2016, and Excel 2013 for Windows and Mac.

Selecting a Range to Be Sorted in Excel

Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area (blank rows and columns between areas of related data are OK). Excel then 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; however, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.

If the same range is to be sorted repeatedly, the best approach is to give the range 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 to have Excel automatically highlight the correct range of data in the worksheet.

Any sorting requires the use of 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 must define the color sort order manually.

How to Sort by Color in Excel

In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:

  1. Highlight the range of cells to be sorted (cells A2 to D11 in the example).

    Highlight the range of cells to be sorted (cells A2 to D11 in the example).
  2. Make sure the Home tab is active and select Sort & Filter > Custom Sort.

    The Sort menu and Custom Sort command
  3. Choose ​Cell Color from the drop-down list under Sort on.

    Make sure the box beside My data has headers isn't checked so that the first row doesn't get cut off.

    Choose Cell Color from the Sort On column
  4. Choose Red from the drop-down list under Order.

    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.

    Choose Red under Order
  5. Choose On Top from the drop-down list next to the sort order box so that the red cells will be at the top of the list, then select OK.

    Select On Top from the final menu
  6. The four records with red backgrounds will be grouped together at the top of the data range.

When working with calculations, you can make negative numbers in Excel appear red by default to help them stand out more.

The four records with red backgrounds grouped together at the top of the data range in Excel

How to Sort by Font Color in Excel

In the example below, the records of students enrolled in nursing programs appear in red while those enrolled in science programs are blue. To sort the data by font color:

  1. Highlight the range of cells to be sorted (cells A2 to D11 in the example).

    Highlight the range of cells to be sorted (cells A2 to D11 in the example).
  2. Make sure the Home tab is active and select Sort & Filter > Custom Sort.

    The Sort menu and Custom Sort command
  3. Choose ​Font Color from the drop-down list under Sort on.

    Make sure the box beside My data has headers isn't checked so that the first row doesn't get cut off.

    Choose ​Font Color from the drop-down list under Sort on.
  4. Choose Red from the drop-down list under Order.

    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.

    Choose Red from the drop-down list under Order.
  5. Choose On Top from the drop-down list next to the sort order box so that the red entries will be at the top of the list.

    Choose On Top from the drop-down list next to the sort order box..
  6. Select Add to add a second sort level.

    The Add button
  7. Use the same settings as the first sort level, but this time choose Blue under Order.

    Use the same settings as the first sort level, but this time choose Blue under Order.
  8. Select OK to sort the data and close the dialog box.

    Select OK to sort the data and close the dialog box.
  9. The two records with the red font color will be grouped together at the top of the data range, followed by the two blue records.

    The two records with the red font color will be grouped together at the top of the data range, followed by the two blue records.

How to Sort by Icons in Excel

Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplight icon set based on the daily maximum temperatures.

Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then red:

  1. Highlight the range of cells to be sorted (cells A2 to B31 in the example).

    Highlight the range of cells to be sorted (cells A2 to B31 in the example).
  2. Make sure the Home tab is active and select Sort & Filter > Custom Sort.

    The Sort menu and Custom Sort command
  3. Choose the column containing the conditional icons (Temperature in the example) from the drop-down list under the Column.

    Due to the way conditional formatting with icons works, you can leave the box beside My data has headers checked.

    Choose the column containing the conditional icons (Temperature in the example) from the drop-down list under the Column.
  4. Choose Conditional Formatting Icon from the drop-down list under the Sort On.

    Choose Conditional Formatting Icon from the drop-down list under the Sort On.
  5. Choose Green from the drop-down list under Order.

    Choose Green from the drop-down list under Order.
  6. Choose On Top from the drop-down list next to the sort order box so that the green icon entries will be at the top of the list.

    Choose On Top from the drop-down list next to the sort order box.
  7. Select Add to add a second sort level.

    The Add button
  8. Use the same settings as the first sort level, but this time choose Yellow under Order.

    Use the same settings as the first sort level, but this time choose Yellow under Order.
  9. Select Add to add a third sort level, then use the same settings as the first two levels, but this time choose Red under Order.

    Select Add to add a third sort level, then use the same settings as the first two levels, but this time choose Red under Order.
  10. Select OK to sort the data and close the dialog box.

    Select OK to sort the data and close the dialog box.
  11. The records with the green icon will be grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.

    The records with the green icon will be grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.