Excel 2010 reinvents the conditional format for data bars. We now have more control over the conditional formatting as a whole and not only databars; however, with data bars we now have the option to plot negative values as well.

Here’s how it looks like:

Figure 1: Excel 2010 Conditional Format Data Bars

Once we apply one of the built-in conditional formats for data bars, we can access the Conditional Format Manage Rules dialog box in order to edit the new conditional format rule:

Figure 2: Determining Negative Value and Axis...


Finally, you can determine various aspects of how the value are displayed as well as the axis:

Figure 3: Determining data bars aspects such as axis settings

Excel 2007 introduced Icon Sets to Excel Conditional Formatting. Excel 2010 introduces No Cell Icon to Conditional Formatting. In Excel 2007, after applying your Icon Set Conditional Format, you would get something like this:

Figure 1: Icon Set Conditional Format

The problem, of course, is that the whole this is a mess. If the idea is to identify poor grades, you’d need to create extra Conditional Formats so that you could hide the undesired icons. Excel 2010 introduces No Cell Icon which removes the need for extra formulas and extra Conditional Formats. Here’s how it looks like:

Figure 2: No Cell Icon

By choosing No Cell Icon, Excel 2010 will omit the icons for the selected conditions saving you all the hassle of extra conditioning:

Figure 3: Result after applying No Cell Icon

VERSION: 14.0.4006.1110

A few days ago, I wrote about the "Backstage View" (see article here: http://www.msofficegurus.com/post/Office-2010-Backstage-View.aspx) which is a new addition to Microsoft Office 2010. However, when we move to Excel Options (or any other "Options" in Microsoft Office), the well-know dialog box is opened:

Figure 1: Excel Options dialog box

I think that a better way to present the Options dialog box is to embed it in the Backstage View. Here's how it might look like:

Figure 2: Options dialog box embeded

VERSION: 14.0.4006.1110

Excel 2010 now comes with sparklines. In the past, we needed third party software in order to work with these little creatures. They are now native to Excel. Here’s a look at the Excel 2010 sparklines:

Figure 1: Line Sparkline

In the above scenario, we have a line sparkline, but Excel 2010 comes with some others such as “column sparkline” and “win/loss sparkline” shown below for the same dataset:

Figure 2: Column Sparkline with high points (light color)

If we consider the conditional formatting data bars introduced in Excel 2007, this is a great addition to Excel 2010.

Most importantly: it is very easy to use. Under the “Insert” tab, close to the “Charts” group you will find the “Sparklines” group. The shaded area in the picture below shows the sparkline location:

Figure 3: Sparkline group under the Insert tab

Once the sparkline is inserted, you can quickly change style, type, show/hide low/high points, etc:

Figure 4: Sparkline extensibility tab and Sparkline Design tab