What a trendline does and when you need one
A trendline is a line that Excel draws through your data points to show the overall direction they are moving. If you have monthly sales figures that bounce up and down, a trendline smooths out the noise and shows whether sales are climbing, falling, or flat. Excel calculates the line mathematically so it sits as close as possible to all your points at once.
You add a trendline when you want to see a pattern that the raw numbers hide. A spreadsheet of daily temperatures might look chaotic, but a trendline shows whether the season is warming or cooling. A chart of website visits over six months might show daily swings, but a trendline reveals whether traffic is growing or shrinking overall.
Trendlines only work on charts that already exist — you cannot add one to raw numbers in cells. If you have not yet made a chart from your data, you will need to create one first.
Key Takeaways
- A trendline appears only on a chart, not on the spreadsheet itself, so you must create a chart before you can add one.
- Right-click on the data series (the dots or bars on your chart) and select "Add Trendline" to open the menu where you choose the line type.
- Linear trendlines work best for data that moves steadily up or down; exponential and polynomial lines fit curved or accelerating patterns.
- You can display the equation and R-squared value on the chart itself, which tells you how closely the trendline matches your actual data.
The steps to add a trendline to an existing chart
Start with a chart that is already on your spreadsheet. Click anywhere on the chart to select it — you will see a border around it. Then click on one of the data points (the dots, bars, or other markers that represent your numbers). All the data points in that series will highlight slightly, showing they are selected.
Right-click on any highlighted data point. A menu will appear. Look for the option that says "Add Trendline" and click it. A panel will open on the right side of your screen (in newer versions of Excel) or a dialog box will pop up (in older versions). This is where you choose what kind of line to draw.
The default choice is usually "Linear", which draws a straight line. This works well for data that is climbing or falling at a steady rate. If your data curves or accelerates, you may want to try "Exponential" or "Polynomial" instead. Start with Linear and only switch if the line does not seem to match your data well.
Once you have chosen your line type, the trendline appears on your chart when ready. You can close the panel or dialog box, and the line stays there.
Showing the equation and R-squared value
Excel can display two numbers on your chart that tell you how well the trendline fits your data. The equation shows the mathematical formula of the line — useful if you want to predict future values. The R-squared value (written as R²) ranges from 0 to 1 and tells you how closely the line matches your actual points. A value of 1 means the line passes through every point perfectly; a value of 0.8 or higher usually means a good fit.
To show these numbers, right-click on the trendline itself (not the data points this time). Select "Format Trendline". In the panel or dialog that opens, look for checkboxes labeled "Display Equation on chart" and "Display R-squared value on chart". Check both boxes if you want both numbers to appear.
The equation and R² value will now show somewhere on your chart, usually in the upper right corner. You can click and drag them to move them if they overlap your data.
Choosing between linear, exponential, and polynomial trendlines
Linear trendlines suit data that moves in a straight line — either steadily up, steadily down, or flat. Monthly revenue that grows by roughly the same amount each month is a good example. If you plot it and the data points seem to form a rough diagonal line, linear is the right choice.
Exponential trendlines fit data that accelerates — it starts slow and then speeds up, or starts fast and then slows down. Bacterial growth, viral spread, or compound interest often follow exponential patterns. If your chart shows points that curve upward or downward in a J or reverse-J shape, try exponential.
Polynomial trendlines bend and curve to follow more complex patterns. A quadratic polynomial (order 2) creates one curve; a cubic polynomial (order 3) creates two curves, and so on. These are useful for data that rises, falls, and rises again — like seasonal sales that peak in winter and summer but dip in spring and fall. Start with order 2 and increase only if the line still does not fit well.
You can add multiple trendlines to the same chart if you want to compare how well different line types fit. Right-click on the data series again and add another trendline with a different type. Excel will draw both lines so you can see which one follows your data more closely.
Removing or editing a trendline
If you want to delete a trendline, right-click on the line itself and select "Delete". The line disappears but your chart and data remain unchanged.
To change the line type after you have added it, right-click on the trendline and select "Format Trendline" (or "Edit Trendline" in older Excel versions). The same panel or dialog opens, and you can switch from Linear to Exponential, adjust the polynomial order, or change any other setting. The chart updates as soon as you make your choice.
Why your trendline might not look right
If the trendline does not seem to match your data, the most common reason is that you chose the wrong line type. A linear trendline drawn through curved data will look like it misses most of your points. Switch to Exponential or Polynomial and see if the fit improves.
Another reason is that your data contains outliers — one or two points that are far away from the rest. A single very high or very low value can pull the trendline toward it. If you suspect an outlier is a data entry error, fix it in your spreadsheet and the chart will update automatically. If it is real data, you may need to accept that the trendline will not fit perfectly.
Check your R² value. If it is below 0.5, the trendline is not capturing the pattern well, and you may need to try a different line type or reconsider whether a trendline is the right tool for this data.
Frequently Asked Questions
Can I add a trendline to a chart that has multiple data series?
Yes. Each data series (each set of dots or bars) can have its own trendline. Right-click on one series, add its trendline, then right-click on the next series and add a different one. Each line will be a different color so you can tell them apart.
What does the R-squared value mean in plain language?
R² tells you what percentage of the ups and downs in your data the trendline explains. An R² of 0.9 means the trendline accounts for 90% of the variation; an R² of 0.5 means it only explains half. Higher is better, but what counts as "good" depends on your field — some data is naturally messier than others.
Can I forecast future values using the trendline equation?
Yes, you can plug future dates or time periods into the equation to predict what the value might be. But remember that this assumes the pattern continues unchanged. Real data often shifts, so predictions further into the future are less reliable than those close by.
Why does my trendline look jagged instead of smooth?
You likely selected a polynomial trendline with too high an order. Lower the order number (try 2 or 3 instead of 5 or 6) and the line will smooth out. A jagged trendline usually means it is overfitting — trying too hard to touch every point instead of showing the overall trend.