What is FORECAST ETS in Excel?

Trying to predict future trends using your historical data can feel like you need a crystal ball. Luckily, Excel has a powerful function that a lot of people overlook: FORECAST.ETS. This article will break down exactly what this function is, when you should use it, and how to apply it step-by-step to your own data to generate surprisingly accurate predictions.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What Exactly is FORECAST.ETS?

In simple terms, FORECAST.ETS is an Excel function designed to predict a future value based on a timeline of existing historical data. The "ETS" stands for Exponential Triple Smoothing, which might sound intimidating, but the concept is straightforward. It's a sophisticated algorithm that is particularly good at handling data with seasonal patterns or other recurring trends.

Think about sales for a retail business. You likely see spikes in November and December for the holidays, a dip in January and February, and maybe another bump in the summer. That repeating, predictable pattern is called seasonality. While simpler forecasting methods might just draw a straight line of best fit, FORECAST.ETS is smart enough to see that yearly pattern and account for it in its predictions, making it much more accurate for real-world business data.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

When to Use FORECAST.ETS vs. a Simpler FORECAST

Excel has other forecasting functions, like FORECAST.LINEAR, which predicts a future value along a simple linear trend. This works fine if your data points follow a reasonably straight line (e.g., steady, non-cyclical growth).

You should choose FORECAST.ETS when:

• Your data has a clear, repeating pattern or cycle (seasonality).
• You're working with time-based data, such as daily website traffic, monthly sales figures, or quarterly expenses.
• Your data trend isn't just a straight line but has peaks and valleys that occur at regular intervals.

Essentially, if you were to graph your historical data and it looks more like a wave than a straight road, FORECAST.ETS is the tool you need.

Breaking Down the FORECAST.ETS Syntax

Before using the function, it's helpful to understand what each part of the formula does. At its core, the syntax looks like this:

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

The first three arguments are required, while the last three are optional but give you more control over the forecast.

Required Arguments:

• target_date: This is the future date for which you want to predict a value. It must be a valid date and come after your timeline of historical dates.
• values: This is the range of your historical data points. For example, the column in your spreadsheet containing monthly sales numbers. These are the values you want to forecast.
• timeline: This is the range of dates that corresponds to your historical values. This range must be the same size as your values range, and the dates should have a consistent interval or "step" — like the first of every month, every Monday, or every quarter.

Optional Arguments:

• [seasonality]: This is where the magic happens. You’re telling Excel the length of the seasonal pattern. For monthly data with a yearly pattern, you'd use 12. For daily data with a weekly pattern, you'd use 7. You have three main options:
• [data_completion]: This handles missing data points in your historical values.
• [aggregation]: This tells Excel what to do if you have duplicate timestamps in your timeline. For example, if you have two sales figures for the same day.

Step-by-Step Guide: Making Your First Forecast

Let's walk through a practical example. Imagine you're an e-commerce manager and you have monthly sales data for the last two years. You want to forecast your sales for the next three months.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 1: Get Your Data Ready

First, make sure your data is organized properly in two columns. One column should have the date (your timeline), and the other should have the corresponding value (your sales). Critically, your dates need to have a consistent step. For monthly data, using the first day of each month is a common best practice.

Your table might look something like this:

Step 2: Set Up Your Cells for the Forecast

Now, add the future dates you want to forecast. In our example, we want to forecast sales for January, February, and March of 2024. Add those dates to your "Date" column.

Step 3: Write the FORECAST.ETS Formula

Let’s assume your dates are in column A (from A2 to A25) and your sales are in column B (from B2 to B25). You want to predict the value for January 1, 2024, which is in cell A26.

Click into cell B26, and type the following formula:

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

Let’s break that down:

• A26: This is our target_date.
• $B$2:$B$25: This is our historical sales data (values). We use dollar signs ($) to lock the range so it doesn't shift when we drag the formula down.
• $A$2:$A$25: This is our historical date range (timeline), also locked.
• 1: This tells Excel to automatically detect the seasonality. For monthly sales, Excel will likely identify a 12-month pattern.

Press Enter, and Excel will calculate the forecasted sales for January 2024. Now you can click on the bottom-right corner of cell B26 and drag the formula down to predict values for February and March as well.

Step 4: Visualize Your Forecast

Numbers in a table are good, but a chart is much better for seeing the trend. You can easily visualize your actual data alongside your forecast.

1. Select both your "Date" and "Sales" columns, including the historical data and your new forecasted values.
2. Go to the Insert tab in Excel's ribbon.
3. In the Charts group, select the Line chart option. A simple 2D line chart works perfectly.

Excel will generate a chart showing your past sales followed by a dotted or different-colored line representing your forecast. This visualization instantly makes it clear whether the forecast seems reasonable and follows the seasonal patterns of your previous years' data.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Common Errors and How to Fix Them

Sometimes you might run into an error when using FORECAST.ETS. Here are the most common ones and what they mean:

• #N/A!: This usually means your timeline range and your values range aren't the same size. Double-check that you’ve selected the same number of cells for both.
• #VALUE!: This error often happens if one of the arguments is not a number when it should be. For instance, putting text in the seasonality argument instead of a number like 1, 0, or 12.
• #NUM!: This error can occur if the timeline dates don't have a consistent step (e.g., mixing monthly and weekly data), if the seasonality you provided is invalid, or if the algorithm just can't find a reliable pattern in your data. Check that your dates are evenly spaced.

Tips for Better Forecasting Accuracy

To get the most out of FORECAST.ETS, keep these tips in mind:

• More data is better. For a yearly pattern, you should have at least two full years of data so the algorithm can reliably detect the cycle. Three or more is even better.
• Clean your data. Ensure there are no major outliers that could throw off the algorithm unless they represent a genuine, recurring event.
• Experiment with seasonality. If you find that the automatic detection (1) isn't capturing the trend correctly, try manually inputting the seasonality (e.g., 12 for monthly data with a yearly cycle) to see if it improves the result.

Final Thoughts

FORECAST.ETS elevates Excel from a simple spreadsheet tool into a powerful predictive engine. By understanding how to use it, you can move beyond simply looking at past data and begin making data-driven predictions about the future, whether it's for sales, web traffic, or inventory needs.

While Excel is fantastic for one-off analyses, creating and managing real-time business dashboards that pull from various sources like Google Analytics, Shopify, and your CRM can be a huge time-sink. Instead of spending hours each week exporting CSVs and updating charts, we built Graphed to connect to your key data sources directly. We let you ask questions in plain English - like "forecast our Shopify revenue for next quarter" - and our AI data analyst builds a live, professional dashboard for you in seconds, saving you time and keeping your insights constantly up-to-date.