## Calculate Nice Axis Scales in Your Excel Worksheet

I recently described how How Excel Calculates Automatic Chart Axis Limits. The problem with these automatic limits is that they often leave a little too much white space around the outside of the plotted data, especially between the lowest data point and zero. So in Calculate Nice Axis Scales in Excel VBA I presented code that takes high and low series values and computes “nice” axis scaling parameters. This code could be called from other VBA procedures, or as a user defined function from the worksheet.

What if you want to get your axis scale parameters in the worksheet, but for some reason want to avoid using VBA? In this tutorial I show how to use boring old worksheet formulas to do just that.

## Calculate Axis Scales in the Worksheet

The following table shows how to set up worksheet calculations of your axis limits. The minimum and maximum of your data are entered into the blue cells B4:B5 (labeled Min and Max), either as constants or as calculated values. Below these values are some calculations.

B6 and B7 (Min’ and Max’) are adjustments to min and max, adding 1% of the difference between the data max and min to the max, and subtracting this amount from the min. If the values are zero or closer to zero than 1% of the difference, then zero is used. This prevents any values except for zero from being located on the edge of the plot area of the chart. (The formulas shown in column C are used in the adjacent cells in column B.)

B8 and B9 determine what the axis tick spacing (called “major unit” by Excel) should be. If this major unit were written in exponential notation, Factor is “like” the pre-exponential coefficient and Power is “like” the power of ten.

The axis tick spacing (Xmajor) is computed in B3, where Factor is used in the lookup table in A11:B15, and multiplied by a function of Power to determine a nicer tick spacing. The tick spacings you calculate may not be exactly right, do to different chart and font sizes or other factors. You can experiment with the values in the lookup table to try to improve them.

Xmin in B1 is calculated as the largest multiple of Xmajor which is less than Min’, and Xmax in B2 is the smallest multiple of Xmajor which is greater than Max’.

Use the values in the red cells to adjust the chart axis manually.

## Calculate Axis Scales Allowing User to Override Values

There are cases where you may want to allow the user to lock in one or more of the axis scale parameters. For example, if your data is for the value axis of a bar chart, you could override the calculated minimum to ensure that the axis scale starts at zero, regardless of the data minimum.

This greatly complicates everything, notably the formulas appearing in column C. The data min and max are in B7:B7 in the modified table below, while the user may enter preferred values into any of the cells B4:B6. Valid entries will be used in B1:B3, invalid entries or blanks will result in values being calculated.

The Min’ and Max’ modifications to the data Min and Max use the override min and max values if they are valid, otherwise use the same algorithm as in the previous case.

Power and Factor are calculated the same way as before.

The logic of the new calculations are as follows:

Xmajor: if there is a valid override entry for tick spacing, use it, otherwise, calculate it as before from Factor and Power.

Xmin: if there is a valid override entry for axis minimum, use it; otherwise, if there is a valid override entry for axis maximum, start counting down from the override maximum in increments of Xmajor, and use the largest value which is less than Min’; otherwise calculate as before.

Xmax: if there is a valid override entry for axis maximum, use it; otherwise, if there is a valid override entry for axis minimum, start counting up from the override minimum in increments of Xmajor, and use the smallest value which is greater than Max’; otherwise calculate as before.

This example with user overrides may be overkill. But I occasionally find it very useful. This post is my way of saving it to the cloud, so I can find it later in a search engine.

## Calculate Nice Axis Scales in Excel VBA

In a recent post I described how How Excel Calculates Automatic Chart Axis Limits. The problem with these automatic limits is that they often leave a little too much white space around the outside of the plotted data, especially between the lowest data point and zero. But it’s tedious to guess at your own axis scale, and it would be nice to calculate your own limits in your latest and greatest VBA charting code.

This article presents code that takes high and low series values and computes “nice” axis scaling parameters. The code can be called by other VBA procedures, or it can be used in the worksheet as a user defined function (UDF).

## The VBA Code

I started with code from a November 2001  newsgroup post by Stephen Bullen. I modified it slightly, and corrected a minor algebraic error.

The following is an entire module, including a user defined variable type (scaleAxisScale) to contain the axis scaling parameters, the main function procedure that does all of the work (fnAxisScale), and a small function procedure that is the UDF interface with the worksheet (udfAxisScale).

``````Option Explicit

Public Type scaleAxisScale
' Calculated Axis Scale Parameters
dMin As Double
dMax As Double
dMajor As Double
dMinor As Double
End Type

Function fnAxisScale(ByVal dMin As Double, ByVal dMax As Double) As scaleAxisScale
' Calculates tidy settings for the chart axes
Dim dPower As Double, dScale As Double, dSmall As Double, dTemp As Double

'Check if the max and min are the same
If dMax = dMin Then
dTemp = dMax
dMax = dMax * 1.01
dMin = dMin * 0.99
End If

'Check if dMax is bigger than dMin - swap them if not
If dMax < dMin Then
dTemp = dMax
dMax = dMin
dMin = dTemp
End If

