In today’s Data Analysis tutorial we’ll learn how to create a scatter plot with a regression line in Microsoft Excel.
Create example data
We’ll get started by creating a very simple data set that you can easily copy into an Excel spreadsheet and follow along.
Sales | Revenue |
345 | 45.41088 |
312.0128 | 68.19295 |
234.7125 | 42.02261 |
248.6958 | 32.54282 |
238.2 | 57.46383 |
242.6315 | 37.59338 |
101.4815 | 3.861853 |
148.8168 | 44.18228 |
348.4879 | 17.8444 |
347.3777 | 29.55875 |
264.6246 | 50.10131 |
218.1895 | 6.150418 |
Insert an XY (scatter) plot in Excel
Adding an xy plot into your Excel spreadsheet is relatively easy. Just amke sure to follow the steps below:
- In Excel, select the columns for your scatter plot. Be sure to select the entire column content by using Ctrl + shift + down arrow.
- Then from the top menu, hit Insert.
- Next on the Charts section of the Ribbon, press the Insert scatter or Bubble chart button.
- Then go ahead and select your chart formatting, add a chart title and axis labels as needed.
- Here’s our chart:
Insert a regression trendline to Excel scatter plot
Next we’ll add the regression line to our xy graph:
- Click your chart.
- You’ll notice three buttons popping up to the right hands side of the chart.
- Hit the Chart Elements (+) button.
- Check the Trendline box.
- Hit the black arrow to the right and select Linear.
- Next hit More Options.
- In the Format Trendline pane select Display Equation on chart check box.
- You might want to re -position the equation text so it doesn’t interfere with the scatter markers.
- Feel free to use the Format Trendline pane capabilities to further modify the line color, compound / dash type etc’ by using the
Here is our xy chart, complete with the linear regression line 🙂