How to Create a Combo Chart in Excel: Step-by-Step

Key Insights

• Combo charts combine two chart types (typically columns and lines) to visualize metrics with different scales on a single visualization, making them ideal for comparing absolute values against rates or percentages.
• The secondary axis is the critical feature that makes combo charts useful—without it, data series with vastly different magnitudes become unreadable, but improper scaling can create misleading visualizations.
• VBA automation transforms combo chart creation from a repetitive manual task into a one-click operation, especially valuable when generating reports from regularly updated datasets.

Introduction to Combo Charts

Combo charts solve a specific visualization problem: how do you display two related metrics that operate on completely different scales? Imagine plotting monthly revenue (in millions) alongside profit margin percentage (0-100%). On a standard chart, the percentage line would flatline at the bottom, rendering it useless.

A combo chart combines two chart types—most commonly clustered columns with a line overlay—and optionally uses two Y-axes. The left axis handles one metric’s scale; the right axis handles the other. This lets viewers see both the absolute values and the relative trends in a single, coherent view.

Use combo charts when you need to:

• Compare volume metrics (sales, units, headcount) against rate metrics (growth %, margin %, conversion rate)
• Show actual values alongside targets or benchmarks
• Display cumulative totals with period-over-period changes

Avoid combo charts when a single chart type suffices. If your metrics share similar scales, a standard grouped column or multi-line chart provides cleaner visualization without the cognitive overhead of tracking two axes.

Preparing Your Data

Excel combo charts work best with data structured in adjacent columns where each column represents a distinct data series. The first column typically contains your category labels (dates, product names, regions), with subsequent columns holding the values for each metric.

Here’s the structure you need:

Key data preparation rules:

1. Keep categories in the first column. Excel uses this for the X-axis labels.
2. Use consistent units within each column. Don’t mix thousands and millions.
3. Avoid blank cells. Use zero or NA() if data is missing.
4. Name your columns clearly. These become your legend labels.

Store this data in a named range if you plan to automate chart creation. Named ranges make VBA code more readable and resilient to worksheet changes.

Creating a Basic Combo Chart (GUI Method)

The manual approach works well for one-off charts. Here’s the exact sequence:

Step 1: Select your entire data range, including headers. In our example, select A1:C13.

Step 2: Navigate to Insert > Charts > Combo Chart (the icon shows overlapping column and line). Click the dropdown arrow to see options.

Step 3: Select “Create Custom Combo Chart” at the bottom of the dropdown. This opens the chart type dialog with full control.

Step 4: In the dialog, you’ll see each data series listed. For each series:

• Set “Revenue ($)” to Clustered Column
• Set “Growth Rate (%)” to Line with Markers
• Check the “Secondary Axis” box for Growth Rate

Step 5: Click OK. Excel generates the combo chart with dual axes.

The result displays revenue as blue columns scaled on the left axis (0 to ~250,000) and growth rate as an orange line scaled on the right axis (-5% to 15%). Both series now occupy the full chart height, making patterns visible.

Quick alternative: If you select your data and choose “Clustered Column - Line on Secondary Axis” from the combo chart presets, Excel automatically assigns the last data series to the secondary axis with a line chart type. This works for simple two-series scenarios.

Customizing with Secondary Axis

The secondary axis is where combo charts either shine or mislead. The axis scaling directly affects how viewers interpret relationships between your metrics.

When to use a secondary axis:

• Metrics differ by more than one order of magnitude
• Metrics use different units (dollars vs. percentages, units vs. rates)
• You want to highlight correlation or divergence between series

Scaling considerations:

Excel auto-scales both axes independently, which can create visual artifacts. If your growth rate ranges from -3% to 13%, Excel might set the secondary axis from -5 to 15. Meanwhile, revenue ranging from 125,000 to 242,000 gets an axis from 0 to 250,000. The zero-baseline difference can make the line appear to correlate with columns when it doesn’t.

To manually set axis scales, right-click the axis > Format Axis > set Minimum, Maximum, and Major Unit values explicitly.

Here’s VBA to programmatically configure the secondary axis:

Sub ConfigureSecondaryAxis()
    Dim cht As Chart
    Dim ax As Axis
    
    Set cht = ActiveSheet.ChartObjects("SalesComboChart").Chart
    
    ' Configure secondary (right) axis
    Set ax = cht.Axes(xlValue, xlSecondary)
    With ax
        .MinimumScale = -5
        .MaximumScale = 15
        .MajorUnit = 5
        .TickLabels.NumberFormat = "0%"
        .HasTitle = True
        .AxisTitle.Text = "Growth Rate"
    End With
    
    ' Configure primary (left) axis
    Set ax = cht.Axes(xlValue, xlPrimary)
    With ax
        .MinimumScale = 0
        .MaximumScale = 300000
        .MajorUnit = 50000
        .TickLabels.NumberFormat = "$#,##0"
        .HasTitle = True
        .AxisTitle.Text = "Revenue"
    End With
