How to extract data from chart or graph in Excel?

doc-extract-chart-data-1
In Excel, we usually use chart to show data and trend for more clearly viewing, but in sometimes, maybe the chart is a copy and you haven’t the original data of the chart as below screenshot shown. In this case, you may want to extract the data from this chart. Now this tutorial is talking about data extracting from a chart or graph.
Extract data from chart with VBA

Navigation--AutoText (add usually used charts to AutoText pane.then one click to insert it when you need.)

excel add in tools for inserting waterfall chart anytime

arrow blue right bubble Extract data from chart with VBA


1. You need to create a new worksheet and rename it as ChartData. See screenshot:

Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.

doc-extract-chart-data-5

2. Then select the chart you want to extract data from and press Alt + F11 keys simultaneously, and a Microsoft Visual Basic for Applications window pops.

3. Click Insert > Module, then paste below VBA code to the popping Module window.

VBA: Extract data from chart.

Sub GetChartValues()
	'Updateby20150203
	Dim xNum As Integer
	Dim xSeries As Object
	xCount = 2
	xNum   = UBound(Application.ActiveChart.SeriesCollection(1).Values)
	Application.Worksheets("ChartData").Cells(1, 1) = "X Values"
	With Application.Worksheets("ChartData")
		.Range(.Cells(2, 1), _
		.Cells(xNum + 1, 1)) = _
		Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
	End With
	For Each xSeries In Application.ActiveChart.SeriesCollection
		Application.Worksheets("ChartData").Cells(1, xCount) = xSeries.Name
		With Application.Worksheets("ChartData")
			.Range(.Cells(2, xCount), _
			.Cells(xNum + 1, xCount)) = _
			Application.WorksheetFunction.Transpose(xSeries.Values)
		End With
		xCount = xCount + 1
	Next
End Sub

4. Then click Run button to run the VBA. See screenshot:

doc-extract-chart-data-2

Then you can see the data is extracted to ChartData sheet.

doc-extract-chart-data-3

Export Range as Graphic

Kutools' Export Range as Graphic is aim to save or export a selection cells as multiple graphic formats.
doc export range as picture

Tip:

1. You can format the cells as you need.

doc-extract-chart-data-4

2. The data of the selected chart is extracted to the first cell of the ChartData sheet in default.

pay attention1If you are interested in this addi-in, download the 60-days free trial.

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments  

Permalink 0 Henning
Good day, i seem to run into a Run-tome error '-2147467259 (80004005)'
Method 'XValues' of object 'series failed'
2015-07-04 09:59 Reply Reply with quote Quote
Permalink 0 Alex
Thank you. This was really helpful!
2016-12-20 15:26 Reply Reply with quote Quote
Permalink 0 Leo
Amazing command, thanks a lot!

I used it with a pivot chart and it works!
2017-01-18 14:12 Reply Reply with quote Quote

Add comment


Security code
Refresh