Excel Forecasting: How to Predict Future Values

How-to basics · Updated November 20, 2026

Excel forecasts future values by extending patterns in historical data. FORECAST.LINEAR fits a straight-line trend, while FORECAST.ETS and the Forecast Sheet can account for trends and recurring seasonal patterns; the best choice depends on how your data behaves.

What forecasting means in Excel

A forecast is an estimate based on past observations, not a guarantee about what will happen. In a spreadsheet, historical measurements such as monthly sales, website visits, or energy use are paired with points on a timeline. Excel uses the relationship between those inputs to estimate values at future points.

Excel offers two broad approaches. A linear forecast assumes that values change at a relatively steady rate. An ETS forecast uses exponential smoothing, a method that gives more weight to recent observations and can recognize recurring seasonal patterns. The Forecast Sheet feature provides a guided way to create an ETS-style forecast and visualize it.

These tools describe patterns in the data you provide. They do not automatically know about a price change, a new competitor, a supply interruption, or a planned promotion. Those factors may matter more than the historical pattern, so treat the result as a planning aid and review it alongside what you know about the situation.

How FORECAST.LINEAR works

FORECAST.LINEAR estimates a value by fitting a straight line through known pairs of x- and y-values. The x-values represent the position or time for each observation; the y-values are the measured results. Excel uses linear regression to find the line that best fits the historical observations, then evaluates that line at the future x-value you supply.

For example, suppose the number of units sold has risen by roughly the same amount each period. A linear model can continue that average increase. It can also work when x-values are dates, because Excel stores dates as serial numbers. The x-values and y-values must correspond in order and have the same number of entries.

With periods 1 through 6 in A2:A7 and sales of 100, 110, 120, 130, 140, and 150 in B2:B7, enter the following in C2 to estimate sales for period 7:

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

The result is 160. The values rise by 10 each period, so the fitted line continues that pattern to 160 at period 7. This neat example makes the arithmetic easy to check; real data usually fluctuates, and the forecast follows the best-fitting overall line rather than simply copying the latest change.

FORECAST is an older function name retained for compatibility with earlier workbooks. For a straight-line forecast in current Excel, FORECAST.LINEAR makes the method explicit. Neither name adds seasonality or automatically adjusts for special events.

When to use ETS and seasonality

Many time series repeat a pattern: retailers may see higher sales near holidays, while electricity demand may vary by season. A straight line cannot represent that repeating cycle. Excel’s FORECAST.ETS function uses exponential smoothing to estimate a future value from a timeline and historical values, and can account for seasonality as well as changing levels or trends.

ETS is most useful when observations are recorded at regular intervals, such as every day, month, or quarter, and there is enough history to reveal a repeatable pattern. The timeline should use a consistent interval. For example, monthly data should represent successive months, rather than a selection of months with unexplained gaps. Duplicate dates and missing observations can affect how Excel interprets the series.

In FORECAST.ETS, the seasonality setting can be left for Excel to detect automatically, or specified when you have a sound reason to choose a cycle length. A setting of zero tells Excel to ignore seasonality; a positive integer specifies a seasonal cycle length. Missing timeline points can be handled through the function’s data-completion option, and duplicate timestamps can be combined using an aggregation option. Those settings change the model’s inputs, so check them rather than assuming the defaults match your data.

ETS is not a causal model: it does not infer that a marketing campaign caused a sales peak or that a staffing change caused a service decline. If the underlying business conditions change, a pattern that fit the past may stop being useful. Compare forecasts against actual results and revisit the model when the process or market changes.

Create a forecast with Forecast Sheet

For a quick visual forecast in Excel for Microsoft 365 on Windows, arrange dates in one column and their corresponding values in the next, with one observation per row. Select the historical timeline and values, then choose Data > Forecast Sheet. In the dialog, choose a line or column chart, set the forecast end date, and review the available options, including seasonality and the confidence interval. Select Create to place the chart and forecast table on a new worksheet.

The forecast chart distinguishes historical observations from estimated future values and commonly displays upper and lower confidence bounds. The table provides the dates and estimated values, making it possible to use the output in other calculations. Check that the selected date and value ranges contain only the intended series and that dates are in chronological order before creating the sheet.

The Forecast Sheet is convenient when you want a guided chart and forecast without writing a function. For a custom model, reusable template, or forecast embedded in an existing calculation, a worksheet function may be more flexible. Ribbon availability and feature support can vary by Excel release and platform; if Forecast Sheet is not available in your Excel version, use a supported forecast function or consult that version’s feature list.

Prepare the data before forecasting

