Home > Forum Home > Presenting and Reporting Data > Automatically fitting chart axis scale Share

Automatically fitting chart axis scale

Excel Help for Automatically Fitting Chart Axis Scale in Presenting and Reporting Data

Forum TopicPost Reply Login

Automatically Fitting Chart Axis Scale

Rate this:
(4/5 from 2 votes)
Sad Often the built-in Excel fitting of chart axis minimum and maximum provides a larger range than required for the underlying data. Options exist to calculate the minimum and maximum data values in cells for subsequent use on chart axis parameters.  This VBA function analyzes the data for all data series in a given chart and then trims the minimum and maximum axis points of the vertical axis based on the major unit amount defined for the chart.

VBA Code:
Sub Trim_Vertical_Axis(chtname As String)
'chtname is name of the chart in the active sheet
Dim sernum As Long
Dim chtdata As Variant
Dim munit As Double
Dim vmin As Double
Dim vmax As Double
Dim nmin As Double
Dim nmax As Double
Dim i As Long
Dim snum As Long

For snum = 1 To ActiveChart.SeriesCollection.Count
    chtdata = ActiveChart.SeriesCollection(snum).Values
    For i = 1 To UBound(chtdata)
        If i = 1 And snum = 1 Then
            vmax = chtdata(i)
            vmin = chtdata(i)
        End If
        If chtdata(i) > vmax Then
            vmax = chtdata(i)
        End If
        If chtdata(i) < vmin Then
            vmin = chtdata(i)
        End If
    Next i
Next snum

munit = ActiveChart.Axes(xlValue).MajorUnit

nmax = Round(vmax / munit) * munit
If nmax < vmax Then
    nmax = nmax + munit
End If
nmin = Round(vmin / munit) * munit
If nmin > vmin Then
    nmin = nmin - munit
End If

ActiveChart.Axes(xlValue).MaximumScale = nmax
ActiveChart.Axes(xlValue).MinimumScale = nmin

End Sub

The same logic could also be applied to horizontal axis trimming based on data dynamics.
 Excel Business Forums Administrator
 Posted by on
There are currently no replies to the "Automatically fitting chart axis scale" topic of the Excel Help Forum for Presenting and Reporting Data.

Post Reply

Find relevant Excel templates and add-ins for Automatically fitting chart axis scale in the