A dynamic chart can be constructed using dynamic ranges that change in size as data is added or removed. This technique is very powerful: you can define flexible ranges limited only by your ability to write an appropriate Refers To formula.

One imitation of this approach is that it can only account for dynamic numbers of points in a series, but not dynamic numbers of series in a chart. Using VBA you can work around this limitation.

Let’s start with a simple data range as shown below. Following best practices, the data is arranged with series in columns, the category labels (X values) are in the first column, the series names are in the first row, and the top left cell of the range is blank. Using the Define Names dialog, we can define a name using these parameters:

Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)

When we click Add and then click in the Refers To box, the range defined by the Refers To formula is highlighted by the marching ants border.

When creating a chart, or editing its source data, we can enter a dynamic range name in the Data Range box.

Excel accepts this definition of the chart data, but it converts the dynamic range name to a static range address. This means that our chart does not update as the dynamic range changes in size.

Here is our chart created with the dynamic range ChtSourceData. When the plot area or chart area of the chart is selected, the source data is highlighted.

Add some data to enlarge the dynamic range. Open the Define Names dialog, select ChtSourceData, and click in the Refers To box: The highlighted range shows that the dynamic range has enlarged to include all the added data.

The chart has not changed, but with a simple VBA procedure we can change the chart’s source data range to reflect the new dimensions of our defined range:

Sub UpdateChartSourceData()
  With ActiveSheet
    .ChartObjects(1).Chart.SetSourceData _
        Source:=.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End With
End Sub
 

This procedure should be placed into a regular code module, as described in How To Use Someone Else’s Macro.

Here is the updated chart:

To make this work more dynamically, you can include the SetSourceData command in a Worksheet_Change event. Right click on the sheet tab, choose View Code, and the VB Editor will appear with a code module representing the active sheet. Put this procedure into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then
    Me.ChartObjects(1).Chart.SetSourceData _
        Source:=Me.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End If
End Sub
 

This procedure executes whenever there is a change to a cell on the worksheet (not a calculation, but a change in an entered value or formula). The If checks whether the change occurred within the dynamic range ChtSourceData; if so, it resets the chart’s source data range. It’s automagic.