Excel has a powerful graphing feature that can add visual depth and clarity to even the most mundane of datasets. Though Excel does most of the heavy lifting, you may wish to edit some aspects of your chart. For instance, you may need to add data points to an existing chart. Hence, in this article, we’ll demonstrate 3 ways how to add data points to an existing graph in Excel. In addition, we’ll also discuss how to get data points from an Excel chart.
Download Practice Workbook
3 Ways to Add Data Points to an Existing Graph in Excel
First and foremost, let’s assume the Population Growth of USA dataset shown in the B4:C12 cells which contain the Year and the Population in Millions respectively.
Using the above dataset, you’ll get the following Excel graph.
Now, let’s see the process of adding data points to the existing graph in detail and with the necessary illustrations. Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
Method 1: Inserting a Single Data Point
First of all, let’s start with a simple example of adding a single data point to an existing graph in Excel. Here, we want to insert a Scatter Plot, therefore, just follow the steps shown below.
📌 Steps:
- First, select the C5:C12 cells >> go to the Insert tab >> choose the Scatter option.
- Next, select the chart >> press the Select Data option.
Now, this opens the Select Data Source window.
- Second, click the Edit button.
- Then, enter the Series name, here it is the “Population Growth of USA”.
- In turn, select the Series X and Y values that correspond to the B4:B12 and C4:C12 ranges respectively.
- Third, we’ll add the predicted “Population of 372 million” for the “Year 2030” as highlighted in the image below.
- From this point, select the chart >> drag the Resizing Handle to add this new data point.
Afterward, we can format the chart using the Chart Elements option.
- In addition to the default selection, enable the Axes Title to provide axes names. Here, it is the “Year” and “Population in Millions”.
- Now, add the Chart Title, for example, “Population Growth of USA”.
- Lastly, you can disable the Gridlines option to give your chart a clean look.
Eventually, this should generate the chart as shown in the figure below.
Read More: How to Skip Data Points in an Excel Graph (with Easy Steps)
Method 2: Using Resizing Handles
Now, I know what you’re thinking. Is there a quicker way? Lucky you! Assuming you’re in a hurry, our next method provides the answer to adding a new data series to an existing Line Graph.
Suppose we have the Breakdown of Marketing Expense and Revenue dataset shown in the B4:D13 cells below, which depicts the Month number, the Marketing Expense, and the Revenue in USD respectively. Hence, let’s see it in action.
📌 Steps:
- Initially, choose the C5:C13 cells >> navigate to the Insert tab >> select the Lines with Markers option.
- Then, click to select the chart >> drag the Resizing Handles to add the x-axis values.
- In a similar style, again drag the Resizing Handles to add a new series, in this case, “Revenue” to the graph.
Now, apply formatting to the chart with the Chart Elements option.
- First, check the Axes Title to name the axes. Here, it is “Month” and “US Dollar”.
- Second, add the Chart Title, for instance, “Breakdown of Marketing Expense and Revenue”.
- Third, insert the Legend option to show the “Marketing Expense” and “Revenue” series.
- Lastly, uncheck the Gridlines option to give your chart a clean look.
Finally, the output should look like the plot shown in the screenshot below.
Read More: How to Select Data for a Chart in Excel (2 Ways)
Similar Readings
- Create Excel Chart Using Data Range Based on Cell Value
- How to Group Data in Excel Chart (2 Suitable Methods)
- Add Data Table in an Excel Chart (4 Quick Methods)
- How to Import and Use Data into Power Pivot in Excel
- Create a Scatter Chart in Excel (with Easy Steps)
Method 3: Utilizing Select Data Option
Alternatively, we can also employ the Select Data option to add data points to an existing graph in Excel. In this situation, let’s consider the data for Stock Price of Companies in the B4:D13 cells, which display the Company name and the 2020 and 2021 Stock Prices respectively.
📌 Steps:
- To begin with, select the C5:C13 cells >> jump to the Insert tab >> click the Insert Column or Bar Chart option.
- Later, select the chart >> hit the Select Data option.
- Next, press the Edit button to add the x-axis labels.
- Now, highlight the B5:B13 cells as the Axis label range >> click OK.
- Following this, click the Edit button to enter a name for the data series.
- Likewise, choose the C4 cell (“Stock Price in 2020”) as the Series name >> hit the OK button.
- Afterward, select the Add option to add the data points for a new series.
- In turn, select the D4 cell (“Stock Price in 2021”) for the Series name >> choose the D5:D13 cells as Series Values.
Finally, apply the formatting to the chart as shown in the previous method to obtain the output shown in the image below.
Read More: How to Add Data to an Existing Chart in Excel (5 Easy Ways)
How to Get Data Points from a Graph in Excel
Last but not least, sometimes we may want to get the data points from a graph; in that case, apply the VBA Code below. It’s simple and easy, just follow along.
📌 Steps:
- First of all, select the chart >> navigate to the Developer tab >> click the Visual Basic button.
Eventually, the Visual Basic Editor window pops out.
- Second, go to the Insert tab >> select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub Extract_Chart_Data()
Dim x_axis_num As Integer
Dim x_axis_series As Object
On Error Resume Next
x_axis_count = 2
x_axis_num = UBound(Application.ActiveChart.SeriesCollection(1).Values)
Application.Worksheets("Get_Chart_Data").Cells(1, 1) = "X Values"
With Application.Worksheets("Get_Chart_Data")
.Range(.Cells(2, 1), _
.Cells(x_axis_num + 1, 1)) = _
Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
End With
For Each x_axis_series In Application.ActiveChart.SeriesCollection
Application.Worksheets("Get_Chart_Data").Cells(1, x_axis_count) = x_axis_series.Name
With Application.Worksheets("Get_Chart_Data")
.Range(.Cells(2, x_axis_count), _
.Cells(x_axis_num + 1, x_axis_count)) = _
Application.WorksheetFunction.Transpose(x_axis_series.Values)
End With
x_axis_count = x_axis_count + 1
Next
End Sub
⚡ Code Breakdown:
Here, we’ll explain the VBA code to get data points from a chart.
- In the first portion, the sub-routine is given a name, here it is Extract_Chart_Data().
- Next, define the variables x_axis_num and x_axis_series as Integer and Object data types respectively.
- In the second potion, set the x_axis_count to 2 and apply the UBound function to get the largest value.
- In addition, use the Worksheet object to define the worksheet name, here it is “Get_Chart_Data” and return the x-axis values into this worksheet.
- Then, utilize the With statement to loop through all the x-axis values and return the values in the specified worksheet.
- In the third portion, combine the For Loop and the With statement to iterate through and extract the y-axis values and the series names.
- Lastly, return them to the adjacent cells.
- Third, click the Run button or press the F5 key to run the VBA code.
Subsequently, the final results appear in the “Get_Chart_Data” worksheet as shown in the picture below.
Read More: How to Edit Chart Data in Excel (5 Suitable Examples)
💡 Things to Remember
Furthermore, let’s discuss an important issue when using the VBA Code to extract data points from an Excel chart.
- Specifically, make sure to select a chart and then run the VBA code, otherwise, the program returns only the string of text “X Values”.
- Additionally, make sure to rename your worksheet to “Get_Chart_Data” or edit the VBA code to enter a worksheet name according to your preference.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
To sum up, we hope this tutorial has provided you with helpful knowledge on how to add data points to an existing graph in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.
Related Articles
- How to Create Org Chart From Excel Data (With Easy Steps)
- Link Excel Data to PowerPoint Chart (3 Simple Methods)
- How to Combine Daily and Monthly Data in Excel Chart
- Selecting Data in Different Columns for an Excel Chart
- How to Create Graph from List of Dates in Excel (with Easy Steps)
- Expand Chart Data Range in Excel (5 Suitable Methods)
- How to Change Data Source in Excel Chart (3 Useful Examples)