Peltier Technical Services, Inc.
- Custom Excel Solutions

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel
- Site Index and Search - RSS

Delink Excel Chart Data

There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart and pasted it into another workbook, and opening the other workbook brings up the dialog box asking whether you want to update the links to another workbook:

There are ways you can make a copy of a chart that is not linked to the parent data.

Paste a Picture of the Chart

The easiest way to represent an unlinked chart is to copy a picture of the chart, then paste it where desired. If you select the chart, then hold down the Shift key while pulling down the Edit menu, a different set of choices will appear:

You are presented with a set of options. I generally use the options shown in this dialog box:

This picture can be pasted wherever you want it to appear. I use this method whenever pasting an Excel chart into another application, such as PowerPoint or Word.

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.). So I find this method unsuitable for use within Excel.

Unlink the Chart Series from Worksheet Data

It is possible to unlink a chart from its source data, without losing the ability to edit it as a chart. Refer to the example data and chart below:

The series formula data source is a reference to two ranges in the worksheet:

=SERIES(,Sheet1!$B$4:$B$15,Sheet1!$C$4:$C$15,1)

Notice that these two ranges are outlined in the sheet when the series is selected. The X Values (Sheet1!$B$4:$B$15) are outlined in purple, and the Y Values (Sheet1!$C$4:$C$15) are outlined in green.

Click in the formula bar, and press F9 (or Ctrl and the '=' key). The range references in the formula are converted to the arrays of data contained within the ranges, and the formula changes to this:

=SERIES(,{15.031454211777,8.1383538616338,10.9998151055087,
13.410691201042,10.6248254334369,10.6371217842496,11.3995831152254,
19.5323146287637,11.0885090885436,4.4742985867933,6.31296089699334,
3.81550937155244},{116855.089242441,239494.950013403,185567.581627991,
64882.6685031719,318350.869651478,129554.347163989,458906.997532184,
31286.2635801765,368058.120913394,304468.990447535,295618.685237458,
376771.217071102},1)

Your chart can now be pasted into any workbook, without generating links to the original data ranges.

You can convert just part of the series formula, if desired. Highlight part of the series formula, and press F9. If the Y Value reference (Sheet1!$C$4:$C$15) was selected, the formula now converts to this:

=SERIES(,Sheet1!$B$4:$B$15,{116855.089242441;239494.950013403;
185567.581627991;64882.6685031719;318350.869651478;129554.347163989;
458906.997532184;31286.2635801765;368058.120913394;304468.990447535;
295618.685237458;376771.217071102},1)

If there are multiple series to be delinked, you can use a macro like this to automate the process:

Sub DelinkChartFromData()
''' Thanks to Tushar Mehta
    Dim mySeries As Series
    Dim sChtName As String

    ''' Make sure a chart is selected
    On Error Resume Next
    sChtName = ActiveChart.Name
    If Err.Number <> 0 Then
        MsgBox "This functionality is available only for charts " _
            & "or chart objects"
    Exit Sub
    End If
    If TypeName(Selection) = "ChartObject" Then
        ActiveSheet.ChartObjects(Selection.Name).Activate
    End If
    On Error GoTo 0

    ''' Loop through all series in active chart
    For Each mySeries In ActiveChart.SeriesCollection
        '''' Convert X and Y Values to arrays of values
        mySeries.XValues = mySeries.XValues
        mySeries.Values = mySeries.Values
        mySeries.Name = mySeries.Name
    Next mySeries
End Sub

Select a chart and run the DelinkChartFromData procedure.

Note: The problem with this approach is that Excel chokes on a series definition formula longer than around 1000 characters. But our formula obviously carries a lot of extra characters (16 per value), so I have modified the procedure above to excise the insignificant digits, in the procedure DelinkChartFromLotsOfData below:

