Data Bars Options

August 15, 2023 - by Bill Jelen

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.

The Negative Value and Axis Settings dialog. Choose a fill color for negative data bars or choose to use the same color as a positive color. Axis settings control where zero appears:
Automatic
Cell Midpoint
None (Show negatives in the same direction as positive) — Figure 1273. 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.

A comparison of negative data bars with Automatic, Midpoint, or None as the axis. — Figure 1274. Three different ways to show the negative axis.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bernard Hermant on Unsplash