3 Ways to Sort by Color in Excel

Sort data by conditional formatting

In This Article

Jump to a Section

There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.

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

Select 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 okay. Excel then determines if the data area has field names and excludes those 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. This way, Excel automatically highlights 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. However, when sorting by colors, no such order exists, so you must manually define the color sort order.

How to Sort by Cell Background 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. Go to the Home tab, then select Sort & Filter > Custom Sort.

    The Sort menu and Custom Sort command
  3. Select the Sort on drop-down arrow and choose ​Cell Color.

    Clear the My data has headers check box so that the first row doesn't get cut off.

    Choose Cell Color from the Sort On column
  4. Select the Order drop-down arrow and choose Red.

    When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list 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 are 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 those numbers 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, and 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. Go to the Home tab, then select Sort & Filter > Custom Sort.

  3. Select the Sort on drop-down arrow and choose ​Font Color.

    Clear the My data has headers check box so that the first row doesn't get cut off.

    Choose ​Font Color from the drop-down list under Sort on.
  4. Select the Order drop-down arrow, then choose Red.

    When Excel finds different font colors in the selected data, it adds those colors to the Order drop-down list 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 select the Order drop-down arrow and choose Blue.

    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 are 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 Icon 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 the red icons:

  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. Go to the Home tab and select Sort & Filter > Custom Sort.

  3. Select the Column drop-down arrow, then choose the column containing the conditional icons (Temperature in the example).

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

    Choose the column containing the conditional icons (Temperature in the example) from the drop-down list under the Column.
  4. Select the Sort on drop-down arrow, then choose Conditional Formatting Icon.

    Choose Conditional Formatting Icon from the drop-down list under the Sort On.
  5. Select the Order drop-down arrow, then choose Green.

    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 select the Order drop-down arrow and choose Yellow.

    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 select the Order drop-down arrow and choose Red.

    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 are 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.
Was this page helpful?