Sub DelinkChartFromLotsOfData()
    Dim nPts As Long, iPts As Long
    Dim xArray As String, yArray As String
    Dim xVals As Variant, yVals As Variant
    Dim sxVal As String
    Dim syVal As String
    Dim ChtSeries As Series
    Dim iChars As Integer
    Dim sChtName As String
    Dim sSrsName As String
    Dim sSrsFmla As String
    Dim iPlotOrder As Integer

    ''' Make sure a chart is selected
    On Error Resume Next
    sChtName = ActiveChart.Name
    If Err.Number <> 0 Then
        MsgBox "This functionality is available only for charts " _
            & "or chart objects"
        Exit Sub
    End If
    If TypeName(Selection) = "ChartObject" Then
        ActiveSheet.ChartObjects(Selection.Name).Activate
    End If
    On Error GoTo 0

    ''' Loop through all series in active chart
    For Each ChtSeries In ActiveChart.SeriesCollection
        nPts = ChtSeries.Points.Count
        xArray = ""
        yArray = ""
        xVals = ChtSeries.XValues
        yVals = ChtSeries.Values
        sSrsName = ChtSeries.Name
        iPlotOrder = ChtSeries.PlotOrder

        For iPts = 1 To nPts
            If IsNumeric(xVals(iPts)) Then
                ''' shorten numbers in X array (remove excess digits)
                iChars = WorksheetFunction.Max _
                    (InStr(CStr(xVals(iPts)), "."), 5)
                ''' handle values in scientific notation
                sxVal = CStr(xVals(iPts))
                If InStr(sxVal, "E") = 0 Then
                    sxVal = Left(sxVal, iChars)
                Else
                    sxVal = Format(xVals(iPts), "0.000E+0")
                End If
                xArray = xArray & sxVal & ","

            Else
                ''' put quotes around string values
                xArray = xArray & """" & xVals(iPts) & ""","
            End If

            ''' shorten numbers in Y array (remove excess digits)
            iChars = WorksheetFunction.Max _
                (InStr(CStr(yVals(iPts)), "."), 5)

           If IsEmpty(yVals(iPts)) Or WorksheetFunction.IsNA(yVals(iPts)) Then
               ''' handle missing data - replace blanks and #N/A with #N/A
               yArray = yArray & "#N/A,"
           Else
               ''' handle values in scientific notation
               syVal = CStr(yVals(iPts))
               If InStr(syVal, "E") = 0 Then
                   syVal = Left(syVal, iChars)
               Else
                   syVal = Format(yVals(iPts), "0.000E+0")
               End If
               yArray = yArray & syVal & ","
           End If
        Next

        ''' remove final comma
        xArray = Left(xArray, Len(xArray) - 1)
        yArray = Left(yArray, Len(yArray) - 1)

        ''' assign arrays to X and Y values
        ChtSeries.Values = "={" & yArray & "}"
        ChtSeries.XValues = "={" & xArray & "}"
    Next
End Sub

Thanks to Andy Pope for correcting a glitch that was reversing the plot order in charts with multiple series, and for reminding me to handle missing values. Also thanks to PGC (Mr Excel MVP) for suggesting a problem with numbers stored in scientific notation.

Look at the new, svelter series formula:

=SERIES(,{15.03,8.138,10.99,13.41,10.62,10.63,11.39,19.53,11.08,4.474,
6.312,3.815},{116855,239494,185567,64882,318350,129554,458906,31286,
368058,304468,295618,376771},1)

Unlink Chart and Axis Titles from Worksheet Cells

One nice feature is the ability to link a chart title or axis title to the contents of a cell. To do this, select the title, press the equals key, then select the cell with your mouse, or type the fully qualified address of the cell after the equals sign in the formula bar. Fully qualified means start with the sheet name, in single quotes if the name has a space, then an exclamation point, and finally the cell address.

The following short macro breaks the links between the chart's titles and the worksheet cells, replacing the cell reference with the contents of the cell.

Sub UnlinkTitles()
    Dim iAx As Integer, iGrp As Integer
    With ActiveChart
        If .HasTitle Then .ChartTitle.Text = .ChartTitle.Text
        For iAx = 1 To 2
            For iGrp = 1 To 2
                If .HasAxis(iAx, iGrp) Then
                    With .Axes(iAx, iGrp)
                        If .HasTitle Then _
                            .AxisTitle.Text = .AxisTitle.Text
                    End With
                End If
            Next
        Next
    End With
End Sub

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile