Download Article
Download Article
This article was co-authored by wikiHow staff writer, Hannah Dillon. Hannah Dillon is a Technology Writer and Editor at wikiHow. She graduated with a B.A. in Journalism from North Dakota State University in 2013 and has since worked in the video game industry as well as a few newspapers. From a young age Hannah has cultivated a love for writing and technology, and hopes to use these passions in tandem to help others in the articles she writes for wikiHow.
There are 7 references cited in this article, which can be found at the bottom of the page.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 178,360 times.
Learn more...
This wikiHow teaches you how to create a line of best fit in your Microsoft Excel chart. A line of best fit, also known as a best fit line or trendline, is a straight line used to indicate a trending pattern on a scatter chart. If you were to create this type of line by hand, you'd need to use a complicated formula. Fortunately, Excel makes it easy to find an accurate trend line by doing the calculations for you.
Adding a Line of Best Fit in Excel
To add a trendline in Excel, right-click one of the data points in your chart, then click Add Trendline. If your data isn't formatted as a chart, highlight it and click Insert, then click the scatter chart icon. Choose the first option to create the chart.
Steps
Adding a Best Fit Line
Download Article
-
Highlight the data you want to analyze. The data you select will be used to create your scatter chart. A scatter chart is one that uses dots to represent values for two different numeric values (X and Y).[1] -
Click the Insert tab. It's at the top of Excel.Advertisement -
Click the Scatter icon on the Charts panel. It's in the toolbar at the top of the screen. The icon looks like several small blue and yellow squares—when you hover the mouse cursor over this icon, you should see "Insert Scatter (X, Y)" or Bubble Chart (the exact wording varies by version).[2] A list of different chart types will appear. -
Click the first Scatter chart option. It's the chart icon at the top-left corner of the menu. This creates a chart based on the selected data.[3] -
Right-click one of the data points on your chart. This can be any of the blue dots on the chart. This selects all of the data points at once and expands a menu.[4]- If you are using a Mac and don't have a right mouse button, hold down the Ctrl button as you click a dot instead.
-
Click Add Trendline on the menu. Now you'll see the Format Trendline panel on the right side of Excel.[5] -
Select Linear from the Trendline Options. It's the second option in the Format Trendline panel. You should now see a linear straight line that reflects the trend of your data.[6] -
Check the box next to "Display equation on chart." It's toward the bottom of the Format Trendline panel. This displays the math calculations used to create the best fit line.[7] This step is optional, but can be useful for anyone viewing your chart who wants to understand how the best fit line was calculated.[8]- Click the X at the top-right corner of the Format Trendline panel to close it.
Expert Q&A
Video
Tips
-
While your chart is selected, you can customize its colors and other features by experimenting with the options on the Design tab. Click Change Colors in the toolbar to choose a color scheme, and then scroll through the "Chart styles" options to find one that looks good for your data.[9]Thanks
References
- ↑ https://support.microsoft.com/en-us/office/select-cell-contents-in-excel-23f64223-2b6b-453a-8688-248355f10fa9
- ↑ https://support.microsoft.com/en-us/office/present-your-data-in-a-scatter-chart-or-a-line-chart-4570a80f-599a-4d6b-a155-104a9018b86e
- ↑ https://researchguides.library.vanderbilt.edu/c.php?g=69346&p=855833
- ↑ https://researchguides.library.vanderbilt.edu/c.php?g=69346&p=855833
- ↑ https://chemed.chem.purdue.edu/genchem/lab/datareports/excel/bestfit.html
- ↑ https://chem.libretexts.org/Ancillary_Materials/Laboratory_Experiments/Wet_Lab_Experiments/General_Chemistry_Labs/Online_Chemistry_Lab_Manual/Chem_11_Experiments/01:_Using_Excel_for_Graphical_Analysis_of_Data_(Experiment)
- ↑ https://mathstat.slu.edu/~may/ExcelCalculus/sec-1-5-IntroBestFitCurves.html
- ↑ https://chemed.chem.purdue.edu/genchem/lab/datareports/excel/bestfit.html
- ↑ https://support.microsoft.com/en-us/office/change-the-color-or-style-of-a-chart-in-office-f4db3f23-f5a1-4b30-abb3-62e8c2c33d9b
About This Article
1. Highlight the data for your chart.
2. Click the Insert tab.
3. Click the Scatter icon.
4. Click the first Scatter chart.
5. Right-click one of the data points on the chart.
6. Click Add Trendline.
7. Select "Linear."
8. Check the box next to "Display equation on chart."
Is this article up to date?
