Auto-Generating Charts

When you only see tables full of numbers, trends aren't clear, right? Whether sales went up or down, which products sell well... You need charts to see these at a glance.

But creating charts can be cumbersome too. You worry about which chart type to use, select data ranges, change colors, add labels... It requires a lot of effort.

With AI, you can automate chart creation as well. Just say "create a chart with this data," and AI recommends the appropriate chart type and even creates VBA code. Charts are created with just one button click.

In this post, we'll learn how to auto-generate charts with AI. From which charts suit which situations to automatically generating them with VBA, we'll explore practical examples.

1. Chart Type Selection Guide

AI Prompt

"Recommend the most suitable chart type for these situations:
1. Monthly sales trend analysis
2. Product sales proportion
3. Regional sales comparison
4. Hourly customer visit patterns
5. Performance vs target"

AI Recommendation Results

1. Monthly sales trend → Line Chart
   - Clearly shows changes over time

2. Product sales proportion → Pie Chart
   - Shows percentage of whole

3. Regional sales comparison → Column Chart
   - Effective for comparing sizes between items

4. Hourly visit patterns → Area Chart
   - Shows density by time period

5. Performance vs target → Combo Chart
   - Column (performance) + Line (target)

2. Auto-Generating Charts with VBA

Monthly Sales Trend Chart

AI Prompt:

"Column A has months (January, February...), column B has sales amounts.
Create VBA code to auto-generate a line chart with this data.
Set chart title to '2025 Monthly Sales Trend' and
display data labels as well."

AI Generated VBA:

Sub CreateMonthlySalesChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("SalesData")

    ' Find last row
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Delete existing chart (if any)
    For Each chartObj In ws.ChartObjects
        If chartObj.Name = "SalesTrendChart" Then
            chartObj.Delete
        End If
    Next

    ' Create new chart
    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("D2").Left, _
        Top:=ws.Range("D2").Top, _
        Width:=500, _
        Height:=300)

    With chartObj.Chart
        ' Set chart type
        .ChartType = xlLineMarkers

        ' Set data range
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)

        ' Chart title
        .HasTitle = True
        .ChartTitle.Text = "2025 Monthly Sales Trend"
        .ChartTitle.Font.Size = 14
        .ChartTitle.Font.Bold = True

        ' Axis titles
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Month"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Sales Amount (Won)"

        ' Display data labels
        .SeriesCollection(1).HasDataLabels = True
        .SeriesCollection(1).DataLabels.NumberFormat = "#,##0"

        ' Display legend
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom

        ' Gridline settings
        .Axes(xlValue).HasMajorGridlines = True
        .Axes(xlValue).HasMinorGridlines = False
    End With

    chartObj.Name = "SalesTrendChart"

    MsgBox "Chart has been created.", vbInformation
End Sub

3. Product Sales Proportion Pie Chart

AI Prompt:

"Create a pie chart with product names and sales amounts,
displaying product name and percentage on each slice.
Show only top 5 and group the rest as 'Others'."

AI Generated VBA:

Sub CreateProductPieChart()
    Dim ws As Worksheet
    Dim chartWs As Worksheet
    Dim chartObj As ChartObject
    Dim dataRange As Range
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("ProductData")

    ' Sort data (descending by sales)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:B" & lastRow).Sort _
        Key1:=ws.Range("B1"), _
        Order1:=xlDescending, _
        Header:=xlYes

    ' Handle top 5 + others
    If lastRow > 6 Then
        Dim etcSum As Double
        etcSum = Application.WorksheetFunction.Sum(ws.Range("B7:B" & lastRow))
        ws.Range("A7").Value = "Others"
        ws.Range("B7").Value = etcSum
        ws.Range("A8:B" & lastRow).Delete Shift:=xlUp
        lastRow = 7
    End If

    ' Create chart
    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("D2").Left, _
        Top:=ws.Range("D2").Top, _
        Width:=400, _
        Height:=400)

    With chartObj.Chart
        .ChartType = xlPie
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)

        ' Chart title
        .HasTitle = True
        .ChartTitle.Text = "Product Sales Proportion"

        ' Data labels (product name + percentage)
        .SeriesCollection(1).HasDataLabels = True
        With .SeriesCollection(1).DataLabels
            .ShowCategoryName = True
            .ShowPercentage = True
            .ShowValue = False
            .Separator = vbNewLine
            .Position = xlLabelPositionBestFit
            .Font.Size = 10
        End With

        ' Hide legend (already shown in labels)
        .HasLegend = False

        ' Color settings
        For i = 1 To .SeriesCollection(1).Points.Count
            .SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = _
                Choose(i, RGB(79, 129, 189), RGB(192, 80, 77), _
                         RGB(155, 187, 89), RGB(128, 100, 162), _
                         RGB(75, 172, 198), RGB(247, 150, 70))
        Next i
    End With

    MsgBox "Pie chart has been created.", vbInformation
