Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

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

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Astro · 1 months ago
    This doesn't appear to work for a scatter plot as it only extracts one set of "x" data. How can I amend it to extract all "x" data sets?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 months ago
      Sorry I did not found the solution about that.
      • To post as a guest, your comment is unpublished.
        Carlos · 4 days ago
        i've tried with a scatter plot graph as well, but only get one line of valor.


        i need so much to find a way to extract data from scatterplot graphs.
  • To post as a guest, your comment is unpublished.
    Ian · 5 months ago
    I failed to get the prices of a fund chart on my mac excel 2011 . Run time error '91' object variable or block variable not set . Don't know how to debug . Appreciate any help .
  • To post as a guest, your comment is unpublished.
    jignesh · 6 months ago
    Very useful and perfect
  • To post as a guest, your comment is unpublished.
    Berk · 8 months ago
    gives me values that i created chart with not all the values in range
  • To post as a guest, your comment is unpublished.
    Leo · 11 months ago
    Amazing command, thanks a lot!

    I used it with a pivot chart and it works!