How To Add a Line of Best Fit in Excel

Rick Akura on
Want to learn how to add a line of best fit in Microsoft Excel? This article will review the steps needed to add a trendline to your charts.

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.

Add a Line of Best Fit in Excel

 

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.

  1. Open the Excel document you want to add the best fit line to. Make sure there’s already data in the workbook.
  2. Highlight the data you want to analyze with the line of best fit. The selected data will be used to create a chart.
    highligh excel data
  3. 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.
    click Insert tab
  4. 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.
    select the first scatter chart option
  5. 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.
    Add trendline in excel chart
  6. 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.
    add trendline in excel
  7. 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)
    display equation on trendline
  8. 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.
    format excel trendline
  9. 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.

  1. Open the Excel document you want to add the best fit line to. Make sure there’s already data in the workbook.
  2. Highlight the data you want to analyze with the line of best fit. The selected data will be used to create a chart.
  3. 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)
    highlight > insert data
  4. 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.
    expand trendline > click more trendline options
  5. 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.
    display equation on chart
  6. 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.

You may also like

» How to Split Column in Excel
» How To Add and Remove Leading Zeros in Excel
» 14 Excel Tricks That Will Impress Your Boss

Tags: Product guides