Gandalf's notebook: New Features in Excel 2010 Conditional Formatting

From Chandoo.org

New Features in Excel 2010 Conditional Formatting: "

Excel 2010 - Conditional Formatting - Review, Improvements and Demo

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

Refer values in other worksheets - excel conditional formatting

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:

Data Bars in Excel 2007 vs. Excel 2010 - a comparison

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:

An In-cell Progress Chart - Excel Conditional Formatting Trick

* 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.

Icon Sets in Excel 2010 Conditional Formatting - Compared with Excel 2007

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.)

What do you think about new CF Features in Excel 2010?

I am excited to try the data bars in real-world project. I find the possibility of referring to other sheets very good. Also, I am not sure if its just me, but Excel 2010 conditional formatting feels fast. In fact, not just CF, almost everything in Excel 2010 feels fast and responsive.

What about you? How are you planning to use Excel 2010 CF features in your work? Please tell us using comments.

PS: By leaving a comment, you can win a copy of Office 2010 – Home & Student Edition. Contest sponsored by Microsoft India.

References: Excel Conditional Formatting Improvements [MSDN blog]