Excel's Forecast Sheet turns a column of dates and a column of monthly totals into a forecast chart with a shaded confidence band. Select your data, choose Data, then Forecast Sheet, set the end date and seasonality, and Excel builds a new sheet using the FORECAST.ETS family of functions. It only gives something meaningful when you have enough months, ideally three years.
How do I prepare the data?
- Column A holds a real date for the first day of each month, such as 1-Apr-2023, and column B the total for that month. Month names typed as text will not work as a timeline.
- The steps between dates must be regular. Monthly is fine; a missing month is handled, but several gaps are not.
- Use complete months only, so leave the current part-month out.
- Keep the totals to everyday spending if you can; large one-off items push the forecast around.
What do I click?
- Select the two columns, including their headers.
- Go to Data, and in the Forecast group choose Forecast Sheet.
- Choose a line or column chart and set the forecast end date, for instance twelve months after your last data point.
- Open Options to set the confidence interval, the seasonality and how missing points are filled.
- Click Create. Excel adds a new sheet with a table and a chart.
The table on the new sheet has the timeline, your values, the forecast, and a lower and an upper confidence bound. These come from FORECAST.ETS and FORECAST.ETS.CONFINT, so you can also call them yourself, for example =FORECAST.ETS(A38,$B$2:$B$37,$A$2:$A$37,12) for a monthly series that repeats yearly.
What do seasonality and the confidence interval mean in plain words?
| Option | In plain words | What to choose for monthly household spending |
|---|---|---|
| Seasonality | The number of months after which the pattern repeats | Leave on automatic first; if the chart looks flat, try 12 for a yearly cycle |
| Confidence interval | The width of the shaded band around the forecast | The default is 95 percent; a lower setting gives a narrower band that is more often wrong |
| Fill missing points using | How Excel treats a blank month | Interpolation is usually fine; zeros suit months you truly did not spend |
| Aggregate duplicates | What to do if two rows share a date | Sum, if you have not already totalled each month |
The band is a range in which the real figure is expected to fall most of the time, not a promise. A wide band is Excel telling you the history is bumpy.
Is there enough history for a seasonal forecast?
Seasonal methods learn the yearly shape by seeing it repeat, so they need at least two full cycles, which means 24 months for a yearly pattern, and three years is more comfortable. With twelve months, Excel has nothing to compare the festive month with, and with fewer than that the forecast is little more than a guess. If you have less, use a simple linear trend, or find older statements and fill the gap.
How can I use the band to plan?
Say Excel's forecast for next November is ₹52,000 with bounds of ₹44,500 and ₹59,500. The band is 7,500 either side. A cautious plan keeps the forecast as the budget and holds a cushion of ₹7,500 in reserve, so that a high month is covered without rebuilding the whole budget. A rougher plan uses only the upper bound of ₹59,500 for the months you know are heavy, such as the festival month.
What are the common problems?
- The timeline has text or mixed dates. Convert them to proper dates.
- The forecast starts at an odd point. Check that Excel has chosen the right start date in the pane.
- The chart looks fine but the numbers do not match your sense. Open the table and look at the figures before the chart.
- Forecast Sheet is not available in every edition of Excel, so check the Data tab on your own copy.
A forecast like this depends on three tidy years of monthly totals. SpendixAI can bring in statements, keep categories consistent and give you monthly report figures, so the history you feed Excel is less of a chore to prepare.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

