Data → Forecast Sheet turns a column of dates and values into a forecast table and chart, with upper and lower confidence bounds, in one click. Behind it is the FORECAST.ETS function, which you can also use directly.
The data
Petrol prices in India from 2007 to 2021, one value per year. Forecast Sheet needs a timeline (dates or numbers) and values, with evenly spaced steps.
Step 1: Create the forecast
Select both columns → Data → Forecast Sheet.
Step 2: Set the end and options
Set Forecast End, here 2027. Click Options to change the confidence interval (95% by default), seasonality, and how missing points are filled.
Step 3: Read the result
Click Create. A new sheet holds a table and a chart.
The wider the gap between the bounds, the less certain the forecast. With only 15 yearly points, treat a 2027 value as a rough guide.
The formulas behind it
| Column | Formula |
|---|---|
| Forecast | =FORECAST.ETS(A17,$B$2:$B$16,$A$2:$A$16,1,1) |
| Lower and upper bound | =forecast ∓ FORECAST.ETS.CONFINT(...) |
For a straight-line trend instead, use =FORECAST.LINEAR(2027,B2:B16,A2:A16) or TREND.
Things to know
- The timeline must have a consistent step: monthly, yearly and so on. Up to 30% missing points are filled in automatically.
- Seasonality is detected automatically; for monthly sales set it to 12 if Excel misses it.
- A forecast only continues past patterns. It can’t know about price controls, tax changes or other one-off events.