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.
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.
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.
Then you will see the link between the selected chart and its source data has been broken immediately.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!