End Sub

4. Regional Comparison Column Chart

AI Prompt:

"Create a column chart for regional sales,
display target line as dotted line.
Color bars blue if target met, red if not."

AI Generated VBA:

Sub CreateRegionalComparisonChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim lastRow As Long
    Dim i As Long
    Dim targetValue As Double

    Set ws = ThisWorkbook.Sheets("RegionalData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    targetValue = ws.Range("D1").Value ' Target value

    ' Create chart
    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("E2").Left, _
        Top:=ws.Range("E2").Top, _
        Width:=500, _
        Height:=350)

    With chartObj.Chart
        .ChartType = xlColumnClustered
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)

        ' Chart title
        .HasTitle = True
        .ChartTitle.Text = "Regional Sales Performance"

        ' Axis titles
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Region"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Sales Amount (Million Won)"

        ' Data labels
        .SeriesCollection(1).HasDataLabels = True
        .SeriesCollection(1).DataLabels.NumberFormat = "#,##0"

        ' Set bar color (by target achievement)
        For i = 1 To .SeriesCollection(1).Points.Count
            If ws.Cells(i + 1, 2).Value >= targetValue Then
                ' Target met - Blue
                .SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(79, 129, 189)
            Else
                ' Target not met - Red
                .SeriesCollection(1).Points(i).Format.Fill.ForeColor.RGB = RGB(192, 80, 77)
            End If
        Next i

        ' Add target line
        With .SeriesCollection.NewSeries
            .Name = "Target"
            .ChartType = xlLine
            .Values = "=" & ws.Name & "!$D$1"
            .AxisGroup = xlPrimary
            .Format.Line.DashStyle = msoLineDash
            .Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            .Format.Line.Weight = 2
        End With

        ' Legend
        .HasLegend = True
        .Legend.Position = xlLegendPositionTop
    End With

    MsgBox "Regional comparison chart has been created.", vbInformation
End Sub

5. Combo Chart (Performance + Target)

AI Prompt:

"Create a combo chart showing monthly performance as columns and target as a line.
Also display achievement rate on secondary axis."

AI Generated VBA:

Sub CreatePerformanceTargetComboChart()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("MonthlyPerformance")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Calculate achievement rate (Column C)
    Dim i As Long
    For i = 2 To lastRow
        ws.Cells(i, 4).Formula = "=B" & i & "/C" & i & "*100"
    Next i

    ' Create chart
    Set chartObj = ws.ChartObjects.Add( _
        Left:=ws.Range("F2").Left, _
        Top:=ws.Range("F2").Top, _
        Width:=600, _
        Height:=400)

    With chartObj.Chart
        ' Set data range
        .SetSourceData Source:=ws.Range("A1:D" & lastRow)

        ' Chart title
        .HasTitle = True
        .ChartTitle.Text = "Monthly Performance vs Target"
        .ChartTitle.Font.Size = 14

        ' Series 1: Performance (Column)
        .SeriesCollection(1).ChartType = xlColumnClustered
        .SeriesCollection(1).Name = "Performance"
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(79, 129, 189)

        ' Series 2: Target (Line)
        .SeriesCollection(2).ChartType = xlLine
        .SeriesCollection(2).Name = "Target"
        .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(192, 80, 77)
        .SeriesCollection(2).Format.Line.Weight = 3
        .SeriesCollection(2).MarkerStyle = xlMarkerStyleCircle

        ' Series 3: Achievement rate (Secondary axis line)
        .SeriesCollection(3).ChartType = xlLine
        .SeriesCollection(3).Name = "Achievement Rate(%)"
        .SeriesCollection(3).AxisGroup = xlSecondary
        .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(155, 187, 89)
        .SeriesCollection(3).Format.Line.Weight = 2

        ' Primary axis settings
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Amount (Million Won)"

        ' Secondary axis settings
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Text = "Achievement Rate (%)"
        .Axes(xlValue, xlSecondary).MinimumScale = 0
        .Axes(xlValue, xlSecondary).MaximumScale = 150

        ' Legend
        .HasLegend = True
        .Legend.Position = xlLegendPositionBottom
    End With

    MsgBox "Combo chart has been created.", vbInformation
End Sub

Conclusion

Auto-generating charts with AI:

• ✅ Automatic selection of appropriate chart types
• ✅ Automate repetitive tasks with VBA
• ✅ Real-time updates with dynamic charts
• ✅ Batch creation of dashboards

In the next post, we'll learn how to find and clean duplicate data.