Sorting/Filtering Conditional Format Results

A client recently e-mailed me inquiring about filtering in his Excel worksheet. It seems he had a list of data in column A and one in Column B. He had created a conditional format such that any data that was in column A and also in colum B, would turn a bolded red color. He wanted to be able to sort by that color.

Fortunately, that is not all that difficult to do.

Assuming that all your data and conditional formats are already set up, follow the steps below to learn how:

  1. Select all the cells you would like included in your sort.
  2. Display the Data tab of your Ribbon.
  3. Click the Sort tool to display the dialog box.

  1. Using  the Sort By drop-down list, select Column A (where your conditional formatting is applied).
  2. Using the SortOn drop-down list, select Font Color.
  3. Using the Order drop-down list, select the color you would like to sort by (red).
  4. Using the second drop-down list under Order, specify whether you want the red font cells to be on the top or bottom of  your list.
  5. Click OK.

If the color of the cells change because of the conditional formatting, you can later re-sort the table and you will have no problems.

Should you want to filter by the colors, that can also be easily accomplished using an AutoFilter.

Follow the steps below to learn how:

  1. Turn on AutoFiltering for your data,  by:
  • Selecting any cell in your list.
  • Displaying the Data tab of your Ribbon.
  • In the Sort & Filter group, clicking the Filter tool. Excel will determine where your column labels are located and adds pull-down arrows to the right side of each label’s cell.
  1. Click the down-arrow at top of Column A, and you will see a lot of options available to you.
  2. Select Filter by Color and Excel will display a list of the colors that are in the column.
  3. Select the color that you want displayed (not filtered out). In most cases, you will select Automatic.

2 thoughts on “Sorting/Filtering Conditional Format Results”

  1. Filter by Color, using conditional color formatting, doesn’t work.
    I’ve tried it on Excel 2010.
    Are you sure of it?

  2. You need to make sure it is sorting by “Cell Color” and not “Font Color” which is the default. Then it works.

Comments are closed.

Scroll to Top

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!