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

Worksheet Calculation of Axis Scale Parameters

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.

Worksheet Calculation of Axis Scale Parameters with User Overrides

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.

 

Peltier Tech Charts for Excel

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

Screenshot of worksheet that uses axis scaling UDF

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

Charts with automatic and calculated axis scales

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

Format Axis dialog with automatic scale parameters

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

Calculated axis scale parameters entered 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

 

Peltier Tech Charts for Excel

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.

Excel 2003 Log Scale Axis - Data

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

Excel 2003 Linear Axis

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

Excel 2003 Log Scale Axis - Log Data

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.

Excel 2003 Log Scale Axis - Add Points for Labels

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

Excel 2003 Log Scale Axis - Add Labels

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.

Excel 2003 Log Scale Axis - With Labels

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

Excel 2003 Log Scale Axis

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

Data for Excel 2007 Log Scale Axis

Here’s the data in a default XY chart.

Excel 2007 Linear Axis

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

Excel 2007 Log Scale Axis - Base 10

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

Excel 2007 Log Scale Axis - Base 2

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

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0