How to Get Data Points from a Graph in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show how to get data points from a graph in Excel in two effective ways. Graphs are a powerful medium to present data. However, we often need to get the value of a point on a graph to do different operations. Excel does not have direct methods to get the data points.

In this article, we will discuss how to get data points from a graph in Excel in 2 different methods. Firstly, we will use a VBA code to get the work done. Then, we will enable the data labels of the chart to get data points.


1. Using VBA Code to Get Data Points from Graph

VBA codes are a powerful way to do recurring operations. They also help to perform different complicated operations in Excel. For example, here, we have a graph, and we need to get the data points from that graph. We will use a VBA code to do that.

how to get data points from a graph in excel

Steps:

  • To begin with, select the graph from which you want to extract the data points.

selecting graph to show how to get data points from a graph in excel

  • After that, open a new worksheet and give it a name.
  • We will name our worksheet “VBACode”.
  • This name will be used in the VBA code that we are going to write.

  • Thereafter, go to the Developer tab.
  • From there, choose the Visual Basic tab.
  • As a result, the Visual Basic window will be on the screen.

opening visual basic tab to show how to get data points from a graph in excel

  • In the Visual Basic window, first, select the Insert tab.
  • Then, from the available options, select Module.

  • As a result, a new module will be opened.
  • Write the following code in the module.
Sub GetDataPoints()
    Dim aM_num As Integer
    Dim aM_series As Object
    On Error Resume Next
    aM_Count = 3
    aM_num = UBound(Application.ActiveChart.SeriesCollection(1).Values)
    Application.Worksheets("VBACode").Cells(4, 2) = "X Values"
    With Application.Worksheets("VBACode")
        .Range(.Cells(5, 2), _
        .Cells(aM_num + 4, 2)) = _
        Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
    End With
    For Each aM_series In Application.ActiveChart.SeriesCollection
        Application.Worksheets("VBACode").Cells(4, aM_Count) = aM_series.Name
        With Application.Worksheets("VBACode")
            .Range(.Cells(5, aM_Count), _
            .Cells(aM_num + 4, aM_Count)) = _
            Application.WorksheetFunction.Transpose(aM_series.Values)
        End With
        aM_Count = aM_Count + 1
    Next
End Sub

writing vba code to show how to get data points from a graph in excel

Here, we declared some variables in the VBA code and used the With Statement to fix the position of the X Values from the extracted data. A For Each Loop then determines the position of the Y Values (Revenue).

  • Next, go back to your sheet where you want to get the data points and run the macro named “GetDataPoints” as it is our current Macro.

  • As a result, we will get the data points from the graph.
  • You can format the data cells to make it more presentable.

Thus, we will get data points from a graph in Excel in a tabular form.

Read More: How to Add Data Points to an Existing Graph in Excel (3 Ways)


2. Enabling Data Labels

This is an easy and quick way to get the data points from a graph. Here, we will not get the data in a tabular form. However, we will see it on the graph in a more convenient way.

Steps:

  • Firstly, click on the graph to select it.
  • Secondly, choose the plus sign to the top right corner of the graph.
  • Thirdly, from the available options, mark the rectangle beside the Data Labels option.

enabling data labels to show how to get data points from a graph in excel

  • Consequently, we will see the data points on the graph.

In this way, we will get the data points from a graph on the graph in Excel.

Read More: How to Exclude Data Points from Trendline in Excel


Practice Section

Here, I am giving you the dataset of this article so that, you can practice on your own and gain expertise.


Download Practice Workbook

You can download the practice workbook here.


Conclusion

In this article, we have discussed 2 ways of how to get data points from a graph in Excel. These methods will allow users to quickly get the values on the graph and interpret the graph more efficiently. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com, and unlock a great resource for Excel-related content.


Related Articles

Adnan Masruf
Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo