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
|
|
|