Every December, someone asks what next year’s sales will look like, and the answer is usually a guess dressed up as a number. Most people copy last year’s totals, add a growth percentage, and hope the holiday rush lands where it did before.

Excel already offers plenty of ways to analyze your sales data, but the best tool for this question, i.e., Forecast Sheet, is on the Data tab. It reads your monthly history, projects it forward, and draws a shaded range around the projection. That range ends up being more useful than the forecast itself.

Forecast Sheet is on the Data tab

One click and one date give you a finished chart

To test it, I built a sheet for a small online store with three years of monthly revenue, from January 2023 to December 2025. Sales grew from $227,320 in 2023 to $271,420 in 2025, and every November and December spiked well above the rest of the year.

1. Click any cell in your data, then go to Data > Forecast Sheet.
2. Set Forecast End to the last month you want predicted. Excel suggested December 2027, so I changed it to December 2026.
3. Click Create.

Excel adds a new worksheet to the left of your data with the original figures, three new columns, and a chart. The Forecast column predicts each month of 2026, and the two confidence bound columns sit on either side of it. A small pop-up also points out that the forecasting formulas are editable, which comes in handy later. You can also switch to a column chart from the same dialog if you prefer bars. Added up, the monthly forecasts put 2026 at about $300,400, roughly 11% above 2025.

The shaded band shows Excel’s confidence

Read it as your best case and worst case

The two thin lines around the forecast are where the title’s promise comes in. Excel’s forecast for December 2026 is $36,076, but the Lower Confidence Bound is $34,177, and the Upper Confidence Bound is $37,975. Instead of one number to trust, you get a realistic floor and ceiling for the store’s busiest month — and a far better sense of how much stock and cash to have ready.

By default, that band is a 95% confidence interval. In plain terms, Excel expects 95% of future values to fall inside it, assuming its errors follow a normal pattern.

The band also widens the further out it goes. January’s range spans about $2,900, while December’s stretches to roughly $3,800. You can change the level under Options, too. At 80%, December’s range tightens to $34,835 through $37,318. The forecast itself stays the same, but Excel is now less sure the real number will land inside that narrower window.

I stick with 95% for planning, budgeting around the lower bound and stocking up for the upper one.

Excel spots the holiday spike on its own

Seasonality detection replaces a pile of formulas

Behind the button, Excel uses a method called exponential triple smoothing (ETS). It tracks the current level of sales, the overall trend, and any pattern that repeats on a schedule, all at the same time. It also decides how heavily to weigh recent months against older ones, so there are no smoothing settings to tune yourself.

With three years of data, Excel picked up the 12-month cycle without any input from me. Its 2026 forecast dips to $19,131 in February and climbs to $32,668 in November, matching the store’s real rhythm, including the January slump that follows each holiday season. It carried the growth forward too, so every 2026 month sits above the same month in 2025. A straight-line forecast built with Excel’s TREND function would smooth those swings away, overstating the slow months and underselling the holidays.

Nothing here is a black box, either. Click any forecast cell, and you’ll see an ordinary FORECAST.ETS formula, while the bounds use FORECAST.ETS.CONFINT. You can audit them, copy them, or reuse them elsewhere in the workbook.

Test the forecast before you trust it

Hindcasting pits Excel against months you already know

Hindcasting means asking Excel to predict a period you already have real numbers for. I set Forecast Start to December 2024 and Forecast End to December 2025, so Excel only used 2023 and 2024 to predict 2025.

This is where Forecast Sheet showed its limits. With just two years of history, automatic detection missed the seasonal pattern entirely and drew a nearly straight line through 2025. It predicted $24,370 for January, when the store made $18,210, and $28,707 for December against a real $34,720. It overshot the quiet months and undershot the holiday peak — the exact months a store owner cares about most. On the chart, the flat forecast line looks almost lazy next to the real one climbing into the holidays.

The fix took one setting. Under Seasonality, I chose Set Manually and entered 12 for a yearly cycle. That version kept 11 of 12 real months inside its band, and its 2025 total came within 1.3% of the actual figure.

Measured against the real 2025 figures, the automatic version missed by $5,337 a month on average, while the manual one missed by $874. If you check Include forecast statistics, it adds Excel’s own scorecard, where MAE (mean absolute error, the average gap between its model and your history) fell from $2,954 to $349.

Forecast Sheet still can’t anticipate a price change or a sudden hit product. For those, Excel’s What-If Analysis tools let you test your own assumptions alongside the forecast.

Next year’s plan starts with a range, not a number

A monthly refresh keeps the forecast honest

A single forecast figure invites false confidence. The band around it is the honest part, because it shows how much room to leave for a slow month or an unexpected rush.

Forecast Sheet also works best as a monthly habit. Add each new month as it closes, rebuild the sheet, and see whether reality stays inside the band. If it keeps escaping, check the seasonality setting first, then look for something your history can’t see, like a new sales channel or a supplier delay you haven’t accounted for yet.

OS

Windows, macOS

Supported Desktop Browsers

All via web app

Developer(s)

Microsoft

Free trial

One month

Price model

Subscription

iOS compatible

Yes