How to Use FORECAST Formula in Excel

Trying to predict future results - whether it's next quarter's sales, website traffic, or inventory needs - can often feel like pure guesswork. But within Excel lies a set of powerful functions designed to take historical data and project it into the future with surprising accuracy. This guide will walk you through exactly how to use the FORECAST family of functions in Excel, moving you from guessing to making data-driven predictions.

What is the FORECAST Formula in Excel?

At its core, the FORECAST formula in Excel is a tool for statistical prediction. It analyzes a series of existing data points to predict a future value. For example, if you have sales data from the last 12 months, you can use a FORECAST function to estimate what sales will be in the 13th month.

Modern versions of Excel (Excel 2016 and newer) have expanded this into a family of functions, each suited for different kinds of data:

• FORECAST.LINEAR: This is the classic function (previously just called FORECAST). It's used for data that follows a relatively straight-line, or linear, trend. Think of a small business with sales that grow steadily month over month.
• FORECAST.ETS: This is a more advanced function that uses an "Exponential Triple Smoothing" algorithm. It's designed for data that has seasonal or cyclical patterns, like a retail store that always sees a surge in sales during the holidays.

Choosing the right function depends entirely on the nature of your data. Let's break down how to use both.

Using FORECAST.LINEAR for Simple Predictions

Use FORECAST.LINEAR when your data shows a consistent trend over time. If you were to plot your data on a chart and could draw a reasonably straight line through it, this is the function for you.

Breaking Down the Formula

The syntax for FORECAST.LINEAR is straightforward:

=FORECAST.LINEAR(x, known_y's, known_x's)

Let's translate that into plain English:

• x (required): This is the new data point for which you want to predict a value. For example, if your historical data is for months 1 through 12, your 'x' would be 13 to predict the value for the 13th month.
• known_y's (required): This is your range of known dependent values. These are the numbers you are trying to predict, like your actual sales figures or website sessions.
• known_x's (required): This is your range of known independent values. This is typically your timeline, like dates, months, or quarters that correspond to your known_y's.

A critical rule here is that your known_y's and known_x's ranges must be the same size. If you have 12 sales figures, you must have 12 corresponding months or dates.

Step-by-Step Example: Forecasting Monthly Sales

Imagine you run a small online store and have your sales data for the first six months of the year. You want to forecast sales for July (Month 7).

Here’s your data in Excel:

