Creating data from a chart

S

Sarah0824

If someone emails me a chart, but does not include the source data, is there
anyway to generate that data from the chart?
 
A

Andy Pope

Hi,

Assuming you have a chart and not an image of a chart you could either
Select a series and go to the formula bar and press F9.

In a very simple test example the following formula converted to,

=SERIES(,[Book3]Sheet1!$A$2:$A$5,[Book3]Sheet1!$B$2:$B$5,1)

=SERIES(,{"a","b","c","d"},{1,2,3,4},1)

or you could try using code.

Sub GetChartData()
' output labels and values to sheet
Dim vntData As Variant
Dim vntLabels As Variant
Dim lngIndex As Long

With ActiveChart
With .SeriesCollection(1)
vntData = .Values
vntLabels = .XValues
For lngIndex = LBound(vntData) To UBound(vntData)
ActiveSheet.Cells(lngIndex, 1) = vntLabels(lngIndex)
ActiveSheet.Cells(lngIndex, 2) = vntData(lngIndex)
Next
End With
End With

End Sub

Cheers
Andy
 
B

Brad

In the event someone is trying to extract data from a chart "picture" where
there are no linked data, there is a neat little Excel-based utility where
one inputs known x and y axes limits and a few other inputs, then it
approximates the values found in the graph.

Andy and others, no doubt, could program such things. This one is someone's
commercial effort...

Google for... Grab It! Graph Digitizer.

Cheers! Brad


Andy Pope said:
Hi,

Assuming you have a chart and not an image of a chart you could either
Select a series and go to the formula bar and press F9.

In a very simple test example the following formula converted to,

=SERIES(,[Book3]Sheet1!$A$2:$A$5,[Book3]Sheet1!$B$2:$B$5,1)

=SERIES(,{"a","b","c","d"},{1,2,3,4},1)

or you could try using code.

Sub GetChartData()
' output labels and values to sheet
Dim vntData As Variant
Dim vntLabels As Variant
Dim lngIndex As Long

With ActiveChart
With .SeriesCollection(1)
vntData = .Values
vntLabels = .XValues
For lngIndex = LBound(vntData) To UBound(vntData)
ActiveSheet.Cells(lngIndex, 1) = vntLabels(lngIndex)
ActiveSheet.Cells(lngIndex, 2) = vntData(lngIndex)
Next
End With
End With

End Sub

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Sarah0824 said:
If someone emails me a chart, but does not include the source data, is
there
anyway to generate that data from the chart?
 
S

Sarah0824

Great. thank you!

Andy Pope said:
Hi,

Assuming you have a chart and not an image of a chart you could either
Select a series and go to the formula bar and press F9.

In a very simple test example the following formula converted to,

=SERIES(,[Book3]Sheet1!$A$2:$A$5,[Book3]Sheet1!$B$2:$B$5,1)

=SERIES(,{"a","b","c","d"},{1,2,3,4},1)

or you could try using code.

Sub GetChartData()
' output labels and values to sheet
Dim vntData As Variant
Dim vntLabels As Variant
Dim lngIndex As Long

With ActiveChart
With .SeriesCollection(1)
vntData = .Values
vntLabels = .XValues
For lngIndex = LBound(vntData) To UBound(vntData)
ActiveSheet.Cells(lngIndex, 1) = vntLabels(lngIndex)
ActiveSheet.Cells(lngIndex, 2) = vntData(lngIndex)
Next
End With
End With

End Sub

Cheers
Andy


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
Sarah0824 said:
If someone emails me a chart, but does not include the source data, is
there
anyway to generate that data from the chart?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top