A line of best fit, also known as a trendline or best fit line, is a straight line used to represent a trending pattern in a scatter graph. To make this line by hand, you'd have to use a complex formula. Fortunately, Microsoft Excel has a reliable method of finding and representing a trendline by doing the calculations for you.
Best fit lines have many purposes and may help you significantly in visually representing your data.
Add a line of best fit trendline in Excel 2013 and up
In newer software versions, such as Excel 2019, the process of adding the best fit line to your charts is quite simple. The process requires you to create a chart first, and then add and customize the line to analyze your data correctly. This will create a line of best fit.
- Open the Excel document you want to add the best fit line to. Make sure there’s already data in the workbook.
-
Highlight the data you want to analyze with the line of best fit. The selected data will be used to create a chart.
-
Use the Ribbon interface and switch to the Insert tab. In the Charts panel, click on the Insert Scatter (X, Y) or Bubble Chart icon as shown on the image below.
-
Select the first Scatter chart option, once again visible on the image below. This is going to insert a Scatter chart into your document, using the data you previously highlighted.
-
Once the chart is inserted into your workbook, right-click on any of the data points. Select the Add Trendline option from the context menu.
-
You should see a pane open on the right side of the window, called Format Trendline. Here, look for the Trendline Options tab, and then select Linear from the available options.
-
At the bottom of the Trendline Options section, ensure that the checkbox next to Display Equation on Chart is enabled. This displays the math calculations used to create the best fit line. (Optional)
-
To customize how the line of best fit appears on the chart, switch to the Fill & Line and Effects tabs in the Format Trendline pane. You can fully customize how the line looks, allowing you to easily differentiate it from the rest of your chart.
- Click the X in the top-right corner of the Format Trendline panel to exit it. You should be able to work with your line of best fit inserted into the chart.
Tip: When your chart is selected, customize the style and colors using the Design tab. You can create unique charts formatted just the way you want them. Combine this with the trendline customization options to fully personalize your charts.
Add a line of best fit trendline in Excel 2010 and older
For older Excel releases, such as Excel 2010 and below, the process is slightly different. Don’t worry — the steps below will guide you through making this chart line.
- Open the Excel document you want to add the best fit line to. Make sure there’s already data in the workbook.
-
Highlight the data you want to analyze with the line of best fit. The selected data will be used to create a chart.
-
Switch to the Insert tab. Click on Scatter in the charts category, and then select the first scatter chart on the list. (See image below)
-
Select the newly created chart, and then switch to the Layout tab in the Chart Tools section. Here, expand the Trendline section and click on More Trendline Options.
-
The Format Trendline window will pop up on the screen. First, select Polynomial from the Trend/Regression Type section. Next, ensure that the checkbox next to the Display Equation on chart is enabled.
- Click the Close button, and then check your chart. The line of best fit should appear visible now.
Final thoughts
If you need any further help with Excel, don’t hesitate to reach out to our customer service team, available 24/7 to assist you. Return to us for more informative articles all related to productivity and modern-day technology!
Would you like to receive promotions, deals, and discounts to get our products for the best price? Don’t forget to subscribe to our newsletter by entering your email address below! Receive the latest technology news in your inbox and be the first to read our tips to become more productive.