3 Ways to 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. Sorting by color can be useful when using conditional formatting, which can be used to change the background color or font color of data that meets certain conditions. You can then sort by color to group this data together for easy comparison and analysis.

01
of 03

Sorting by Cell Background Color in Excel

Sorting Data by Cell Color
Sorting Data by Cell Background Color. © Ted French

Before data can be sorted, Excel needs to know the exact range that is to be sorted, and usually, Excel is pretty good at selecting areas of related data, as long as

  1. No blank rows or columns were left within an area of related data.
  2. Blank rows and columns were left between areas of related data.

Excel will even determine fairly accurately if the data area has field names and exclude 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 will automatically highlight the correct range of data in the worksheet.

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 the user defines the color sort order in the Sort dialog box.

In the example, for the range of cells H2 to L12,  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 were followed to sort the data by cell background color.

  1. Highlight the range of cells to be sorted (H2 to L12).
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the dropdown list.
  4. Click on Custom Sort in the dropdown list to bring up the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose ​Cell Color from the dropdown 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 dropdown 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. Click OK to sort the data and close the dialog box.
  10. The four records with the red cell color should be grouped together at the top of the data range.
02
of 03

Sort Data by Font Color in Excel

excel-2013-sort-font-color.jpg
Sorting Data by Font Color in Excel. © Ted French

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 H2 to L12, 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.

The data were sorted by font color using this method:

  1. Highlight the range of cells to be sorted (in the example, H2 to L12).
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the dropdown list.
  4. Click on Custom Sort in the dropdown list to bring up the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose Font Color from the dropdown 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 dropdown 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, click on the Add Level button to add the second sort level.
  10. For the second level, under the Order heading, select the color blue from the dropdown 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. Click OK to sort the data, and close the dialog box.
  13. The two records with the red font color should be grouped together at the top of the data range, followed by the two blue records.
03
of 03

Sort Data by Conditional Formatting Icons in Excel

excel-2013-conditional-format-icon-sort.jpg
Sorting by Conditional Formatting Icons. © Ted French

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 user sets the sort order in the Sort dialog box.

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

The following steps were followed to sort the data by icon color.

  1. Highlight the range of cells to be sorted (I3 to J27).
  2. Click on the Home tab of the ribbon.
  3. Click on the Sort & Filter icon on the ribbon to open the dropdown list.
  4. Click on Custom Sort in the dropdown list to bring up the Sort dialog box.
  5. Under the Sort On heading in the dialog box, choose Cell Icon from the dropdown 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 dropdown 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 the Add Level button to add the second sort level.
  10. For the second level, under the Order heading, select the amber or yellow icon from the dropdown list.
  11. Again, chose On Top under the sort order if necessary. This will place 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. Click OK to sort the data and close the dialog box.
  14. The records with the green icon should be grouped together at the top of the data range, followed by the records with the amber icon, and then those with a red icon.