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.
Steps:
- To begin with, select the graph from which you want to extract the data points.
- 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.
- 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
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.
- 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
- How to Change Date Range in Excel Chart (3 Quick Ways)
- Excel Chart Disappears When Data Is Hidden (3 Solutions)
- How to Remove One Data Point from Excel Chart
- Show Only Dates with Data in Excel Chart
- How to Skip Data Points in an Excel Graph (with Easy Steps)
- Create Org Chart From Excel Data (With Easy Steps)
- How to Link Excel Data to PowerPoint Chart (3 Simple Methods)