How to break chart links to source data in Excel?
In general, the chart will change automatically when we modify the source data. However, in some cases I want to keep the chart still when the source data changes. Here, this article will show you the easy way to break chart links to their source data in Excel.
Recommended Excel Productivity Tools
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 then paste the VBA 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.