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

Candlestick Charts

Microsoft Excel has some built-in chart types for displaying how stock prices vary during a trading period. This is usually a day, but it could also show weeks, months, or years, or even intervals of minutes or hours during a trading day. These are the prices at open and close of the market as well as the high and low while the market is open. For example, the Open-High-Low-Close (OHLC) candlestick chart below was designed to show the high and low for a trading period as the top and bottom of the vertical line for each period, and the change from open to close as a rectangle, filled white for an increasing value or black for a decreasing value.

To make this chart type, you need five columns of data, in order Date (or other label of the trading period), Open, High, Low, and Close. If you omit the dates or labels, Excel will build the chart and label the periods 1, 2, 3, etc. Select the data, then go to the Insert tab > Other Charts, and pick the second stock chart type.

I guess I’m too dumb to remember such a straightforward convention, but I always have to check whether the white or black bars refer to increasing or decreasing values. So I like to color the bars red and green, like so:

The problem is that the colored bars do not photocopy well in black and white. Also, while the red and green colors in Excel’s default theme are distinguishable by most people with color vision deficiencies, I can’t imagine the colors they see are as easy to distinguish as red and green, or as black and white, for that matter.

Stock Charts with Open and Close Tickmarks

The best approach then is to use the OHLC chart style found in newspapers. These have a vertical line to indicate the spread from low to high during a trading period, with a small tickmark to the left indicating the opening price and a small tickmark to the right at the closing price. Excel has a High-Low-Close stock chart with the vertical line and a single tickmark, to the right, to signify closing price. But no leftward tickmark.

So we must resort to some smoke and mirrors. Actually, some short horizontal error bars will serve as our tickmarks.

Start with the data arranged in the same way, but create a line chart instead of a stock chart.

Select any series in the chart, go to the Chart Tools > Layout tab, click on Lines, and choose High-Low Lines.

Format the High and Low series to remove the line color.

Change the Open and Close series to XY: right click on each series in turn, select Change Chart Type, and choose an XY type.

These XY points don’t line up and there are misaligned secondary axes, but this is simple to fix. Format both XY series to assign them to the primary axis.

Format the Open and Close series to hide the markers.

Select the Open and Close series in turn, and add error bars. There are no markers to click on, but if you select the chart you can find the Chart Element selector on the Chart Tools > Layout and Format tabs. The Chart Element selector and the pop-up tool tip are highlighted with a red border in this screen shot. This is a handy tool to add to your arsenal.

Use the Chart Element selector to select the Open and Close series, one at a time. (You could also select a visible series or another visible chart element, and use the up or down arrows to cycle through the chart’s elements until the desired series is selected.)

With the series selected, go to the Chart Tools > Layout tab, select Error Bars, and choose one of the options. I simply used the Standard Error option, since it was easier to add everything and adjust or remove the parts as needed. Repeat for both Open and Close.

Reminds me of a swarm of starfighters from Star Wars.

Select the vertical (Y) error bars for each series. You may want to use the Chart Element selector for this.

Select the vertical (Y) error bars, and remove them by pressing the Delete key.

Select the “Open” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Minus direction and the No Cap end style, and enter a fixed value of 0.4.

Select the “Close” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Plus direction and the No Cap end style, and enter a fixed value of 0.4.

The vertical lines are one unit (one trading period) apart, so 0.4 reaches almost halfway to the next vertical line on either side.

This style of OHLC chart works well in black and white, and after the first time you go through the protocol, it doesn’t take too long to set up.

Excel’s automatic axis scaling often seems somewhat mysterious, and it’s not easy to find information about it. Microsoft has a couple articles in the MSDN knowledge base, How Chart Axis Limits Are Determined and XL2000: How Chart Axis Limits Are Determined, but the most recent of these was directed at Excel 2000. The algorithms described in these articles are unchanged in Excel 2002 and 2003, and seem to be the same in Excel 2007 and 2010 as well.

The Automatic Axis Scaling Rules

I won’t go into all scenarios, but will discuss cases where the minimum and maximum values are both greater than zero. The behavior is the same for Y values in line, column, bar, area, bubble, and XY charts, and for X values in XY and bubble charts (substitute X for Y in this discussion for X axes). Y axes in other chart types (particularly 3-D charts, which you should avoid anyway) may behave slightly differently.

The automatic maximum Y axis scale value is the first major unit above Ymax + (Ymax – Ymin)/20, where Ymax is the maximum Y value, and Ymin is the minimum Y value or the minimum Y axis setting if it has been fixed.

If Ymin is less than 5/6 of the Ymax, the automatic minimum Y axis scale value is zero. If Ymin is 5/6 of Ymax or greater, then the automatic minimum Y axis scale value is the first major unit less than or equal to Ymin – (Ymax – Ymin)/20 (the two MSDN articles have an unfortunate typographical error, showing the divisor equal to 2, not 20).

Read more »

I’ve written often about panel charts, and I’ve made some simple ones, but I don’t seem to have explained the process in enough detail for people to reproduce these simple ones with their own data. Easy Two-Panel Column Chart in Excel was the first of several quick tutorials on easy panel charts. This article shows the instructions for a two-panel line chart.

Let’s use the following simple data for this exercise.

Read more »

I’ve written frequently about panel charts, and I’ve made some simple ones, but I don’t seem to have explained the process in enough detail for people to reproduce them with their own data. This is one of several quick tutorials on easy panel charts.

This article also answers the question, “How do I fix a column chart that has some series on the primary axis and some on the secondary axis?” The problem is that the secondary columns obscure the primary columns. Well, you can offset the columns so the primary ones are no longer obscured, but a far better approach is to offset the series, into separate panels for primary and secondary data.

Read more »

In Grouping by Date in a Pivot Table I showed how to summarize daily data in a pivot table by grouping into monthly values. I’ll review this technique, then show how to clean up the dates when you don’t use the default starting and ending dates in the Grouping dialog.

Here is a pivot table with daily values. I worked this out in Excel 2003, because that’s what was open at the time, but the technique is much the same in all versions. I want to condense this into monthly values, and show only data from 2010 and 2011.

I navigate to the Group and Show Detail > Group command, and choose the appropriate parameters in the Grouping dialog.

Read more »