How to Forecast in Excel Based on Historical Data

Trying to predict next quarter's revenue or how much traffic your website will get next month doesn't have to be a blind guess. If you have historical data, you can use Excel to create surprisingly accurate forecasts right on your desktop. This article walks you through preparing your data and using Excel's built-in Forecast Sheet to turn past performance into future insights.

Getting Your Data Ready for Forecasting

Before you can ask Excel to predict the future, you have to give it a clean picture of the past. The quality of your forecast depends entirely on the quality and organization of your data. Think of it as telling a clear, consistent story so Excel can understand the plot and write the next chapter.

Your data needs to be in a simple time-series format. This just means you need two columns:

• A timestamp column: This contains your dates or times (e.g., day, month, year).
• A value column: This contains the metric you want to forecast (e.g., sales, page views, units sold).

Here are a few quick rules to follow for the best results:

Maintain consistent intervals. Your timestamps should be recorded at regular intervals. If you’re tracking monthly sales, make sure you have an entry for every month. If it's a daily metric, every day should be listed. Irregular intervals can confuse the forecasting algorithm and produce unreliable results.

Handle missing values. An empty cell can throw a wrench in the works. Ideally, your dataset shouldn't have any gaps. If you do have them, Excel can help you fill them in. A common approach is interpolation, where Excel calculates a value for the missing spot based on its neighbors, creating a smoother average.

Ensure chronological order. Sort your data from oldest to most recent. While Excel is usually smart enough to figure this out, starting with a chronologically sorted list prevents potential errors from the start.

Here’s an example of a perfectly formatted dataset ready for forecasting monthly sales:

Your dataset must have this simple two-column structure. Once it does, you're ready to start forecasting.

The Easiest Way: Excel's Forecast Sheet

For most forecasting needs, Excel's built-in Forecast Sheet is all you'll ever need. It's an automated tool found on the Data tab that takes your historical data, runs it through an advanced forecasting algorithm (Exponential Triple Smoothing, or ETS), and generates a new worksheet with the forecasted data and a professional-looking chart. It’s powerful, fast, and remarkably intuitive.

Step-by-Step: Creating Your First Forecast

Let's use the monthly sales data from our example. Building a forecast is a matter of a few clicks.

1. Select your entire data range, including both column headers (in our case, "Month" and "Sales Revenue").
2. Navigate to the Data tab on the Ribbon.
3. In the Forecast group, click on Forecast Sheet.

Instantly, a new window pops up showing a preview of your forecast. Excel plots your historical data and adds a projected line showing the future trend. It also includes a "confidence interval" - the upper and lower bounds where the actual results are most likely to fall.

If you're happy with what you see, you can simply click Create, and Excel will generate a new sheet with all this information. But the real power comes from customizing the options.

Customizing Your Forecast Options

In the "Create Forecast Worksheet" window, click the "Options" arrow at the bottom left to see all the settings you can adjust.

• Forecast End: This is a simple but important setting. It lets you define how far into the future you want to predict. By default, Excel will choose a reasonable endpoint, but you can easily change the date to predict the next quarter, year, or any period you need.
• Confidence Interval: A forecast is never a certainty - it’s a probable outcome. The confidence interval visually represents this uncertainty. The default is 95%, meaning Excel is 95% confident that future values will fall between the upper and lower boundary lines. You can increase or decrease this percentage to make the range wider or narrower depending on how much certainty you want to account for.
• Seasonality: This is arguably the most powerful feature. Excel can automatically detect recurring patterns in your data, such as a sales spike during the holidays or a dip in the summer. If you have at least one full cycle of data, the "Detect automatically" option works fantastically. However, if you know your business cycle - for example, a 12-month sales cycle - you can select "Set manually" and enter the number 12 for even greater accuracy.
• Fill Missing Points Using: If you have gaps in your data, this setting tells Excel how to deal with them. You can choose "Zeros" if a missing point means no activity (e.g., zero sales). More commonly, you'll choose "Interpolation," where Excel intelligently fills the gap by averaging the data points on either side.
• Aggregate Duplicates Using: Sometimes your data might contain multiple entries for the same timestamp. For example, you might have three separate sales on the same day. This setting lets you consolidate them by choosing a function like Average, Sum, Median, or Count.

