When creating sparkline charts in Excel one of the problems is that the vertical axis doesn’t always start at zero. It is a manual process to reset the vertical access to zero and a macro can make it much quicker and easier.
In the image below you can see a set of sparkline charts in column I.
You may notice that some of the charts show a very small column for the lower values. This is because the vertical axis is not set to zero.
To reset the vertical axis to 0 takes 3 clicks.
With the sparklines selected in the Sparkline tab you need to click the Axis drop-down.
Then you need to click on the Custom Value option.
Then click OK.
In general the axis for all column charts should always start at zero.
In some cases, you may not start the axis at zero for line charts.
If you use sparkline charts a lot these extra steps are frustrating.
The macro that resets the axis of the selected sparkline charts to zero is shown below.
Sub Reset_Sparkline_Axis()
'Set axis of selected sparklines to zero
On Error Resume Next
With Selection.SparklineGroups.Item(1).Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.CustomMinScaleValue = 0
End With
End SubThe code is based on a recorded macro. I added the With and End With to shorten the code.
I also added the On Error Resume Next statement to handle the situation when the range selected does not include sparkline charts, or if you don’t have a range or cell selected.