We can add best fit line in excel when we have two columns of data that we wish to use in making a forecast or check for relationships and possible connections. In what follows, we look step-by-step at how to add line of best fit.
Figure 1 – How to insert Best Fit Line
How to Add Line of Best Fit
- We will select the range of cells that we want to chart and add a best fit line to. In our case, it is A2:B21.
Figure 2 – Highlight the area with the data
- We will click on Charts.
- We will select Scatter and then Marked Scatter or Bubble Scatter (depending on whether the computer is a MAC or Windows PC).
Figure 3 – Select Scatter and Pick Marked Scatter
- We can format the chart to look as we desire using the tabs under Charts, for instance, adding titles to the axis and removing gridlines.
Figure 4 – Under Charts, apply desired Formats and Layout options
After some formatting, we will have a chart like this before adding line of best fit excel;
Figure 5 – The Chart obtained after formatting before adding best fit line
We will click on Chart Layout under the Charts tab, and select Trendline; we can then select any desired trendline that match our scatter plot. In this case, we stick with Linear Trendline because we think it is the best fit line excel provides for our type of data.
Figure 6: Select a Trendline that match the Scatter plot data
- After we add the scatter plot line of best fit, we can format its color, line intensity, etc. as we desire under the Charts tab.
Figure 7 – We format “best fit line” features after plotting
And with line of best fit, we can easily infer the relationship between the variables plotted: Number of Customers and Price.