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.

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

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.

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.