How to Draw Best Fit Line in Excel (3 Simple Ways)

Excel shines as a powerful data analysis software. It analyzes your data through different charts. One of the fundamental aspects of data analysis is linear regression. This involves finding the relationship between two or more variables. To visualize the trend or pattern in the data, you may need to know how to draw the best fit line by plotting in Excel.

Have you ever tried to draw the best fit line in your graph? In the below image, the red line indicates the best fit line.

In this article, we will show you how to draw the best fit line in Excel. So, let’s get started.

What Is the Best Fit Line, and What Are Its Benefits?

The line of best fit is the straight line that shows the relationship between two variables in a dataset. Commonly, it is also known as “linear regression”. It helps us predict the value of an independent variable with respect to the dependent variable based on the relationship.

It has the following benefits.

• Visualization: it shows you a general trend of the data. So, you will be able to identify any unusual data points or outliers points of your graph. Also, it provides a clear and concise way to understand the relation between two points.
• Prediction: while extrapolating the line, you can calculate what is the value of a dependent variable with the value of a known variable.
• Correlation: The best fit line allows you to determine the correlation between two variables. A positive relation indicates that the variable is moving in the same direction, while a negative correlation means that they are moving oppositely.
• Simplification: it helps you to understand the nature of your graph, and it simplifies any complex dataset in an easy-to-understand line.

As we want to draw the best fit line in Excel, we need to take a dataset of two variables. Here, we have taken a dataset of the “Solubility of NaOH at Different Temperatures”. You can generate a chart with the help of this dataset and then draw the best fit line.

Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.

Use Chart Wizard to Draw Best Fit Line in Excel

When you try to draw the best fit line, you need to create a chart. We have generated a scatter chart in this article so that you can relate the outliers’ points and visualize the best fit line in a better way. Follow the instructions stated below to draw the best fit line in Excel.

Steps:

• Initially, you need to select the entire dataset and navigate to the Insert tab for inserting a scatter chart.
• Then, under the Charts group select Insert Scatter (X, Y) or Bubble Chart >> pick Scatter.

• A scatter chart will be plotted where we will draw the best fit line.

• Sequentially, right-click on any of the data points and choose to Add Trendline option from the Context Menu.

• A dotted trendline will be placed on the chart, representing the best fit line. We chose a Linear trendline from the Trendline Options.

You can format the trendline according to your preferences from the Format Trendline sidebar. We have formatted our trendline to a solid one. This line indicates the best fit line of your corresponding chart.

Apply Trendline Option from Chart Elements to Draw Best Fit Line

In this method, we will plot a scatter chart with straight lines. You can understand the outliers’ points more easily here. Not all the points are connected in the best fit line. Inserting the chart is quite easy. Follow the below steps to do it.

Steps:

• Firstly, select the entire dataset >> hover over Insert tab >> choose Insert Scatter (X, Y) or Bubble Chart >> pick Scatter with Straight Lines and Markers.

• Now, click on the chart and choose the ➕ (plus) sign representing the Chart Elements. Check the Trendline box and select Linear there.

A linear trendline will be added to the chart. This is our best fit line for this chart. Format the line as per your preference.

Note: Here, both methods involve creating a scatter plot with data points and then adding a trendline to the plot. The only difference is in the way the trendline is added. In the first method, we use the “Add Trendline” option in the context menu after right-clicking on a data point, while in the second method, we use the “Trendline” option in the  “Chart Elements” group.

Read More: How to Create Trend Chart in Excel

Use VBA Macro to Draw Best Fit Line in Excel

This time we will draw the line automatically with the help of VBA macros. VBA macros automate the process of drawing the best fit line, making it faster and more efficient than manually drawing the line. This saves time and reduces the chance of errors. This ensures that the line accurately represents the relationship between the variables. Follow the below steps to use the VBA code.

Steps:

• First, select the Developer tab >> choose Visual Basic.

• The Visual Basic Editor window appears. Select Insert tab >> Module >> Module1.

• In the editor, write up the following VBA code.

