How to Create a Line of Best Fit in Excel


In statistics, a line of best fit is the line that best “fits” or describes the relationship between a predictor variable and a response variable.

The following step-by-step example shows how to create a line of best fit in Excel.

Step 1: Enter the Data

First, let’s enter the following dataset that shows the number of hours spent practicing and the total points scored by eight different basketball players:

 

Step 2: Create a Scatter Plot

Next, let’s create a scatter plot to visualize the relationship between the two variables.

To do so, highlight the cells in the range A2:B9, then click the Insert tab along the top ribbon, then click the option titled Scatter in the Charts group:

The following scatter plot will automatically be created:

Step 3: Add the Line of Best Fit

To add a line of best fit to the scatter plot, click anywhere on the chart, then click the green plus (+) sign that appears in the top right corner of the chart.

Then click the arrow next to Trendline, then click More Options:

In the Format Trendline panel that appears, click the button next to Linear as the trendline option, then check the box next to Display Equation on chart:

The line of best fit along with the equation for the line will appear on the chart:

line of best fit in Excel

Step 4: Interpret the Line of Best Fit

From the chart we can see that the line of best fit has the following equation:

y = 2.3095x – 0.8929

Here is how to interpret this equation:

  • For each additional hour spent practicing, average points scored increases by 2.3095.
  • For a player who practices zero hours, average points scored is expected to be -0.8929.

Note that it doesn’t always make sense to interpret the intercept value in a regression equation.

For example, it’s not possible for a player to score negative points.

In this particular example, we’re mostly interested in the value for the slope of the regression line which is 2.3095.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Perform Simple Linear Regression in Excel
How to Perform Multiple Linear Regression in Excel
How to Calculate R-Squared in Excel

2 Replies to “How to Create a Line of Best Fit in Excel”

  1. wow your site is amazing. Answered my questions- “to the point” and helped me to get my Excell plots perfect! thank you!!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *