To make a quick forecast in Excel for Microsoft 365 on Windows, put dates in one column and the matching values in the next, select both columns, then choose Data > Forecast Sheet. Set the forecast end date in the dialog and select Create; Excel builds a forecast worksheet with projected values, a chart, and confidence bounds.
Use Forecast Sheet for a fast result
Forecast Sheet is the quickest built-in option when you have a time-ordered series, such as monthly sales, weekly website visits, or daily demand. It creates a separate worksheet, so your original data remains available. The new sheet typically includes the historical values, forecast values, upper and lower confidence-bound values, and a chart that makes the trend easier to scan.
- Arrange the time points in the first column and the observations in the second. Include a header row if useful, but keep the data itself contiguous.
- Select the date and value columns, including the headings if you have them.
- On the ribbon, choose Data > Forecast Sheet.
- In the dialog, review the chart preview and enter the date or time at which the forecast should end.
- Open Options if you need to change seasonality or the confidence interval, then choose Create.
The forecast horizon is the number of future time points Excel must estimate between the last observed point and the end date. For example, if the last observation is March and you set the end date to September on a monthly series, Excel forecasts six months. A longer horizon usually means more uncertainty, so start with a period that is useful for the decision rather than extending the chart as far as Excel allows.
Prepare the time series first
Forecast quality depends heavily on the input. Use actual Excel dates or times in the timeline column, not text that merely looks like a date. Sort the records from earliest to latest, and use a consistent frequency: every day, every week, or every month, for example. A missing month or a mixture of monthly and quarterly entries can make the pattern harder to interpret.
Check that each timeline point has a corresponding numeric observation. Remove blank rows within the series and investigate errors or text values in the number column. If a period has no observation, decide whether it should truly be missing or whether a value can be supplied from a reliable source; do not fill gaps with guesses simply to make the chart look complete. If the data contains duplicate dates, review them before forecasting. Several sales transactions on one date may need to be summed into a daily total rather than treated as separate observations.
- Use one row per time interval and keep the interval length consistent.
- Confirm that dates are recognized as dates, not left-aligned text strings.
- Look for exceptional events, such as a one-time promotion or shutdown, that could distort the pattern.
- Keep a copy of the source data before editing or summarizing it.
How Excel calculates the forecast
Forecast Sheet uses Excel’s exponential-smoothing time-series approach, associated with the FORECAST.ETS family of functions. In practical terms, it looks for patterns in earlier observations and uses them to estimate later ones. Depending on the series, those patterns can include a general level or trend and a repeating seasonal cycle. This is different from simply extending the last value or drawing a straight line through every point.
Excel can detect seasonality automatically, or you can specify a seasonal length in the Forecast Sheet options. For monthly observations with a yearly cycle, that cycle is 12 points; for weekly data with a weekly pattern, it might be 7 daily points if the observations are daily. A seasonal setting should match the interval and a pattern that is genuinely repeated. Choosing an unsuitable period can create a forecast that looks mathematically tidy but does not reflect how the process behaves.
The method assumes the historical series contains information relevant to the future. It does not know about a planned price change, a new competitor, a supply interruption, or a policy change unless those effects are reflected in the data pattern. Treat the result as a statistical baseline, not as a promise or a complete business plan.
Read the chart and confidence bounds
The central forecast is Excel’s estimated value for each future time point. The shaded region or accompanying upper and lower values represent a confidence interval, giving a view of uncertainty around those estimates. Forecast Sheet uses a 95% confidence interval by default, and you can adjust the confidence interval in its options. A wider interval indicates that the forecast is less precise; it is not a guarantee that the actual result will fall inside the band.
Compare the forecast with the history before acting on it. Does the projected direction make sense? Does Excel repeat a seasonal rise or fall that is visible in previous years? Are the bounds so wide that the estimate is not useful for the decision? A forecast of 500 with a range from 300 to 700 may be less actionable than a stable estimate with a narrow range, even though both display a single central number.
Do not confuse a confidence interval with a target or best-case/worst-case plan. It describes uncertainty under the model’s assumptions. It does not account automatically for every risk, and the interval can be misleading if the history is short, irregular, or changing quickly.
A small example of a linear forecast
For a simple straight-line estimate, Excel also provides FORECAST.LINEAR. This is useful when you want a quick trend projection and have reason to model the relationship as linear; it is not the same as Forecast Sheet’s seasonal time-series method.
With period numbers 1 through 5 in A2:A6 and values 100, 110, 120, 130, and 140 in B2:B6, enter this formula in another cell to estimate the value for period 6: =FORECAST.LINEAR(6,B2:B6,A2:A6). The result is 150: the observations rise by 10 for each one-period increase, so the fitted straight-line projection continues that pattern by one step.
This example is deliberately regular. Real data rarely increases by exactly the same amount each period. If a series has seasonal peaks or dips, a single linear projection may miss them. If it has an abrupt change, a linear formula can keep extending a trend that is no longer relevant. Choose the method based on the pattern and purpose, not just on which formula is easiest to type.
Options to check before creating the sheet
The Forecast Sheet dialog lets you set the forecast end point and review options such as seasonality and the confidence interval. Automatic seasonality is a reasonable starting point when you are unsure, but inspect the result against the historical chart. If you know a meaningful cycle from the process—for instance, 12 monthly points in an annual pattern—you can set it explicitly. Avoid forcing a seasonal cycle just because a calendar-based series exists; the values should show evidence of a repeating pattern.
Excel also has options for handling missing points and aggregating repeated timestamps in its ETS calculations. Those settings can help the calculation run, but they do not replace data review. For instance, averaging repeated values may be wrong if each row is a transaction and the intended measure is total sales. Summarize the source according to what the quantity means, then forecast the resulting series.
After Excel creates the worksheet, review its cells and chart rather than relying only on the visual. You can change chart labels, number formats, or presentation to suit your audience. Keep the forecast values and bounds intact if readers need to distinguish the estimate from actual results.
Common problems and how to fix them
- Forecast Sheet is unavailable: Confirm that the selection includes a valid timeline and numeric values, and that you are using a supported desktop Excel version. On Mac, the ribbon layout and feature availability can differ by Excel build; if the command is not present, use an available forecasting function or open the workbook in Excel for Microsoft 365 on Windows.
- The dates appear out of order: Sort the complete dataset by the date column, keeping each value attached to its date. Sorting only one column can corrupt the pairings.
- The forecast looks flat or odd: Check for text-formatted numbers, large outliers, missing intervals, or an unsuitable seasonal setting. A few extreme observations can pull estimates away from the typical pattern.
- The forecast does not match expectations: Expectations may include information not present in the historical series. Add relevant, verified data where appropriate, or use the Excel estimate as one input alongside informed assumptions.
- The chart suggests certainty: Display and discuss the confidence bounds, and explain that uncertainty grows as the forecast extends farther beyond observed data.
When to use a different approach
Forecast Sheet is convenient for a quick, visual time-series estimate. Use a different approach when the goal requires explicit assumptions, such as separately modeling price, volume, staffing, or a known policy change. A driver-based model lets you alter those inputs directly, but it requires clear assumptions and careful checks. For a basic trend without seasonality, FORECAST.LINEAR may be easier to audit. For complex forecasts, specialized statistical tools or a model designed around the process may be more appropriate.
Whatever method you choose, compare past predictions with what actually happened when possible. For example, hide the latest few months of actual data, create a forecast using earlier months, and compare the estimates with the withheld observations. This is a simple way to see whether the approach has been useful on your series. A model that fits historical data well is not automatically accurate in the future, but a back-check can expose an obviously poor choice of method.
Frequently Asked Questions
Is Forecast Sheet the same as FORECAST.LINEAR?
No. Forecast Sheet uses an exponential-smoothing time-series approach that can account for seasonality, while FORECAST.LINEAR estimates a straight-line relationship. The appropriate choice depends on the shape and repetition in your data.
How many data points do I need?
There is no single number that guarantees a useful forecast. You need enough observations to show the pattern you want Excel to estimate. A seasonal forecast needs enough history to reveal repeated cycles; a very short series cannot reliably establish an annual pattern.
Can Excel forecast with missing dates?
It may be able to handle missing points through its ETS settings, but first determine why the dates are absent. A genuine zero, an unrecorded value, and a period when no measurement was possible are different situations and should not be treated as interchangeable.
Can I forecast sales or demand by month?
Yes. Put valid monthly dates in one column and the corresponding monthly totals in the next, then create a Forecast Sheet. Review seasonality, unusual promotions, and the confidence bounds before using the estimate for planning.
Why does my forecast change when I extend the history?
New observations can alter the estimated trend, seasonal pattern, and recent level. That is normal: the forecast is recalculated from the data supplied, so a new period can provide evidence that changes the projection.
Related
- Excel Redo Shortcut: How to Repeat an Action in Excel
- Excel Insert Row Shortcut: Steps for Windows and Mac
- MS Excel Open: How to Launch and Start a Workbook
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Freeze Panes: Change Them While Scrolling?No—Excel does not change the Freeze Panes boundary automatically as you scroll. To move it, unfreeze the current…
- Excel: Consolidate Text Into One CellYes. In Microsoft 365, use TEXTJOIN to combine text from several cells into one, with a separator and…
- Excel Heatmap Guide: Create, Customize, and Read OneTo make a heatmap in Excel for Microsoft 365, select the numeric cells, then choose Home > Conditional…
- How to Hide Columns in Excel Using Keyboard ShortcutsTo hide a column in Excel for Microsoft 365 on Windows, select a cell in it, press Ctrl+Space…
- Google Sheets: Highlight Duplicates in a ColumnTo highlight duplicate values in a Google Sheets column, select the cells, choose Format > Conditional formatting, and…
- Google Sheets Timestamp: Insert, Format, and AutomateTo insert a fixed timestamp in Google Sheets, select a cell and press Ctrl+; to enter the current…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.