You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
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.
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).
In Excel 2010, data bars are now drawn proportionally according to their values. Using our example, the following table describes the behavior in detail:
- 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)
- 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)
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
Advantage
- Easy to see text
- Easy to see data bar edges
- 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.
- 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.
Looking at the “profit increase” column, each year can be categorized into two types:
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
- 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.
To Summarize, Excel 2010 allows you to see more trends with your data bars with the following new features:
We would love to hear from you about data bars and what you think about these new features in Excel 2010.
Comments: (11) Collapse
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
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
Comments: (loading) Collapse