End Sub

Automating Combo Charts with VBA

For recurring reports, VBA automation eliminates manual chart creation. The following macro generates a complete combo chart from a selected data range:

Sub CreateComboChartFromSelection()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim rngData As Range
    Dim seriesCount As Integer
    
    Set ws = ActiveSheet
    Set rngData = Selection
    
    ' Validate selection
    If rngData.Columns.Count < 2 Then
        MsgBox "Select at least two columns of data.", vbExclamation
        Exit Sub
    End If
    
    ' Create chart object
    Set cht = ws.ChartObjects.Add( _
        Left:=rngData.Left + rngData.Width + 20, _
        Top:=rngData.Top, _
        Width:=500, _
        Height:=300)
    
    With cht.Chart
        ' Set data source
        .SetSourceData Source:=rngData
        
        ' Convert to combo chart
        .ChartType = xlColumnClustered
        
        ' Get series count (excluding category column)
        seriesCount = .SeriesCollection.Count
        
        ' Set first series as clustered column (primary axis)
        With .SeriesCollection(1)
            .ChartType = xlColumnClustered
            .AxisGroup = xlPrimary
            .Format.Fill.ForeColor.RGB = RGB(68, 114, 196)
        End With
        
        ' Set remaining series as lines on secondary axis
        Dim i As Integer
        For i = 2 To seriesCount
            With .SeriesCollection(i)
                .ChartType = xlLine
                .AxisGroup = xlSecondary
                .Format.Line.Weight = 2.5
                .MarkerStyle = xlMarkerStyleCircle
                .MarkerSize = 8
            End With
        Next i
        
        ' Add chart title
        .HasTitle = True
        .ChartTitle.Text = "Revenue vs. Growth Rate"
        
        ' Position legend at bottom
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
        
        ' Add data labels to line series
        .SeriesCollection(seriesCount).HasDataLabels = True
        .SeriesCollection(seriesCount).DataLabels.Position = xlLabelPositionAbove
    End With
    
    cht.Name = "SalesComboChart"
End Sub

This macro handles the common scenario where the first data series should be columns and subsequent series should be lines. Adjust the loop logic for different configurations.

For more granular control over chart types, use these constants:

' Common chart type constants for combo charts
' xlColumnClustered = 51 (vertical bars, grouped)
' xlColumnStacked = 52 (vertical bars, stacked)
' xlLine = 4 (line without markers)
' xlLineMarkers = 65 (line with markers)
' xlArea = 1 (filled area chart)
' xlXYScatter = -4169 (scatter plot)

Formatting and Best Practices

Effective combo charts follow consistent visual principles:

Color coding: Use distinct, high-contrast colors for each series. Avoid red/green combinations (colorblind users) and similar hues that blend together. The column series should use a solid, muted color; the line series benefits from a brighter accent color.

Legend placement: Bottom-center works best for combo charts. Side legends consume horizontal space that dual-axis charts need. Ensure legend labels match your column headers exactly.

Axis labels: Always include axis titles that specify units. “Revenue ($)” and “Growth Rate (%)” are clearer than “Revenue” and “Growth.” Format tick labels appropriately—currency for dollars, percentages for rates.

Common pitfalls to avoid:

1. Dual-axis distortion: Manipulating axis scales can make unrelated metrics appear correlated. Always start value axes at zero unless you have a compelling reason not to.

2. Overloading data: Combo charts work best with 2-3 series maximum. Beyond that, consider multiple charts or a dashboard approach.

3. Mismatched time periods: Ensure all series cover the same date range. Gaps create visual discontinuities that confuse viewers.

Exporting and Sharing

Save frequently-used combo chart configurations as templates. Right-click the chart > Save as Template. Templates preserve formatting, colors, and axis configurations for consistent reporting.

For programmatic export, this VBA snippet saves the chart as a PNG file:

Sub ExportChartAsPNG()
    Dim cht As ChartObject
    Dim filePath As String
    
    Set cht = ActiveSheet.ChartObjects("SalesComboChart")
    
    ' Build file path (same folder as workbook)
    filePath = ThisWorkbook.Path & "\ComboChart_" & _
               Format(Now, "yyyymmdd_hhnnss") & ".png"
    
    ' Export at 2x scale for high resolution
    cht.Chart.Export Filename:=filePath, FilterName:="PNG"
    
    MsgBox "Chart exported to: " & filePath, vbInformation
End Sub

For PDF export of the entire sheet including the chart, use ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF. For embedding in PowerPoint or Word, copy the chart object and paste-special as an enhanced metafile for scalable vector quality.

Combo charts remain one of Excel’s most practical visualization tools for business reporting. Master the secondary axis configuration and automate repetitive creation tasks, and you’ll produce clearer, more insightful reports with less effort.