excelavon

Excel Macro to Generate a Chart Automatically in VBA

VBA & macros · Updated November 3, 2026

Excel Macro to Generate a Chart Automatically in VBA

Yes. In desktop Excel for Microsoft 365 on Windows, a VBA macro can create a chart from a worksheet range, set its chart type and title, and place it on the sheet. The example below charts categories in column A against values in column B, adding rows automatically as the data grows.

What an Excel chart macro does

An Excel macro is a set of instructions that automates tasks. For a chart, VBA can identify a data range, add a chart object, connect the chart to that range, and apply basic formatting. This is useful when the same chart needs to be made repeatedly, when a report is refreshed each week, or when a workbook is distributed to people who should not have to build a chart manually.

A macro does not make the underlying data correct or decide what the chart ought to communicate. You still choose a sensible chart type and ensure the range includes the intended labels and values. A column chart can compare amounts across categories, for example, but it may not be suitable for showing a continuous trend over time. The code automates construction; it does not replace those choices.

The sample expects a simple table: headers in A1:B1, category labels below in column A, and numeric values in column B. It places the chart on the same worksheet beginning near cell D2. If your workbook uses another layout, change the range and placement references before running it.

Prepare the workbook and data

Put the category names in A1:A and the corresponding numbers in B1:B. Use a descriptive header in each column, such as Month and Sales. Keep the two columns together, with one record per row and no blank rows inside the table. The macro looks down column A to find the final used row, so a gap in that column can make its range stop too soon.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA in the file. A standard .xlsx workbook cannot store VBA project code; Excel may prompt you to save in a macro-enabled format or remove the code. The chart itself can be stored in an ordinary .xlsx file after creation, but the macro will not remain available there.

On Windows, if the Developer tab is not visible, open File > Options > Customize Ribbon and select Developer in the main tabs list. Then open Developer > Visual Basic, choose Insert > Module in the editor, and paste the procedure below into the module.

VBA macro to generate a chart

This version creates a clustered column chart, names it SalesChart, and replaces a chart with that same name if the macro is run again. It leaves other charts on the worksheet alone. Its row detection uses column A, so the first column should be filled for every data record.

Sub CreateSalesChart()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim chartObject As ChartObject

    ' Use the active sheet and find the last category in column A.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "Add a header and at least one data row in columns A and B."
        Exit Sub
    End If

    ' Replace only the chart created by this procedure.
    On Error Resume Next
    ws.ChartObjects("SalesChart").Delete
    On Error GoTo 0

    ' Place the chart beside the table, starting at cell D2.
    Set chartObject = ws.ChartObjects.Add( _
        Left:=ws.Range("D2").Left, _
        Top:=ws.Range("D2").Top, _
        Width:=420, _
        Height:=260)

    chartObject.Name = "SalesChart"

    With chartObject.Chart
        .SetSourceData Source:=ws.Range("A1:B" & lastRow)
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Sales by Month"
        .HasLegend = False
    End With
End Sub

The line using SetSourceData includes the header row, allowing Excel to use the headings as series information. The chart type constant xlColumnClustered specifies side-by-side vertical columns. Because this example has one values column, it hides the legend; if you add multiple data series, a legend can help identify them.

Run the macro and check the result

On Windows, return to Excel and use Developer > Macros, select CreateSalesChart, and choose Run. Alt+F8 opens the macro list; Alt+F11 opens the Visual Basic Editor. If you prefer to run from the editor, click inside the procedure and press F5. These shortcuts can vary with keyboard settings and laptop function-key behavior.

On Mac, desktop Excel supports VBA, though some features and object behavior can differ from Windows. Open the Visual Basic Editor from the Developer tab or Tools > Macro, and use the macro list to run the procedure. Option+F11 (sometimes Fn+Option+F11) commonly opens the editor, depending on the keyboard. Excel for the web can display a workbook containing a chart, but it cannot create or run VBA macros; open the file in desktop Excel to run this code.

After it runs, inspect the chart at D2. Confirm that the category labels and values match the source table, that the chart title is appropriate, and that no data row was omitted. The macro is intentionally tied to the active sheet, so activate the worksheet with the table before running it. For a shared workbook, consider changing ActiveSheet to a specific worksheet reference so a user cannot accidentally run it on the wrong tab.

Adapt the chart to your report

Several settings can be changed without rewriting the whole macro. For example, change the chart title text to match the data, adjust Width and Height to fit the report, or change the cell used for placement. The chart starts at the upper-left corner of D2; it is positioned by worksheet coordinates, not inserted into the cell as cell content.

  • For a horizontal comparison, use the bar chart constant xlBarClustered instead of xlColumnClustered.
  • For a trend over numeric or date values, consider an XY scatter chart, such as xlXYScatter, and check that Excel interprets the horizontal values as intended.
  • For multiple series, include additional value columns in the source range, such as A1:C followed by the last row, and keep a clear heading above each series.
  • To retain a legend for several series, set .HasLegend = True. You can also change .HasLegend to False if Excel displays a legend that adds no useful information.

