A Step-by-Step Guide to Creating Best Fit Lines in Excel

Andrew J. Pounds, Ph.D
Departments of Chemistry and Computer Science
Mercer University

The assumption is that you have some data from your laboratory that you need to graph and that the data from your experiment needs to be fit to a best fit line. This is easily accomplished in Excel. Let us assume that you have recorded the average number of bananas consumed per hour for various sized cages of monkeys. In the diagram below I have entered the data.


I then need to highlight the data with the mouse.


After I have the data selected I go to the top menu and hit the "Insert" menu and select the "Chart" submenu. Once the chart menu comes up, select the "XY Scatter" chart type. The screen should then look like what is seen below.


Hit the NEXT button and you will get the screen below.


At this point Excel is going to let me customize my plot by adding a title and axis labels. You should do this at a minimum. Other customizations are also possible but will not be discussed here. I have entered a title and axis labels for the data collected.


After hitting NEXT, I am taken to a screen that asks where the new chart is to be drawn. Select "As new sheet" as shown in the figure below.


Once I have gotten to this point I can hit FINISH. Excel will then draw the chart in a new sheet in the current workbook and place me on that sheet. It will look something like the screen shot below.


Now the task is to add the best fit line. Excel calls this a trendline. To start this process select the "Chart" menu option and the "Add Trendline" menu suboption. You will be presented with a selection box that looks like the following. Select the "Linear" regression type as indicated.


Then select the "Options" tab. Add any custom title you want for the best fit line, and check the box to Display the equation on the chart as indicated below.


Once you have done this, select "OK" and the chart, with the associated best fit line should appear on your chart along with the equation for the best fit line.