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:
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.
The following tutorials explain how to perform other common tasks in Excel: