How to Get Forecast Sheet in Excel

Predicting the future is easier than you think, especially when you have historical data and Microsoft Excel. Instead of guessing your future sales, inventory needs, or website traffic, you can use Excel's Forecast Sheet feature to create a data-driven projection in just a few clicks. This article will walk you through exactly how to prepare your data, generate a forecast, and customize the results to fit your specific needs.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is the Forecast Sheet in Excel?

The Forecast Sheet in Excel is a one-click tool that takes your historical time-based data and uses it to predict future trends. It visualizes this data in a new line chart, showing what has already happened and what is likely to happen next based on established patterns.

Behind the scenes, Excel uses an industry-standard algorithm called Exponential Smoothing (ETS) to generate these forecasts. This algorithm is particularly good at detecting trends and seasonal patterns, making it perfect for common business scenarios:

• Forecasting monthly sales for the next quarter.
• Predicting website traffic for an upcoming holiday season.
• Estimating inventory requirements for the next six months.
• Projecting customer support ticket volumes.

The best part is that you don't need to be a data scientist or a statistician to use it. Excel bundles the complicated calculations and statistical models into a simple, user-friendly wizard, giving you a powerful forecasting tool without a steep learning curve.

Step 1: Preparing Your Data for Forecasting

Before you can use the Forecast Sheet, your data needs to be organized properly. Garbage in, garbage out - the quality of your forecast depends entirely on the quality of your input data. Luckily, the required format is simple.

You need two columns minimum:

1. A Timeline Column: This contains your dates or timestamps (e.g., days, months, years).
2. A Values Column: This contains the corresponding numeric data you want to forecast (e.g., sales figures, user counts, units sold).

Here’s a simple sales data example:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Best Practices for Your Data:

• Be Consistent: Ensure the time interval in your date column is consistent. If you have monthly data, every step should be one month. If you have weekly data, every step should be a week. Excel's forecasting algorithm is smart enough to handle up to 30% of missing data points by filling them in (a process called interpolation), but your results will always be more accurate with complete, consistent data.
• One Date, One Value: While Excel can aggregate duplicate timestamps, your data will be cleanest if each date in your timeline corresponds to a single value.
• Keep It Tidy: Make sure your data is in a single table or range with no blank rows or columns in the middle.

Step 2: How to Create a Forecast Sheet

Once your data is cleaned and formatted, creating the forecast takes less than a minute. Here are the steps:

1. Select Your Data

Click and drag to select your entire data table, including the headers for both the timeline and values columns. In our sales example, you'd select the "Date" and "Sales" headers and all the rows of data beneath them.

2. Navigate to the Data Tab

With your data selected, go to the main menu bar at the top of Excel and click on the Data tab.

3. Click Forecast Sheet

In the "Forecast" group on the Data ribbon, you'll see an icon with a small line chart and a crystal ball. Click this Forecast Sheet button.

That's it! Excel will immediately open a "Create Forecast Worksheet" window, showing a preview of your forecast chart.

Step 3: Customizing Your Forecast Options

The initial forecast Excel creates is a great starting point, but you can refine it for better accuracy and clarity. In the "Create Forecast Worksheet" window, click the small arrow next to Options at the bottom left to reveal several customization settings.

Forecast End

This is where you tell Excel how far into the future you want to predict. By default, it will forecast a few periods ahead, but you can click the calendar icon to select any future date.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Confidence Interval

The confidence interval is the gray area around your forecast line. It represents the range in which Excel is "confident" the actual future values will fall. The default is 95%, meaning there's a 95% probability that the actual data points will be somewhere between the upper and lower bounds of this range.

You can adjust this value up or down. A higher percentage (e.g., 99%) will create a wider, more cautious range, while a lower percentage (e.g., 80%) will produce a narrower, more optimistic (but riskier) range.

Seasonality

Seasonality refers to predictable, repeating patterns in your data that occur over a set period. For example, retail sales often spike in the fourth quarter (Q4) every year, or hotel bookings might peak every summer.

• Detect Automatically (Default): Excel will analyze your data and automatically identify if a seasonal pattern exists. For most use cases, this works great.
• Set Manually: If you know your business operates on a specific cycle that Excel might not detect, you can set it manually. For instance, if you have monthly data and know there's a strong annual pattern, you would enter "12" to tell Excel that the seasonal cycle is 12 points long (12 months). This can significantly improve your forecast's accuracy.

Fill Missing Points Using

If your data has some gaps, this option tells Excel how to fill them. Your choices are:

• Interpolation (Default): Excel will calculate a weighted average of the values just before and after the missing point. This is usually the best option as it creates a smooth, logical transition.
• Zeros: This option will fill any missing data point with a zero. This is generally not recommended unless a missing value truly means zero activity (e.g., zero sales on a day the store was closed).

Aggregate Duplicates Using

If your data contains multiple entries for the same date, this option tells Excel how to combine them into a single data point. The default is Average, but you can change it to Sum, Median, Count, and others depending on what makes sense for your data.

For example, if you logged three separate sales on the same day, you'd likely want to Sum them to get the total daily sales.

Include Forecast Statistics

For more advanced users, checking this box will include a table of statistical measures in your forecast results. These metrics (like Alpha, Beta, Gamma, MASE, and MAPE) provide insight into the forecast model's accuracy and the smoothing factors it used. It's not necessary for a basic forecast, but it's valuable for those who want to validate the statistical integrity of the model.

Once you are happy with your settings, click Create. Excel will generate a brand new worksheet containing your forecast chart and data table.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Interpreting the Forecast Sheet Results

The new worksheet Excel creates is clean and well-organized. It contains two main components:

The Forecast Chart

This is the most visible output. The chart displays three distinct lines:

• The Blue Line (Historical Data): This represents the actual data you provided.
• The Orange Line (Forecast): This is the prediction of what your values will be in the future.
• The Gray Lines/Area (Confidence Bounds): These two thinner lines represent the upper and lower confidence bounds you set, creating a "cone of uncertainty" around the forecast.

The Data Table

To the left of the chart, you'll find a data table that builds on your original data. It includes the historical values plus three new columns for the forecast period:

1. A column for the forecasted value (the orange line).
2. A column for the lower confidence bound (the bottom gray line).
3. A column for the upper confidence bound (the top gray line).

This table is incredibly useful because it gives you the specific predicted numbers alongside your historical data, which you can then use in other reports or calculations.

Final Thoughts

Excel's Forecast Sheet transforms what used to be a complex statistical task into a simple, accessible tool. By organizing your time-series data and following the steps above, you can quickly move from historical reporting to predictive analysis, helping you make smarter, more informed business decisions without writing a single formula.

Of course, the biggest challenge is often getting all of your data into a single Excel sheet in the first place. Your sales data might be in Shopify, your marketing performance in Google Analytics, and your CRM data in Salesforce. Instead of spending hours manually downloading CSVs and stitching them together, we built Graphed to automate this process entirely. We connect directly to all your favorite marketing and sales platforms, allowing you to create real-time forecasting dashboards by simply asking questions in plain English. If you’re ready to spend less time wrangling data and more time acting on it, give Graphed a try.