Sorting or Filtering by Conditional Format Results

by Allen Wyatt
(last updated October 5, 2013)

3

John asked if it is possible to sort or filter based on the conditional formatting in a range of cells. He has a list of values in column A and a list in column B. He created a conditional format that makes any values in column A that are also in column B appear as bold red, and he wanted to sort or filter by that result.

The short answer is that no, you cannot sort by conditional formatting results—at least not without updating to Excel 2007 or a later version of the program. Doing so is much more complex than one might think, as shown at this page on Chip Pearson's Web site:

http://www.cpearson.com/excel/CFColors.htm

The easiest answer is to simply recreate your conditional formula in column C, to the right of your two columns of data. The formula should return True or False, depending on whether the value in column A is found somewhere in column B. With the True/False value in column C, you can filter or sort based on what is there.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2384) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Relative References within Named Ranges

Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some ...

Discover More

Exact Matches with DSUM

The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you ...

Discover More

Selecting a Paper Source

When you print a worksheet, you may want to specify that the printout be done on a particular paper tray in a particular ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

MORE EXCELTIPS (MENU)

Removing Conditional Formats, but Not the Effects

Conditional formatting is very powerful, but at some point you may want to make the formatting "unconditional." In other ...

Discover More

Using Multiple Test Conditions

When creating conditional formats, you are not limited to only one condition. You can create up to three conditions, all for ...

Discover More

More than Three Conditional Formats

Conditional formatting is a great feature for making the data in your worksheets more understandable and usable. What if you ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 8?

2015-09-12 06:11:52

kashif

I want inner store stock report with many order
I want date wise speared first January then February at last December

Kindly tell me how to change it


2014-09-03 19:51:09

Christina D

Try the Contextures website for information on formatting entire row based on one cell.

http://www.contextures.com/xlCondFormat02.html


2014-08-27 07:06:02

Vladimir

I am using MS Excel 2010. When sorting a table with Conditionally Formatted cells the conditional formatting stays with the original location (C7 before and after sorting for example) and doesn't move with the whole row. Is there a solution to this problem?
Thanks


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share