'Make dMax a little bigger and dMin a little smaller (by 1% of their difference)
If dMax > 0 Then
dMax = dMax + (dMax - dMin) * 0.01
ElseIf dMax < 0 Then
dMax = WorksheetFunction.Min(dMax + (dMax - dMin) * 0.01, 0)
Else
dMax = 0
End If
If dMin > 0 Then
dMin = WorksheetFunction.Max(dMin - (dMax - dMin) * 0.01, 0)
ElseIf dMin < 0 Then
dMin = dMin - (dMax - dMin) * 0.01
Else
dMin = 0
End If

'What if they are both 0?
If (dMax = 0) And (dMin = 0) Then dMax = 1

'This bit rounds the maximum and minimum values to reasonable values
'to chart.  If not done, the axis numbers will look very silly
'Find the range of values covered
dPower = Log(dMax - dMin) / Log(10)
dScale = 10 ^ (dPower - Int(dPower))

'Find the scaling factor
Select Case dScale
Case 0 To 2.5
dScale = 0.2
dSmall = 0.05
Case 2.5 To 5
dScale = 0.5
dSmall = 0.1
Case 5 To 7.5
dScale = 1
dSmall = 0.2
Case Else
dScale = 2
dSmall = 0.5
End Select

'Calculate the scaling factor (major & minor unit)
dScale = dScale * 10 ^ Int(dPower)
dSmall = dSmall * 10 ^ Int(dPower)

'Round the axis values to the nearest scaling factor
fnAxisScale.dMin = dScale * Int(dMin / dScale)
fnAxisScale.dMax = dScale * (Int(dMax / dScale) + 1)
fnAxisScale.dMajor = dScale
fnAxisScale.dMinor = dSmall

End Function

Public Function udfAxisScale(ByVal dMin As Double, ByVal dMax As Double) As Variant
' Worksheet interface to fnAxisScale
' Returns a horizontal array to the worksheet
Dim scaleMyScale As scaleAxisScale
Dim scaleOutput As Variant

scaleMyScale = fnAxisScale(dMin, dMax)

ReDim scaleOutput(1 To 4)
scaleOutput(1) = scaleMyScale.dMin
scaleOutput(2) = scaleMyScale.dMax
scaleOutput(3) = scaleMyScale.dMajor
scaleOutput(4) = scaleMyScale.dMinor

udfAxisScale = scaleOutput
End Function``````

## Using as a User Defined Function (UDF)

The procedure returns a four-element horizontal array. In its simplest implementation, select a horizontal range of four cells (or three cells if you don’t care about the minor unit), type the following formula into the formula bar (min and max can be cell references, constants, or formulas), and hold Ctrl+Shift while pressing Enter to create an array formula. Don’t type the curly brackets; Excel does that if the array formula is valid.

``{=udfAxisScale(min,max)}``

To enter the axis scale parameters into a vertical range of cells, select the range of three or four cells, enter the following formula (without the curly braces) into the formula bar, and hold Ctrl+Shift while pressing Enter to create an array formula.

``{=TRANSPOSE(udfAxisScale(min,max))}``

The following screen shot shows this in action. The min and max for an unseen axis are entered into B3 and B4. A three member horizontal array of axis min, axis max, and major unit is entered in B8:D8 using the formula shown in E8. A four member array including minor unit is entered in B12:E12 using the formula shown in F12 (it’s the same formula used in the three member array). A three member vertical array of axis min, axis max, and major unit is entered in C15:C17 using the formula shown in D15. A four member array including minor unit is entered in C20:C23 using the formula shown in D20 (it’s the same formula used in the three member array).

The following further illustrates the UDF. The cells in the upper three outlined rows contain X values (0 and 5 in the first column) and Y values (4 and 5 in the second column), which are plotted in both charts. The cells in the lower three outlined rows contain calculated values for minimum, maximum, and major unit for the X axis (first column) and Y axis (second column).

The axis parameters are manually changed from the left chart’s automatic values. . .

to the right chart’s calculated values, by entering the new values into the Format Axis dialog.

## Call from Other VBA Procedures

### Procedure

The following procedure takes an XY chart, finds the min and max of all series plotted in the chart, passes these to fnAxisScale above, then applies the calculated scale parameters to the chart. If the chart is not an XY chart, the X axis cannot be rescaled in this way, so the code must be modified.

``````Sub ScaleChartAxes(cht As Chart)
Dim AxisScaleX As scaleAxisScale
Dim AxisScaleY As scaleAxisScale
Dim dXMin As Double, dXMax As Double, dYMin As Double, dYMax As Double
Dim vXValues As Variant, vYValues As Variant
Dim iSeries As Long, iPoint As Long
Dim srs As Series

With cht
' loop through all series and all points to find X and Y min and max
For iSeries = 1 To .SeriesCollection.Count
Set srs = .SeriesCollection(iSeries)
vXValues = srs.XValues
vYValues = srs.Values