Forecast quality depends heavily on the quality and consistency of the historical series. Before running a forecast, verify that each observation is assigned to the correct date or period and that the measurement uses the same definition throughout. A column that changes from gross sales to net sales halfway through is not a consistent series, even if every cell contains a number.

  • Use regular intervals. Decide whether the series is daily, weekly, monthly, or another consistent frequency. Irregular gaps can make a seasonal cycle difficult to detect.
  • Check blanks and zeros. A blank may mean a missed measurement, while zero may be a real observation. They are not interchangeable; choose a completion approach that reflects what happened.
  • Review unusual values. Correct data-entry errors, but do not delete a genuine event simply because it is unusually high or low. Mark exceptional events and consider whether they are likely to recur.
  • Keep the timeline aligned. Each date or period should match the value on the same row. Avoid including labels, totals, or unrelated categories in the selected ranges.
  • Use enough history. A few observations may show a short-term direction but usually cannot establish a dependable annual seasonal pattern. The amount of history needed depends on the cycle and the stability of the process.

When a series has many gaps, inconsistent intervals, or a major structural change, address those issues before interpreting a forecast. A function can calculate a number from imperfect inputs without making those inputs meaningful.

Choose the right method

Choose a linear forecast when the relationship between time and the measured value is reasonably close to a steady rise or fall, and repeating seasonal effects are not central to the question. It is simple to explain and can be useful as a baseline. If the values level off, accelerate, or repeat a strong cycle, a straight-line extension may produce implausible estimates.

Choose ETS or Forecast Sheet when the observations form a regular time series and recurring patterns are relevant. For example, monthly demand that reliably rises each summer and falls each winter is a better candidate for a seasonal method than for a single straight line. If you cannot see a consistent seasonal pattern, automatic seasonality detection is not a reason to assume one exists; compare the forecast with a simpler baseline.

Forecasting a value from causes or explanatory factors requires a different kind of analysis. If sales depend on price, advertising, and store count, a time-only forecast may miss important relationships. Excel’s statistical and analytical tools can support more involved models, but the model should match the question and the available data. A forecast should not be selected only because it produces a smooth chart.

Read uncertainty and check forecast results

A forecast value is an estimate, and uncertainty generally grows farther into the future. Forecast Sheet’s confidence interval communicates a range around estimated values under the model’s assumptions; it is not a promise that the actual result will fall inside that range. A narrow interval does not account for every possible event or a sudden change in the conditions that generated the historical data.

Check results in context. Do the predicted values remain within realistic limits? Does the shape make sense for the business or process? Are seasonal peaks occurring at plausible times? Compare the model with a simple baseline, such as carrying forward the latest observed value or the usual value for that season. If a complex forecast does not outperform a sensible baseline on past periods, it may not be adding useful information.

A practical check is to withhold some known observations, forecast those periods using only earlier data, and compare predicted values with what actually happened. This is often called backtesting. Look at errors across several periods rather than judging a model by one unusually good or bad result. For important decisions, document assumptions, update the forecast as new observations arrive, and avoid presenting a point estimate without its uncertainty and limitations.

Common problems and misconceptions

A frequent mistake is treating every forecast as a straight-line prediction. FORECAST.LINEAR does not identify a seasonal pattern, while ETS is designed for time series and may behave poorly when the timeline is irregular or the history is too short. Another misunderstanding is that a forecast explains why values changed. These functions extrapolate patterns; they do not establish cause and effect.

If Excel returns an error or an unexpected result, confirm that the timeline and values have matching lengths, that the ranges contain numeric observations, and that dates are valid Excel dates rather than text that merely looks like a date. For an ETS series, inspect interval consistency, duplicate dates, and blanks. Also confirm that the forecast target is later than the observations when you intend to predict the future.

Finally, do not assume a forecast will incorporate future plans. If you know that a store will close, a product will launch, or a reporting definition will change, the historical pattern alone cannot account for it. Forecast the unchanged baseline if useful, then evaluate the planned change separately and state the assumptions clearly.

Frequently Asked Questions

What is the difference between FORECAST and FORECAST.LINEAR?
FORECAST is the older name for the linear forecasting function and remains for workbook compatibility. FORECAST.LINEAR states explicitly that the calculation is linear. Both use historical x- and y-values to estimate a value at a supplied x-value.

Can Excel forecast monthly sales?
Yes. Put monthly dates and sales values in aligned columns, then use Forecast Sheet or an ETS forecast if the history is regular and seasonal patterns may matter. A linear forecast is another option when a steady trend is a reasonable assumption.

How much historical data do I need?
There is no universal minimum that guarantees a reliable forecast. A linear trend can be calculated from a small set of points, but more observations help reveal variation. To estimate annual seasonality from monthly data, you generally need multiple years of consistent monthly observations to judge whether the cycle repeats.

Why does my forecast look too high or too low?
Check for a trend that is not actually steady, unusually large values, gaps, duplicate dates, and an incorrect forecast end date. Then compare the output with actual historical behavior and a simple baseline. The model may be extending a pattern that is no longer relevant.

Does Excel forecasting work in Excel for the web or on Mac?
Forecast function and Forecast Sheet availability can differ across Excel releases and platforms. On Windows Microsoft 365, the Forecast Sheet command is under Data > Forecast Sheet. If a command or function is missing on Mac or in Excel for the web, check the feature support for that version and use an available forecast function or desktop Excel.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.