What a line of best fit does and why you might need one
A line of best fit is a straight line drawn through a scatter plot that shows the general trend in your data. Excel can draw this line for you automatically, and it lets you see whether two things move together, move apart, or have no clear relationship. If you have months of sales data and want to know whether revenue is climbing or falling, or if you have test scores and study hours and want to see if more studying predicts higher scores, a line of best fit answers that question visually.
The line itself is calculated using a method called least squares regression, which means Excel finds the single straight line that comes closest to all your data points. You do not need to understand the math — Excel does the work — but the result is a line that balances the distance from points above it against the distance from points below it.
Adding a line of best fit takes about 30 seconds once your data is in a scatter chart. The line appears on top of your existing points, and you can also ask Excel to show you the equation of that line and how well it fits your data.
Key Takeaways
- A line of best fit shows the overall trend in a scatter plot and requires a scatter chart, not a bar or line chart.
- You add the line by right-clicking the data series in your chart, selecting "Add Trendline", and choosing "Linear".
- You can display the equation and R-squared value on the chart itself to see how well the line matches your actual data.
- A line of best fit works only when you have two related variables plotted against each other, not for single columns of numbers.
Setting up your data and creating a scatter chart
Your data needs to be in two columns: one for your X values (the thing you are measuring on the horizontal axis) and one for your Y values (what you are measuring on the vertical axis). For example, if you are tracking study hours and test scores, put study hours in column A and test scores in column B. Each row is one data point — one student's hours and their score.
Select both columns including headers, then go to the Insert tab and click Scatter. Choose the first scatter chart option (points only, no lines). Excel will create a chart with your data points plotted as dots. If your chart shows connected lines instead of separate dots, you chose the wrong scatter type — delete it and start over with the points-only version.
Your chart does not need to be perfect at this stage. You are just getting the data into a visual form so you can add the trend line. The axes will adjust themselves, and you can resize or move the chart later.
Adding the line of best fit to your chart
Click on your scatter chart to select it. You should see a border around the chart and small handles at the corners. Now right-click directly on one of the data points (the dots themselves, not the background). A menu will appear. Look for Add Trendline and click it.
A panel will open on the right side of your screen (or a dialog box, depending on your Excel version). Make sure Linear is selected — this is the straight-line option and the most common choice. You will see other options like Exponential or Polynomial, but linear is what "line of best fit" usually means. Click OK or Close, and a straight line will appear through your data points.
The line is now part of your chart. If you want to move or resize the chart, you can do that without affecting the line. If you want to remove the line, right-click it and select Delete.
Showing the equation and R-squared value
The equation of your line tells you the exact relationship between your two variables. It takes the form y = mx + b, where m is the slope (how steep the line is) and b is where the line crosses the Y axis. If your equation is y = 2x + 5, it means for every one unit increase in X, Y increases by 2 units.
To display the equation, right-click the line itself and select Format Trendline. Check the box next to Display Equation on chart. The equation will appear as text on your chart. You can click and drag it to move it somewhere that does not cover your data points.
In the same panel, check Display R-squared value on chart. The R-squared value (also called R²) tells you how well the line fits your data. It ranges from 0 to 1. A value close to 1 means the line fits very well and your two variables are strongly related. A value close to 0 means the line is not a good fit and the relationship is weak. An R² of 0.85 or higher is generally considered a strong fit; below 0.5 suggests the line is not capturing the pattern very well.
Understanding what the line actually tells you
A line of best fit shows a trend, but it does not prove that one thing causes another. If you plot ice cream sales against temperature and get a strong upward line, that shows they move together — but the temperature does not cause the ice cream sales. Both respond to the season. This is an important distinction: correlation (moving together) is not the same as causation (one thing making another happen).
The line also assumes a straight-line relationship. If your data points form a curve or a U-shape, a linear trendline will not fit well, and your R² value will be low. In that case, you might try a polynomial trendline instead (right-click the line, choose Format Trendline, and select Polynomial with degree 2). But for most business and household data, linear is the right choice.
If your R² is very low (below 0.3), the line is not telling you much. Your two variables may not be related at all, or the relationship may be too complicated for a straight line to capture. In that case, the line of best fit is still technically correct — it is just not useful.
Common mistakes and how to fix them
The most common mistake is trying to add a trendline to a chart that is not a scatter plot. If you created a line chart or bar chart instead, the trendline option will not appear when you right-click. Delete the chart and create a scatter chart instead. The scatter chart is the only type that works with lines of best fit.
Another mistake is right-clicking the chart background instead of the data points. If you right-click the white space of the chart, you will get a different menu without the trendline option. Make sure you are clicking directly on one of the dots.
If you have multiple data series in one chart (for example, sales data for two different regions), right-clicking will add a trendline only to the series you clicked. If you want trendlines for both, right-click each series separately and add a trendline to each one.
When a line of best fit is not the right tool
A line of best fit works when you have two variables and want to see their relationship. It does not work if you are just looking at one column of numbers over time (like daily temperature readings with no second variable). In that case, you want a straightforward line chart, not a scatter plot with a trendline.
It also does not work if your data is categorical rather than numerical. If you are comparing sales across different product types or regions, a bar chart is more appropriate. A line of best fit needs numbers on both axes.
If you have a very small amount of data (fewer than five or six points), a line of best fit can be misleading because a line will always connect any two points perfectly, even if the relationship is not real. With more data, you can see whether the pattern holds across many observations.
Frequently Asked Questions
Can I add a line of best fit to a chart I already made?
Yes, as long as it is a scatter chart. Click the chart, right-click a data point, and select Add Trendline. If your chart is a line chart or bar chart, you will need to delete it and create a new scatter chart from your data.
What does a negative slope mean?
A negative slope means the line goes downward from left to right, so as X increases, Y decreases. If your equation shows y = -3x + 10, it means for every one unit increase in X, Y drops by 3 units. This indicates an inverse relationship between your two variables.
Can I change the color or style of the trendline?
Yes. Right-click the line and select Format Trendline. Look for the Line Color or Line Style options (the exact names vary by Excel version). You can make the line thicker, thinner, dashed, or a different color to make it stand out from your data points.
What if my data points are all over the place with no clear pattern?
The line of best fit will still appear, but your R² value will be very low (close to 0), which tells you the line is not a good fit. This is actually useful information — it means your two variables are not strongly related. You can still display the equation and R² value on the chart to show that the relationship is weak.
Do I need to know calculus to use a line of best fit?
No. Excel does all the calculation for you. You just need to understand what the line represents: the general trend in your data. You do not need to know how the least squares method works to use it.