Dual-Series Clustered Bar Chart

A standard in the Healthcare industry is a dual-series clustered bar chart that compares a series of key statistics on a month and year-to-date basis. In Excel, the data source for this chart would be set up similar to below.

Unfortunately it can be a frustrating experience if you use the Chart Wizard to set this chart up for the first time. As can be seen in the image below the category axis labels, bars, and legend data all appear to be backwards.

The chart can be cleaned up in just a few easy steps. The frustrating part is that if you're not very familiar with Excel, figuring out these steps can take a lot of time. The steps are:

• Category Axis - Format Axis - Patterns - Tick Mark Labels = Low
• Category Axis - Format Axis - Scale = Categories in Reverse Order
• Value Axis - Format Axis - Patterns - Tick Mark Labels = None

A simple macro will accomplish the same thing:

Sub SetUpChart()

    Application.ScreenUpdating = False
    
    With ActiveChart.Axes(xlCategory)
        .ReversePlotOrder = True
        .TickLabelPosition = xlLow
        .MajorTickMark = xlNone
    End With

    With ActiveChart.Axes(xlValue)
        .MinimumScale = -0.2
        .MaximumScale = 0.2
        .MajorTickMark = xlNone
        .TickLabelPosition = xlNone
        .Border.LineStyle = xlNone
    End With

    ActiveChart.Axes(xlValue).HasMajorGridlines = False
    ActiveChart.Axes(xlValue).HasMinorGridlines = False
    
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.SeriesCollection(2).ApplyDataLabels

    ActiveChart.PlotArea.Border.LineStyle = xlNone
    ActiveChart.PlotArea.Interior.ColorIndex = xlNone
    
    ActiveChart.Legend.Position = xlBottom

    ActiveChart.Deselect

End Sub

The final chart looks like the one below. The Y-axis labels now flow from the top down, the monthly bars appear on top of the YTD bars, and the month appears before YTD in the legend. At this point only the formatting needs further work.