Excel: Data Bars Options

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


Cells that contain 0 will get no data bar.

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.

  1. Change the color or the axis setting.

    The axis settings offer three settings. 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 seems strange. 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.

  2. Three different ways to show the negative axis.