To enable Excel’s Forecast Sheet, put a chronological timeline in one column and the matching numeric values in the next, select both columns, then choose Data > Forecast Sheet. Set the forecast end date and select Create. Excel adds a new worksheet with a forecast chart and supporting data; there is no separate switch to turn on first.
This guide is for Excel for Microsoft 365 on Windows. The Forecast Sheet feature uses historical data to estimate future values, so the result is a projection rather than a guaranteed outcome. Check the source data and forecast settings before using the results for planning.
What You’ll Need
- A desktop version of Excel that includes Forecast Sheet, such as Excel for Microsoft 365. If the command is missing in Excel for the web or on another platform, open the workbook in desktop Excel for Windows and check for updates.
- A single, uninterrupted data range with dates or times in the first column and the corresponding measurements in the next column. For example, use month-end dates in column A and monthly sales in column B.
- Enough reliable historical observations to show a useful pattern. More data generally gives Excel more evidence to work with; for a seasonal pattern, include multiple complete cycles when possible.
Step-by-Step
- Arrange the timeline and values. Put one date or time per row in the first column and the value recorded at that point in the second. Add clear headings, such as Month and Units Sold. Keep the data together in a rectangular range, without blank rows, totals, notes, or extra series inside it. The first row can contain headings; Excel will usually recognize them.
- Check that the dates are real Excel dates. A date that only looks like a date may actually be text, which can stop Excel from understanding the timeline. Select a date cell and check the formula bar or change the number format temporarily. If it behaves like a number/date and can be formatted as a date, it is likely a real date. Make sure each date represents the intended interval, such as one week or one month.
- Sort observations from oldest to newest. The timeline should progress forward down the sheet. Select the complete data range before sorting so each value stays paired with its date. Do not sort the date column alone. Check for duplicate dates and decide whether they should be combined into a single value before forecasting.
- Select the full data range. Drag across the date and value columns, including the headings if present. Avoid selecting entire worksheet columns or unrelated data. A focused selection helps Excel identify which field is the timeline and which field contains the values.
- Open the Forecast Sheet command. On the ribbon, select Data > Forecast Sheet. In the Create Forecast Worksheet window, Excel displays a preview. Choose the line chart for most timelines, especially when you want to see the past and future as a continuous trend. A column chart can be useful for comparing separate time periods.
- Choose how far ahead to forecast. In the Forecast End box, set the last date or time you want Excel to estimate. Use a horizon that fits the data and the decision you are making. For example, if you have two years of monthly observations, forecasting the next few months may be easier to interpret than projecting many years beyond the available history.
- Review the options before creating the sheet. Select Options in the dialog to see controls such as Forecast Start, Confidence Interval, Seasonality, Timeline Range, Values Range, Fill Missing Points Using, and Aggregate Duplicates Using. The exact choices can vary with the data and Excel build. In most cases, leave seasonality on automatic to start. Adjust the timeline or values ranges if Excel selected the wrong cells, and review how it will handle gaps or repeated dates.
- Create and inspect the worksheet. Select Create. Excel adds a new worksheet containing the historical data, forecast values, confidence bounds, and a chart. Read the chart and table together: the forecast line is an estimate, while the confidence bounds show a range of uncertainty. Check that the first forecast period follows the last historical period and that the dates and values are sensible.
- Save and label the result. Rename the new sheet to something clear, such as Sales Forecast, and save the workbook. Keep the original observations intact. If the historical data changes later, do not assume the existing forecast worksheet has refreshed; recreate the forecast from the updated range and compare the new result with the old one.
Common Mistakes
Using text instead of dates: Dates pasted from a report or imported from another system may be stored as text. Excel can then misread the timeline or refuse to make a forecast. Convert the entries to actual dates and verify the displayed order before selecting Forecast Sheet.
Selecting mismatched ranges: Each timeline entry needs a corresponding value. Extra headings, blank rows, totals, or unrelated columns can confuse the preview. Select only the two relevant columns and correct any range selections in the dialog before creating the worksheet.
Ignoring inconsistent intervals: A monthly series with a missing month, or a weekly series with irregular dates, can make the pattern harder to interpret. Confirm that the timeline uses a consistent frequency. If observations are missing, find out why rather than filling gaps automatically without review.
Expecting certainty: Forecast Sheet extrapolates patterns in the data. A sudden change in pricing, customer behavior, supply, or measurement methods may not be reflected in the historical pattern. Do not treat the forecast as a promise or as a substitute for checking current conditions.
Forecasting too far ahead: The farther the end date is from the historical data, the more uncertainty usually matters. A long forecast may still appear precise in the worksheet, but the estimate can become less useful as it extends beyond the observed pattern.
Tips
- Use consistent units throughout the values column. Do not mix dollars with thousands of dollars, or units sold with percentages, in the same series.
- Keep a copy of the original data and note when it was collected. That makes it easier to explain what information the forecast used.
- Start with the automatic seasonality setting. Only set a specific season length when you have a sound reason and enough observations to support it. For monthly data with a clear annual cycle, for example, a yearly season has 12 periods.
- Use the confidence interval as a reminder that an estimate has uncertainty. A wider band indicates a less certain range; it does not mean every possible outcome is equally likely.
- Compare the forecast with a simple baseline, such as the most recent period or the same period last year. If the forecast differs sharply, investigate the data and assumptions before sharing it.
- When new actual values arrive, create an updated forecast and compare the older prediction with what happened. This helps you see whether the data and forecast horizon are useful for your situation.
Troubleshooting
Forecast Sheet is missing from the Data tab. Confirm that you are using a supported desktop Excel version and that the workbook is open in the Excel app, not just a browser preview. Ribbon layouts can differ by platform and build. Check for Office updates, widen the Excel window or look for the command in the Data tab’s Forecast group. If it remains unavailable, try opening the workbook in desktop Excel for Windows.
The command is unavailable or the preview does not appear. Select a simple range containing one timeline column and one values column, with several data rows. Remove merged cells and unrelated content from the selection. Confirm the dates are actual Excel dates and the values are numeric, then try again.
The forecast has unexpected dates or a strange trend. Recheck the first and last selected dates, sort order, gaps, duplicates, and units. Open Options and confirm the Timeline Range and Values Range. If the chosen forecast end date is too distant, shorten it and review the preview again. A chart that rises sharply may reflect the source data rather than a software error.
Excel fills missing points or combines repeated dates in an unexpected way. Return to Options and review Fill Missing Points Using and Aggregate Duplicates Using. Choose a treatment that matches how the measurements were collected; for example, averaging repeated measurements may be appropriate in some cases but not when the values represent separate transactions that should be summed. If no available treatment makes sense, clean and summarize the source data first, then create a new forecast.
The forecast does not change after editing the original data. Treat the generated worksheet as a snapshot of the data and settings used to create it. Select the updated source range and create a fresh Forecast Sheet. Keep the earlier sheet if you need to document the previous projection.
Frequently Asked Questions
Is Forecast Sheet a setting I need to turn on?
No. In supported Excel desktop versions, the feature is available from Data > Forecast Sheet. You create a forecast worksheet from selected historical data; there is no separate enable switch.
What kind of data works best?
A chronological series with regular intervals and one numeric value for each date or time is a good starting point. Consistent, well-maintained measurements make the results easier to interpret.
Can I use daily, weekly, or monthly data?
Yes, the timeline can use different regular intervals. Make sure the dates accurately represent the spacing of observations and that the data range is not mixing frequencies.
Can I change the forecast after creating it?
You can inspect the generated worksheet, but the practical way to use changed source data or different settings is to create a new Forecast Sheet. Update the selected range or options first, then compare the new output with the earlier version.
Why is my forecast different from a calculation I made elsewhere?
Different methods, date ranges, missing-data treatments, and seasonality assumptions can produce different estimates. Forecast Sheet uses Excel’s forecasting model and settings, so compare the input data and assumptions before comparing results.
Does the confidence interval show guaranteed limits?
No. It indicates uncertainty around the forecast based on the model and data; it is not a guarantee that future values will stay inside the displayed range. Use it as context, not certainty.
Related
- Excel Delete Shortcut: Clear Cells, Rows, and Columns
- How to Delete a Header in Excel Without Removing Data
- How to Delete Highlighted Cells in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make a Budget Tracker in Google SheetsMake a monthly budget tracker in Google Sheets by listing expense categories, entering planned and actual amounts, and…
- Filter Google Sheets Without Affecting OthersTo filter a shared Google Sheet without changing what collaborators see, use a filter view: in a browser,…
- How to Create a Private Distribution List in OutlookTo create a personal distribution list in Outlook, open People, choose New contact list (or New Contact Group…
- Excel: Sum Values Based on Other ValuesYes. In Excel, use SUMIF to add values that match one condition, or SUMIFS to add values that…
- How to Add Page Numbers in MLA Format in WordTo add MLA-style page numbers in Word, insert an automatic page number in the top-right header, then place…
- How to Add a Timestamp in Google Sheets AutomaticallyTo add a fixed timestamp in Google Sheets, select a cell and press Ctrl+Alt+Shift+; on Windows or ChromeOS;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.