• Column A (known_x's): Month (1, 2, 3, 4, 5, 6)
• Column B (known_y's): Sales ($10,200, $11,500, $12,100, $13,000, $13,800, $14,500)

Now, let’s forecast the sales for Month 7.

1. Click on the cell where you want your forecast to appear (e.g., C7).
2. Type the following formula:

=FORECAST.LINEAR(A8, B2:B7, A2:A7)

Let’s break that down:

• A8 is our x, the month we want to predict (7).
• B2:B7 is our known_y's, the actual sales data.
• A2:A7 is our known_x's, the months corresponding to the sales data.

1. Press Enter. Excel will calculate the forecast. Based on this data, the result would be approximately $15,626.

You can then drag this formula down to predict months 8, 9, and beyond by adding those numbers to your 'Month' column.

Tackling Seasonality with FORECAST.ETS

Real-world data is rarely a perfect straight line. What if your business sells more in summer and less in winter? This is seasonality, and FORECAST.ETS is built to handle it.

When to Use FORECAST.ETS

Use this function when your data exhibits a repeating pattern or cycle. Examples include:

• E-commerce stores seeing sales spikes in Q4.
• Ice cream shops with higher revenue in the summer.
• Website traffic that peaks on weekdays and drops on weekends.

FORECAST.ETS automatically detects these seasonal patterns in your historical data and incorporates them into its predictions, making it much more accurate for cyclical trends.

Understanding the Formula

The syntax for FORECAST.ETS is a bit more involved, but its core components are similar:

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

• target_date (required): The date or time point you want to predict a value for.
• values (required): The range of historical values (your "known_y's").
• timeline (required): The range of dates or times corresponding to your values (your "known_x's"). This must be a consistent interval (e.g., daily, monthly, quarterly).
• [seasonality] (optional): This tells Excel the length of a seasonal pattern. For example, if you have quarterly data, your pattern repeats every 4 points. For monthly, it would be 12. If you omit this, Excel tries to detect it automatically, which usually works well.
• [data_completion] (optional): How to handle missing data points. The default is 1, which fills them in by calculating an average of the neighboring points.
• [aggregation] (optional): If your timeline has duplicate dates, this tells Excel how to aggregate the values (e.g., AVERAGE, SUM, COUNT).

Step-by-Step Example: Predicting Quarterly Website Traffic

Let's say you have two years of quarterly website traffic and notice that traffic is always highest in Q4. You want to predict traffic for Q1 2024.

Your data in Excel:

• Column A (timeline): Quarter End Dates (e.g., 3/31/2022, 6/30/2022, ... 12/31/2023)
• Column B (values): Website Traffic (e.g., 45000, 48000, 51000, 62000, 54000...)

To predict traffic for the end of Q1 2024 (3/31/2024):

1. Click on a blank cell for your prediction.
2. Type in the formula:

=FORECAST.ETS("3/31/2024", B2:B9, A2:A9, 4)

Let's examine it:

• "3/31/2024" is our target_date.
• B2:B9 is the range of historical traffic values.
• A2:A9 is the corresponding quarterly timeline.
• 4 is our optional seasonality, indicating a quarterly pattern.

1. Press Enter. Excel will provide a forecast that accounts for both the overall upward trend and the expected seasonal dip in Q1 traffic compared to Q4.

Visualize Your Predictions: Create an Excel Chart

A number is good, but a visual is better. Creating a chart that shows your historical data alongside your forecasted data is the best way to see the trend and evaluate your prediction.

1. Combine Your Data: Make sure your historical data and your forecasted values are in continuous columns. For example, your timeline in column A, historical data in column B, and your forecasts starting on the next row in column B.
2. Select the Data: Highlight both the timeline and the data columns, including the historical and forecasted portions.
3. Insert Chart: Go to the Insert tab on the ribbon and click on Recommended Charts. Excel will often suggest a Line Chart, which is perfect for this purpose. Select it and click OK.
4. Format a Little: You now have a chart showing your entire data series. You can click on the line, go to the Format Data Series options, and change the forecasted portion to a dashed line or a different color to easily distinguish it from the actual data.

Troubleshooting Common FORECAST Errors

Sometimes your formulas might not work as expected. Here are a few common issues and their solutions:

• #N/A Error: This usually means your known_y's and known_x's ranges are not the same size. Double-check that you have an 'x' value for every 'y' value.
• #VALUE! Error: This typically happens if your data contains text or non-numeric values where there should only be numbers. Make sure your values and timeline ranges are clean.
• #DIV/0! Error: For FORECAST.LINEAR, this can occur if the known_x's values are all the same, as Excel can't establish a trend.
• Inaccurate results: Your forecast is only as good as your data. If predictions seem wild, consider if you're using the right function. Are you using FORECAST.LINEAR on clearly seasonal data? Or, do you simply need more historical data to establish a stronger pattern?

Final Thoughts

Excel's forecasting functions are incredibly handy for turning past performance into a glimpse of the future. By understanding the difference between linear and seasonal data, you can choose the right tool - FORECAST.LINEAR or FORECAST.ETS - and start making more informed, data-driven decisions right in your spreadsheet.

While mastering these formulas is worthwhile, we know that sometimes you need answers without getting stuck in cells and ranges. At Graphed, we’ve made forecasting as easy as asking a question. By connecting your sources like Google Analytics or Shopify, you can just ask, "Forecast my website traffic for next month," and instantly see a real-time visualization without building it yourself. The goal is to get you from data to decision faster, and we created Graphed to do exactly that.