How to Add a Trendline in Excel: Complete Step-by-Step Guide

You finally have your sales numbers plotted on a chart, but the dots look messy. Where is the actual pattern hiding? That is exactly what a trendline does for you in Excel. It draws a clean line through scattered data so the direction jumps out, whether your numbers are climbing, falling, or doing something stranger.

Most people only know one type of trendline. Excel actually gives you six, and choosing the wrong one will make your forecast look silly. So we will walk through every option, the right keyboard path to add one, the formulas behind each model, and a couple of tricks that even experienced analysts miss.

By the end of this guide, you will know how to add a trendline to a chart in three clicks, when to use exponential versus polynomial, how to display the R-squared value, and how to project a forecast into the future. Excel does not make any of this obvious in the menus, but once you see where the controls live, it becomes second nature.

What Is a Trendline in Excel?

A trendline is a best-fit line that Excel draws through your data points based on a mathematical formula. Think of it as Excel asking: if I had to summarize this entire chart with one curve, what would it look like? The software then runs a regression in the background and plots the result on top of your existing series.

Trendlines are only available on certain chart types. Scatter plots, line charts, bar charts, column charts, and area charts all support them. Pie charts, doughnut charts, radar charts, 3D charts, and stacked charts do not. If the option is grayed out, switch your chart type first, then come back to add the trendline.

Each trendline carries an equation and an R-squared value. The equation lets you predict y-values from any x-input. The R-squared value, which ranges from 0 to 1, tells you how well the line fits your real data. Anything above 0.9 is excellent. Anything below 0.5 means the model is mostly guessing.

The Quick Method: Add a Trendline in 3 Clicks

Here is the fastest route, the one you should memorize first. Click your chart so the green plus sign appears in the upper-right corner. Click that plus, hover over Trendline, and pick a sub-type. That is it. Excel adds a linear trendline by default if you just click the checkbox. The whole operation takes about two seconds once you know where to look.

If your chart has more than one data series, Excel will ask which series you want to add the trendline to. Pick the right one or your line will fit the wrong numbers. You can add multiple trendlines to the same chart, one per series, useful for comparing two products, two regions, or two campaigns on the same view. Each trendline can be a different type, which means you can fit a linear line to one series and a polynomial to another in the same chart.

Right-clicking works too. Click any data point in the series, choose Add Trendline from the context menu, and the Format Trendline pane slides in from the right. This pane is where the real power lives. The checkbox method in the green plus menu is fine for a quick linear fit, but anything beyond that requires the full pane. Keyboard shortcut fans will be disappointed: Microsoft has not added a hotkey for trendlines, so the right-click or chart-icon path is what you have.

The Six Trendline Types and When to Use Each

Picking the right model matters more than any other step. Use the wrong one and your equation will fit your historical data badly, which means any forecast you draw from it will be wrong too. Worse, a bad fit can make a flat dataset look like it is exploding upward, or vice versa, which has cost more than one analyst their credibility in a meeting. Here is how to decide.

Linear is your default. Use it when your data increases or decreases at a steady rate. Monthly subscription growth at a consistent pace or daily temperature in a stable season fit linear models. The equation is y = mx + b, the same one you learned in algebra class, where m is the slope and b is the y-intercept where the line crosses the vertical axis.

Exponential works when your data rises or falls at a rate that itself keeps growing. Compound interest, viral shares, and unchecked population growth all curve upward this way. Exponential does not work with zero or negative y-values, so Excel will hide the option if your data contains either. The shape is steep on the right side and almost flat on the left, the opposite of logarithmic. If you draw an exponential trendline through linear data, the line will look wrong almost immediately, so always check the R-squared before trusting the fit.

Logarithmic is the opposite shape from exponential. It rises quickly at first, then flattens out. Customer adoption rates often follow a logarithmic curve. Polynomial is for data that goes up and then down, or down and then up. Excel lets you set the order from 2 to 6. Order 2 gives a single curve, order 3 adds another bend. Do not go higher than order 4 unless you really know why, because high-order polynomials will overfit and chase every random wiggle in your data.

Power is similar to exponential but uses y = ax^b. It is most useful when comparing measurements that share a fundamental relationship, like surface area versus volume. Moving Average is the odd one out. It is not really a trendline. Instead, Excel averages a sliding window of data points and connects the averages. Use it to smooth noisy data when you want to see the underlying pattern without forcing a single equation.

Displaying the Equation and R-Squared Value

Once you have picked a trendline type, scroll to the bottom of the Format Trendline pane. You will see two checkboxes you absolutely want to turn on: Display Equation on chart and Display R-squared value on chart. The first puts the formula directly on your chart. The second prints the goodness-of-fit number right below it.

The R-squared value is your reality check. If you set a polynomial of order 6 and your R-squared is 0.999, that does not necessarily mean you have a great model. It might mean you have overfit. Try dropping back to order 2 or 3 and see if the value stays high. A simpler model with a slightly lower R-squared is almost always more useful for forecasting than a complex model that hugs every data point.

Forecasting Future Periods

This is the feature most people miss. Inside the Format Trendline pane, find the Forecast section. Type a number into the Forward box, and Excel will extend your trendline that many periods into the future. Type a number into the Backward box, and it will extend backward, which is handy when you want to estimate what a metric might have looked like before your data collection started.

