How to Use FORECAST.ETS in Excel

Predicting the future is tricky, but Excel’s FORECAST.ETS function can make it a lot easier, turning your historical data into a data-driven look at what’s likely coming next. This powerful tool goes beyond simple linear predictions by understanding patterns, especially seasonal ones that affect so many businesses. This guide will walk you through exactly how to use FORECAST.ETS, breaking down its syntax and showing you a practical, step-by-step example.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is FORECAST.ETS and Why Should You Use It?

At its core, FORECAST.ETS is one of Excel’s most advanced forecasting functions. It's designed specifically for time-series data - think monthly sales, daily website traffic, or quarterly user signups - where patterns repeat over time.

The "ETS" stands for Exponential Triple Smoothing, which might sound intimidating, but the concept is fairly straightforward. It's a gold-standard forecasting algorithm that considers three key components to make its predictions:

• Error (E): It looks at how far off its previous predictions were and adjusts to minimize future errors.
• Trend (T): It identifies the overall long-term direction of your data. Is it generally increasing, decreasing, or staying flat?
• Seasonality (S): This is the secret sauce. It detects and accounts for recurring, cyclical patterns. For an e-commerce store, this might be a sales spike every December during the holidays or a dip every February.

While simpler functions like FORECAST.LINEAR can only predict based on a straight-line trend, FORECAST.ETS is much smarter. It understands that business doesn't always move in a straight line. By incorporating seasonality, it provides a much more nuanced and realistic forecast for data that ebbs and flows with the calendar.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding the FORECAST.ETS Syntax

To use the function effectively, you first need to understand its parts. Here’s what the formula looks like:

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

Let's break down each argument one by one.

Required Arguments:

• target_date: This is the specific future date for which you want to create a forecast. If your data is monthly, this would be the first day of the next month.
• values: This is the range of your historical data points - the numbers you want to forecast. For example, your B2:B24 range containing monthly sales revenue.
• timeline: This is the range of dates corresponding to your historical values. Every data point in your values range must have a corresponding date in the timeline range. For example, A2:A24.

Optional Arguments:

These arguments give you more control, but Excel can often handle them automatically.

• [seasonality]: This is arguably the most important optional argument. It tells Excel the length of your seasonal cycle.
• [data_completion]: This argument tells Excel how to handle missing data points in your values range. You have two options:
• [aggregation]: This determines how Excel should handle multiple data points that have the same timestamp in your timeline. The default option is AVERAGE, but you can choose SUM, COUNT, MIN, MAX, among others. For most standard forecasts, you won't need to change this.

A Step-by-Step Example: Forecasting Monthly Sales

Theory isn't that helpful without practice. Let's walk through forecasting monthly product sales for a small online business. Imagine you have two years of sales data and want to predict sales for the next six months.

Step 1: Set Up Your Data

First, organize your data into two columns in an Excel sheet. Ensure it's clean and consistently formatted.

• Column A: Date (e.g., 1/1/2022, 2/1/2022, 3/1/2022, etc.)
• Column B: Sales (e.g., $15,000, $16,500, $18,000, etc.)

For FORECAST.ETS to work properly, your dates in the timeline need to have a consistent interval. In this case, we're using the first day of each month. Let's assume your data runs from cell A2 to B25 (representing 24 months of data).

Before writing the formula, extend your Date column (Column A) to include the future months you want to forecast. If your last data point is 12/1/2023 in A25, you'll add 1/1/2024 to A26, 2/1/2024 to A27, and so on.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Write the FORECAST.ETS Formula

Now you're ready to build the formula. Click into the cell next to your first future date (in this example, C26, even though we are predicting for B26). Here you'll enter the formula.

Based on our setup, the formula will look like this:

=FORECAST.ETS(A26, $B$2:$B$25, $A$2:$A$25, 12, 1, 1)

Let's walk through that formula with our example:

• A26 is our target_date - the first future date we want to predict.
• $B$2:$B$25 is our values range, containing all 24 months of historical sales data. We use dollar signs ($) to create an absolute reference so this range doesn’t change when we drag the formula down.
• $A$2:$A$25 is our timeline range, containing the dates that correspond to our sales figures. This is also an absolute reference.
• 12 is our seasonality. Since we have monthly data and expect an annual pattern (e.g., holiday spikes), a 12-month cycle makes sense.
• The final two 1s represent data_completion (interpolate missing values) and aggregation (average duplicates), which are the standard defaults.

Step 3: Extend Your Forecast

With the formula entered, press Enter. Excel will calculate the forecasted sales for January 2024.

The best part? You can now grab the small square (the fill handle) at the bottom-right corner of cell C26 and drag it down for as many months as you need a forecast. Because you used absolute references for your values and timeline ranges, they will remain fixed, while the target_date reference will correctly move down your list of future dates.

Step 4: Visualize Your Results

Numbers in a table are good, but a chart is better. It allows you to see the trend and how your forecast fits with historical performance.

Select all three columns - your original dates, original sales, and the forecasted sales. Go to the Insert tab in Excel and choose a Line Chart. Excel will plot a chart showing your historical performance and projected forecast on the same graph, giving you a clear, intuitive view of where your sales might be headed.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Best Practices and Common Issues

As you use FORECAST.ETS, keep these tips in mind to avoid common errors and get the most accurate results possible.

• You Need Enough Data: To properly detect seasonality, the function needs at least one full seasonal cycle's worth of data. For a business with an annual cycle, that means you need at least 12 months of historical data. Two to three full cycles is even better for accuracy.
• Watch for Mismatched Ranges: A common source of #N/A! errors is when your values and timeline ranges are not the same size. Double-check that for every sales number, there is a corresponding date.
• Keep Your Timeline Consistent: The function expects a constant step between your dates (e.g., every day, every month, every quarter). Inconsistent date intervals can confuse the algorithm and produce unreliable forecasts.
• Know When NOT to Use It: FORECAST.ETS is designed for time-series forecasting. It's not a magic bullet for all predictions. For example, it wouldn't be the right tool to predict housing prices based on square footage, as that relationship isn't based on time, trend, and seasonality.

Final Thoughts

FORECAST.ETS is an incredibly useful tool for anyone needing to make data-backed predictions from time-series data in Excel. By understanding its components and preparing your data correctly, you can move beyond simple linear guesses and create forecasts that account for the real-world trends and seasonal cycles that drive your business.

Of course, the most tedious part of any analysis in Excel is often getting the data in there in the first place. Manually exporting CSVs from Shopify, Google Analytics, Salesforce, and all your ad platforms to build these forecasts is time-consuming and prone to errors. At Graphed, we found this manual-reporting grind was holding marketing and sales teams back, which is why we built a better way. We help you connect all those data sources in a few clicks, so generating a forecast isn't a four-hour project - it's a 30-second question. You can ask "Graphed to forecast my Shopify sales and Google Ads spend for the next quarter" and get a live, auto-updating dashboard in seconds, without ever touching a spreadsheet formula again.