If iSeries = 1 Then
dXMin = vXValues(1)
dXMax = vXValues(1)
dYMin = vYValues(1)
dYMax = vYValues(1)
End If

For iPoint = 1 To srs.Points.Count
If dXMin > vXValues(iPoint) Then dXMin = vXValues(iPoint)
If dXMax < vXValues(iPoint) Then dXMax = vXValues(iPoint)
If dYMin > vYValues(iPoint) Then dYMin = vYValues(iPoint)
If dYMax < vYValues(iPoint) Then dYMax = vYValues(iPoint)
Next
Next

' compute X and Y axis scales
AxisScaleX = fnAxisScale(dXMin, dXMax)
AxisScaleY = fnAxisScale(dYMin, dYMax)

' apply X axis scale
With .Axes(xlCategory)
If .MinimumScale > AxisScaleX.dMax Then
.MaximumScale = AxisScaleX.dMax
.MinimumScale = AxisScaleX.dMin
Else
.MinimumScale = AxisScaleX.dMin
.MaximumScale = AxisScaleX.dMax
End If
.MajorUnit = AxisScaleX.dMajor
End With

' apply Y axis scale
With .Axes(xlValue)
If .MinimumScale > AxisScaleY.dMax Then
.MaximumScale = AxisScaleY.dMax
.MinimumScale = AxisScaleY.dMin
Else
.MinimumScale = AxisScaleY.dMin
.MaximumScale = AxisScaleY.dMax
End If
.MajorUnit = AxisScaleY.dMajor
End With

End With
End Sub``````

### Entry Points

The ScaleChartAxes procedure above can be called in several ways by the following procedures. Each one is run by the user, and passes one or more charts into ScaleChartAxes for rescaling.

This procedure rescales the active chart.

``````Sub ScaleActiveChartAxes()
If Not ActiveChart Is Nothing Then
ScaleChartAxes ActiveChart
End If
End Sub``````

This procedure rescales all charts on the active sheet.

``````Sub ScaleActiveSheetCharts()
Dim cht As ChartObject

Application.ScreenUpdating = False
For Each cht In ActiveSheet.ChartObjects
ScaleChartAxes cht.Chart
Next
Application.ScreenUpdating = True
End Sub``````

This procedure rescales all charts that have been selected (using Shift or Ctrl to select multiple charts).

``````Sub ScaleSelectedCharts()
Dim obj As Object

Application.ScreenUpdating = False
If Not ActiveChart Is Nothing Then
ScaleChartAxes ActiveChart
End If
On Error Resume Next
For Each obj In Selection
If TypeName(obj) = "ChartObject" Then
ScaleChartAxes obj.Chart
End If
Next
On Error GoTo 0
Application.ScreenUpdating = True
End Sub``````

## Custom Axis, Y = 1, 2, 4, 8, 16

I was recently asked whether Excel can make an axis in which the value of each label was double the previous value, but the labels were equally spaced. I’ve covered custom axis scales several times (see Custom Axis Scales using Dummy Series), and this is simply another example.

The specific question was about labels 1, 2, 4, 8, etc. This kind of progression can be linearized by a logarithmic transformation; since the specific labels are powers of 2, base 2 logarithms are an exact fit.

The trick with a fake axis is that the data values are subjected to the same transformation as the axis label values, so the data lines up with the axis. The original axis label data is used for the labels themselves.

Excel 2003

We’ll start with this sample data. B2:C9 is the original data. D2:D9 has the base 2 logarithms of C2:C9.

Data for the custom axis labels is in B11:D18. Column B has zeros, for the X position of the axis points. Column C has Y values selected for the Y axis labels. Column D has the base 2 logs of these values, for positioning of the axis labels.

Here’s what the original data in column C looks like The Y position is proportional to the Y value.

This is the transformed Y data in column D. The Y position in the chart is proportional to the logarithm of the Y value.

We can’t leave those Y axis labels there. People can’t readily convert 0, 1, 2, etc. into 1, 2, 4 in their heads. Most people, anyway. Select B11:B18, hold Ctrl, and select D11:D18, so that both areas are selected. Copy, select the chart, and use Paste Special to add the data as a new series, with categories in the first column and series names in the first row.

Add the labels to the new series. I’ve used Rob Bovey’s Chart Labeler, which is one of the best free Excel utilities on the internet. The range C12:C18 contains the labels, and they’re added to the left of the added data points. I’ve colored the labels to match the points.>

Format the real Y axis to hide the labels, but in this case we can keep the axis line and tick marks. Adjust the width of the plot area to make room for the labels.

Hide the added series by removing lines and markers, and the chart is done.

Excel 2007

Excel 2007 makes the chart much easier, because Microsoft introduced the ability to use a logarithmic axis scale with any base. All we need is the original data. (In general the protocol would be the same for both versions, but the log transformation makes this case different.)

Here’s the data in a default XY chart.

Here’s the data with the default base-10 log axis scale.

Here is the chart with a base 2 log axis scale.

This more flexible log axis is one of the rare examples of Excel 2007 charting actually improving on Excel 2003’s charting.