New Features in Excel 2010 Conditional Formatting

Conditional formatting is one of favorite features in Excel. CF has helped me save the day at work more than a dozen occasions. I almost became project manager just because I knew how to make a gantt chart in excel using conditional formatting. I have written extensively about it.

So, I was naturally curious to explore what is new in Excel 2010′s Conditional Formatting. In this post, I will share some of the coolest improvements in CF.

1. You can refer to data in other worksheets now

This is the best new addition to CF capabilities in Excel 2010. Now we can refer to data in other worksheets without using any named ranges or copying the data over to primary sheet.

2. Solid Data Bars, Finally!

In Excel 2007, MS introduced a new feature called “data bars”. It felt like an exciting thing, except for one gnawing problem. The bars have gradients. So, not only they looked ugly, but they were also difficult to read (also, they were inaccurate at default settings).

Thankfully MS rectified these problems and significantly improved data bars in Excel 2010.

Now, you can,

• Create data bars with solid fill
• Apply borders to data bars (so that even gradient fills look elegant)
• Have negative data bars
• Have an axis so that comparison is easy

Here is a small comparison between Excel 2007 & Excel 2010 Data Bars:

Using data bars to create in-cell progress charts:

You can use data bars to create in-cell progress charts (or thermo-meter charts) like this:

* Hint: The trick is to use cell background color along with data bar.

[Related: Jon Peltier has written a beautiful article reviewing data bars in Excel 2010.]

3. More Icon Sets in Conditional Formatting

Although I rarely use icons in conditional formatting, I am happy to report that MS has added 3 new sets of Icons to the conditional formatting library.

Also, you can mix and match icons depending on the rules (how I wish they didnt allow this. Mix and match can produce more evil combinations than good ones.)