3 ways to sort by color in Excel

In addition to sorting by values, such as text or numbers, Microsoft Excel provides customizable options that let you sort cells by color.

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

Contents
  1. For sorting
  2. Sort by cell background color in Excel
  3. Sort data by font color in Excel
  4. Sort data by conditional formatting icons in Excel

For sorting

Before sorting data, Excel needs to know the exact sort range. Excel can select linked data regions if:

  • There are no empty rows or columns in the linked data area.

  • Empty rows and columns are between linked data areas.

Excel determines whether there are field names in the data area and excludes those rows from the records to be sorted.

Allowing Excel to select a sort range is suitable for small amounts of data that can be visually inspected to provide the following:

  • This is the correct range selected.

  • That there are no holes in the assortment.

  • That the data records are intact and correct after sorting.

For large data areas, the easiest way to make sure the correct range is selected is to select it before sorting.

If the same range needs to be sorted repeatedly, it’s best to assign it Name

If there is a name defined for the range you want to sort, enter it in the Name field or select it from the appropriate drop-down list and Excel will automatically highlight the correct data range in the worksheet.

Sort by cell background color in Excel

Each sort requires the use of sort order. When sorting by values, there are two sorting orders: ascending and descending. However, there is no such order when sorting by color, so you define the sort order by color in the dialog box To sort

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

Rather than changing the cell color of all student records, conditional formatting only affected those 20 or younger, and everything else remained unchanged.

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

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

  1. Select the range of cells you want to sort (in the example, cells A2 through D11).

  2. Select At home

  3. Select Sort and filter to open the drop-down list.

  4. Select Custom Sort in the drop-down list to open the dialog box To sort

  5. under the title sort on in the dialog select cell color in 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 heading Order in the dialog box.

  7. under the title Order select red from the drop-down list.

  8. Select if necessary Upwards sorted so that the data is in red at the top of the list.

  9. Select Okay to sort the data and close the dialog box.

  10. The four red cell entries are grouped at the top of the data range.

Sort data by font color in Excel

Like sorting by cell color, sorting by font color quickly organizes data with colored text.

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

In the figure, cell range A2 to D11 used conditional formatting to change the font color of student records based on their curriculum:

  • Red Font: Nursing Students

  • Blue font: Students enrolled in a beta program

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

The sort order by font color was red followed by blue. Default black font submissions are not sorted.

To sort data by font color:

  1. Select the range of cells you want to sort (in the example, cells A2 through D11).

  2. Select Home

  3. Select Sort and filter to open the drop-down list.

  4. Select Custom Sort in the drop-down list to open the dialog box To sort

  5. under the title sort on in the dialog select font color in 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 heading Order in the dialog box.

  7. under the title Order pick a color red in the drop-down list.

  8. Select if necessary Upwards sorted so that the data is in red at the top of the list.

  9. At the top of the dialog, select Add level to add a second sorting level.

  10. For the second level under the head Order pick a color blue in the drop-down list.

  11. Select Above in sort order to have the data in blue above those entries with default black font.

  12. Select Okay to sort the data and close the dialog box.

  13. Two entries with a red font color are grouped at the top of the data range, followed by two blue entries.

Sort data by conditional formatting icons in Excel

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

Similar to sorting by cell color, sorting by icon color sets the sort order in the dialog box. To sort

In the example above, the range of cells containing temperature data for Paris, France has been conditionally formatted with the icon stop signal based on daily maximum temperatures for July 2014. data, with records showing green icons first, then yellow icons, then red icons.

Follow these steps to sort data by icon color:

  1. Select the range of cells you want to sort (in the example cells from A3 to B31).

  2. Select Home

  3. Select Sort and filter to open the drop-down list.

  4. Select Custom Sort in the drop-down list to open the dialog box To sort

  5. under the title Kind in the dialog select cell icon in the drop-down list.

  6. When Excel finds cell icons in the selected data, it adds those icons to the options listed under the heading Order in the dialog box.

  7. under the title Order select the green icon from the drop-down list.

  8. Select if necessary Upwards in sort order so that data with green icons is at the top of the list.

  9. At the top of the dialog, click Add level to add a second sorting level.

  10. For the second level under the head Order select yellow or yellow icon from the drop-down list.

  11. Select if necessary Above in order. This places the second group of items below those with green icons, but above all other items that are sorted.

  12. Since there are only three icon variants in this set, there is no need to add a third level to sort items with red icons. These are the only remaining entries that will be placed at the bottom of the range.

  13. Select Okay to sort the data and close the dialog box.

  14. Records with a green icon are grouped at the top of the data range, followed by records with a yellow icon, then records with a red icon.

Leave a Reply

Your email address will not be published.