THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Formulas and Functions Fundamentals

Forecasting Excel Basics: A Beginner's Step-by-Step Guide

Learn simple forecasting techniques using trends.
By:

The regional ops manager dropped a spreadsheet on my desk in late 2023 and asked for a three-month projection by Friday. The data went back two years: weekly shipment volumes, one column of dates, one column of numbers. Clean enough to work with. Except three of the weeks were missing entirely, the date column had two entries formatted as text, and whoever built the original sheet had left a blank row in the middle like a pothole. I've been building forecasting dashboards professionally since 2019, and I still spent twenty minutes on data cleanup before I touched a single formula. That's the part most tutorials skip, and it's exactly where forecasting in Excel either holds together or falls apart.

This guide walks you through three things: preparing your data so it doesn't silently corrupt your results, building a real forecast with FORECAST.LINEAR, and knowing when to switch to FORECAST.ETS or the Forecast Sheet tool instead. By the end, you'll have a working projection you can hand to someone and understand why it says what it says.

Excel forecasting basics with FORECAST.LINEAR showing a two-column time series and trendline
A clean two-column layout is all you need before writing your first forecasting formula in Excel.

What Your Data Needs to Look Like Before You Run a Single Formula

Every forecasting method in Excel works with time series data: a sequence of values measured at consistent intervals over time. Weekly sales totals. Monthly headcount. Daily ticket volume. The specific metric doesn't matter. What matters is that the intervals are consistent and the data has enough history to show a pattern.

Before you write any formula, your data needs to pass three checks.

  1. No gaps in the date column. A missing week or month will cause FORECAST functions and chart trendlines to plot against the wrong baseline without warning you.
  2. Numbers stored as actual numbers. Every value in your numbers column must be a real number, not text formatted to look like one. SUMIFS returns zero with text-stored numbers; FORECAST functions behave the same way.
  3. No blank rows. A blank row anywhere in your data range breaks the continuity that forecasting functions depend on.

The One Question That Determines Which Forecasting Method to Use

Once your data is clean, ask one question: does your data follow a straight-line trend, or does it repeat a seasonal pattern (higher every December, lower every August)? Your answer determines whether you use FORECAST.LINEAR or FORECAST.ETS. If you're not sure, plot the data as a simple line chart first. A rough upward or downward line points to FORECAST.LINEAR. Waves that repeat at regular intervals point to FORECAST.ETS.

If you're new to structuring data before analysis, the guide on preparing data for analysis in Excel covers the column formatting and cleanup steps in more detail.

Step 1: Use FORECAST.LINEAR to Build Your First Forecast Formula

With clean data in hand, FORECAST.LINEAR is the right starting point for most beginners. It draws a straight line through your historical data points using linear regression and extends that line to whatever future point you specify. Not magic. Just math that fits the best straight line to your past values and reads off where that line lands next.

How to Write the FORECAST.LINEAR Syntax

The function takes three arguments: =FORECAST.LINEAR(x, known_y's, known_x's). In plain terms: the future date you're projecting to, the column of historical values, and the column of historical dates. If your dates are in A2:A25 and your sales numbers are in B2:B25, and you want to project the value for the date in A26, the formula is:

=FORECAST.LINEAR(A26, B2:B25, A2:A25)

Excel runs a least-squares regression on your historical range and returns the projected value.

Why FORECAST.LINEAR Replaced the Old FORECAST Function

If you've been using Excel for a while, you may have used the older FORECAST function (without ".LINEAR"). Microsoft deprecated it in favor of FORECAST.LINEAR as part of the broader Microsoft 365 forecasting function family. They perform the same calculation. Stop using the deprecated version: there's no reason to lean on it when the replacement is identical to type and won't carry forward any support risk.

FORECAST.LINEAR assumes your trend is a straight line. If your data has a seasonal curve (shipping volumes that spike every Q4, for example), the function will flatten that curve and give you a projection that looks reasonable on paper but is systematically off at every seasonal peak. That's when you need Step 2.

Step 2: Switch to FORECAST.ETS or the Forecast Sheet When Your Data Has Seasonality

Once you've run FORECAST.LINEAR and looked at the results, you'll know quickly if something's off. If your projections feel too flat (missing the peaks and valleys you can see in the historical data), that's the seasonal pattern the linear method can't detect.

