To show a forecast in Excel for Microsoft 365 on Windows, put dates and values in two columns, select the data, and choose Data > Forecast Sheet. Set the forecast end date and create the sheet; Excel builds a chart with projected values and a confidence interval. To extend an existing chart instead, add a trendline and set its Forecast forward value. Use Forecast Sheet for time-based data that may be seasonal, and a trendline for a simple visual extension of a chart’s existing pattern.
What You’ll Need
- Excel for Microsoft 365 desktop, preferably on Windows for the Forecast Sheet steps below. Menu placement or feature availability can vary in Excel for Mac and Excel for the web.
- A timeline and numeric observations in adjacent columns, such as monthly dates in column A and sales in column B. Keep the timeline in chronological order and use a consistent interval.
- Enough historical data to show a pattern. For seasonal data, such as monthly sales, having at least two complete seasonal cycles gives Excel more information to work with.
- An existing chart only if you want to extend that chart rather than create the chart that Forecast Sheet generates.
Step-by-Step
- Prepare the timeline and values. Put a clear header above each column, such as Month and Units. Enter one date and one numeric value on each row, with no totals or notes inside the data range. Sort the dates from oldest to newest. Use actual Excel dates, not text that only looks like a date; click a date cell and check that Excel recognizes it as a date. Monthly data should advance month by month, and weekly data should use a consistent weekly interval. Regular spacing helps Excel interpret the series correctly.
- Select the historical data. Drag across both columns, including the headers and all historical rows. For example, select A1:B37 if row 1 contains headers and rows 2 through 37 contain 36 monthly observations. Do not include unrelated columns, blank title rows, or a grand-total row. If you have several series, such as separate product lines, make sure each series has a corresponding value for each date.
- Open the Forecast Sheet tool. On the ribbon, choose Data > Forecast Sheet. In the Create Forecast Worksheet window, choose the line chart or column chart preview. A line chart is usually the clearest option for dates that unfold over time. The preview helps you see whether Excel has interpreted the timeline and values correctly before it creates anything. If the date labels or series look wrong, cancel and correct the selected range rather than accepting a misleading chart.
- Choose how far ahead to forecast. In the Forecast End box, enter or select the last date you want Excel to project. For instance, if the final observation is June and you need a one-year outlook, set the end date to the following June. Excel forecasts the intervals between the last historical point and that end date. Avoid projecting much farther than the history can reasonably support: a long forecast can look precise on a chart even when uncertainty is substantial.
- Review the forecast options. Select Options to inspect settings such as the forecast start, confidence interval, seasonality, and how Excel handles missing points. Excel normally detects seasonality automatically. If you know the cycle length, you can specify it—for example, 12 for monthly observations that repeat yearly. Leave automatic detection in place when you do not know the cycle or have too little data to identify it. The confidence interval is a range around the forecast, not a promise that the actual result will fall inside it.
- Create and check the worksheet. Choose Create. Excel adds a new worksheet containing the forecast table and a chart. The table generally includes the timeline, historical data, forecast values, and lower and upper confidence bounds. Inspect the first forecast dates and a few later rows: the projected dates should follow your interval, and the forecast values should be plausible in relation to the data. The chart’s forecast line and shaded or bounded interval distinguish the estimate from the historical observations.
- Format the generated chart for readers. Click the chart title and replace it with a specific description, such as Monthly Units: Actual and Forecast. Add axis titles if the units or date scale are not obvious. Use the chart’s plus button or Chart Design > Add Chart Element to adjust elements such as the legend and axis titles. Keep the actual and forecast series visually distinct, and retain the confidence interval when uncertainty matters. Avoid colors that make the forecast difficult to distinguish from the historical line.
- Extend an existing chart with a simple trendline, if appropriate. Select the chart, then choose Chart Design > Add Chart Element > Trendline > More Trendline Options. In the Format Trendline pane, choose a model that suits the pattern and enter a value under Forecast, Forward. The number represents periods on the chart’s horizontal axis, not necessarily days or months; confirm that one category corresponds to one period. A linear trendline is a straight-line extrapolation, not the same as Excel’s seasonal Forecast Sheet model. It can be useful for a quick visual extension, but it does not automatically provide the same forecast table and confidence bounds.
- Add Forecast Sheet values to an existing chart when you need a projected series. Create a Forecast Sheet using the same dates and historical values as the chart. Copy the future dates and forecast values from its table, then add those dates to the existing chart’s source data and place the forecast values in a separate column aligned to the dates. Leave that column blank for earlier dates, and include the last historical value at the transition if you want the forecast line to connect visually. Select the chart and choose Chart Design > Select Data. Update the chart data range to include the added dates, choose Add under Legend Entries (Series), and select the forecast column as the series values. Set the horizontal axis labels to the expanded date range. Check that the forecast series starts at the intended point and that blanks do not create an unwanted line.
Common Mistakes
Using dates stored as text: Excel may treat text labels as categories rather than a usable timeline, causing Forecast Sheet to be unavailable or the result to be incorrect. Convert the entries to real dates and verify their order.
Mixing intervals: A timeline with some monthly and some quarterly entries does not represent a consistent sequence. Decide on one interval and fill or correct the dates before forecasting.
Including totals or unrelated rows: A total at the bottom can be read as another observation and distort the output. Select only the date-and-value records.
Confusing a trendline with a forecast model: A trendline extends a chosen mathematical pattern. It does not automatically account for seasonal cycles or produce the Forecast Sheet confidence interval.
Presenting estimates as certain: A smooth chart is not proof that the future will follow the projection. Label the projected portion and keep uncertainty visible when the chart will guide decisions.
Tips
- Use a descriptive title and state the unit and timeline, such as Weekly Orders, rather than leaving the default Chart Title.
- Keep actual observations and forecast values in contrasting but related colors. Make the point where the forecast begins easy to spot, especially when past and projected values are connected.
- For monthly or quarterly data, compare the forecast with the same season in previous years. A rise every December, for example, may reflect seasonality rather than a lasting upward trend.
- Try a different forecast end date and review how the chart changes. A shorter horizon is often easier to explain and less likely to suggest more certainty than the data supports.
- Keep the original observations unchanged and retain a copy of the source data. If the underlying values change, recreate or refresh the forecast rather than relying on an old chart.
- When adding a forecast to an existing chart, use the same date scale for both series. A forecast can appear shifted if the chart uses category labels that do not match the added dates.
Troubleshooting
Forecast Sheet is missing or disabled: Confirm that you are using a supported desktop version of Excel and have selected a range with a timeline and numeric values. The Data ribbon can differ on Mac, and some Forecast Sheet controls may not be available in Excel for the web. Try the current desktop Excel app on Windows. If the feature is unavailable, a trendline is an alternative for a basic extension, though it is not equivalent to a seasonal forecast.
The chart shows the dates as numbers or in the wrong order: Check that the cells contain valid dates, sort the source data oldest to newest, and verify the selected range begins with the date column. Apply a date number format if the values are real dates but display as serial numbers.
The forecast line is flat or looks implausible: Check for zeros, outliers, missing values, or a total accidentally included in the source. Review the frequency and seasonality settings. If the pattern changes over time, a single model may not represent every part of the history; compare the chart with the raw data before relying on the result.
The forecast does not appear on an existing chart: Open Chart Design > Select Data and check that the forecast series points to the intended value cells and that the horizontal axis labels include the future dates. A series containing only blanks, text, or values outside the chart’s selected range will not display correctly.
The trendline extends the wrong distance: Reopen More Trendline Options and check the Forecast, Forward setting. It counts chart periods, so a value of 12 means 12 categories on the horizontal axis. If your categories are irregularly spaced, a trendline extension may not correspond to the calendar duration you expect.
Frequently Asked Questions
Can Excel make a forecast chart automatically?
Yes. Select a date-and-value range and use Data > Forecast Sheet. Excel creates a new worksheet with a chart and forecast table. Check the preview and settings before creating it.
Can I show a forecast on a chart I already have?
Yes. For a basic extension, add a trendline and set its forward forecast period. For a separate forecast series, use the future values from a Forecast Sheet, add matching dates and values to the chart’s source data, and add that column through Select Data.
What is the difference between a trendline and Forecast Sheet?
A trendline extends a selected pattern on an existing chart. Forecast Sheet produces a time-based projection with forecast values and confidence bounds, and it can account for seasonality. They answer related but different questions.
How much historical data do I need?
There is no single suitable amount for every dataset. More history can help reveal patterns, and seasonal data needs enough repeated cycles to identify them. For monthly data with yearly seasonality, two or more years gives Excel repeated cycles to examine.
What does the confidence interval mean?
It shows a range around the forecast estimate at the selected confidence level. A wider range indicates greater uncertainty. It is useful context, not a guarantee that future observations will stay within the band.
Can I create a Forecast Sheet in Excel for Mac or the web?
Availability and ribbon placement depend on the Excel build. On Mac, look for Forecast Sheet on the Data tab; if it is absent, use the Windows desktop app or a trendline for a basic chart extension. Excel for the web may not provide the full Forecast Sheet workflow.
Related
- Resize Excel in Word: Fit a Spreadsheet to the Page
- Resize Cells in Excel: Keyboard Shortcuts That Work
- Resize an Excel Table in Word: A Step-by-Step Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Legend Icons: How to Make Symbols BiggerYes—Excel can make chart legend icons look bigger, but it does not provide a separate control for setting…
- Gantt Chart in Excel: How to Insert One Step by StepTo insert a Gantt chart in Excel, arrange each task with a start date and duration, then insert…
- Excel Pivot Table: How to Delete a Calculated FieldTo delete a calculated field in an Excel PivotTable, select the PivotTable, then choose PivotTable Analyze > Fields,…
- Power Pivot Slicers: How to Add and Connect ThemA Power Pivot slicer is a clickable filter for a PivotTable built from Excel’s Data Model. In Excel…
- Excel Chart Won’t Update With New Data? Causes and FixesIf an Excel chart will not update with new data, first check whether its source range includes the…
- Excel Bar Chart Different Color Bars: How to Set ThemYes. In Excel for Microsoft 365 on Windows, select the chart, right-click a bar, choose Format Data Series,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.