Start the Vertical Scale at Zero on Trending Charts for Excel Reports
One of the most common concepts used in Excel dashboards and reports is the concept of trending. The vertical axis on trending charts should almost always start at zero. The reason it’s almost always is because you may have trending data that contains negative values or fractions. In those situations, it’s generally best to keep Excel’s default scaling.
However, if you have only non-negative integers, ensure that your vertical axis starts at zero.
This is because the vertical scale of a chart can have a significant impact on the representation of a trend. For instance, compare the two charts shown in this figure. Both charts contain the same data. The only difference is that in the top chart, nothing was done to fix the vertical scale assigned by Excel (it starts at 96), but in the bottom chart, the scale starts at zero.
Now, you may think the top chart is more accurate because it shows the ups and downs of the trend. However, if you look at the numbers closely, you see that the units represented went from 100 to 107 in 12 months. That’s not exactly a material change, and it certainly doesn’t warrant such a dramatic chart.
In truth, the trend is relatively flat, yet the top chart makes it look as though the trend is way up.
The bottom chart more accurately reflects the true nature of the trend. You can achieve this effect by locking the Minimum value on the vertical axis to zero.
To adjust the scale of your vertical axis, follow these simple steps:
Right-click the vertical axis and choose Format Axis.
The Format Axis dialog box appears, as shown in the figure.
In the Format Axis dialog box, expand the Axis Options section and set the Minimum value to 0.
(Optional) Set the Major Unit value to twice the Maximum value in your data.
Setting this value ensures that your trend line gets placed in the middle of your chart.
Click Close to apply your changes.
Many would argue that the bottom chart shown in the figure hides the small-scale trending that may be important. That is, a seven-unit difference may be very significant in some businesses.
Well, if that’s true, why use a chart at all? If each unit has such an impact on the analysis, why use a broad-sweep representation like a chart? A table with conditional formatting would do a better job at highlighting small-scale changes than any chart ever could.