How to Make Trendline Extend in Excel

Ever created a chart in Excel and wished you could see where the trend is heading? Extending a trendline to predict future data points is a powerful feature for simple forecasting, whether you're projecting next quarter's sales or estimating website traffic a few months from now. This guide will walk you through exactly how to extend a trendline in Excel, explain which type of trendline to use, and cover an alternative formula-based method for more advanced forecasting.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Extend a Trendline in the First Place?

Before jumping into the how-to, it's helpful to understand why this is such a useful analytical tool. Extending a trendline is essentially a form of visual forecasting. It uses your existing historical data to project what might happen in the future if the current trend continues.

Here are a few common scenarios where this is incredibly useful:

• Sales Forecasting: If your monthly sales data shows a steady upward trend, you can extend the line to estimate sales for the next 3, 6, or 12 months.
• Resource Planning: By projecting the number of support tickets, you can decide if you need to hire another team member in the coming quarter.
• Budgeting: Forecasting an increase in ad spend a few periods into the future can help you allocate marketing and advertising budgets.
• Goal Setting: Visualizing where you'll be in six months based on your current trajectory can be a powerful motivator or a clear signal that you need to adjust your strategy.

Essentially, it turns your historical data from a simple record into a forward-looking analytical tool.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Getting Started: Adding a Basic Trendline to a Chart

You can't extend a trendline that doesn't exist. Let's start with a quick refresher on how to create a basic line or scatter chart and add a standard trendline. For our example, we'll use a simple dataset of monthly website traffic.

Imagine your data looks like this in Excel:

Month | Sessions Jan | 5200 Feb | 5500 Mar | 5900 Apr | 6100 May | 6600 Jun | 7000

Step-by-Step Instructions

1. Select Your Data: Highlight the cells containing your data, including the headers (e.g., A1 through B7).
2. Insert a Chart: Go to the Insert tab on the Ribbon. In the Charts section, click "Recommended Charts." Excel will often suggest a Line Chart or a Scatter Plot, both of which work perfectly for trendlines. Select the one you prefer and click OK.
3. Add the Trendline: Click anywhere on your new chart to bring up the Chart Design and Format tabs. Select your chart, then click the green + icon (Chart Elements) on the right side of the chart. Check the box next to Trendline. A basic linear trendline will immediately appear on your chart.

You now have a standard chart with a trendline that maps the general direction of your past data. Now, let's get to the important part: answering your real question.

How to Make a Trendline Extend in Excel to Forecast Data

With your basic trendline in place, extending it into the future requires just a few clicks. This is where you tell Excel how many periods forward (or backward) you want to project.

1. Open the Trendline Options: Find the trendline itself and double-click on it directly. This will open the Format Trendline pane on the right side of your Excel window. If you have trouble clicking the line, you can also right-click the trendline and select "Format Trendline..." from the menu.
2. Locate the Forecast Option: In the "Format Trendline" pane, make sure you're on the "Trendline Options" tab (it looks like a bar chart icon). Scroll down, and you’ll find a section called Forecast.
3. Set Your Forecast Period: You'll see two boxes: "Forward" and "Backward." Our data is in months, so if we want to project traffic a full quarter into the future, we would enter 3 in the "Forward" periods box. Press Enter.

As soon as you enter a number, you'll see the trendline on your chart extend beyond your existing data points.

1. (Optional but Recommended) Display Equation and R-squared: While you're in this pane, scroll to the bottom and check the boxes for "Display Equation on chart" and "Display R-squared value on chart."

That's it! Your trendline is now extended, providing a clear visual representation of where your website traffic might be headed over the next three months if the current trend holds.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Choosing the Right Type of Trendline

Excel defaults to a Linear trendline, which is a simple straight line. But sometimes your data doesn't grow in a straight line. Choosing the wrong trendline type can lead to wildly inaccurate forecasts. In the "Format Trendline" pane, you can select from several types. Here are the most common ones and when to use them:

• Linear: The default option. Use this when your data increases or decreases at a steady, consistent rate. It's the simplest model for things like consistent sales growth.
• Exponential: Use this when your data values are rising or falling at increasingly higher rates. This results in a sharp curve upward or downward. It's perfect for modeling things that grow exponentially, like viral content engagement or compound interest.
• Logarithmic: This type is best when your data increases or decreases quickly and then levels off. The line is a curve that flattens over time. Think of product sales after a new launch - a huge spike at first, which then slows down to a steady rate.
• Polynomial: This is a more complex option that uses curves with hills and valleys to fit the data. A polynomial (Order 2) can follow a simple curve, while a higher-order can accommodate more fluctuations. Be careful here - it's easy to "overfit" the data, meaning the line fits your past data perfectly but makes a poor forecast for the future. Use this if your data shows predictable peaks and troughs, like seasonal sales data.

Experiment by clicking a few different options. Your R-squared value will update with each change, helping you see which model is mathematically the best fit for your historical data.

A Different Method for Forecasting: Using the FORECAST.LINEAR Function

While extending a trendline on a chart is great for visualization, sometimes you need the actual forecasted numbers in a cell. For this, you can use Excel's built-in forecasting function. The most common is FORECAST.LINEAR.

This function calculates a future value along a linear trend based on existing data. The syntax is:

=FORECAST.LINEAR(x, known_y's, known_x's)

• x: The future data point you want to predict. In our monthly example, this would be month "7" (July).
• known_y's: The range of your existing dependent data values (your website sessions).
• known_x's: The range of your existing independent data values (months 1 through 6).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Example in Practice:

To forecast traffic for July (period 7), you would type this formula into a cell:

=FORECAST.LINEAR(7, $B$2:$B$7, $A$2:$A$7)

This formula would return a predicted session count for July. You can drag this formula down to calculate predictions for August (period 8), September (period 9), and so on. Using dollar signs $ makes the ranges absolute, so they won't change as you drag the formula.

This method gives you raw numbers to work with, which can be even more powerful for building budgets and reports than a simple visual chart.

Final Thoughts

Extending a trendline in Excel is a straightforward way to turn historical reports into dynamic forecasts. Whether you use the visual tools on a chart or the FORECAST.LINEAR function for precise numbers, this technique adds a valuable layer of analysis, helping you make smarter, more data-informed decisions about the future.

While Excel is great for these one-off projections, we know firsthand that manually pulling data, creating charts, and updating reports week after week can become a major time sink. That’s why we built Graphed. Our platform connects directly to your live data sources like Google Analytics, Shopify, and your ad platforms, so your dashboards are always up-to-date in real time. Instead of playing with format panes, just ask a question like "show me a chart of my website traffic projected for the next 60 days" and get an interactive dashboard instantly.