How to Do a Forecast in Excel

Forecasting in Excel is like peering into the future with your trusty spreadsheet. You can predict sales, budget expenses, or even plan your next big project. By leveraging Excel’s built-in tools, you can easily create forecasts using historical data. Ready to dive in? Let’s go through a step-by-step guide to mastering this skill.

How to Do a Forecast in Excel

By following these steps, you’ll be able to use Excel to predict future data points with ease. Basically, you’ll input your historical data, use Excel’s functions to analyze it, and then generate a forecast. Let’s break it down.

Step 1: Prepare Your Data

Start by organizing your historical data in a clean table format.

Put the dates in one column and the values in the next. The dates need consistent intervals, such as the first of every month. According to Microsoft’s Forecast Sheet guide, it is fine if up to 30% of the points are missing or a few dates repeat, but the timeline should otherwise be regular. This sets the foundation for accurate forecasting. Ensure your dates are in one column and the values you’re forecasting are in another.

Step 2: Select Your Data

Highlight the data you want to use for the forecast.

Click and drag to select your data range. This will typically be two columns: one for dates or time periods and one for the values you want to forecast. Excel needs this data to create a meaningful trend line.

Step 3: Insert the Forecast Sheet

Head over to the ‘Data’ tab and click on ‘Forecast Sheet’.

This action opens a new window where you can customize your forecast. You can see a preview of the forecast chart here. Excel automatically detects the best forecasting model based on your historical data.

Step 4: Customize Your Forecast

Adjust settings like forecast end date and confidence intervals.

In this step, you can refine your forecast by tweaking the end date or adding confidence intervals to see the range of possible outcomes. This customization helps tailor the forecast to your specific needs.

Step 5: Create the Forecast Sheet

Click ‘Create’ to generate your forecast.

Excel will now create a new sheet with your forecasted data and a chart. This sheet includes the historical data, forecasted values, and a confidence interval to show the range of potential future values.

Once you complete these steps, Excel will generate a forecast chart and table on a new sheet. You’ll see how the historical data trends into future predictions, helping you make informed decisions.

Example: Forecasting Monthly Sales

Suppose column A lists the first day of each month from January 2024 to December 2025, and column B holds total sales for each month. Sales rise every November and December. To predict the next six months:

1. Select both columns, including headings.
2. Click Data > Forecast Sheet and choose a line chart.
3. Set Forecast End to June 2026.
4. Open Options, leave Seasonality on Detect Automatically, and tick Include forecast statistics.
5. Click Create.

Excel adds a sheet with your 24 months of history, six forecast months, and upper and lower confidence bounds, plus a chart where the forecast continues as a separate line. Because two full years of data are included, Excel can recognize the end-of-year spike and repeat it in future years. Check the Seasonality value in the statistics table: a value of 12 confirms Excel found a yearly pattern in monthly data.

Forecast Sheet Options Explained

Click Options in the Create Forecast Worksheet window to fine-tune the forecast:

• Forecast Start: start the forecast before your last real data point to compare predictions with actual values and judge accuracy.
• Confidence Interval: shows a range above and below the forecast line. The default is 95%; a lower value gives a narrower band.
• Seasonality: Excel detects repeating patterns automatically. Choose Set Manually and enter a number, such as 12 for monthly data with a yearly cycle, if detection is wrong.
• Include forecast statistics: adds a table of smoothing coefficients and error measures such as MAPE and RMSE.
• Fill Missing Points Using: interpolation or zeros.
• Aggregate Duplicates Using: how to combine values that share a date, such as Average or Sum.

Forecast Sheet uses exponential triple smoothing (the ETS algorithm), which picks up trends and seasonal patterns. It is available in Excel 2016 and later for Windows and in current versions of Excel for Mac, but not in Excel for the web.

Forecast With Formulas Instead

If you want the forecast next to your data rather than on a new sheet, use a forecasting function. With dates in A2:A25 and values in B2:B25, and the future date in A26:

• =FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25) predicts the value using the same ETS method as Forecast Sheet, including seasonality.
• =FORECAST.LINEAR(A26,$B$2:$B$25,$A$2:$A$25) fits a straight line through the data. It works well for steady growth without seasonal swings. The older FORECAST function gives the same result.
• =FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25) returns how far above and below the forecast the confidence range extends.

Fill the formula down for each future date. Use dollar signs on the data ranges, as shown, so they stay fixed when you copy the formula.

Which Forecast Method Should You Use?

• Forecast Sheet: quickest option for a chart and table, best for seasonal data such as monthly sales.
• FORECAST.ETS: the same model, but inside your own sheet so you can build on the results.
• FORECAST.LINEAR or a chart trendline: simple straight-line projections for data without seasonal patterns.

Troubleshooting

The Forecast Sheet button is grayed out

Select a single range with dates and values, make sure you are not editing a cell, and check that you are using desktop Excel 2016 or later. The feature is not available in Excel for the web.

Excel says the timeline is not evenly spaced

Your dates have irregular gaps or are stored as text. Convert text to real dates and make the intervals consistent. Our guide to converting date formats in Excel can help.

#NUM! from FORECAST.ETS

This usually means the timeline is not evenly spaced, the ranges are different sizes, or there is not enough data to detect seasonality. Check the ranges and try setting the seasonality argument to 1 for no seasonality.

The forecast looks unrealistic

One unusual spike, too little history or a recent change in the business can distort results. Remove obvious errors, use at least two full seasonal cycles of data if possible, and compare the forecast against recent actual values.

Tips for Doing a Forecast in Excel

• Clean Data: Always start with clean, consistent data to ensure accurate forecasts.
• Use Sufficient Data: The more historical data you have, the better your forecast will be.
• Adjust Confidence Intervals: Use confidence intervals to understand the range of possible outcomes.
• Check for Seasonality: If your data has seasonal patterns, consider this when analyzing your forecasts.
• Validate Your Forecasts: Regularly compare forecasted data with actual results to improve accuracy over time.

Frequently Asked Questions

What is a forecast in Excel?

A forecast in Excel predicts future data points based on historical data using built-in functions and tools.

Can I forecast non-numeric data?

No, forecasts require numeric data to predict future values accurately.

How do I handle missing data points?

Forecast Sheet handles them for you. Under Options, Fill Missing Points Using is set to interpolation by default, or you can choose zeros. If more than about 30% of the points are missing, fill the gaps yourself before forecasting.

Can I use Excel to forecast sales?

Yes, Excel is commonly used to forecast sales by analyzing past sales data.

What is the ‘Forecast Sheet’ tool?

The ‘Forecast Sheet’ tool in Excel simplifies the process by automatically generating a forecast chart and table based on your historical data.

Related: make a monthly budget in Excel.