Adding a trend line shows the direction your data is moving

A trend line is a straight or curved line that Excel draws through your data points to show the overall pattern. If you have sales numbers going up month by month, a trend line makes that upward movement obvious at a glance. Excel calculates the line automatically once you tell it which data to use.

The process takes about 30 seconds: select your chart, right-click the data series (the bars or points themselves, not the background), and choose "Add Trend Line". Excel then shows you options for the type of line and whether to display the equation or R-squared value on the chart.

Trend lines work on most chart types — column charts, line charts, scatter plots, and area charts. They do not work on pie charts or doughnut charts because those chart types do not show progression over time or categories.

Key Takeaways

  • Right-click the data series itself (the bars or points), not the chart background, to access the trend line option.
  • Excel offers five trend line types: linear, exponential, logarithmic, polynomial, and power, each showing different patterns in your data.
  • The R-squared value tells you how well the trend line fits your actual data — closer to 1.0 means a better fit.
  • You can display the trend line equation on the chart so others see the mathematical relationship, or hide it if the visual line alone is enough.

How to insert a trend line in three steps

Start with a chart already in your spreadsheet. Click anywhere on the chart to select it — you will see a border around the entire chart area. Then click specifically on one of the data points or bars. When you click the data series itself, all the points or bars will show selection handles (small squares) around them.

Right-click on any of those selected points or bars. A menu will appear. Look for "Add Trend Line" — it is usually near the top of the menu. Click it. The Format Trend Line panel will open on the right side of your screen (in Excel 2016 and newer) or as a dialog box (in older versions).

The default is a linear trend line, which works for most situations. If your data curves rather than moving in a straight line, you can change the type in the Format Trend Line panel. Click the chart to close the panel when you are done. The trend line now appears on your chart.

Choosing the right trend line type for your data

Excel offers five trend line types, and picking the right one depends on how your data actually moves. A linear trend line is a straight line and works when your data rises or falls at a steady rate — sales increasing by roughly the same amount each month, for example. This is the default and the one you will use most often.

An exponential trend line curves upward and works when your data accelerates — like bacteria growth or viral spread, where the rate of increase itself increases. A logarithmic trend line curves the opposite way, flattening out over time, and fits data that rises quickly at first then levels off — like how much value you get from each additional hour of studying.

A polynomial trend line can curve up and down multiple times and works for data with several peaks and valleys. You choose the degree (2 for one curve, 3 for two curves, and so on). A power trend line is less common and works for data where one variable is proportional to another raised to a power.

To change the type: right-click the trend line itself (not the data points), choose "Format Trend Line", and select a different type from the panel. Excel will redraw the line to match your choice. If the new line looks worse than the original, switch back to linear.

Understanding R-squared and the trend line equation

When you add a trend line, Excel can show you two pieces of information: the equation and the R-squared value. The equation is the mathematical formula that describes the line — something like y = 2.5x + 10. You do not need to understand the math; the equation is mainly useful if someone else needs to calculate predicted values using the same relationship.

The R-squared value (written as R²) tells you how well the trend line actually fits your data. It ranges from 0 to 1. An R² of 0.95 means the trend line explains 95 percent of the variation in your data — a very good fit. An R² of 0.60 means the trend line only explains 60 percent, so your actual data points scatter quite a bit around the line.

To display these on your chart: right-click the trend line, choose "Format Trend Line", and check the boxes for "Display Equation on chart" and "Display R-squared value on chart". The values will appear as text on the chart itself. You can click and drag them to move them if they overlap other elements.

Moving or deleting a trend line

Once a trend line is on your chart, you can adjust it. To move the equation or R² text, click on the text itself and drag it to a new location. To change the line's appearance — its color, thickness, or style — right-click the trend line and choose "Format Trend Line". The panel shows options for line color, width, and dash style.

To remove a trend line entirely, right-click it and choose "Delete". The line disappears but your data and chart remain unchanged. You can add a new trend line at any time using the same process.

If you want to try different trend line types to see which fits best, add one, check its R² value, then delete it and try another. The R² value is the quickest way to compare which type matches your data most closely.

Common reasons a trend line looks wrong

If your trend line does not appear or looks strange, the most common cause is that you right-clicked the chart background instead of the data series itself. The chart background is the white or colored area behind the data. The data series is the actual bars, points, or line that represents your numbers. Make sure you click directly on a bar or point, see the selection handles appear, then right-click.

Another issue is choosing the wrong trend line type for your data. If you selected polynomial but your data is actually linear, the curve will look like it is fighting against your points. Switch back to linear and check the R² value. A linear trend line with R² of 0.92 is usually better than a polynomial with R² of 0.88.

If your chart has multiple data series (multiple sets of bars or lines), you can add a separate trend line to each one. Right-click each series individually and add its own trend line. Each will appear in a different color by default.

Frequently Asked Questions

Can I add a trend line to a chart I did not create myself?

Yes. Click the chart to select it, then follow the same steps. You do not need to have created the chart originally — as long as it is in your spreadsheet and you can click on it, you can add a trend line to any of its data series.

What does it mean if my R-squared value is very low?

A low R² (below 0.70) means your data does not follow a clear pattern, or the pattern is not a straightforward line or curve. This can happen if your data is noisy or affected by many different factors. It does not mean the trend line is wrong — it just means the relationship is weaker or more complex than a single trend line can show.

Can I add a trend line to a scatter plot?

Yes. Scatter plots are actually one of the best places to use trend lines because they show the relationship between two variables. Right-click any data point in the scatter plot and add a trend line the same way you would for any other chart type.

What if I want to forecast future values using the trend line?

You can extend the trend line forward by right-clicking it, choosing "Format Trend Line", and entering a number in the "Forward" field under "Forecast". This extends the line beyond your actual data to show where the pattern would go if it continued. The equation displayed on the chart can also be used to calculate predicted values manually.

Do I need to update the trend line if my data changes?

No. Excel automatically recalculates the trend line whenever your underlying data changes. If you add new rows of data to your chart, the trend line adjusts itself to fit the new points without any action from you.