How to Forecast in Excel Based on Historical Data
You can forecast in Excel three ways. Use Forecast Sheet for a one-click forecast with a chart. Use FORECAST.LINEAR when your numbers grow in a straight line. Use FORECAST.ETS when they rise and fall with the seasons. All three need past data with evenly spaced dates.
| Method | Best for | Works in |
|---|---|---|
| Forecast Sheet | A fast forecast with a chart and a range | Excel 2016 or later, Windows only |
| FORECAST.LINEAR | Steady growth or decline | Excel 2016 or later (older versions use FORECAST) |
| FORECAST.ETS | Data with seasons, like holiday spikes | Excel 2016 or later |
Want to follow along? Download the free forecasting practice workbook with every sample table and the answers.
How do you forecast in Excel based on historical data?
Put your dates in column A and your numbers in column B. Then pick a method from the table above. Excel finds the pattern in your past numbers and carries it forward.
How should you set up your data?
Excel gives better results when your data follows these rules:
- Dates are evenly spaced, like the first day of every month.
- Each date has one value. If a date shows up twice, Excel averages the values by default.
- Gaps are small. Excel can fill missing points as long as fewer than 30% are missing.
- Messy daily rows are summed into weeks or months first.
How much history do you need?
For seasonal data, give Excel at least two full cycles. For monthly sales with a yearly pattern, that means 24 months. With fewer than two cycles, Excel cannot spot the seasons. When it finds no seasonal pattern, it uses a straight-line trend instead.
How do you create a forecast sheet in Excel?
- Click any cell inside your dates and values.
- Go to Data > Forecast Sheet (in the Forecast group).
- Pick a line chart or a column chart.
- Choose a date in the Forecast End box.
- Click Create.
Excel adds a new sheet in front of your data. It holds a table of past and predicted values, plus a chart. It also shows a 95% confidence range by default. That is the band where Excel expects 95% of future values to land.
What do the Forecast Sheet options do?
| Option | What it does |
|---|---|
| Forecast Start | Starts earlier, so you can compare with real numbers |
| Confidence Interval | Shows or hides the range (95% by default) |
| Seasonality | Finds the cycle length for you, or lets you set it, like 12 for monthly data |
| Fill Missing Points Using | Averages nearby points, or treats gaps as zero |
| Aggregate Duplicates Using | Averages values that share a date, or uses median or count |
| Include Forecast Statistics | Adds a table of accuracy numbers |
Can you control where the forecast ends?
You can set the end date in the Forecast End box. Excel has no setting that caps the values. The forecast column is built from FORECAST.ETS formulas, so you can wrap them. For example, =MAX(0, FORECAST.ETS(...)) stops results from going below zero.
What do the forecast statistics mean?
| Statistic | Plain meaning |
|---|---|
| Alpha | How much weight recent values get. Closer to 1 means recent data counts more. |
| Beta | The same idea, for the trend |
| Gamma | The same idea, for the seasonal pattern |
| MASE | Error compared with a simple benchmark guess. Below 1 means Excel did better. |
| SMAPE | Average error as a percent |
| MAE | Average error in your own units |
| RMSE | Like MAE, but big misses count more |
How do you add a Forecast Sheet in Excel for Mac?
You can’t. The Forecast Sheet button is only in desktop Excel for Windows. It is not in Excel for Mac or Excel for the web. On a Mac, build the same result with formulas: FORECAST.ETS for the forecast, FORECAST.ETS.CONFINT for the range, and a line chart. Type =FORECAST.ETS( in a cell to check that your version has it.
How do you forecast in Excel using a formula?
Use FORECAST.LINEAR for straight-line growth and FORECAST.ETS for seasonal data. Both need three things: the date you want to predict, your past values, and the dates of those values.
What does the FORECAST function do?
FORECAST draws the best-fitting straight line through your past numbers and reads the next value off that line. FORECAST.LINEAR gives the same result. Microsoft recommends FORECAST.LINEAR, since the old name will be retired at some point.
The syntax looks like this:
=FORECAST.LINEAR(x, known_y's, known_x's)Example. Monthly sales for months 1 to 6 sit in B2:B7, and the month numbers sit in A2:A7.
| Month (A) | Sales (B) |
|---|---|
| 1 | 120 |
| 2 | 135 |
| 3 | 128 |
| 4 | 150 |
| 5 | 162 |
| 6 | 170 |
| 7 | =FORECAST.LINEAR(A8, B2:B7, A2:A7) |
The formula returns about 179.5 for month 7.
How do you use FORECAST.ETS for seasonal data?
The syntax looks like this. The last three parts are optional:
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])- seasonality: 1 (the default) finds the cycle for you. 0 means no seasons. Any other number sets the cycle length, like 12.
- data_completion: 1 (the default) fills gaps with the average of nearby points. 0 treats gaps as zero.
- aggregation: sets how values that share a date get combined. The default is the average.
Helper functions: FORECAST.ETS.CONFINT gives the plus-or-minus range, FORECAST.ETS.SEASONALITY shows the cycle length, and FORECAST.ETS.STAT returns the statistics.
Which forecast method should you use?
Match the method to the shape of your data.
| Your data looks like this | Use | Why |
|---|---|---|
| Grows or shrinks by about the same amount each period | FORECAST.LINEAR or TREND | Fits a straight line |
| Rises and falls on a cycle, like holiday peaks | FORECAST.ETS or Forecast Sheet | Finds the cycle and repeats it |
| Grows by about the same percent each period | GROWTH | Fits a curve that compounds |
| Jumps around with no clear pattern | Moving average | Smooths out the noise |
| Depends on several things, like price and ad spend | TREND or the Regression tool | Uses more than one input |
How does Excel forecasting work?
FORECAST.LINEAR uses linear regression. It finds the line y = a + bx that sits closest to all your points. Here, b is the slope and a is where the line starts.
FORECAST.ETS and Forecast Sheet use exponential smoothing, in a version Microsoft calls AAA. Recent values count more than old ones, and Excel tracks the level, trend and seasons separately.
What other ways can you forecast in Excel?
How do you forecast monthly data with a moving average?
Average the last few months to predict the next one. Using the sales table above, =AVERAGE(B5:B7) averages 150, 162 and 170 and gives 160.7 for month 7. You can also use Data > Data Analysis > Moving Average.
How do you forecast using regression?
FORECAST.LINEAR is regression with one input, such as time. With several inputs, use =TREND(known_y's, known_x's, new_x's) and give it more than one x column. For R squared and more, turn on the Analysis ToolPak and use Data > Data Analysis > Regression.
How do you forecast annual data?
Yearly data has no seasons inside it, so use FORECAST.LINEAR, or FORECAST.ETS with seasonality set to 0, like =FORECAST.ETS(A12, B2:B11, A2:A11, 0).
How do you forecast population growth?
Population usually grows by a percent, not by a fixed number, so use GROWTH. Say a town of 50,000 grows 3% a year and reaches 56,275 in year 5. With years 1 to 5 in A2:A6, =GROWTH(B2:B6, A2:A6, 6) predicts about 57,963 for year 6.
How do you forecast sales in Excel?
- Collect at least 24 months of sales, one total per month.
- Run Forecast Sheet or FORECAST.ETS on it.
- Adjust for planned events, like a price change or a new product.
- Test it against months you already have.
- Run it again each month when new sales come in.
How do you forecast several products at once?
Forecast Sheet handles one series per run. For many products, put dates in column A and each product in its own column. With 24 months in rows 2 to 25, type =FORECAST.ETS($A26, B$2:B$25, $A$2:$A$25) in B26. Copy it across and down. The dollar signs lock the dates while each column uses its own sales.
How do you handle a one-time spike?
A one-time event, like a big sale, can make Excel expect it again. Before you forecast, swap that value for a normal estimate and write down the change. Becker’s test on US auto sales shows the risk. Its forecasts for March to June 2020 came in far above real sales, because the data could not predict COVID lockdowns.
How do you check if your forecast is accurate?
Hide your last few months, forecast them, and compare with what really happened. Using months 1 to 6 from the sales table, FORECAST.LINEAR predicts months 7 to 9. Here it is next to sample actual sales:
| Month | Actual (C) | Forecast (D) | Error |
|---|---|---|---|
| 7 | 175 | 179.5 | 2.55% |
| 8 | 192 | 189.6 | 1.27% |
| 9 | 196 | 199.6 | 1.86% |
With actuals in C2:C4 and forecasts in D2:D4, use these formulas:
- MAPE (average percent error):
=AVERAGE(ABS((C2:C4-D2:D4)/C2:C4))gives 1.89%. - MAE (average error in units):
=AVERAGE(ABS(C2:C4-D2:D4))gives 3.52. - RMSE (big misses count more):
=SQRT(SUMXMY2(C2:C4, D2:D4)/COUNT(C2:C4))gives 3.61.
Lower is better for all three. In Excel 2019 or older, press Ctrl+Shift+Enter after typing the MAPE and MAE formulas. Run the same test on each method and keep the one with the lowest error.
Why is your forecast wrong or showing an error?
| What you see | Likely cause | Fix |
|---|---|---|
| #VALUE! from FORECAST | x is not a number | Use a number or a real date |
| #N/A from FORECAST | The two ranges are different sizes | Make known_y’s and known_x’s the same length |
| #DIV/0! from FORECAST | All x values are the same | Use different periods |
| FORECAST.ETS returns an error | Dates are not evenly spaced | Use one step size, like every month |
| A straight line where you expected seasons | Excel found no seasonal pattern | Add more history or set seasonality by hand |
| Values below zero | The trend points down | Wrap the formula in MAX(0, …) |
| No Forecast Sheet button | Mac, Excel for the web, or a version before 2016 | Use the formulas instead |
Can Copilot or ChatGPT forecast in Excel?
Yes, but check the result. Microsoft’s Copilot example uses this prompt: “Forecast revenue by category for the next two years, with historic data in a single chart.” Copilot runs that analysis with Python in Excel and needs Copilot in Microsoft 365. ChatGPT and Claude can write the formulas if you describe your columns. Check any AI forecast with the accuracy test above.
Which Excel formulas help most with financial forecasting?
| Formula | Use it for |
|---|---|
| FORECAST.LINEAR or TREND | Straight-line projections |
| FORECAST.ETS | Seasonal revenue or costs |
| GROWTH | Growth by a steady percent |
| SUMIFS | Totals by month, category or account |
| EDATE or EOMONTH | Building a monthly date list |
| PMT | Loan payments |
| NPV | Today’s value of future cash |
Is Excel good enough for forecasting?
For one business and a few dozen products, yes. Excel covers trends, seasons and a confidence range at no extra cost. It gets harder to manage with thousands of products, because Forecast Sheet runs one series at a time.
Practice challenge
A shop sold these units over 8 quarters: 40, 44, 47, 52, 55, 58, 63, 66. Put quarters 1 to 8 in A2:A9 and units in B2:B9. Try each task before you read the answer.
- Task 1: Forecast quarter 9 with FORECAST.LINEAR.
- Task 2: Forecast quarter 9 with a 3-quarter moving average.
- Task 3: Which answer fits a shop that grows every quarter?
Answers
Task 1: =FORECAST.LINEAR(9, B2:B9, A2:A9)
returns about 69.9
Task 2: =AVERAGE(B7:B9)
returns about 62.3
Task 3: FORECAST.LINEAR, because a moving average trails a rising trendFree Excel forecasting template
Download the free forecasting practice workbook. It includes the sample data, every formula on this page, the accuracy test and the challenge.
FAQ
Is the FORECAST function going away?
It still works today. Microsoft recommends FORECAST.LINEAR for new work.
Does Forecast Sheet work in Excel for the web?
No. It is only in desktop Excel for Windows.
Can Excel forecast more than a year ahead?
Yes. Set a later Forecast End date or target date. The confidence range usually gets wider the further out you go.
About this guide
Practice file: Download the free forecasting practice workbook with every sample table, every formula and the answers.