Have you ever needed to extract chart data? If this question makes you wonder, you are not alone. At first, this sounds quite irrelevant. Why somebody will ever need to extract data from a chart that he/she probably created. But, let’s consider the following scenario:
You receive an email with an attachment from one of your colleagues. The attachment is just a workbook that contains a single chart showing the products sold every month. The chart, in fact, has been copied from another workbook, but you don’t get the data, but only the chart.
When you open the workbook, you receive the annoying message shown in the image below:
You need the chart data to process them further and to create additional charts. What will you do? You will probably ask your colleague to send you the workbook with the data, or you can save some time and use VBA to extract the chart data.
The above scenario is not fictional. It has happened to me several times. If you are dealing with many spreadsheets, it is not surprising to receive dashboards that contain a lot of charts. However, some people often forget to include the data in these workbooks, so you end up having charts without the actual data (e.g., the data are in other workbooks).
VBA code to extract chart data
If you want to avoid situations like the one described above, you can use the following VBA code. Just select the chart from which you want to extract the data and run the macro. Automatically, all the data from all the chart series will be written to a new worksheet named “Chart Data.”
Option Explicit Sub ExtractChartData() '----------------------------------------------------------------- 'Extracts all the data from all the series of the selected chart. 'It writes the data to a new worksheet named "Chart Data". 'Written By: Christos Samaras 'Date: 12/06/2013 'Last Updated: 21/06/2020 'E-mail: [email protected] 'Site: https://www.myengineeringworld.net '----------------------------------------------------------------- 'Declaring the necessary variables. Dim myChart As Chart Dim sht As Worksheet Dim i As Integer Dim mySeries As Series 'Check if a chart was selected. If ActiveChart Is Nothing Then MsgBox "No chart was selected!" & vbCr & "Please select a chart and retry...", vbCritical, "Chart Error" Exit Sub End If 'Stop the screen flickering. Application.ScreenUpdating = False 'Asign the active chart to the myChart variable. Set myChart = ActiveChart 'Add a new worksheet. Set sht = Worksheets.Add 'Rename the new worksheet and change its tab's color to red. On Error Resume Next With sht .Name = "Chart Data" .Tab.Color = vbRed .Activate End With 'Loop through all the chart series and extract their data. For i = 1 To myChart.SeriesCollection.Count Set mySeries = myChart.SeriesCollection(i) With sht 'For the x-values, use the axis title (if it exists). If myChart.Axes(xlCategory, xlPrimary).HasTitle = True Then .Cells(1, 2 * i - 1).Value = myChart.Axes(xlCategory, xlPrimary).AxisTitle.Caption .Cells(1, 2 * i - 1).Font.Bold = True End If 'Series name. .Cells(1, 2 * i).Value = mySeries.Name .Cells(1, 2 * i).Font.Bold = True 'Actual data. .Cells(2, 2 * i - 1).Resize(mySeries.Points.Count).Value = Application.Transpose(mySeries.XValues) .Cells(2, 2 * i).Resize(mySeries.Points.Count).Value = Application.Transpose(mySeries.Values) End With Next i 'Fix the columns' width that contain the data. Columns("1:" & myChart.SeriesCollection.Count * 2).EntireColumn.AutoFit 'Re-enable the screen. Application.ScreenUpdating = True 'Inform the user that the macro finished. MsgBox "Chart data were extracted successfully!", vbInformation, "Done" End Sub
The sample workbook you will find in the Downloads section contains a chart with two series but without their data. However, by pressing the “Extract Data” button, all the data are extracted.
If you have ever faced this “extract chart data situation,” just let me know in the comments.
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Note that if you receive the “We can’t update some of the links in your workbooks right now.” error shown in the above image, click the “Continue” button and then run the macro to extract chart data.
Page last updated: 21/06/2020