When to Pick FORECAST.ETS Over FORECAST.LINEAR

The decision comes down to one question: does your data repeat a pattern on a fixed cycle? Quarterly revenue that climbs in Q4 every year. Monthly website traffic that dips every August. If yes, use FORECAST.ETS. It uses exponential smoothing to weight recent values more heavily than older ones and recognizes repeating seasonal cycles in a way linear regression can't. The syntax is more involved:

=FORECAST.ETS(target_date, values, timeline, seasonality)

Setting seasonality to 1 lets Excel detect the cycle length automatically. FORECAST.ETS handles seasonality reliably, assuming your date column has no gaps and your values are actual numbers rather than text strings. I've seen it fail silently in production exactly once because a missing month in the timeline broke the cycle detection at month eleven. Check the date continuity. Every time.

How to Generate a Forecast Sheet in Under a Minute

If you want a visual output fast, the Forecast Sheet wizard is the quickest path. Select your two-column data range, go to the Data tab, and click Forecast Sheet. Excel builds a chart with your historical data, a projected trend line, and confidence bands, all without writing a single formula. The underlying calculation uses FORECAST.ETS, so the same seasonal logic applies. For a quick presentation or an initial read on the data's direction, this is the tool. For a formula embedded in a live dashboard, write the function directly.

For readers doing this analysis on a Mac: some Forecast Sheet options behave slightly differently. The data analysis in Excel on Mac guide covers those differences.

Common Forecasting Mistakes (and the Checks That Catch Them Early)

Excel doesn't throw an error when your forecast is quietly wrong. It produces a number that looks plausible. The four mistakes below are the ones that cost analysts the most time, usually at the worst possible moment.

Irregular date gaps

Missing periods in your timeline break FORECAST.ETS's cycle detection and shift your FORECAST.LINEAR baseline. Run a quick check with COUNTIFS, or sort the date column and eyeball the intervals before you build anything.

Using the deprecated FORECAST function

It still works in 2026, but there's no reason to use it. FORECAST.LINEAR is the direct replacement with identical behavior. Swap it out now rather than waiting for Microsoft to drop support.

Projecting too far forward

A trendline extended six months into the future on twelve months of data is guesswork wearing math's clothing. Keep projections inside roughly one-third of your historical range unless there's a strong business reason to go further.

Never validating accuracy

A confident forecast chart that turns out to be projecting nonsense is a rite of passage nobody talks about. Use a simple MAPE check (Mean Absolute Percentage Error): hold out your last few months of known data, run the forecast against the earlier history, and compare. =ABS((actual-forecast)/actual) averaged across those periods tells you how far off your method actually is. CFI (Corporate Finance Institute) has solid documentation on forecast accuracy metrics if you want to go deeper.

For a broader foundation on the analysis skills underneath all of this, the Excel for Beginners complete starter guide covers the core concepts. If you're applying these methods to inventory or retail data specifically, the walkthrough on data analysis in Excel for retail inventory shows how the same forecasting logic maps to stock and demand planning.

Frequently Asked Questions

What is the difference between FORECAST.LINEAR and FORECAST.ETS?

FORECAST.LINEAR fits a straight line through your historical data and extends it. It works well when your data trends consistently in one direction without repeating patterns. FORECAST.ETS uses exponential smoothing to detect seasonal cycles, weights recent data more heavily, and handles data that repeats on a fixed interval (quarterly peaks, annual dips) far more accurately than linear regression can.

Does the old FORECAST function still work, or should I stop using it?

It still works as of 2026, but Microsoft deprecated it in favor of FORECAST.LINEAR, which performs the exact same calculation. There's no advantage to keeping the old version in your formulas. Switch to FORECAST.LINEAR and you're covered whether Microsoft eventually drops support for the deprecated function or not.

How do I check forecast accuracy in Excel using MAPE?

Hold out the last several periods of your known historical data, run your forecast against the earlier history, then calculate =ABS((actual-forecast)/actual) for each held-out period and average the results. That average is your MAPE: lower is better. It tells you what percentage error your chosen method typically produces, which is far more useful than a forecast chart that just looks plausible.