How to add a regression line to an Excel scatter chart?

by

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.

SalesRevenue
34545.41088
312.012868.19295
234.712542.02261
248.695832.54282
238.257.46383
242.631537.59338
101.48153.861853
148.816844.18228
348.487917.8444
347.377729.55875
264.624650.10131
218.18956.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 🙂