How to break chart links to source data in Excel?

This method will introduce a VBA to quickly break the links between all charts and their source data in an Excel worksheet. Please do as follows:

1. Enable the specified worksheet where you want to break all chart links, and press "Alt "+ "F11" keys together to open the Microsoft Visual Basic for Applications window.

2. Click "Insert" > "Module", and paste the VBA code into the new module window.

VBA: Break all chart links in a worksheet

Sub BreakChartLinks()
Dim I As Long
Dim xChartCount As Long
Dim xLinkArr As Variant
Dim xSerColl As Variant
xLinkArr = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
If Not IsEmpty(xLinkArr) Then
For I = LBound(xLinkArr) To UBound(xLinkArr)
ActiveWorkbook.BreakLink xLinkArr(I), xlLinkTypeExcelLinks
Next I
End If
xChartCount = ActiveSheet.ChartObjects.Count
If xChartCount <> 0 Then
For I = 1 To xChartCount
ActiveSheet.ChartObjects(I).Activate
For Each xSerColl In ActiveChart.SeriesCollection
xSerColl.Values = xSerColl.Values
xSerColl.XValues = xSerColl.XValues
xSerColl.Name = xSerColl.Name
Next
Next
End If
End Sub

3. Press" F5" key or click the "Run" button to run this VBA.

Now you will see all chart links are broken to their source data in the current worksheet.

If you have "Kutools for Excel" installed, you can apply its "Detach Chart" tool to quickly break the link between a chart and its source data with only one click in Excel.

1. Select the chart whose link you need to break in Excel.

2. Click "Kutools" > "Charts "> "Chart Tools "> "Detach Chart" to enable this feature.

The link between the selected chart and its source data will be broken immediately.

Conclusion

The VBA method is useful when you need to break the source-data links for all charts on the current worksheet at once. The macro automatically loops through the charts and their data series, so there is no need to edit each chart series manually. However, users must open the VBA Editor, insert a module, paste the code, and run the macro. In addition, the code demonstrated in the article processes all charts on the active worksheet rather than allowing users to simply select one specific chart.

Kutools for Excel's Detach Chart provides a simpler and more targeted approach. Select the chart you want to freeze and run Detach Chart to immediately break its connection to the source data. There is no need to write VBA, create a module, run a macro, or process individual data series. This makes it particularly convenient when you need to preserve a specific chart as a static snapshot while allowing the underlying worksheet data to continue changing.

How to break chart axis in Excel?