How to Predict Data in Excel

Trying to predict future results using gut feelings alone often ends in wasted budgets and missed opportunities. Excel, however, has powerful built-in tools that can help you create surprisingly accurate data-driven forecasts. This article will show you exactly how to predict data in Excel, from a simple one-click method to more advanced functions that account for things like seasonality.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Bother Predicting Data in Excel?

Forecasting isn't about having a crystal ball, it's about making more informed decisions. By analyzing historical data, you can build a statistical model to anticipate future outcomes. For business owners, marketers, and sales teams, this is incredibly practical. You can:

• Estimate future sales or revenue to set realistic goals.
• Predict website traffic to plan server capacity or marketing campaigns.
• Forecast inventory needs to avoid stockouts or over-ordering.
• Project team performance to better allocate resources.

The key ingredient is historical data with a time component - think daily sales figures, monthly user sign-ups, or quarterly revenue. The more clean, reliable data you have from the past, the better Excel can predict the future.

Method 1: The Easiest Way with Excel's Forecast Sheet

If you're new to forecasting, this is the perfect place to start. Excel has a feature called "Forecast Sheet" that analyzes your data and automatically creates a forecast visualization and table in a new worksheet. It's fast, easy, and requires zero formula writing.

How to Use the Forecast Sheet

Let's say you have monthly sales data for the last two years and you want to predict sales for the next six months.

Step 1: Get Your Data Ready

First, make sure your data is structured correctly. You need two columns next to each other:

• One column for the date or time periods (e.g., month-ending dates).
• One column for the corresponding values (e.g., monthly sales).

Step 2: Create the Forecast Sheet

Once your data is ready, simply select the entire data range (including headers). Then, navigate to the Data tab on the Ribbon and click on Forecast Sheet in the "Forecast" group.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What to Adjust in the Forecast

A dialog box will pop up showing you a preview of the forecast. Here, you can adjust a few settings by clicking "Options":

• Forecast End: Set the date you want your forecast to extend to.
• Confidence Interval: This creates upper and lower boundary lines showing a range of outcomes. A 95% confidence interval (the default) means Excel is 95% confident that future values will fall within this range. You can adjust this percentage up or down.
• Seasonality: Excel is smart and usually detects seasonal patterns automatically (e.g., holiday sales spikes). You can also set this manually if you know your business runs on a specific cycle (e.g., '12' for a 12-month pattern in monthly data).

When you're happy with the settings, click Create. Excel will instantly generate a new sheet containing a table of your original data plus the forecasted values, a lower confidence bound, and an upper confidence bound, along with a line chart visualizing it all.

Method 2: Predicting a Single Value with the FORECAST.LINEAR Function

Sometimes you don't need a whole chart and table. You might just want to know, "Based on my trend, what are our projected sales for next January?" For this, the FORECAST.LINEAR function is perfect. It uses linear regression to calculate a future value based on your historical data.

This works best for datasets that show a relatively straight-line trend up or down, without strong seasonality.

How to Use FORECAST.LINEAR

The syntax for the formula is: =FORECAST.LINEAR(x, known_y's, known_x's)

Let's break that down:

• x (required): The future date you want to predict a value for.
• known_y's (required): Your range of historical numeric values (e.g., your past sales numbers).
• known_x's (required): Your range of historical dates corresponding to the values.

Using our previous example, let's say our sales data from C2:C25 corresponds to dates in B2:B25. If we want to predict sales for a new date we've entered in cell B26, the formula in C26 would be:

=FORECAST.LINEAR(B26, $C$2:$C$25, $B$2:$B$25)

Note: We use dollar signs ($) to create absolute references for the known_y's and known_x's ranges. This locks the ranges, so if you drag the formula down to predict for more dates, it continues to reference the correct historical data.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 3: Forecasting with Seasonality Using FORECAST.ETS

What if your business has predictable peaks and valleys? Maybe you sell more in the summer or always have a huge fourth quarter. FORECAST.LINEAR will miss these patterns. This is where FORECAST.ETS comes in. It's a much more sophisticated function that accounts for seasonal cycles in your data.

This function powers the "Forecast Sheet" tool behind the scenes, but using the formula directly gives you more flexibility to embed forecasts right into your existing models.

How to Use FORECAST.ETS

The basic syntax is: =FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

The first four arguments are the most important for getting started:

• target_date (required): The future date you want to predict a value for.
• values (required): The range of historical data values.
• timeline (required): The range of historical dates.
• [seasonality] (optional): An integer representing the length of the seasonal pattern. For example:

Here's how you'd use it to predict sales for a future date in Cell B26, assuming our data has a 12-month seasonal pattern:

=FORECAST.ETS(B26, $C$2:$C$25, $B$2:$B$25, 12)

Like before, you can drag this formula down to fill out forecasts for multiple future periods, and it will produce a much more realistic prediction for businesses with cyclical trends.

Best Practices for Better Forecasts

While Excel's tools are powerful, the quality of your output depends on the quality of your input and process. Here are a few tips to make your predictions more reliable.

1. Use Enough Historical Data

More data is almost always better. A forecast based on three months of data is much less reliable than one based on three years. If your data has a seasonal cycle, try to include at least two full cycles to help Excel identify the pattern accurately.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Clean Your Data First

Outliers can throw off your forecast. Before you build your prediction, scan your data for anomalies. For example, did a one-time viral post cause a massive, unrepeatable traffic spike? Was there a data entry error one month? You might consider excluding that data point or averaging it with the surrounding periods to prevent it from skewing the long-term trend.

3. Understand the Limitations

Every forecast is an educated guess based on the past. These models can't predict unexpected market shifts, new competitor actions, or global events. Think of your Excel forecast as a baseline, and use your industry knowledge to adjust it based on factors the data can't account for.

4. Update Your Forecast Regularly

A forecast is not a "set it and forget it" task. As each new month or quarter of actual data comes in, add it to your historical dataset and regenerate your forecast. This allows your model to "learn" from the most recent trends and become more accurate over time.

Final Thoughts

Predicting future trends in Excel moves your business planning from guesswork to data-informed strategy. By using tools like the Forecast Sheet, FORECAST.LINEAR, and the more powerful FORECAST.ETS, you can create a reliable baseline for making smarter decisions about your budget, resources, and goals.

While Excel is fantastic for forecasting, the initial process of pulling, cleaning, and consolidating data from all your different platforms can still be a major headache. That's why we built Graphed. We connect directly to your data sources like Google Analytics, Shopify, and various ad platforms in seconds, so you waste zero time on manual data prep. You can then use simple, plain English to ask questions and generate real-time, interactive dashboards, allowing you to get predictions and insights without ever touching a spreadsheet again.