Categories
Excel

Sort Data with Conditional Formatting Criteria

You normally sort the records or rows of a table by the values in one or more columns of the list or table. However, Excel also lets you sort by the font color, fill color, or cell icons that you apply to them with conditional formatting criteria. This is how you can do it.

  1. Sort Data Based on Cell Color
  2. Sort Data Based on Font Color
  3. Sort Data Based on Conditional Formatting Icon

Sort Data Based on Cell Color

To sort data based on cell color in Excel, you can follow these steps:

  1. Select the data that you want to apply conditional formatting. We selected B2:B12.
  2. Go to the Home tab and click on Conditional Formatting in the Styles group.
  3. Choose Highlight Cells Rules or Color Scales option.
  4. Choose the colors that will determine how the cells are formatted.

This will apply the conditional format to the range. Next, follow these steps to sort the data based on cell colors (that we applied with the conditional formatting):

  1. Select the data/range.
  2. Go to the Home tab and click on Sort & Filter in the Editing group.
  3. In the Sort dialog box, choose the column that you applied the conditional formatting from the Sort by drop-down list.
  4. Under Sort on, choose Cell Color from the drop-down list.
  5. Under Order, choose the color that you want to stay on the top or bottom.
  6. Click OK to close the Sort dialog box.

Sort Data Based on Font Color

  1. Select the data that you want to apply conditional formatting. We selected B2:B12.
  2. Go to the Home tab and click on Conditional Formatting in the Styles group.
  3. Choose New Rule, it opens the New Formatting Rule dialog box.
  4. Choose Format only values that are above or below average.
  5. Choose above from the Format values that are: menu.
  6. Click Format button, it opens the Format Cell dialog box.
  1. Select Font tab in the Format Cell dialog box and choose a color (Green) from the Color menu.
  2. Click OK twice to return back to the sheet.
  3. Now repeat all the above steps again and in step 5 choose below and choose a different font color for lower values.

The above steps apply the conditional formatting (green color) on cells that hold values above the average of selected cells and red color on cells that contain values lower than the average of selected cells.

Now follow these steps to sort the data based on font color (that we applied with the conditional formatting):

  1. Select the range of cells or the table you want to sort.
  2. Click on the Sort & Filter button in the Home tab and choose Custom Sort.
  3. In the Sort dialog box, choose the column that contains the font color you want to sort by from the Sort by dropdown list.
  4. In the Sort On dropdown list, select Font Color.
  5. Choose the desired color in the Order dropdown list.
  6. Specify the sort order, either On Top or On Bottom.
  7. Click the OK button to apply the sorting.

Excel will rearrange the data based on the selected font color. The rows with the specified color will be sorted according to the chosen sort order.

Sort Data Based on Conditional Formatting Icon

To sort data based on conditional formatting icons in Excel, you can follow these steps:

  1. Select the data that you want to apply conditional formatting. We selected B2:B12.
  2. Go to the Home tab and click on Conditional Formatting in the Styles group.
  3. Choose Icon Sets option.
  4. Choose the icon that will determine how the cells are formatted.

Next, in the Sort dialog box choose the column that contains the conditional formatting icons you want to sort by from the Sort On dropdown list, you can follow these steps:

  1. Select the range of cells or the table you want to sort.
  2. Click on the Sort & Filter button in the Home tab and choose Custom Sort.
  3. In the Sort dialog box, choose the column that contains the font color you want to sort by from the Sort by dropdown list.
  4. In the Sort On dropdown list, select Conditional Formatting Icon.
  5. Choose the desired icon in the Order dropdown list.
  6. Specify the sort order, either On Top or On Bottom.
  7. Click the OK button to apply the sorting.

Excel will rearrange the data based on the selected conditional formatting icon. The data or table with the specified icon will be sorted according to the chosen sort order.

Conditional Formatting

  1. Setting Up Checkboxes for Conditional Formatting
  2. Highlighting Formula Cells with Conditional Formatting
  3. Sum Cells That Meet Conditional Formatting Criteria
  4. Highlight Every Other Row or Column with MOD Function and Conditional Formatting
  5. Enable and Disable Conditional Formatting with a Checkbox
  6. Sort Data By Conditional Formatting Criteria