How to Extrapolate Data in Excel

Excel is one of the most widely used tools for data forecasting. Whether you’re projecting quarterly revenue, estimating next month’s inventory needs, or predicting user growth, Excel has built-in functions that make extrapolation accessible to anyone with a spreadsheet.

But knowing how to extrapolate data in Excel requires choosing the right method for your situation. This guide walks you through four approaches — from simple formulas to one-click forecast sheets — so you can pick the one that fits your data and skill level. If you’re new to the concept itself, start with our guide on what is extrapolation.

The Four Excel Extrapolation Methods at a Glance

Method 1: Using the TREND Function

What It Does

The TREND function calculates predicted values along a linear trend. It takes your known data, fits a straight line through it using least squares, and returns the y-values for any new x-values you specify.

Syntax

=TREND(known_y's, known_x's, new_x's)

• known_y’s: The range of known dependent values (e.g., sales figures)
• known_x’s: The range of known independent values (e.g., month numbers)
• new_x’s: The x-values you want to predict for

Visual Overview

Step-by-Step Example

Say you have monthly sales data for January through June and want to predict July and August:

Month	Sales
1	$10,000
2	$12,000
3	$14,500
4	$16,000
5	$18,500
6	$20,000

1. Enter your data in columns A (Month) and B (Sales)
2. In cell D2, enter: =TREND(B2:B7, A2:A7, 7) — this predicts month 7
3. In cell D3, enter: =TREND(B2:B7, A2:A7, 8) — this predicts month 8

Excel fits a linear trend through months 1–6 and projects it forward. The TREND function returns approximately $22,143 for month 7 and $24,286 for month 8.

TREND is especially useful when you need to predict multiple values at once — you can pass an array of new x-values and get all predictions in one step.

Method 2: Using the FORECAST.LINEAR Function

What It Does

FORECAST.LINEAR (available in Excel 2016 and later) predicts a single y-value for a given x-value based on existing data. It uses the same linear regression algorithm as TREND but is designed for single-point predictions.

Syntax

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

• x: The specific data point you want to predict
• known_y’s: The range of known dependent values
• known_x’s: The range of known independent values

Visual Overview

Step-by-Step Example

Using the same sales data, predict revenue for month 12:

1. In any empty cell, enter: =FORECAST.LINEAR(12, B2:B7, A2:A7)
2. Excel returns the projected sales for month 12 based on the linear trend through months 1–6

The result will be approximately $32,857 — nearly double month 1’s value, reflecting the steady upward trend.

FORECAST.LINEAR is the simplest way to get a quick single-value extrapolation. For linear extrapolation on stable data, this one formula is often all you need.

Method 3: Using a Trendline in a Chart

What It Does

A trendline adds a visual projection to your Excel chart. You can choose the type of trendline (linear, polynomial, exponential) and extend it forward to see where your data is heading. This is the most intuitive method because you can see the forecast.

Visual Overview

Step-by-Step Instructions

1. Select your data (months and sales)
2. Go to Insert → Chart and choose a Line or Scatter chart
3. Click on any data point in the chart to select the series
4. Right-click and choose Add Trendline
5. In the Trendline Options pane:
   • Select Linear (or Polynomial/Exponential if your data curves)
   • Under Forecast, set Forward to the number of periods you want to project (e.g., 6 months)
   • Check Display Equation on chart and Display R-squared value to see the fit quality

The trendline extends beyond your data, showing the projected values visually. The R² value tells you how well the line fits — see our guide to R² scores for interpretation.

When to Use This Method

Trendlines are best when you need a visual forecast for presentations or reports. They don’t give you precise cell values, but they make the trend immediately understandable to non-technical audiences.

Method 4: Using Excel’s Forecast Sheet (One-Click)

What It Does

The Forecast Sheet feature (Excel 2016+) generates a complete forecast with a chart, confidence intervals, and a table of predicted values — all in one click. It’s the fastest way to get a professional-looking forecast.

Visual Overview

Step-by-Step Instructions

1. Select your data range (including dates and values)
2. Go to the Data tab and click Forecast Sheet
3. In the Create Forecast Worksheet dialog:
   • Set the Forecast End date
   • Adjust the confidence interval if needed (default is 95%)
   • Choose whether to include seasonality detection
4. Click Create

Excel generates a new sheet with a chart showing historical data, predicted values, and confidence bounds. It also produces a table with each forecasted value and its confidence range.

When to Use This Method

Forecast Sheet is ideal for business reports where you need both the numbers and a polished visualization. It handles seasonality automatically, which the simpler methods don’t. For data with repeating seasonal patterns (e.g., retail sales peaking every December), this is the best Excel option.

Comparison Table: Which Method Should You Use?

Method	Best For	Excel Version	Ease of Use
TREND	Multiple data points	All versions	Medium
FORECAST.LINEAR	Quick single value	2016+	Easy
Trendline	Visual forecasting	All versions	Easy
Forecast Sheet	Advanced forecast with seasonality	2016+	Easiest

Each method assumes a linear relationship by default, though trendlines and Forecast Sheets support other patterns. For comparing linear vs curved approaches, see our guide on polynomial vs linear extrapolation.

Tips & Best Practices

• Check for outliers first — a single anomalous data point can pull the trendline far from reality
• Use enough historical data — at least 5–6 data points for a meaningful trend; fewer than that and you’re guessing
• Validate the trend before extending it — if your R² is below 0.7, the linear model may not fit well enough for reliable extrapolation
• Linear trends work best for stable data — if your growth is accelerating or decelerating, consider polynomial or exponential extrapolation methods
• Don’t forecast too far ahead — the further beyond your data, the less reliable any extrapolation becomes

Limitations of Excel Extrapolation

Excel’s forecasting tools are convenient but have real constraints:

• Linear by default: TREND and FORECAST.LINEAR assume a straight-line relationship. If your data follows a curve, these functions will mislead you
• No automatic model selection: Excel doesn’t compare methods or tell you which trend type fits best. You have to check R² yourself
• Risk of overconfidence: The Forecast Sheet’s confidence intervals look precise, but they’re based on the assumption that the future will resemble the past — exactly the assumption that breaks down during market shifts or disruptions

For data that clearly curves, the interpolation calculator and dedicated extrapolation tools provide more method options than Excel’s built-in linear defaults.

When to Use a Dedicated Calculator

Excel is great for working with spreadsheets, but when you need quick, accurate extrapolation with multiple method options, a dedicated tool saves time. The extrapolation calculator offers five methods — linear, exponential, logarithmic, polynomial, and quadratic — so you can compare results side by side and pick the best fit.

No formulas to write, no chart setup, no guesswork about which trendline type to use. Enter your data, select a method, and get your projection instantly. For modeling the underlying relationship between variables, the regression calculator provides detailed regression analysis.

Conclusion

You can extrapolate data in Excel using four methods: the TREND function for multi-point predictions, FORECAST.LINEAR for quick single values, trendlines for visual forecasts, and the Forecast Sheet for one-click reports with seasonality. Each has its place depending on your data and what you need from the forecast.

For faster results with five different extrapolation methods — and no formula debugging — try the free extrapolation calculator.

Frequently Asked Questions

Can Excel extrapolate data?

Yes. Excel offers several built-in tools for extrapolation: the TREND function, FORECAST.LINEAR function, chart trendlines, and the Forecast Sheet feature. All use linear regression by default, though trendlines and Forecast Sheets support other patterns.

What is the TREND function in Excel?

TREND calculates predicted y-values along a linear trend based on your known data. Its syntax is =TREND(known_y's, known_x's, new_x's). It fits a straight line through your data using least squares and returns the projected values for the new x-values you specify.

How do I extend a trendline in Excel?

Add a trendline to your chart by right-clicking a data point and selecting “Add Trendline.” In the Trendline Options, set the “Forward” forecast field to the number of periods you want to project beyond your data. The trendline will extend visually into the future range.

Is FORECAST.LINEAR accurate?

FORECAST.LINEAR is accurate for data that follows a genuine linear trend. Like all linear methods, it becomes unreliable when the underlying pattern is non-linear or when you project far beyond the observed data range. Always check the R² value to assess fit quality before trusting the prediction.