There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart from another workbook, and you no longer have access to that workbook. Maybe you want to avoid the headaches that may arise from pasting a chart into PowerPoint or another program. Maybe you’re just tired of seeing this warning when you open the file:
There are several ways to disconnect your chart from its data source.
First let’s review chart data. I’ve written a lot about chart data, including
- Good Chart Data – The definitive description
- The Excel Chart SERIES Formula – also definitive
- Change Series Formula – Improved Routines
- How to Edit Series Formulas
- Simple VBA Code to Manipulate the SERIES Formula and Add Names to Excel Chart Series
- Edit Series Formulas
Below is a simple chart. A series is selected so the SERIES formula appears in the formula bar and the ranges in the formula are highlighted in the worksheet.
The SERIES formula looks like this:
The arguments in the formula describe the sources of the data.
Series_Namecan be a link to a worksheet range, text (enclosed in double quotes), or blank.
X_Valuescan be a link to a worksheet range, an array enclosed in curly braces, or blank (and the chart will use 1, 2, 3, … for its X values).
Y_Valuescan be a link to a worksheet range, or an array enclosed in curly braces.
Plot_Orderis an whole number between 1 and the number of series in the chart, signifying the order in which the series is drawn (complicated by chart type and axis group).
The cell addresses in the SERIES formula always use absolute references, such as $A$1, not relative references, like A1. But if you manually type relative references and press Enter, they will be converted to absolute references. The addresses always include the worksheet name.
If the chart links to data in another open Excel workbook, the SERIES formula includes the workbook name in square brackets before the worksheet name.
=SERIES('[Data Source.xlsm]Sheet1'!$C$2,'[Data Source.xlsm]Sheet1'!$B$3:$B$8,'[Data Source.xlsm]Sheet1'!$C$3:$C$8,1)
If the chart links to data in a closed Excel workbook, the SERIES formula includes the path, then the workbook name in square brackets, and finally the worksheet name.
=SERIES('C:\Long Path[Data Source.xlsx]Sheet1'!$C$2,'C:\Long Path[Data Source.xlsx]Sheet1'!$B$3:$B$8,'C:\Long Path[Data Source.xlsx]Sheet1'!$C$3:$C$8,1)
Copy a Picture of the Chart
One way to represent an unlinked chart is to copy a picture of the chart, then paste it where desired.
Select the chart, then on the Home tab of Excel’s ribbon, under the Copy dropdown, select Copy as Picture…
… then select the appropriate options (usually Bitmap instead of Picture; I haven’t been able to figure out the difference between on screen vs. as printed) …
Then go to the other application, and Paste.
The disadvantage to this technique is that the pasted picture is no longer an Excel chart. You can no longer format any of the chart elements (rescale the axes, change marker styles or colors, etc.). Therefore, this method is unsuitable for use within Excel.
Change the Cell References to Hard-Coded Values
You can unlink chart data and still retain the actual chart with its formatting capabilities by editing the SERIES formula. Recall that the series formula in our first chart above was:
where the arguments referred to various links to the series data
Select the series so that the SERIES formula appears in the formula bar, click in the formula bar so that the cursor is in the formula, and press F9. This keystroke converts references in the formula to their values:
Plot_Order is unchanged, of course, because it can only be a number, never a reference.
Press Esc to revert to the formula with references, or press Enter to keep the formula with hard-coded values.
If you select just one of the references in the formula, the F9 key only converts that reference to its value. These SERIES formulas are all valid:
Automate with VBA
Any repetitive task that you can do manually, VBA can do faster with much less tedium.
Simple VBA Algorithm
To unlink chart data from all series in the active chart, simply run this code:
Sub DelinkChartFromData0() If ActiveChart Is Nothing Then MsgBox "Select a chart and try again", vbExclamation, "No Active Chart" Else Dim srs As Series For Each srs In ActiveChart.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End If End Sub
More Flexible Code
That’s nice enough, but I like to adjust a sub like this by including an argument, so I can pass in the object I want to process from any entry point. The function corresponding to this is
Sub DelinkChartFromData1(cht As Chart) Dim srs As Series For Each srs In cht.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End Sub
To process the active chart, I would call it with this entry point:
Sub DelinkActiveChartFromData1() If Not ActiveChart Is Nothing Then DelinkChartFromData1 ActiveChart End If End Sub
To process all charts on the active sheet, I would use this:
Sub DelinkAllChartsFromData1() Dim chob As ChartObject For Each chob In ActiveSheet.ChartObjects DelinkChartFromData1 chob.Chart End If End Sub
To select on or more charts to process, and ignore the rest, I use this approach:
Sub DelinkSelectedChartsFromData1() If Not ActiveChart Is Nothing Then DelinkChartFromData1 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData1 shp.Chart End If Next End If End Sub
In fact, the last sub is all I need, since it does the active chart if there is one, replacing
DelinkActiveChartFromData1, and it does any selected charts, so I could select all charts and run it to mimic
Fix the Date Axis
I was working on an example to show that it works for chart with lots of data points, and I happened to use dates as my X values.
DelinkSelectedChartsFromData1 and got the resulting chart.
The SERIES formula is pretty long, but is nowhere near the limit (see Excel Chart Series Size Limits).
Unfortunately, the X axis tick labels have lost their date formatting. In the sub, the
srs.XValues = srs.XValues converted the input dates into numbers, because internally Excel stores dates as the number of days since 1 January 1900. Easy enough to apply the date format manually.
Apparently, the nice spacing that comes with an actual date axis is gone. The chart above has dates, but the axis is a simple category axis. I’ll have to adjust the procedure to prevent a date axis from being changed into a category axis. Before I convert the links to values, I will apply the number format to the axis ticks. Then if it is a date axis, I will make sure the chart treats it as one; this is complicated by the fact that Excel often applies the date category type automatically based on the data.
Sub DelinkChartFromData2(cht As Chart) Dim iGrp As XlAxisGroup For iGrp = xlPrimary To xlSecondary Dim ax As Axis Set ax = cht.Axes(xlCategory, iGrp) With ax ' apply formats .TickLabels.NumberFormat = .TickLabels.NumberFormat If IsDateAxis(ax) Then ' apply date type ax.CategoryType = xlTimeScale End If End With Next Dim srs As Series For Each srs In cht.SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End Sub
This function tests whether an axis is a date category type. The
BaseUnit property is undefined unless the axis is a date axis.
Function IsDateAxis(ax As Axis) As Boolean If ax.Type = xlCategory Then Dim vTest As Variant On Error Resume Next vTest = ax.BaseUnit IsDateAxis = (Err.Number = 0) On Error GoTo 0 End If End Function
The corresponding entry point that I call the above with is familiar:
Sub DelinkSelectedChartsFromData2() If Not ActiveChart Is Nothing Then DelinkChartFromData2 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData2 shp.Chart End If Next End If End Sub
The resulting chart is now indistinguishable from the original:
Unlink the Chart and Axis Titles
It’s easy to link many of a chart’s text elements to a worksheet range. Select the text element, click in the formula bar, type
= and click on the cell or range containing the text you want displayed. The result is a link formula like
=Sheet1!$A$1, and the text element updates dynamically to display whatever is in the reference. This works for the chart title, axis titles, data labels, and textboxes and other shapes that contain text.
If you’re delinking the chart’s data, you probably want to delink the titles in the chart. A simple VBA routine to do just that is shown below. For each possible title, see if the formula begins with an equals sign (if not, the formula just shows the text), and if so, replace the title’s text with the title’s text.
Sub UnlinkTitles() If Not ActiveChart Is Nothing Then With ActiveChart If .HasTitle Then If Left$(.ChartTitle.Formula, 1) = "=" Then ' convert chart title link to text .ChartTitle.Text = .ChartTitle.Text End If End If Dim iAx As XlAxisGroup For iAx = xlCategory To xlSeriesAxis Dim iGrp As XlAxisType For iGrp = xlPrimary To xlSecondary If .HasAxis(iAx, iGrp) Then With .Axes(iAx, iGrp) If .HasTitle Then If Left$(.AxisTitle.Formula, 1) = "=" Then ' convert axis title link to text .AxisTitle.Text = .AxisTitle.Text End If End If End With End If Next Next End With End If End Sub
Let’s merge this into our last Delink The Chart routine (I’ve also included the test for a date axis:
Sub DelinkChartFromData3(cht As Chart) With cht If .HasTitle Then If Left$(.ChartTitle.Formula, 1) = "=" Then ' convert chart title link to text .ChartTitle.Text = .ChartTitle.Text End If End If Dim iAx As XlAxisType For iAx = xlCategory To xlSeriesAxis Dim iGrp As XlAxisGroup For iGrp = xlPrimary To xlSecondary If .HasAxis(iAx, iGrp) Then Dim ax As Axis Set ax = .Axes(iAx, iGrp) With ax If .HasTitle Then If Left$(.AxisTitle.Formula, 1) = "=" Then ' convert axis title link to text .AxisTitle.Text = .AxisTitle.Text End If End If If iAx = xlCategory Then ' apply formats .TickLabels.NumberFormat = .TickLabels.NumberFormat If ax.Type = xlCategory Then Dim vTest As Variant On Error Resume Next vTest = ax.BaseUnit If (Err.Number = 0) Then ' apply date type ax.CategoryType = xlTimeScale End If On Error GoTo 0 End If End If End With End If Next Next Dim srs As Series For Each srs In .SeriesCollection ' Convert X Values to arrays of values srs.XValues = srs.XValues ' Convert Y Values to arrays of values srs.Values = srs.Values ' Convert series name to text srs.Name = srs.Name Next srs End With End Sub
And we’ll call it using the familiar entry point:
Sub DelinkSelectedChartsFromData3() If Not ActiveChart Is Nothing Then DelinkChartFromData3 ActiveChart ElseIf TypeName(Selection) = "DrawingObjects" Then Dim shp As Shape For Each shp In Selection.ShapeRange If shp.HasChart Then DelinkChartFromData3 shp.Chart End If Next End If End Sub
Recalc Or Die says
Hi Jon, excellent post.
Is it possible to link the plot order argument of the SERIES formula to a cell’s value right on the formula bar?
Last, the sub DelinkChartFromData3 is quite interesting.
Jon Peltier says
If you try to enter a cell reference into the plot order (or series order) argument of the SERIES formula, you get the following warning. The message is actually incorrect because the plot order must be between 1 (not zero) and 255.