How to Make Trendline Longer in Excel

An Excel trendline stopping right at your last data point can feel like a story ending on a cliffhanger. You know there's a pattern, but the chart leaves you guessing what happens next. Fortunately, you can easily push that line into the future to create simple forecasts. This guide will show you exactly how to make your trendline longer, covering the simple point-and-click method and a powerful formula-based approach.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Extend a Trendline in Excel?

Extending a trendline is all about forecasting. It’s a simple way to visually project future results based on your historical data. By analyzing the "line of best fit" through your existing data points, Excel can estimate where that line will go next.

This is incredibly useful for:

• Sales Projections: Estimate next quarter’s sales based on the past year’s performance.
• Website Traffic: Predict how many users you might have in the coming months based on your growth trend.
• Budget Planning: Project future expenses to prepare your budget more effectively.
• Inventory Management: Forecast product demand to avoid stockouts or over-ordering.

While not a substitute for sophisticated statistical modeling, it’s a fast and effective way to get a directional sense of where things are heading right inside the spreadsheet you're already using.

How to Make a Trendline Longer: The Quick Method

The fastest way to extend your trendline is by using the built-in "Forecast" option in the trendline formatting tools. Let’s walk through it step-by-step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create Your Chart

First, you need a line or scatter chart with time-series data. This means one axis (usually the horizontal or X-axis) should represent a sequence of time, such as days, months, or years.

Let's use a simple example of monthly website visits:

1. Organize your data in two columns. For example, Column A for "Month" and Column B for "Website Visits."
2. Highlight your data, including the headers.
3. Go to the Insert tab on the ribbon.
4. In the Charts group, select Insert Scatter (X, Y) or Bubble Chart and choose a scatter plot, or select Insert Line or Area Chart and choose a line chart. A line chart often looks best for this kind of data.

Step 2: Add a Trendline to Your Chart

With your chart created and selected, it's time to add the trendline.

1. Click on the chart to select it. You will see a Chart Design tab appear in the ribbon.
2. Click on Add Chart Element > Trendline. Choose one of the options like Linear, Exponential, or Linear Forecast. For now, Linear is a great starting point.

Alternatively, you can right-click on one of the data points in your chart and select Add Trendline... from the context menu.

You’ll now see a dotted line running through your data points. By default, it stops at your very last point.

Step 3: Access the Format Trendline Options

This is where the magic happens. You need to open the formatting pane to find the forecast settings.

1. Double-click on the trendline you just added. If that’s tricky, you can also right-click on the trendline and select Format Trendline...
2. A “Format Trendline” pane will appear on the right side of your Excel window.
3. Make sure you are in the Trendline Options tab.

Step 4: Use the "Forecast Forward" Field to Extend the Line

Inside the Trendline Options, you'll see a section for "Forecast."

1. Find the input box labeled Forward.
2. Enter a number into this box. This number represents the number of periods you want to project into the future.

For example, if your chart data is monthly, entering "3" into the Forward box will extend the trendline three months into the future. If your data is daily, it will extend it three days forward.

As soon as you enter a number and press Enter, you will see the trendline on your chart instantly extend past your last data point. You have successfully made your trendline longer!

Pro Tip: You can also use the Backward field to extend the trendline into the past, which can be useful for estimating what the data might have looked like before you started recording it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Picking the Right Trendline for Your Forecast

Just extending the line isn’t enough, you need to make sure you're using the right type of trendline for your data, as this dramatically impacts the accuracy of your forecast.

In the "Format Trendline" pane, you can choose from several types:

• Linear: Best for data that increases or decreases at a steady rate. Think of a straight line. This is the most common and simplest type.
• Exponential: Use this when your data grows or shrinks at an increasingly rapid rate. It results in a curved line.
• Logarithmic: Ideal for data that rises or falls quickly at first and then starts to level off.
• Polynomial: Excellent for analyzing data that has peaks and valleys (fluctuations). You can set the "Order" to determine how many curves the line has. An order of 2 has one curve, an order of 3 has two, etc. Be careful not to overcomplicate it.

To help choose, you can check the box for Display R-squared value on chart in the Trendline Options. The R-squared value (from 0 to 1) tells you how well the trendline fits your data. A value closer to 1 means a more reliable fit.

Get the Actual Forecasted Numbers with the FORECAST Function

A visual line is great, but what if you need the actual numbers that the forecast is predicting? For this, you'll want to use Excel’s FORECAST.LINEAR function. This is perfect for when you need to use the projected values in other calculations.

The syntax for the function is:

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

• x: The new date or period for which you want to predict a value.
• known_y's: Your range of historical numeric data (e.g., your past website visits).
• known_x's: Your range of historical dates or time periods (e.g., your past months).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Example: Using the FORECAST Function

Let's use our previous website traffic example. Imagine your data for Jan-Jun is in cells A2:B7.

1. Extend your "Month" column with future months you want to predict (e.g., Jul, Aug, Sep) in cells A8, A9, and A10.
2. In cell B8 (next to "Jul"), type the following formula:

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

Let's break that down:

• A8 is the month we want to predict a value for ("Jul").
• $B$2:$B$7 is our known website traffic data. We use dollar signs ($) to lock this range so it doesn't shift when we drag the formula down.
• $A$2:$A$7 is our known month data, also locked with dollar signs.

Press Enter, and Excel will calculate the projected website visits for July. Now, you can click on the corner of cell B8 and drag the formula down to B9 and B10 to get forecasts for August and September. You now have the raw numbers to complement your visual trendline.

Final Thoughts

Extending a trendline in Excel is a straightforward process that turns your historical data into a simple, forward-looking forecast. By using the format options, you can get a quick visual projection in seconds, and by using the FORECAST.LINEAR function, you can get the actual data points for more detailed planning and analysis.

While mastering these Excel techniques is fantastic for quick projections, we know the real challenge often comes before you even create the chart: pulling, cleaning, and consolidating data from multiple sources. We built Graphed specifically to solve this problem by turning hours of data prep and reporting into a quick conversation. It connects directly to your marketing and sales platforms, allowing you to build real-time, interactive dashboards just by describing what you want to see - no more exporting CSVs or wrestling with pivot tables.