Once you’ve adjusted the settings to your liking, click Create. Excel will generate a new worksheet with your forecast organized cleanly and ready to share.

Understanding the Forecast Sheet Output

The new sheet contains two key things: a table and a chart.

The table will have your original data, followed by three new columns for the forecasted period:

1. Forecast (Sales Revenue): This column shows the single most likely value for a given date based on Excel's algorithm. It's the primary trend line.
2. Lower Confidence Bound: The pessimistic scenario. Excel is 95% confident that the actual value won't be lower than this.
3. Upper Confidence Bound: The optimistic scenario. Excel is 95% confident that the actual value won't be higher than this.

The chart visualizes this table, making it easy to see the historical trend, the future projection, and the range of possibilities at a glance. It's a boardroom-ready visualization generated in under a minute.

A Quick Look at Underlying Forecast Formulas

The Forecast Sheet works for 90% of use cases, but for those who want more control or want to embed forecasts directly into their existing models, it's useful to know about the formulas powering it.

You don't need to be an expert in these, but knowing they exist gives you more flexibility.

FORECAST.ETS: The Heavy Lifter

The FORECAST.ETS function is the engine behind the Forecast Sheet. It's designed for handling complex time series data, including seasonality.

The basic syntax looks a little intimidating, but it follows the same logic as the options menu we just covered:

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

With this, you can generate a single forecasted value for a specific future date without creating a whole new worksheet, giving you more flexibility within your existing dashboards.

TREND: For Simple, Linear Projections

What if your data doesn't have any seasonality? If you're forecasting something that grows (or declines) in a relatively straight line, the simpler TREND function is a great choice.

The syntax is a bit more straightforward:

=TREND(known_y's, known_x's, new_x's, [const])

Here, known_y's are your historical values (e.g., sales) and known_x's are your historical time periods (e.g., dates). New_x's is the future date you want to predict.

The TREND function is perfect for simple growth projections where seasonal fluctuations aren't a concern.

Common Pitfalls and Best Practices

Creating forecasts in Excel is easy, but creating good forecasts requires a bit of awareness. Keep these tips in mind to avoid common mistakes.

Use enough historical data. A forecast based on only three months of data is not going to be reliable. For a monthly forecast with seasonality, aim to have at least two full years of data if possible. The more history you provide, the better Excel can identify long-term trends and seasonal patterns.

Remember that forecasts are not guarantees. Historical data is an amazing predictor, but it can't account for everything. A forecast won't know about the disruptive competitor launching next month or your plan to double your marketing spend. Use forecasts as an educated baseline, not an unbreakable promise.

Always review and validate. When your forecasted period comes to an end, compare the actual results to what Excel predicted. Did the actuals fall within the confidence interval? Were they higher or lower? Learning from past forecasts is the single best way to improve a new one you make.

Final Thoughts

Excel turns forecasting from a complex statistical exercise into an accessible and powerful tool for anyone. By properly preparing your time-series data, you can use the Forecast Sheet to create reliable, data-driven projections in just a few clicks. It's an excellent way to start making more informed decisions about the future of your business.

Of course, the manual loop of exporting data, cleaning it in a spreadsheet, and creating forecasts is time-consuming, especially when your data lives across Google Analytics, Shopify, your CRM, and a dozen ad platforms. That's why we built Graphed to do the heavy lifting for you. We connect to all your marketing and sales sources in one place, keeping your data live and always up-to-date. Instead of building tables and charts yourself, you can just ask plain-English questions like "forecast our revenue for the next 6 months based on last year's trends" and get an interactive dashboard instantly, no spreadsheets required.