How to Add a Line of Best Fit in Excel (Trendline Guide)
A line of best fit — also called a trendline — is a straight or curved line drawn through a scatter plot that best represents the overall direction of your data. In Excel, adding one takes just a few clicks, but understanding which type of trendline to add, and how to interpret it, requires a bit more thought. Here's a clear walkthrough of the process and the decisions involved.
What a Line of Best Fit Actually Does
When you plot data points on a chart, they rarely form a perfect pattern. A line of best fit uses regression analysis to draw a line that minimizes the distance between itself and all the data points — specifically, it minimizes the sum of squared differences. This gives you a visual summary of the trend in your data, which is useful for spotting patterns, making rough predictions, and communicating relationships clearly.
Excel handles the math automatically. You don't need to calculate anything manually — you just need to choose the right chart type and the right trendline option.
Step-by-Step: Adding a Trendline in Excel
Step 1 — Create a Scatter Plot (or Compatible Chart)
Trendlines work best with scatter plots (XY Scatter). You can also apply them to line charts, bar charts, and column charts, but scatter plots are the standard choice for showing relationships between two numerical variables.
To create one:
- Select your data range (two columns — one for X values, one for Y values)
- Go to Insert → Charts → Scatter
- Choose Scatter with only Markers
Step 2 — Add the Trendline
Once your chart is created:
- Click on any data point in the chart to select the data series
- Right-click and choose "Add Trendline"
- The Format Trendline panel will open on the right side of the screen
Alternatively, click the "+" (Chart Elements) button that appears next to the chart when it's selected, then check Trendline.
Step 3 — Choose Your Trendline Type
This is the decision that matters most. Excel offers six trendline types:
| Trendline Type | Best Used When |
|---|---|
| Linear | Data follows a straight-line trend |
| Exponential | Data rises or falls at an increasing rate |
| Logarithmic | Data changes quickly then levels off |
| Polynomial | Data fluctuates with curves (specify degree 2–6) |
| Power | Data follows a power-law relationship |
| Moving Average | You want to smooth out short-term fluctuations |
For most everyday use cases — sales over time, height vs. weight, temperature trends — Linear is the right starting point.
Step 4 — Display the Equation and R² Value
In the Format Trendline panel, scroll down and check:
- ✅ Display Equation on chart — shows the formula Excel used (e.g., y = 2.3x + 14)
- ✅ Display R-squared value on chart — shows how well the trendline fits your data
📊 The R² value (R-squared) ranges from 0 to 1. A value closer to 1 means the trendline closely fits the data. A value closer to 0 means the trendline is a poor fit — which may signal that a different trendline type would be more appropriate, or that the data simply doesn't have a strong linear trend.
Step 5 — Extend the Trendline (Optional)
If you want to project the trend forward or backward, use the Forecast options in the Format Trendline panel:
- Forward — extends the line beyond your existing data range
- Backward — extends it before your data starts
Enter the number of periods you want to project. This is useful for rough forecasting but should be interpreted carefully — Excel is extrapolating, not predicting.
Formatting Your Trendline
Once added, you can customize the trendline's appearance:
- Click directly on the trendline to select it
- Right-click → Format Trendline
- Adjust line color, weight (thickness), and dash style to make it stand out from your data points
A common choice is a bold dashed line in a contrasting color — this clearly distinguishes the trendline from the raw data without obscuring the points.
Common Issues and What Causes Them
Trendline option is greyed out: This usually means your chart type doesn't support trendlines (e.g., pie charts, 3D charts, stacked charts). Switch to a standard 2D scatter or line chart.
The trendline looks wrong or misleading: A linear trendline on non-linear data can misrepresent the relationship. Always check the R² value — if it's below 0.7, consider trying other trendline types.
The equation shows unexpected values: If your X-axis uses dates, Excel converts them to serial numbers internally, which can produce large or confusing coefficients in the equation. This doesn't affect the visual line — but it does complicate manual calculations using the equation.
What Determines the Right Approach for Your Data 🔍
Several factors shape which trendline setup makes sense:
- Data distribution — Is the relationship between your variables roughly linear? Curved? Does it flatten out over time?
- Purpose — Are you presenting to stakeholders who need a clean visual trend, or doing actual analysis where R² and the equation matter?
- Data volume — A trendline through 5 data points tells a very different story than one through 500
- Excel version — The core trendline feature has been stable for many versions, but the interface layout differs slightly between Excel 2016, 2019, Microsoft 365, and Excel for Mac
- Chart type already in use — If your chart is already a bar or column chart, trendlines are supported but may be harder to read visually
A researcher working with experimental data will approach trendline selection differently than a sales manager tracking quarterly revenue — and both will interpret the R² value with different levels of rigor. The technique is the same; what "good enough" looks like varies considerably depending on the context you're working in.