Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

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

  ''' Make sure a chart is selected
  If Not ActiveChart Is Nothing Then
    ''' 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 If
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 sSrsName As String
  Dim sSrsFmla As String
  Dim iPlotOrder As Integer

  ''' Make sure a chart is selected
  If Not ActiveChart Is Nothing Then
    ''' 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 iPts

      ''' 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 & "}"
      ChtSeries.Name = sSrsName
    Next ChtSeries
  End If
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
  If Not ActiveChart Is Nothing Then
    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 If
End Sub
 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2013. 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

Microsoft Most Valuable Professional

My MVP Profile