If your x-axis is dates, the periods match your data interval. Monthly data forecasts in months, daily in days, and so on. Set Forward to 12 and you will see a one-year projection drawn right on the chart. This is the fastest way to share a forecast with a stakeholder without building a separate prediction sheet. Many product managers use this feature in weekly status reports because it lets them show the trajectory in seconds.

A word of caution. Forecasts only work as well as your trendline fits the past. If your R-squared is 0.4, do not trust a forecast 12 periods out. Use shorter horizons and revisit the line as new data arrives. A good rule of thumb is to forecast forward no more than one-third of the periods you already have. If you have 12 months of data, project forward at most 4 months.

Set Intercept manually if you know what value the trendline should pass through at x equals zero. For example, if you are tracking revenue from a product that did not exist before launch, set Intercept to 0 so the line starts at the origin. Excel normally calculates this intercept to maximize fit, but a forced value can make the model more realistic.

Formatting Your Trendline for Clarity

The default Excel trendline is a thin dashed line in a color you probably do not want. Right-click the trendline and choose Format Trendline. In the pane, click the paint bucket icon to access line settings. You can change the color, width, and dash style to match your report. Pick a weight of 2.25 to 3 points for slide decks and 1.5 to 2 for printed documents.

For business reports, a solid line in a high-contrast color makes the trend pop without overwhelming the data. You can also rename the trendline. Excel calls it something generic like "Linear (Series1)" by default. In the Format Trendline pane scroll to Custom and type a clearer name like "3-Year Projection" or "Seasonal Fit." The legend updates automatically and your audience instantly knows what they are looking at without you having to explain.

Common Errors and How to Fix Them

The most common mistake is trying to add a trendline to a chart type that does not support it. If the option is missing or grayed out, switch your chart to Scatter or Line and try again. Stacked charts and 3D charts will never accept a trendline, no matter how you rearrange the data. A radar chart can show beautiful comparisons but it will refuse every trendline you try to draw on it.

The second most common problem is bad x-values. If your x-column contains text labels like month names instead of actual dates or numbers, Excel will plot them as evenly-spaced categories. That works for line charts but makes the trendline equation meaningless because the math is treating positions 1 through 12 as your x-values, not the actual months. Convert your labels to real numeric values or dates first, then rebuild the chart and the trendline equation will mean something again.

The third issue: exponential and power trendlines fail silently when your data contains zeros or negatives. Excel either hides the menu option or draws a strange flat result. Filter or shift your data before fitting, then label the chart clearly so readers know what transformation you applied. The fourth common error is leaving the default Excel formatting in place. A trendline drawn in the same color as your data points blends in and confuses readers. Always pick a contrasting color and a thicker stroke so the trendline reads as a deliberate analytical layer rather than another data series.

Trendlines on Multiple Series

When your chart shows two or more series, you can add a separate trendline to each one. Click the first series, add a trendline. Click the second series, add another. This is useful when comparing performance across products, regions, or time windows.

To make a comparison chart readable, give each trendline a different style. One solid, one dashed, both in their parent series colors. Add the equations and R-squared values, but position them so they do not overlap.

If you are presenting this kind of comparison, walk your audience through one trendline at a time before highlighting the difference. "Region A grew at 3.2 percent per month. Region B grew at 1.8 percent." That is the kind of story a single chart with two trendlines can tell instantly.

One trick the pros use: build a second chart that only shows the trendlines, with the original data points hidden. This stripped-down view makes the comparison even cleaner for executive summaries. To do it, right-click each data series, choose Format Data Series, and set the marker fill to no fill and the line to no line. The trendlines remain visible because they are a separate chart layer.

Excel Versions and Compatibility

Trendline features work the same way in Excel 2016, Excel 2019, Excel 2021, and Microsoft 365. The interface is identical. Excel for Mac includes the same six trendline types, though the Format Trendline pane appears as a floating window. Excel for the web is more limited. You can add a linear trendline and a moving average, but the other four types and the forecast feature are not available.

Google Sheets users will notice that trendlines work but live in a different place. In Sheets, double-click a chart, open the Customize tab, and find Series. Within Series, scroll down and check Trendline. The math is the same, but the menu layout is different.

From Trendline to Forecast Sheet

If a trendline forecast is not precise enough, Excel has a more powerful built-in tool called Forecast Sheet. Select your data, go to Data on the ribbon, and click Forecast Sheet. Excel generates a new sheet with a forecast, confidence intervals, and a fresh chart, all using the FORECAST.ETS function under the hood.

Trendlines are great for visual storytelling. Forecast Sheets are great for actual numbers with uncertainty bounds. Use trendlines in dashboards. Use Forecast Sheets in financial models.

Real-World Example: Sales Trendline

Imagine a sales worksheet with 24 months of revenue. Highlight the date column and the revenue column. Insert a scatter chart with smooth lines. The dots show clear growth but a few dips around the holidays. A linear trendline gives an R-squared of 0.72. Not bad, but the dips are not captured.

Switch to a polynomial order 3. The R-squared jumps to 0.91 because the curve now follows the seasonal swings. Display the equation. Set forecast forward to 6 months. You now have a chart that tells your manager: revenue is growing overall, dips every December, and is projected to hit a certain figure by June.

That is what trendlines are for. Not the math itself, but the story they let you tell with three or four clicks. If you want structured practice with charts and formulas, our Excel practice test and Excel functions practice test walk you through realistic scenarios, and the Microsoft Excel job interview questions set covers the chart and trendline topics hiring managers love to ask.