• Excel Book Excerpt

Excel Data Bars Options in Excel 2010

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Data Bars Options in Excel 2010

Problem: Microsoft took a lot of heat in Excel 2007 with the data bars. The smallest value in the range would get a tiny amount of color. This happened even when the cell contained zero. Data visualization experts howled that including any color for zero was completely wrong.

Strategy: In Excel 2010, a value of zero can have no color.

In the process of changing the logic to all zero values, a new setting is available in the Manage Rules dialog for data bars. You can control if the minimum value is automatic, or the smallest value.

LE10001147.jpg

Figure 1115 In Excel 2010, zero values can get no data bar.

If you choose the smallest value, then the smallest value will get no color, as you can see for the 5’s on the right side of this figure.

LE10001148.jpg

Figure 1116 Control which value gets no color.

Also, new in Excel 2010, Excel will show negative data bars. Click the Negative Value and Axis button in the Edit Formatting Rule dialog to access the dialog shown below.

LE10001149.jpg

Figure 1117 Change the color or the axis setting.

The axis settings offer three settings, two of which make sense to me. The values in the figure below go from -20 to +30. The Automatic setting will show the axis about 40% of the way across the cell. The Midpoint setting will put the axis in the middle of the cell. The None setting is the one that is bizarre to me. The smallest value, -17 gets no color. Everything from -16 to -1 will get some red color heading in a positive direction. Values from 0 to 30 will get green color.

LE10001150.jpg

Figure 1118 Three different ways to show the negative axis.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: