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.
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 🙂