Sorting by Cell Background Color in Excel

Sorting by Color in Excel

In addition to sorting by values - such as text or numbers -  Excel has custom sort options that permit sorting by 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.

As shown in the image above, sorting by color can then be used to group this data together for easy comparison and analysis.

This series of tips covers different methods of sorting data in Excel using color. Specific information for the different sort by color options can be found on the following pages:

1. Sort by Cell Background Color (this page below)
2. Sort by Font Color
3. Sort by Conditional Formatting Icons

Selecting Data to be Sorted

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 - so long as when it was entered,

1. no blank rows or columns were left within an area of related data;
2. and 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 this row 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.

Sorting by Color and Sort Order

Sorting requires the use of a sort order.
When sorting by values, there are two possible sort orders - ascending or descending. When sorting by colors, however, no such order exists so it is the user who defines the color sort order in the Sort dialog box.

Sort by Cell Color Example

In the image above, 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 drop down list
4. Click on Custom Sort in the drop down list to bring up the Sortdialog 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, chose 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

Sort by Font Color

Very similar to sorting by cell color, sorting by font color can be used to quickly sort data with different colored text.

Changes in font color can be done using conditional formatting or as a result of number formatting - such as when displaying negative numbers in red to make them easier to find.

Sort by Font Color Example

In the image above, for the range of cells H2 to L12  conditional formatting was used to change the font color of student records based on their program 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 following steps were followed to sort the data by font 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 drop down list.
4. Click on Custom Sort in the drop down list to bring up the Sortdialog 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

1. At the top of the dialog box, click on the Add Level button to add the second sort level
2. For the second level, under the Order heading, select the color blue from the drop down list
3. Chose On Top under the sort order so that the blue-colored data will be above those records with the default black font
4. Click OK to sort the data and close the dialog box
5. The two records with the red font color should be grouped together at the top of the data range followed by the two blue font colored records

Sort by Conditional Formatting Icons

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 font and cell formatting changes.

As with sorting by cell color, when sorting by icon color the user sets the sort order in the Sortdialog box.

Sort by Icon Color Example

In the image 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 temperature 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 drop down list.
4. Click on Custom Sort in the drop down list to bring up the Sortdialog 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 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 drop down list

1. 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
2. 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, since they are the only records left and will be located at the bottom of the range
3. Click OK to sort the data and close the dialog box
4. 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