Bacon Bits:

Christmas is coming soon and this week will be a slow work week for many of you. This lull in the action gives me time to talk about Excel 2010.

I've dubbed this week, 'Excel 2010 Improvements week'. Every day this week, I'll take time to blog about some of the new features in Excel 2010 that strike me as real improvements.

The first topic of the week is Data Bars.

Data Bars in Excel 2007

Microsoft introduced Data Bars with Excel 2007 as a way to add visualizations to worksheets without relying solely on charts.

Data Bars allow you to add a chart-like visualization inside each cell based on that cell's value.

I suspect that Data Bars were supposed to blow our minds with their visualization potential.  Unfortunately, the version released in Excel 2007 seemed "unfinished" (to put it politely).

To demonstrate what I mean, I've applied Data Bars to a range of cells. Next to that, is a chart that uses the same range.

In theory, the Data Bars should look exactly like the chart. But we can see a few problems right out of the gate:

1. The zero in cell D3 should not be showing any bar. The chart to the right correctly shows no bar for this first data point.
2. The data bar representing the number 1 in cell D4 is about half as big as the bar in cell D5 where there is a 10. This is obviously an incorrect representation of the magnitude of change. The chart on the right shows a more accurate view.
3. Cell D14 illustrates how Data Bars in Excel 2007 couldn't even handle negative numbers. Ideally, the data bars should be inverted as in the chart on the right.
4. If you want to get picky, the gradients at the end of the data bars can make it difficult for some users to determine where the bar ends.

Data Bars in Excel 2010

Here is the same data shown above with Excel 2010 Data Bars applied.

As you can see in Excel 2010, Microsoft has put in a lot of work to fix the open issues with Data Bars.

The zero has no bar and the size of each bar correctly corresponds (more or less) with the values in the cell. Plus, Negative values are accurately represented!

Cosmetically, you'll notice that the Data Bars shown are solid. In Excel 2010, you can choose to go with solid bars or the old Excel 2007 gradient style.

In terms of formatting, you can obviously change the colors on both positive and negative bars, as well as reset the axis to always start in the middle of the cell.

I'll admit that I dismissed the Data Bars in 2007, choosing not to use them for a whole host of reasons. But the Data Bars in Excel 2010 are a definitely different story.

Although I still think backward comparability will definitely be an inhibitor to using the Data Bars in Excel 2010, I would say that the improved functionality makes Data Bars a feature worth using.

• Previous Entry: French Toast Bacon Sandwiches
• Next Entry: Excel 2010 Week – PivotTable SubTotals