# Data Bar Improvements in Excel 2010

Data Bars are a popular type of conditional formatting that was introduced in Excel 2007. The basic idea of data bars is that Excel draws a bar in each cell whose length corresponds to the value of the cell relative to the other cells in the selected range. Data bars are a great way of seeing trends in your data.

In our example, we have the historical Sales and Profit amounts for a fictional bookstore. The last column has the percentage of increase in profits when compared to the previous year. We have applied Excel 2007 data bars to some of these columns. In the example below, we can see that sales rose in early 2000’s and are decrease for the past few years.

However, the data in the above example has several nuances which are hard to spot in Excel 2007. In Excel 2010, we have made several improvements to data bars which make them an even better choice for your data analysis. We shall look at data bar improvements in Excel 2010 and how these improvements help you get more information from your data.

#### Proportional Data Bar Lengths

In our example above, we can see that the sales rose from 1999 to the early 2000’s and have fallen since then for the past few years. While this is good information, Excel 2007 does not provide a good indication of how much the sales have increased or decreased during that period. The sales amount was \$210,374.88 in 1997 and they rose to \$271,884.86 in 2004. Thus, sales in 1997 were about 78% of the sales in 2004.

However if someone would try to compare the lengths of the data bars, they might be fooled into thinking that sales rose by about 5 times during that period or 500% (since the length of the data bar corresponding to year 2004 is about 5 times the length of data bar corresponding to year 1997).

 Why does this happen in Excel 2007? By default, Excel assigns the shortest data bar to the lowest value in the range and the longest data bar to the highest value in the range. All other data bars are assigned a length in between according to their values.

In Excel 2010, data bars are now drawn proportionally according to their values. Using our example, the following table describes the behavior in detail:

 Excel 2007 Excel 2010 Minimum default value - Lowest Value in Range - Occupies 10% of cell width - Automatic (Minimum of zero or lowest negative value in range) - Occupies 0% cell width (not drawn) Maximum default value - Highest Value in Range - Occupies 90% of cell width - Automatic (Maximum of zero or highest positive value in range) - Occupies 100% cell width (entire cell) Key observation - Data bars cannot be used to compare values. - Proportional data bars lets you compare values. Example - Users may incorrectly feel that sales increased 5 times from 1997 to 2004. - The 1997 sales are about 78% of the 2004 sales. Screen shot

#### Formatting Options – Solid Fills and Borders

We received a lot of feedback from users that it was hard to see the ends of your data bars since we used a gradient fill to color data bars. Gradient fill has the advantage that it makes the text values in the cells easier to see. However as in our example above, it is hard to see which cell has the highest sales value (answer: 2004).

Taking the limitations of gradient fill into account - we introduced new formatting options, such as borders and solids fills, for data bars in Excel 2010:

 Formatting Styles Gradient Fill (Excel 2007 & Excel 2010) Gradient Fill with Borders (Excel 2010) Solid Fill (Excel 2010) Advantage - Easy to see text - Easy to see data bar edges - Easy to see text - Prominently see data bars Disadvantage - Hard to see edges of data bars - Borders may overlap some text - Harder to see text (depending on color) Example - Difficult to see which year (2003, 2004 or 2005) has the highest sales amount. - Text is easily visible for each cell. - Easy to see that 2004 has the highest sales. - In year 2001, the edge of the data bar overlaps the last digit “8” making it harder to read. - Easy to see that 2004 has the highest sales. - In year 1998, some portion of the text is on green background and the remainder is on the white background making it harder to read. Screen Shot

Note: Formatting options only affect the visual look of the data bar and does not change the behavior of data bar in any way.

#### Negative Value Data Bars

Looking at the “profit increase” column, each year can be categorized into two types:

• Profit Growth: years where profits increased from the previous year.
• Profit Decline: years where profits fell when compared to the previous year.

However in Excel 2007, data bars do not make any distinction between the positive and negative values making it hard to see the difference.

In Excel 2010, we have introduced negative value data bars which can help analyze trends when negative values are involved. By default, we smartly position the axis in the cell so that a small negative value will not occupy half the cell lengths when bigger positives values are also in the range. If you prefer, we let you position the axis in the center of the cell.

 Excel 2007 Excel 2010 Key Observation - Hard to see different between positive and negative values - Easily highlight negative values Example - It is hard to see here that in years 2006 to 2007 we are seeing substantial drop in profits. - We can easily observe the large drop in profits from 2006 to 2008. Screen Shot

To Summarize, Excel 2010 allows you to see more trends with your data bars with the following new features:

• Proportional data bar lengths
• Negative data bars
• customize formatting of your data bars:
• Choose between Gradient and Solid fill style
• Apply borders to data bars
• Change fill / border colors for both positive and negative value data bars.

We would love to hear from you about data bars and what you think about these new features in Excel 2010.

• Joseph,

The solid fill in Databars is a great improvement.

But Why is the inaccurate and misleading "Gradient Fill" still kept. Just have Solid Fill

• "But Why is the inaccurate and misleading "Gradient Fill" still kept."

Backward compatibility with Excel 2007 would be my guess.

Showing both numbers and bars in the cell is so confusing that it's hard to compare either (bars or numbers) well. Far more clarity can be achieved by displaying each in separate columns. The new changes to data bars are on the money though. I was hoping that in this release, the formula property of data bars would surface in the UI. This would've allowed easy setting of the bar color based on, say, a target threshold. A consideration for Excel 15 perhaps?

• I've already written about the improvements to data bars in <a href="peltiertech.com/.../">Sparklines and Data Bars in Excel 2010</a>.

I think the old style gradients were retained because people have laready (unfortunately) gotten used to them. The argument to make the bars fade at the end, to make the text more legible, is rather lame. If the right colors for bar and text are used, or if the values are shown in one cell and the bars in the next (which makes more sense), there is no such problem.

• This negative value data bars is a very important improvement in Excel. I'm looking forward to use the new version!

• Is ist possible to manually change the xml file to get solid bars also in excel 2007?

• Hey folks, I'll be doing my best to answer questions along with the other posters while Joseph is away.

sam: gradient is there for backwards compat, but also a style thing.  in the more recent builds, we've added a default border so the end of the bar is well defined.

Max: solid data bars in 2007 is not possible.

• it is a exicting improvement of the new Excel verison, also i hope there is a stride in the VBA application

• it is a exicting improvement of the new Excel verison, also i hope there is a stride in the VBA application

• This stuff looks fantastic.  What I would like to see (and maybe this is already possible) is combining data bars with old school (excel 2003) conditional formatting.  What I mean is instead of only being able to split the color of the bar based on positive/negative values, you would be able to set up colors for explicit ranges of values. e.g. 0-10 red, 11-25 yellow, 26-50 green.  Now we're getting somewhere!

If only I could get my company to move past Excel '03!

• I am the author of this blogpost and I will do my best to answer some of the questions raised here.

Andrew: Although there isn't a way in the UI to achieve this, David Gainer wrote a blogpost on how to achieve this using the object model. And, yes... you can do this in Excel 2007.

blogs.msdn.com/.../538875.aspx

• Negative value databars are really great. They convey the meaning aptly, which was not done in XL 2007