Sub AddTrendline()
Dim cht As Chart
Dim ser As Series
Set cht = ActiveSheet.ChartObjects("Chart 2").Chart
Set ser = cht.SeriesCollection(1)
' Add a trendline to the series
ser.Trendlines.Add Type:=xlLinear, _
DisplayEquation:=False, DisplayRSquared:=False
' Format the trendline
With ser.Trendlines(1)
.Border.Weight = xlThin
.Border.Color = RGB(225, 0, 0)
End With
End Sub

Run the code with the F5 key, and you will get the best fit line like the image below.

How to Show the Equation of Best Fit Line in Excel

We have already shown you how to draw the best fit line or trendline in your chart. This trendline must follow a linear equation. You can show this equation in your chart.

• To do this, left-click on the trendline, and you will get the Format Trendline sidebar.
• From the Trendline Options, check the Display Equation on chart box. As you can see, the equation is shown in the chart. See the below image for better visualization.

Read More: How to Show Equation in Excel Graph

How to Calculate the Slope of Best Fit Line in Excel

In the above discussion, we show you how to insert the equation. If you notice the equation carefully, you can see it is an equation of a straight line that follows the trend of y=mx+b, where m is the slope. So when we displayed the equation of our best fit line, we could see the equation is y=0.6002x+87.909. In this case, the slope is 0.6002. We can get any value of y for a value of x from the equation.

Read More: How to Find Slope of Trendline in Excel

Things to Remember

• The accuracy of the best fit line relies on the quality and quantity of your data. If you have a small sample size or a lot of variability in your data, the line may not accurately represent the correlation between the variables.
• You need to be cautious while extrapolating outside the line. The line may not behave accurately for an outside point.

Download Practice Workbook

Download the following practice workbook. It will help you understand the topic more clearly.

Conclusion

So, that’s the end of today’s article. We strongly believe you understand the above methods clearly enough to draw the best fit line in Excel. If you use a scatter chart to draw the best fit line, it is easier to analyze or interpret the data. Whether you’re a student, researcher, or professional, knowing how to draw the best fit line in Excel can help you make informed decisions and gain valuable insights from your data. But if you find any difficulties in drawing the best fit line, feel free to knock us through comments. Till then, goodbye. Happy Excelling!

Frequently Asked Questions

• Can we customize the appearance of the best fit line in Excel?

Yes, you can alter the hue, thickness, and design of the best fit line in Excel to suit your preferences. Right-click the trendline and choose “Format Trendline” from the context menu to accomplish this. To make your intended adjustments, select “Line Color,” “Line Style,” and “Line Weight” from the choices.

• How can we remove a best fit line from a chart in Excel?

Simply click the trendline to select it, and hit the “Delete” key on your computer to delete the best fit line from an Excel chart. Alternatively, you can use the context option when you right-click on the trendline and choose “Delete“.

• Can we add multiple best fit lines to a chart in Excel?

In Excel, you can include more than one best fit line in a graph. To add a trendline to each data set you wish to do so, just follow the same procedure.

• Can we edit the data used to create the best fit line in Excel?

Yes, you can change the data that Excel used to generate the best match line. Click the “Select Data” option in the “Data” menu of the Excel ribbon after choosing the data set you want to change in the graphic. The region of cells that the data series pertains to can then be modified from there.

• Can we copy a best fit line from one chart to another in Excel?

In Excel, a best fit line can be copied from one figure to another. Simply pick the trendline-containing graphic, perform a right-click, and choose “Copy” from the context menu. Then, after choosing the chart you want to transfer the trendline to, use the context menu to right-click the chart and choose “Paste.”

Related Articles

• How to Make a Polynomial Trendline in Excel
• How to Plot Quadratic Line of Best Fit in Excel
• How to Insert Trendline in an Excel Cell
• How to Calculate Trend Analysis in Excel
• How to Create Monthly Trend Chart in Excel
• How to Calculate Trend Percentage in Excel

<< Go Back To Trendline in Excel | Excel Charts | Learn Excel