Sort data by conditional formatting

There are several ways tosort data in Microsoft Excel.

Learn how to use conditional sorting inExcelto sort by font color, cell background color, or icon color.

Blank rows and columns between areas of related data are okay.

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

Excel then determines if the data area hasfield namesand excludes those rows from the records to be sorted.

Allowing Excel to snag the range to be sorted is fine for small amounts of data.

If the same range is to be sorted repeatedly, the best approach is togive the range a name.

The Sort menu and Custom Sort command

This way, Excel automatically highlights the correct range of data in the worksheet.

Any sorting requires the use ofsort order.

When sorting by values, there are two possible sort orders: ascending and descending.

Choose Cell Color from the Sort On column

Go to theHometab, then selectSort & Filter>Custom Sort.

Select theSort ondrop-down arrow and choose Cell Color.

Clear theMy data has headerscheck box so that the first row doesn’t get cut off.

Choose Red under Order

Select theOrderdrop-down arrow and chooseRed.

The four records with red backgrounds are grouped together at the top of the data range.

To sort the data by font color:

Select theSort ondrop-down arrow and choose Font Color.

Select On Top from the final menu

Select theOrderdrop-down arrow, then chooseRed.

SelectAddto add a second sort level.

Use the same parameters as the first sort level, but this time select theOrderdrop-down arrow and chooseBlue.

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

SelectOKto sort the data and exit the dialog box.

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

Go to theHometab and selectSort & Filter>Custom Sort.

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

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

Select theSort ondrop-down arrow, then chooseConditional Formatting Icon.

Select theOrderdrop-down arrow, then chooseGreen.

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

Use the same tweaks as the first sort level, but this time select theOrderdrop-down arrow and chooseYellow.

Choose Red from the drop-down list under Order.

Choose On Top from the drop-down list next to the sort order box.

The Add button

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

Select OK to sort the data and close the dialog box.

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.

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

Choose Green from the drop-down list under Order.

Choose On Top from the drop-down list next to the sort order box.

The Add button

Use the same settings as the first sort level, but this time choose Yellow 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.

Select OK to sort the data and close the dialog box.