XY-Bar Combination Chart
Recently I've had to create a series of Bar charts that incorporate points that allow additional data labels. I've created these points by building in an XY scatter chart into the Bar chart. The additional data labels are tied to the XY points using a tool such as John Walkenbach's J-Walk Chart Tools or Rob Bovey's XY Chart Labeler.
An example of one of these charts, less data labels, looks like this:
The person who requested the chart wanted each XY point to be visible as well as vertically centered within each bar. To do so, I started by setting the gap width to 100%. Next, a calculation creates the Y values of the XY points as illustrated below.
The calculation starts with the source data for the bar chart which is contained in the range B4:E8. There are four categories and three series within each category. I've entered those numbers into the yellow highlighted cells D19 and D20. The calculation of the lowest Y point on the XY chart vertical axis is contained in cell D23. The calculation is:
=D20/(D20+1)
Once this calculation is done, the results can be applied to all of the Y values of the XY chart. The XY chart source is contained in the range C11:H15. The results of the calculation above are linked to cell D12. Cell D13 adds the series per category value to the results in C12. The formula in D13 is:
=D12+$D$20
Cell F12 adds the series per category value to the results in C12. The formula in F12 is:
=D12+$D$23
Finally, the maximum value of the secondary axis is simply the number of categories multiplied by the series per category. The formula in cell D24 is:
=D19*D20
The VBA procedure below attempts to quickly build this chart based on the template above. Please note that it references the color palette that I currently use - your colors may be different. At this point the output produces the following:
Given time the procedure could be written to complete the formatting. It could also be modified to automatically accept a reasonable number of changing categories and series, use a custom color palette, and allow more efficient range references.
Option Explicit
Sub BuildChart()
Application.ScreenUpdating = False
Dim XVals As Range
Dim Srs1 As String
Dim Srs2 As String
Dim Srs3 As String
Set XVals = ActiveSheet.Range("B5:B8")
Srs1 = ActiveSheet.Range("C4").Value
Srs2 = ActiveSheet.Range("D4").Value
Srs3 = ActiveSheet.Range("E4").Value
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs1
.ChartType = xlBarClustered
.XValues = XVals
.Values = Sheets("Sheet1").Range("C5:C8")
.Interior.ColorIndex = 22
.Border.LineStyle = xlNone
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs2
.ChartType = xlBarClustered
.XValues = XVals
.Values = Sheets("Sheet1").Range("D5:D8")
.Interior.ColorIndex = 35
.Border.LineStyle = xlNone
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs3
.ChartType = xlBarClustered
.XValues = XVals
.Values = Sheets("Sheet1").Range("E5:E8")
.Interior.ColorIndex = 24
.Border.LineStyle = xlNone
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs1 & "_XY"
.ChartType = xlXYScatter
.XValues = Sheets("Sheet1").Range("C12:C15")
.Values = Sheets("Sheet1").Range("D12:D15")
.MarkerSize = 3
.MarkerStyle = xlDiamond
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs2 & "_XY"
.ChartType = xlXYScatter
.XValues = Sheets("Sheet1").Range("E12:E15")
.Values = Sheets("Sheet1").Range("F12:F15")
.MarkerSize = 3
.MarkerStyle = xlDiamond
.MarkerBackgroundColorIndex = 10
.MarkerForegroundColorIndex = 10
End With
With ActiveChart.SeriesCollection.NewSeries
.Name = Srs3 & "_XY"
.ChartType = xlXYScatter
.XValues = Sheets("Sheet1").Range("G12:G15")
.Values = Sheets("Sheet1").Range("H12:H15")
.MarkerSize = 3
.MarkerStyle = xlDiamond
.MarkerBackgroundColorIndex = 5
.MarkerForegroundColorIndex = 5
End With
ActiveChart.ChartGroups(1).GapWidth = 100
ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = ActiveSheet.Range("D25").Value
ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = 0
ActiveChart.Axes(xlCategory, xlSecondary).MajorUnit = 1
ActiveChart.Axes(xlValue).MaximumScale = ActiveSheet.Range("D25").Value
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MajorUnit = 1
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveSheet.Range("D24").Value
ActiveChart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = "#,##0"
ActiveChart.Parent.Height = 189.75
ActiveChart.Parent.Width = 319.5
End Sub
