Macro to Set Sparkline Axis to Zero

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 Sub

The 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.