When editing the range for more than two columns, make sure the macro’s final-row calculation still follows a column that is populated on every record. If column A sometimes contains blanks, use a more reliable key column or an Excel Table as the data source. A table is often helpful for recurring reports because it expands as rows are added, although the sample code deliberately uses a straightforward range for clarity.

Choose a good source range and chart type

Chart quality depends heavily on how the source data is arranged. Put labels in the first row and categories in the first column when using this example. Avoid including totals in the plotted range unless they are meaningful comparisons; a grand total beside individual months, for instance, can overwhelm the scale and distort the visual story. Also check that values are numbers rather than text that only looks numeric.

Use a column or bar chart when readers need to compare a modest set of categories. Use a line chart when the horizontal order represents time and the purpose is to show a trend. Use a scatter chart when both axes represent numeric measurements and the spacing between x-values matters. Pie charts are generally best reserved for a small number of parts that add up to a meaningful whole. Selecting the first available chart type is not necessarily the right choice for the question the data is meant to answer.

If the macro produces a chart with the wrong orientation, inspect the source range and headings first. Excel infers series and categories from the selected data; ambiguous ranges, extra blank columns, or merged headings can lead to surprising results. A clearly structured rectangular table makes both manual charting and VBA charting more predictable.

Common problems and how to fix them

  • No chart appears: Make sure the correct worksheet is active, the macro ran without an error, and the chart is not positioned outside the visible area. The example places it at D2, so horizontal scrolling or narrow columns may affect what you see.
  • The chart stops before the final record: Check for a blank cell in column A. The code finds the last nonblank cell in that column and cannot infer records below a gap.
  • Labels or values are missing: Confirm that the source range begins at A1, includes the intended rows, and has column headings. Check for text-formatted numbers or unexpected empty cells.
  • Running it twice creates duplicates: This sample deletes and recreates only the chart named SalesChart. If you changed that name or ran a different macro, older chart objects may remain and need to be removed or renamed.
  • Excel blocks the macro: The file may be untrusted, downloaded, or stored in a location with restrictive security settings. Do not enable macros in an unfamiliar workbook just to view a chart; ask the sender to confirm what the code does.

When VBA reports an error, note the highlighted line and check the worksheet name, range, and chart name. A common customization mistake is changing the source range but not the last-row logic, or changing the worksheet layout without updating the cell references. Make one change at a time and rerun the macro on a copy so it is easier to identify the cause.

Macro or manual chart: which approach fits?

A macro is worthwhile when the process is repeated, the same formatting is required every time, or the chart must be generated as part of a larger reporting task. Once written, a procedure can save repetitive clicks and reduce variation between reports. It is particularly useful when data rows change from one reporting period to the next, provided the code identifies the range correctly.

For a one-off chart, the ribbon is usually simpler: select the data, open Insert, and choose a recommended or specific chart. A PivotChart may be a better fit for summarizing and filtering a larger dataset. If the main goal is to refresh an existing chart as data changes, an Excel Table can often expand the chart’s source automatically without VBA. These options avoid the need to maintain and distribute macro code.

VBA is also not the same as an Excel formula. A formula calculates a value in a cell; a macro performs actions such as adding or formatting a chart. If you need a repeatable process across many workbooks or a cloud-based workflow, consider whether Office Scripts or another approved automation tool is more suitable. Excel for the web does not execute VBA, so this distinction matters for teams that work entirely in a browser.

Safety and workbook considerations

Save a separate copy of the workbook before running or modifying a macro. VBA actions generally cannot be reversed with Ctrl+Z, and a procedure can change or delete workbook content. This sample removes a chart named SalesChart before creating a replacement; it does not delete the data, but it still changes the workbook. Review code before running it and use macros only from sources you trust.

Do not lower Excel’s macro security settings globally just to make one file run. Follow your organization’s security policy, and use an approved trusted location or signed macro when required by your workplace. Keep a clear note of what the macro changes, particularly if the workbook is shared, and test it on representative data before using it for a recurring report.

Frequently Asked Questions

Can Excel automatically create a chart when data changes?

Yes, but this example creates or recreates the chart when you run the macro; it does not run automatically after every edit. An Excel Table linked to a chart can expand as new rows are added. To run VBA in response to a worksheet event requires additional event code and careful testing.

Can a macro create a chart from a different worksheet?

Yes. Instead of assigning ActiveSheet to ws, assign the worksheet by name, such as ThisWorkbook.Worksheets(“Report”). Use the actual tab name, and make sure the named sheet exists. This makes the destination predictable even if another sheet happens to be active.

Can I use the macro in Excel for the web?

No. Excel for the web can open and display macro-enabled workbooks, but it cannot run VBA. Open the workbook in desktop Excel for Windows or Mac to execute the macro. A browser-based automation need may call for Office Scripts instead.

Why does Excel say macros are disabled?

Excel may block VBA because of security settings, file origin, or workplace policy. Only run code you understand and trust. Ask the workbook’s sender or your administrator for guidance rather than broadly changing macro security controls.

Can I make a chart without VBA?

Yes. Select the data and use Insert to create a chart manually. For expanding data, format the range as a Table first and build the chart from that table. This can be easier to maintain when you do not need to automate chart placement or formatting.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.