For most monthly or quarterly business data, the best way to forecast in Excel is Data > Forecast Sheet. It builds an ETS forecast, chart, and confidence bounds from your dated history, so it is a strong starting point when patterns may repeat by season; use a different method when your data or decision calls for it.
What to Look For
Choose a forecasting method based on the shape of the history and the decision you need to make—not on which formula looks most advanced. Before forecasting, make sure the source data is trustworthy: dates should be real Excel dates, sorted from oldest to newest, and recorded at a consistent interval. A blank month, duplicate date, one-off event, or change in how sales were recorded can affect a forecast more than the choice of tool.
- Seasonality and trend: Monthly sales that rise every December need a method that can model seasonal repetition. A steady upward line may be adequately forecast with a linear trend. A flat, noisy series may call for smoothing.
- History length: A seasonal method needs enough observations to identify a repeating cycle. Two years of monthly data gives only two annual cycles; more history is often better, provided the business has not changed substantially.
- Drivers: If the outcome depends on price, advertising, staffing, or another measurable factor, a date-only forecast may miss the reason the numbers change. Consider regression or a model built around those drivers.
- Transparency and maintenance: A chart-based Forecast Sheet is quick to inspect. Worksheet formulas are easier to copy into a model, while a regression analysis needs more setup and assumptions to explain.
- Forecast error: Compare predictions with data that was not used to build the forecast. A method that fits historical values closely can still perform poorly on future periods. Check error by period and look for consistent over- or underestimation.
Keep a simple baseline, such as last period or a recent average, for comparison. If a more complicated method does not improve on that baseline over several held-out periods, it may not be worth the extra complexity.
1. Excel Forecast Sheet: Best Starting Point for Seasonal Data
In Excel for Microsoft 365 on Windows, put a regular series of dates in one column and the corresponding numeric observations in the next. Select the two columns, then choose Data > Forecast Sheet. Choose a line or column chart, set the forecast end date, and open Options to review the confidence interval, seasonality, and treatment of missing points. Excel creates a new worksheet containing the forecast and chart, leaving the source data in place.
This is the best default for many practical time series because it applies Excel’s ETS approach, which can account for level, trend, and seasonality. The chart makes the projection and its uncertainty easier to communicate than a single number. Inspect the shaded confidence bounds: they are a useful reminder that a forecast is uncertain, not a target or guarantee.
Trade-offs: Forecast Sheet extrapolates patterns in the historical series; it does not know that a promotion, supply interruption, price change, or new competitor is coming. Its automatic seasonal detection can also be unreliable with very short or inconsistent history. If the timeline has gaps or duplicate dates, confirm the data handling options and consider cleaning or aggregating the source first. The feature and its placement can vary by Excel version and platform; if it is unavailable in your installation, use a worksheet function or another option below.
Best for: Users who want a fast, reviewable forecast of regularly spaced data such as monthly revenue, weekly orders, or quarterly demand, especially when seasonality is plausible.
2. FORECAST.ETS: Best for a Repeatable Seasonal Formula
Use Excel’s FORECAST.ETS function when you want an ETS forecast directly in a worksheet cell rather than a separate generated report. It forecasts a target point from a values range and a matching timeline range. Its optional arguments let you specify seasonality, how to complete missing timeline points, and how to aggregate duplicate dates. For automatic seasonality, use the function’s default behavior or set the seasonality argument to 1; a specified seasonal period such as 12 can suit monthly data with annual repetition.
This method is useful for models that need forecasts to update when source data changes, or when you want to fill a forecast column and link the results to other calculations. It shares the key strength of Forecast Sheet—ETS can represent seasonal patterns—without requiring a manually copied chart output. The function is available in current Microsoft 365 Excel; check function availability if you share the workbook with users on older releases.
Trade-offs: The output depends on the data and argument choices, and the function does not add business drivers by itself. Monthly seasonality should not be assumed just because there are twelve months in a year: examine the series and allow enough historical cycles to support the pattern. Dates must form a usable timeline, and a series with missing or irregular observations needs careful handling. Document any non-default seasonality or aggregation setting so another person can understand the forecast.
Best for: Analysts who want a formula-driven, refreshable seasonal forecast and are comfortable checking timeline quality and function arguments.
3. FORECAST.LINEAR: Best for a Clear, Straight Trend
When a series moves at a roughly steady rate and has no strong seasonal shape, a linear trend is easy to explain. Give Excel a numeric period index, such as 1, 2, 3, and so on, and the observations for those periods. With period numbers in A2:A6, observations in B2:B6, and the next period number 6 in A7, enter =FORECAST.LINEAR(A7,$B$2:$B$6,$A$2:$A$6). If B2:B6 contains 100, 110, 120, 130, and 140, the formula returns 150: the fitted increase is 10 units per period, extended one period beyond 140.
The function calculates a straight-line estimate using the known x and y values. The example arithmetic is a rise of 10 for each one-step increase in period, so period 6 is 100 + 5 × 10, or 150. This makes the result intuitive and easy to reproduce in a worksheet. A scatter chart with a linear trendline can also help you see whether a straight-line assumption looks reasonable before you use it.
Trade-offs: A straight line does not capture seasonal peaks, changing growth rates, saturation, or sudden shifts. Forecasting far beyond the observed range magnifies that risk. Do not treat a high historical fit as proof that the trend will continue; compare the projected values with a recent-average baseline and business context.
Best for: A short-term projection for data with a stable, approximately straight trend, or a simple benchmark against which to judge more complex methods.
4. Moving Average: Best for Smoothing Noisy Data
A moving average forecasts the next period using the average of a chosen number of recent observations. It is especially handy when individual weeks or months are volatile but the underlying level changes slowly. With the latest three observations in B2:B4—100, 120, and 110—enter =AVERAGE(B2:B4) in the next-period forecast cell. The result is 110, since (100 + 120 + 110) ÷ 3 = 110. As new actuals arrive, adjust or fill the range so it always contains the most recent three periods.
The method is transparent, requires little setup, and can dampen random spikes. You choose the window length: a shorter window reacts faster to recent changes, while a longer one gives a smoother line. A moving average chart can help communicate the underlying direction without suggesting that each noisy observation is meaningful.
Trade-offs: This basic method does not model seasonality or explain causes. It also tends to lag when the level is rising or falling, and the chosen window can substantially change the result. Compare more than one window against prior actuals rather than selecting the one that makes the historical chart look prettiest.
Best for: Short-horizon operational estimates for noisy but relatively stable data, such as a rough staffing baseline or routine weekly volume.
5. Regression with the Analysis ToolPak: Best When Drivers Matter
Use regression when you have a reason to believe the outcome changes with measurable inputs. For example, monthly orders might relate to advertising spend, price, and the number of selling days. Excel’s Analysis ToolPak can fit a regression model: enable the add-in if needed, then choose Data > Data Analysis > Regression on Windows. Set the outcome column as the input Y range and the driver columns as the input X range, indicate whether the first row contains labels, and choose an output location. The output includes coefficients and statistical summaries.
To create a future estimate, you need values for the drivers for the forecast period and must apply the fitted coefficients consistently. This is more informative than extending sales alone when the drivers are relevant, and it can support questions such as how a planned change in price might affect volume. Keep the training data aligned by period and do not include variables that are merely correlated by coincidence or that would not be known when making the forecast.
Trade-offs: Regression is not automatically better because it has more inputs. It can be sensitive to outliers, correlated predictors, small samples, and relationships that change over time. A regression summary is not a guarantee of predictive accuracy. Test predictions on later periods and use domain judgment before relying on the result. On Mac, the Analysis ToolPak is managed through Tools > Excel Add-ins; the add-in may need to be enabled before the Data Analysis command appears.
Best for: Users with a defensible business question and historical driver data, rather than anyone who simply needs a quick time-based projection.
How to Choose
Start with the use case. For a quick monthly sales outlook with a repeating seasonal pattern, begin with Forecast Sheet. For a forecast column that updates inside an existing model, choose FORECAST.ETS if seasonality matters. For a stable, straight trend, use FORECAST.LINEAR. For a noisy series without clear seasonality, compare a moving average. When the outcome depends on inputs you can forecast or set, consider regression.
Match effort to budget and value. These Excel options are built into many Microsoft 365 desktop installations, so the main cost is time spent preparing, validating, and maintaining the data. A small team making a rough internal plan may not need a regression model. A costly inventory or capacity decision can justify more careful validation, clearer assumptions, and review by someone who understands the business process. If Excel’s built-in methods cannot represent the problem, the right next step may depend on available skills, data, and budget—not a claim that one tool is universally best.
Test before committing. Hold back several recent periods, build the forecast using only earlier data, and compare predictions with the actual values. Repeat that process across more than one forecast origin if possible. Look at both average error and whether errors are consistently positive or negative. For seasonal data, ensure the test includes relevant seasons. A forecast that repeatedly misses a December peak is not useful just because its average error across the year looks acceptable.
Keep assumptions visible. Record the data range, forecast horizon, chosen method, seasonal period or moving-average window, and any known events not represented in the model. Review forecasts when actuals arrive. If a business process changes, old history may no longer be a reliable guide. Do not extend a method far beyond the history simply because Excel will calculate a number.
Frequently Asked Questions
Is Forecast Sheet the same as FORECAST.ETS?
Forecast Sheet creates a chart and report using Excel’s ETS forecasting approach. FORECAST.ETS places an ETS estimate in a worksheet cell and offers arguments for controlling how the timeline is handled.
How much historical data do I need?
There is no universal minimum that guarantees a good forecast. You need enough observations to represent the patterns you expect to repeat. For annual seasonality in monthly data, two years provides only two cycles, so check the result carefully and use more history when it remains relevant.
Can Excel forecast missing months?
Some Excel forecasting tools can handle missing timeline points through their options, but missing records may also indicate a data problem. Confirm whether a month truly had no activity or whether its value was not recorded, and keep dates consistent before forecasting.
Why does my forecast look too high or too low?
Check for outliers, incorrect dates, duplicates, an overly long horizon, and a pattern that has recently changed. Also compare the forecast with a simple baseline. Excel extrapolates the patterns and settings it receives; it does not know about future business events unless you model them.
Should I use a trendline or a forecast formula?
A chart trendline is useful for visual inspection and explanation. A formula is easier to link to a model and recalculate. Both rely on assumptions about the data, so validate the predictions against periods excluded from fitting.
Bottom Line
Start with Excel Forecast Sheet for a clean, regularly spaced series when you want a quick seasonal forecast with a chart and confidence bounds. Switch to FORECAST.ETS for an integrated formula, FORECAST.LINEAR for a convincing straight trend, a moving average for noisy stable data, or regression when measurable drivers matter. Whichever you choose, clean the timeline, compare against a baseline, and test the forecast on data the method did not use.
Related
- Excel Content Analysis: A Practical Guide
- Add Percentages to an Excel PivotTable
- Accounting Year-End Checklist Template for Excel Users
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Merge Cells in Excel: The Easy Way and Safer OptionsThe easiest way to merge cells in Excel for Microsoft 365 on Windows is to select the cells,…
- How to Apply a Filter in Google Sheets: Narrow DataTo filter data in Google Sheets in a web browser, select your table, choose Data > Create a…
- Excel Replace Shortcut: Find and Replace Keys ExplainedIn Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and Replace with the Replace tab…
- Box and Whisker Plot in Excel: A Step-by-Step GuideTo build a box and whisker plot in Excel for Microsoft 365 on Windows, arrange each data group…
- Excel Slicer Connections: Link One Slicer to PivotsExcel slicer connections determine which PivotTables respond when you click a slicer. In Excel for Microsoft 365 on…
- Excel Keyboard Shortcut to Zoom Out: Keys and OptionsIn Excel for Microsoft 365 on Windows, there is no dedicated keyboard shortcut that simply lowers worksheet zoom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.