Summary
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 271,420 in 2025, and every November and December spiked well above the rest of the year.
- Click any cell in your data, then go to Data > Forecast Sheet.
- Set Forecast End to the last month you want predicted. Excel suggested December 2027, so I changed it to December 2026.
- 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 36,076, but the Lower 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 3,800. You can change the level under Options, too. At 80%, December’s range tightens to 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 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 18,210, and 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 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 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.