New Features in Excel 2010 Conditional Formatting

Share

Facebook
Twitter
LinkedIn

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.)
Mix and Match Icons in Excel 2010 CF - Use with care

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]

Related: Excel 2010 – What is new? | Overview of Excel 2010 Sparklines

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

2 Responses to “Weighted Sorting in Excel ”

  1. Oleg says:

    Just add a column calculating the "performance" or whatever is your criteria and sort by it? No?
    have no patience to waste 13min. Save your time too.

  2. Andrew says:

    Just thought I would mention, the "weird" custom sort behavior mentioned at 5:45 where "% return" doesn't appear to be sorting is because the "August Purchases" field has the sort preference and since these are such unique values, no additional sorting is possible on the "% return" field. If there were two entries that had the same "Customer Since" year AND the same "August Purchases" amount, THEN you would see a sorting of the "% return" on these two entries.

Leave a Reply