TL;DR — A chart does not store data — it stores a reference to a Range. Each series'
.Valuesis a formula like=Sheet1!$B$2:$B$13, so the chart is only ever as correct as that reference. Create an embedded chart withws.ChartObjects.Add(left, top, width, height), get its.Chart, set.ChartType, and bind data in one shot with.SetSourceData Source:=Range(...). The two things that bite: embedded charts live inChartObjects(notCharts, which are chart sheets), and re-running the macro stacks duplicate charts unless you delete the old ones first.
Dim co As ChartObject, ch As Chart
Set co = Worksheets("Report").ChartObjects.Add( _
Left:=300, Top:=20, Width:=420, Height:=260)
Set ch = co.Chart ' the ChartObject is the frame; .Chart is the chart
ch.ChartType = xlColumnClustered
ch.SetSourceData Source:=Worksheets("Data").Range("A1:B13") ' bind the whole range at once
ch.HasTitle = True
ch.ChartTitle.Text = "Monthly Sales"
Charting in VBA looks easy until the second run, when you find two charts, or the first run, when the bars plot the wrong column. Both come from the same misunderstanding, and the fix for both is the same idea: a chart does not contain your data — it points at it. Every series holds a reference to a range, written as a formula. Move the data, insert a row, rebuild the source in a different place, and the chart keeps pointing where it was told, faithfully drawing the wrong cells. Once you hold "a chart is a view bound to a Range," the API stops being fiddly and the two classic bugs become obvious.
What you'll learn
- The mental model — a chart holds a reference to a Range, not a copy of the data
- The two containers: embedded
ChartObjectsversus chart sheets inCharts - Creating a chart and binding data in one shot with
SetSourceData - Adding and controlling series by hand with
SeriesCollection - The duplicate-chart trap on re-run, and how to clear old charts
- Binding the source to a Table so the chart tracks the data
The mental model: a chart points at a Range
When you chart a range, Excel does not copy the numbers into the chart. It stores, for each series, a formula that references the range — the same way a cell formula references other cells. You can read it back:
Debug.Print ch.SeriesCollection(1).Formula
' =SERIES("Sales",Data!$A$2:$A$13,Data!$B$2:$B$13,1)
That SERIES(...) formula is the whole truth about the chart: a name, an X-values reference, a Y-values
reference. The chart is a live view over those references. This is why "my chart shows the wrong data" is
so common — the chart is not wrong, its reference is pointing at cells that no longer hold what you
think. And it is why the durable fix, at the end of this guide, is to make the reference track the data
instead of freezing at $B$2:$B$13.
The two containers: ChartObjects versus chart sheets
Excel has two homes for charts, and confusing them is the first thing that trips a chart macro. An
embedded chart floats on a worksheet and lives in that sheet's ChartObjects collection; a
chart sheet is a whole tab that is nothing but a chart, and lives in the workbook's Charts
collection:
' Embedded: a ChartObject is the frame/container; its .Chart is the chart itself
Dim co As ChartObject
Set co = Worksheets("Report").ChartObjects.Add(300, 20, 420, 260)
co.Chart.ChartType = xlLine ' set properties on .Chart, not on co
' Chart sheet: a tab of its own
Dim ch As Chart
Set ch = ThisWorkbook.Charts.Add
The subtlety that costs people an hour: with an embedded chart, ChartObjects.Add gives you the
frame (a ChartObject), and the chart you actually format is co.Chart. Reaching for Charts(1)
when your chart is embedded raises an error, because Charts only holds chart sheets. Most reporting
lives on a worksheet, so you will use ChartObjects far more often — just remember the two-layer
frame/chart split.
Creating a chart and binding data in one shot
The clean pattern is: add the frame, grab .Chart, set the type, and bind the whole source with
SetSourceData in a single call:
Dim co As ChartObject, ch As Chart
Set co = Worksheets("Report").ChartObjects.Add(300, 20, 420, 260)
Set ch = co.Chart
ch.ChartType = xlColumnClustered
ch.SetSourceData Source:=Worksheets("Data").Range("A1:B13")
SetSourceData hands Excel the whole block — headers, categories, and values — and lets it work out the
series, exactly like selecting the range and pressing the chart button. This is almost always what you
want; it is fewer lines and fewer mistakes than building series one at a time. Include the header row and
the category column in the range and the chart labels itself. Set .ChartType before or after — common
values are xlColumnClustered, xlLine, xlPie, and xlXYScatter.
Adding and controlling series by hand
When you need precise control — one series at a time, values from a non-contiguous place, a custom name —
add series through SeriesCollection:
Dim s As Series
Set s = ch.SeriesCollection.NewSeries
s.Name = "2026"
s.XValues = Worksheets("Data").Range("A2:A13")
s.Values = Worksheets("Data").Range("B2:B13")
Each series has three parts that map straight onto that SERIES(...) formula: .Name, .XValues (the
category labels), and .Values (the numbers). Add a second series with another NewSeries; remove one
with ch.SeriesCollection(2).Delete. Setting .Values to a range is the same as writing the reference
into the series formula — which is the mechanism behind the wrong-data bug, and the reason binding to a
stable source matters.
The duplicate-chart trap, and clearing old charts
Here is the bug that produces twelve charts after twelve runs: ChartObjects.Add always adds a new
one. It never reuses the chart from last time, so an unattended macro quietly stacks copies. Clear the
sheet's existing charts before you build:
Dim co As ChartObject
For Each co In Worksheets("Report").ChartObjects
co.Delete ' remove last run's charts first
Next co
' ...now build the fresh chart
Loop ChartObjects and Delete each, then create your chart from a clean slate — the same
collection discipline you would use for any repeated build. If you would rather
keep and update one specific chart, name it (co.Name = "chtSales"), look it up by that name next run,
and call SetSourceData again instead of adding a new frame. Either way, decide deliberately: build
fresh or update in place, never blindly add.
Binding the source to a Table so the chart tracks the data
Now the durable fix for the wrong-data bug. Because a series is a reference, a chart bound to A1:B13
freezes at thirteen rows — add a month and the new point is outside the chart. Bind the source to a
Table instead, and the reference grows with the data:
' The chart's source is a Table's range -> new rows appear automatically
ch.SetSourceData Source:=Worksheets("Data").ListObjects("tblSales").Range
When the source is a ListObject, adding a row extends the Table, the Table extends the range the series
references, and the chart redraws with the new point — no code, no re-SetSourceData. This is the same
move that keeps a pivot current, and it is why a Table is the
foundation worth building first: point your pivots and charts at it once and both self-update. To hand a
chart off as an image, co.Chart.Export "C:\Reports\sales.png" writes a PNG straight to disk.
How ExcelMaster helps
The chart mistakes that reach a finished report are quiet: the second chart nobody deleted, the series
still pointing at last quarter's range, the bars plotting the wrong column because SetSourceData got a
block that had shifted. Each one renders cleanly — it just shows the wrong picture.
ExcelMaster builds charts like someone who
has been burned by all three. Ask it to "chart monthly sales by region," and it clears the old
ChartObjects before it draws, binds the source to a Table so new rows appear on their own, uses
SetSourceData for the whole block instead of hand-wiring series, and formats through co.Chart rather
than tripping on the frame/chart split. You describe the picture you want; it wires the reference so the
chart keeps telling the truth as the data grows.
Frequently asked questions
How do I create a chart in VBA?
Add an embedded chart with ChartObjects.Add, then set its type and data through .Chart:
Set co = ws.ChartObjects.Add(300, 20, 420, 260), Set ch = co.Chart, ch.ChartType = xlColumnClustered,
ch.SetSourceData Source:=Range("A1:B13"). The ChartObject is the frame and its .Chart is the chart
you format. SetSourceData binds the whole range in one call.
Why does my VBA chart plot the wrong data?
Because a chart holds a reference to a range, not a copy of it — each series' .Values is a formula
like =Sheet1!$B$2:$B$13. If the data moves, rows are inserted, or the source is rebuilt elsewhere, the
reference still points at the old cells and the chart draws them. Re-bind with SetSourceData, or point
the source at a Table so the reference tracks the data.
What is the difference between ChartObjects and Charts in VBA?
ChartObjects is a worksheet's collection of embedded charts that float on the sheet; each
ChartObject is a frame whose .Chart is the actual chart. Charts is the workbook's collection of
chart sheets — tabs that are nothing but a chart. Reaching for Charts(1) when your chart is embedded
raises an error; use ws.ChartObjects(1).Chart instead.
How do I add a series to a chart in VBA?
Use SeriesCollection.NewSeries, then set its parts:
Set s = ch.SeriesCollection.NewSeries, s.Name = "2026", s.XValues = Range("A2:A13"),
s.Values = Range("B2:B13"). Add more with another NewSeries; remove one with
ch.SeriesCollection(2).Delete. For a simple chart, SetSourceData on the whole range is easier than
adding series by hand.
How do I stop VBA from creating duplicate charts?
ChartObjects.Add always adds a new chart, so re-running a macro stacks copies. Before building, delete
the existing ones: For Each co In ws.ChartObjects: co.Delete: Next co. If you want to keep and refresh a
specific chart, name it (co.Name = "chtSales"), find it by that name on the next run, and call
SetSourceData again instead of adding a new frame.
Tested in
Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-02.
Related guides: VBA Pivot Table · VBA Table · VBA Range · VBA Collection · VBA Worksheet