This post presents a VBA procedure that extracts all data from a chart, and places it into a new worksheet. Why would you want to extract a chart’s data? Sometimes a chart gets its data from diverse sources, and you’d like to have the data in one place. You may receive a workbook that contains a chart, but the chart’s data is in a workbook which you don’t have access to. For whatever reason, this procedure comes in handy.
Sub ExtractChartData()
Dim iSrs As Long
Dim cht As Chart
Dim srs As Series
Dim ws As Worksheet
If ActiveChart Is Nothing Then Exit Sub
Set cht = ActiveChart
Set ws = Worksheets.Add
For iSrs = 1 To cht.SeriesCollection.Count
Set srs = cht.SeriesCollection(iSrs)
On Error Resume Next
ws.Cells(1, 2 * iSrs).Value = srs.Name
ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.XValues)
ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _
WorksheetFunction.Transpose(srs.Values)
Next
End Sub
The procedure merely extracts the data, it does not change the data source of the chart to the ranges in the new worksheet. If desired, you could embellish this macro, so it restores information such as chart type, series formatting (marker or fill style and colors, line or border styles and colors), axis types and scales, axis and chart titles, data labels, and more.
If you are not sure how to use this procedure, read How To Use Someone Else’s Macro.
Clint says
Wow! I think this little utility is something that might come in very handy – thanks!
Haffy says
Thanks, Jon. I have to extract data from charts regularly and sorted out a workbook to do it – but your code is so much faster – one of those “Why on earth didn’t I try that?” moments.
Thanks again.
TV says
Is there a series property or some other trick to get the address of the chart data (or at least a point)?
Jon Peltier says
TV –
This can be done by parsing the series formula. John Walkenbach has code to do this:
A Class Module To Manipulate A Chart Series.
John says
I want something where I canextract only some of the data points from a chart.
I have a series of data with a date and price which I plot in excel. I want to be able to click or double click on the graph on a subset of the displayed points and have the dates for the selected points transferred to another part of the spreadsheet so I can do some cycle analysis on this set of points.
Does anyone know how to do this.
Thanks
John
Jon Peltier says
You can write some code that will respond to mouse clicks in the chart:
Chart Events in Microsoft Excel
Get Information about a Point in an Embedded Chart (zip file)
I’ve used similar techniques to help users select a range of points for regression or other detailed analysis, to highlight certain points, or to sort points into two subsets. It’s a lot of work to set up, but it’s slick from the user’s perspective.
John says
Thanks Jon,
I had just found your post “Get INformation about a point in an embedded chart” today and it does what I need, if I could also somehow highlite the selected points so the user knows which ones he has selected tha would also be beficial.
John
Jon Peltier says
Just time for a quick response to give you an idea. The click returns the series number (Arg1) and the point number (Arg2). You could have your code act on
ActiveChart.SeriesCollection(Arg1).Points(Arg2)
If the marker is a blue diamond, change it to a red square; otherwise change it back.
Good idea for a blog post. Unfortunately I have a backlog if ideas and a shortage of time.
IS says
Dear Jon,
This is faboulous. Cant figure out how to get it to copy the data, and the chart with all formats as well. Tried to embed the macro in the personal.xlsb workbook but still no result.
grateful for your response
I
J says
I pasted your codes on my VB Module of my powepoint and tried to run it on my graph, but it pops up with Compile error; User-defined type not defined, and highlighted the part that reads ws As Worksheet. Please help thanks!
Jon Peltier says
Jack –
This procedure was written to run within Excel. I don’t offhand know how to modify it to run in PowerPoint.
Jakub says
Hi John,
Firstly, great blog and incredible resources! Alway my first stop for excel/VBA info.
The “Get Information about a point in an embedded chart” has been a great tool, and am currently assessing some data with it. The post above dated March 14 2009 has also been useful to highlight the selected data on the chart itself. However, it would also be useful to be able to highlight the actual cell in the sheet from which the data point is taken (i.e. to determine the actual cell location from the data series and data point). Any thoughts on how this can be done easily?
Jon Peltier says
Jakub –
The “Get Information” routine provides series number and point number. Get the SERIES formula of the indicated series, and parse the series to find the ranges used for X and Y of that series. The point number is the number of the cells within those ranges that have the data for that point.
zahra says
Hello
I have a chart with standard error bars but i have lost the data for standard error, can i find it from the bars on the chart
thanks
Jon Peltier says
Zahra –
I doubt that the error bars will tell you anything useful. You could estimate based on their length in the chart, but that’s only as good as your estimation skills. You could calculate it from the data plotted in the chart.
Gianfranco Ceni says
Thank you very much!
RUDOLF WILHELM says
Jon,
You are my HERO!
Thank you for this and all the other useful and working macros,
Rudy