You have monthly sales figures, quarterly revenue, or weekly inventory counts and need to predict future numbers. Manually building forecast models with formulas like FORECAST.ETS or analyzing trend lines can take time and requires statistical knowledge. Copilot in Excel can analyze your date-stamped data and generate a forecast directly in your worksheet. This article explains the prerequisites for using Copilot for forecasting, walks through the specific prompts and steps to create the forecast, and covers common mistakes that produce inaccurate results.
Key Takeaways: Generating Forecasts with Copilot in Excel
- Data must be in a single column with dates and a single column with values: Copilot requires a clean, continuous range of historical data with no blank rows or missing dates.
- Use the prompt “Generate a forecast” followed by the date range and value range: Copilot creates a new worksheet with the forecast table and a chart.
- Copilot uses the FORECAST.ETS function internally: The resulting forecast includes confidence intervals and seasonal patterns based on your historical data.
How Copilot Forecasts Data in Excel
Copilot in Excel uses the FORECAST.ETS function to generate predictions from historical time-series data. FORECAST.ETS stands for Exponential Triple Smoothing, an algorithm that detects seasonal patterns and trends in your data. Copilot automates the setup of this function, so you do not need to write the formula manually or configure seasonal length, confidence intervals, or missing data handling.
Before you use Copilot for forecasting, your data must meet these requirements:
- One column containing dates or times. The dates must be sequential with a consistent interval such as daily, monthly, or quarterly. Missing dates cause gaps in the forecast.
- One column containing numeric values. Text, blank cells, or error values in the value column prevent the forecast from generating.
- At least two complete cycles of historical data. For monthly data with a yearly season of 12 months, you need at least 24 data points. For weekly data with a yearly season of 52 weeks, you need at least 104 data points.
- The data must be in a contiguous range. No blank rows or columns between the dates and values.
Copilot does not work on data stored in an Excel table that has been filtered. Clear any filters before you ask for a forecast. Copilot also cannot read data from PivotTables or external data connections. Copy the data to a regular range first.
Steps to Generate a Forecast with Copilot in Excel
Follow these steps to have Copilot create a forecast from your historical data.
- Open the Copilot pane in Excel
Open your workbook that contains the historical data. On the Excel ribbon, select the Home tab. In the Copilot group, click the Copilot button. The Copilot pane opens on the right side of the Excel window. - Select the data range
Click and drag to select the cells that contain your dates and values. Include the header row if you have one. Copilot uses the entire selected range to identify the date column and the value column. - Type the forecast prompt
In the Copilot text box at the bottom of the pane, type:Generate a forecast for this data
Then press Enter. Copilot analyzes the selected range and confirms it can create a forecast. If the data does not meet the requirements, Copilot displays an error message explaining what is missing. - Review the forecast preview
Copilot shows a preview of the forecast in the pane, including the number of future periods it predicts and the confidence interval. By default, Copilot forecasts for the same number of periods as your historical data. For example, if you have 24 months of data, Copilot forecasts 24 months into the future. To change the number of periods, type a new prompt such as:Generate a forecast for 12 future months - Insert the forecast into the workbook
In the Copilot pane, click the Insert button. Copilot creates a new worksheet named “Forecast” and adds the forecast table and a chart. The table contains three columns: the date, the predicted value, and the lower and upper confidence bounds. The chart shows the historical data line and the forecast line with shaded confidence intervals.
Customizing the Forecast After Insertion
After Copilot inserts the forecast, you can modify the FORECAST.ETS parameters directly in the formula bar. Select any cell in the forecast column. The formula bar shows the FORECAST.ETS formula with arguments for target date, historical values, timeline, seasonality, data completion, and aggregation. Change the seasonality argument from 0 for automatic detection to a specific number like 12 for monthly seasonality. The forecast chart updates automatically when you change the formula.
Common Problems When Using Copilot for Forecasting
Copilot says “I can’t generate a forecast from this data”
This error appears when the selected range does not meet the data requirements. Check that the date column contains sequential dates with no gaps. Ensure the value column contains only numbers and no blank cells. If your data has missing dates, add the missing dates and leave the value cell blank. Copilot can handle a small number of missing values through interpolation, but a gap in the date sequence causes the error.
The forecast shows a flat line or no seasonality
A flat forecast line means Copilot did not detect a seasonal pattern in your data. This happens when you have fewer than two full cycles of data. For monthly data with a yearly season, you need at least 24 months. If you have enough data but the forecast is still flat, change the seasonality argument in the FORECAST.ETS formula from 0 to the expected seasonal length. For example, change 0 to 12 for monthly data with yearly seasonality.
The forecast includes future dates that are incorrect
Copilot extends the date sequence based on the interval of your historical dates. If your historical dates are not consistently spaced, Copilot may generate incorrect future dates. Check that your historical dates are equally spaced. For monthly data, use the first day of each month. For daily data, use consecutive calendar days without skipping weekends or holidays. If your data has irregular intervals, add a helper column that normalizes the dates to a consistent interval.
Copilot Forecast vs Manual FORECAST.ETS Formula
| Item | Copilot Forecast | Manual FORECAST.ETS Formula |
|---|---|---|
| Setup time | Seconds with one prompt | Minutes to write and test formula |
| Seasonality detection | Automatic, uses default algorithm | Manual, requires setting seasonality argument |
| Confidence intervals | Added automatically as lower and upper bounds | Must be added with separate FORECAST.ETS.CONFINT formula |
| Chart creation | Created automatically on a new worksheet | Must be created manually from the forecast table |
| Customization | Limited to prompt changes before insertion | Full control over all parameters and outputs |
Copilot is the faster option for a standard forecast with default settings. Use the manual FORECAST.ETS formula when you need specific seasonality values, custom confidence intervals, or when your data requires preprocessing that Copilot cannot handle.
You now know how to use Copilot in Excel to generate a forecast from historical date-stamped data. Start by selecting your clean data range and typing the prompt “Generate a forecast for this data.” After Copilot inserts the forecast, check the seasonality argument in the formula bar if the forecast does not match your expected pattern. For advanced control, modify the FORECAST.ETS formula directly or use the FORECAST.ETS.CONFINT function to adjust confidence intervals.