How to Add a Line of Best Fit in Excel (Trendline Guide)

A line of best fit — also called a trendline — is one of Excel's most practical charting tools. It draws a straight (or curved) line through your data points to reveal the underlying trend, helping you visualize patterns, make projections, and communicate data stories clearly. Here's exactly how it works and what affects your results.

What a Line of Best Fit Actually Does

When you plot data on a scatter chart or line chart, individual points rarely fall in a perfect line. A line of best fit uses statistical regression to calculate the line (or curve) that sits closest to all those points simultaneously — minimizing the total distance between the line and each data point.

In Excel, this is called a trendline, and it supports several regression types beyond simple straight lines. Understanding which type fits your data is just as important as knowing the steps to add one.

How to Add a Trendline in Excel 📊

The process is straightforward across modern versions of Excel (Microsoft 365, Excel 2019, Excel 2016):

1. Create a chart first — Trendlines only work on existing charts. A scatter plot (XY Scatter) is the most appropriate chart type for a true line of best fit, though trendlines also work on bar, line, and bubble charts.
2. Click on any data series in your chart to select it.
3. Right-click the selected series and choose "Add Trendline" from the context menu. Alternatively, click the "+" (Chart Elements) button that appears beside the chart and check Trendline.
4. The Format Trendline panel opens on the right side of your screen.
5. Select your trendline type (see below).
6. Optionally, check "Display Equation on chart" and "Display R-squared value on chart" for statistical detail.

That's the core workflow. The panel stays open so you can adjust settings in real time.

Trendline Types and When to Use Each

Excel offers six trendline options. Choosing the wrong one produces a misleading fit.

For most general use cases — sales trends, grade distributions, simple projections — Linear is the right starting point. If the straight line clearly misses clusters of your data, that's a signal to try a different type.

Understanding R-Squared Values

When you enable "Display R-squared value", Excel shows a number between 0 and 1 (written as R²). This is the coefficient of determination — a measure of how well the trendline fits your actual data.

• R² close to 1.0 → the trendline closely matches your data
• R² close to 0 → the trendline is a poor fit for the data pattern

An R² of 0.95, for example, means roughly 95% of the variation in your data is explained by the trendline. A low R² doesn't mean your data is wrong — it may mean you need a different trendline type, or that your variable simply has high natural variability.

Extending the Trendline for Forecasting

One underused feature: Excel lets you project the trendline forward or backward beyond your existing data range.

In the Format Trendline panel, under Forecast, enter the number of periods you want to extend:

• Forward — projects the trend into the future
• Backward — extrapolates the trend before your earliest data point

This is useful for rough projections, but worth treating with appropriate caution — a trendline extended far beyond your data range amplifies any inaccuracies in the model.

Variables That Affect Your Results 🔍

The line of best fit you get isn't just a function of clicking the right buttons. Several factors shape whether your trendline is actually meaningful:

• Sample size — Small datasets produce trendlines with high uncertainty. A trend across 8 data points is far less reliable than one across 80.
• Data quality — Outliers can significantly pull a linear trendline away from the main cluster of points. Consider whether outliers are valid data or data entry errors before plotting.
• Chart type — Trendlines on scatter charts use both X and Y values statistically. Trendlines on bar or column charts only evaluate the Y-axis values, which changes the mathematical behavior.
• Trendline type chosen — A polynomial trendline with a high degree (e.g., degree 6) can appear to fit data almost perfectly while actually being a poor predictive model — a problem called overfitting.
• Excel version — Functionality is consistent across Excel 2016, 2019, and Microsoft 365, but the interface layout differs slightly. Web-based Excel (Excel for the web) has limited trendline options compared to the desktop application.

What the Equation on the Chart Tells You

Enabling "Display Equation on chart" shows the algebraic formula Excel used to draw the trendline. For a linear trendline, this looks like y = mx + b, where:

• m = the slope (rate of change)
• b = the y-intercept (where the line crosses the Y-axis)

You can use this equation manually in Excel cells with the FORECAST or TREND functions if you want to calculate predicted values programmatically rather than reading from a chart visually.

When the Same Steps Lead to Different Outcomes

Two people following identical steps can get very different trendlines depending on what they're working with. A dataset tracking monthly revenue over two years with consistent growth will produce a clean, high-R² linear trendline. A dataset tracking daily stock prices or website traffic with high day-to-day volatility may produce a weak linear fit regardless of how the trendline is added — and might be better served by a moving average instead.

The mechanics of adding a trendline in Excel are fixed and consistent. What varies — and what determines whether the result is actually useful — is the nature of the data itself, the question you're trying to answer, and which trendline type honestly reflects the relationship in your dataset.