What Does the FORECAST Function in Excel Do?

Trying to guess what your sales or website traffic will look like next month can feel like gazing into a crystal ball. But instead of mysticism, Excel gives you a powerful tool based on math: the FORECAST function. This article will walk you through exactly what this function does, how to use it, and how to visualize the results to make smarter predictions for your business.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding Excel's FORECAST Function

At its heart, the FORECAST function predicts a future value by following a linear trend line calculated from your historical data. In simpler terms, it looks at your past performance, draws the straight "line of best fit" through those data points, and extends that line to tell you where the next point is likely to land.

This whole process is based on a statistical method called linear regression. You don't need a degree in statistics to use it, but understanding the concept helps. Imagine you're tracking customer growth each month. You have data for January, February, and March. Linear regression finds the one straight line that comes closest to all three of those data points. The FORECAST function then uses that line's trajectory to predict the value for April.

To do this, the function needs three pieces of information:

• The future point you want to predict (x): This is your independent variable, often a unit of time like a future date, month number, or year. (e.g., "Month 7").
• Your existing dependent data (known_y's): This is the data you want to forecast. These are the values that depend on the time period, like sales revenue, units sold, or website visitors.
• Your existing independent data (known_x's): This is the timeline that corresponds to your dependent data. These are the historical months, weeks, or years you already have data for.

Think of it this way: if your known_x's are the months (1, 2, 3) and your known_y's are your sales for those months ($500, $550, $600), you can ask the FORECAST function to calculate the sales for x (Month 4).

Graphed

Still Building Reports Manually?

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

Watch the Demo

FORECAST vs. FORECAST.LINEAR: Which One Should You Use?

If you've been using Excel for a while, you might remember just a plain FORECAST function. When Excel 2016 was released, Microsoft introduced a new suite of forecasting functions that offered more capabilities, retiring the old one.

Here’s the simple breakdown:

• FORECAST: This is the original function. It's now classified as a "compatibility" function, meaning it's kept around so older spreadsheets don't break. While it still works perfectly for linear forecasting, it's best practice to use its modern replacement.
• FORECAST.LINEAR: This is the new, recommended function that directly replaces the older FORECAST function. It performs the exact same linear regression calculation and uses the same arguments. All new forecasting you do should use this function.

The short answer: Always use =FORECAST.LINEAR() in new workbooks. We will use this version for the rest of our examples.

How to Use the FORECAST.LINEAR Function: A Step-by-Step Guide

Let's walk through a practical example. Imagine you run an e-commerce store and want to forecast your sales for the next two months based on your performance over the last six months.

Step 1: Set Up Your Data in Excel

First, organize your historical data into two columns. It's crucial that your independent variable (the time period) is numerical. If you have dates like "Jan 2024," it's often easier to create a "Month" column with simple numbers like 1, 2, 3, etc.

Here’s our sample data:

We want to predict sales for Month 7 and Month 8.

Step 2: Understand the Syntax

The syntax for the function is simple and clean:

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

• x: The specific datapoint you want to predict. For our first forecast, this will be 7 (for Month 7).
• known_y's: The range containing your past sales. In our example, this is B2:B7.
• known_x's: The range containing the corresponding past months. In our example, this is A2:A7.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Write the Formula

Now, let’s build the formula. Click on an empty cell where you want your forecast to appear (say, cell B8 to be next to "Month 7").

Type the following and press Enter:

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

Excel will run the calculation and return a value. Based on this sample data, the result should be approximately $16,114.29. That's your sales forecast for Month 7 based on the linear trend of the previous six months.

Pro Tip: Using Absolute References

If you want to forecast for Month 8 as well, you can drag the formula down. But first, you should lock the ranges for your historical data using the dollar sign ($) for absolute references. This tells Excel to always look at the same data ranges even when you copy the formula to a new cell.

Modify the formula to:

=FORECAST.LINEAR(A8, $B$2:$B$7, $A$2:$A$7)

Here, we are referencing cell A8 (where you would type the number "7"). Now you can add "8" in cell A9, drag the formula down from B8 to B9, and it will automatically calculate the forecast for Month 8 without messing up the data ranges. The forecast for Month 8 would be approximately $17,042.86.

Visualizing Your Forecast with a Chart

A number is a great start, but a visual chart gives you a much better perspective and helps you double-check if a trend really makes sense for your data.

Step 1: Create a Basic Chart With Your Historical Data

1. Select your original data, including the headers (cells A1:B7 in our example).
2. Go to the Insert tab on the Ribbon.
3. In the Charts group, select "Insert Scatter (X, Y) or Bubble Chart" and choose the "Scatter with Straight Lines" option.

You’ll immediately see a chart plotting your sales over the last six months.

Step 2: Add a Trendline to Show the Forecast Path

This is where you make Excel's trendline feature do the forecasting for you visually. This trendline is the exact same one the FORECAST.LINEAR function calculates behind the scenes.

1. Click on your chart to activate it. You'll see "Chart Design" and "Format" tabs appear at the top.
2. Click on the Chart Design tab.
3. On the far left, click Add Chart Element > Trendline > Linear. A dotted line will appear across your data points, showing the line of best fit.

Step 3: Extend the Trendline into the Future

This is the fun part. You can extend this trendline beyond your existing data to see where it heads next.

1. Double-click the dotted trendline you just added. This will open up the "Format Trendline" pane on the right side of your screen.
2. Look for the "Forecast" section in the Trendline Options.
3. In the "Forward" box, type the number of future periods you want to project. Since we forecasted for month 7 and month 8, we’ll type 2 and press Enter.

You will see the trendline extend for two more periods, visually showing you your sales forecast. You can even combine this with your original data (add your forecast results for months 7 and 8) to see your actual numbers, your forecasted numbers, and the trendline all in one place.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Common Mistakes and Best Practices

The FORECAST.LINEAR function is powerful, but it’s not infallible. It’s a tool for estimation, not a perfect prediction machine. Here’s how to use it wisely.

When Is a Linear Forecast the Wrong Choice?

The biggest mistake is using a linear forecast for data that isn’t linear. The function assumes growth (or decline) will continue in a straight line forever.

• Seasonality: If your sales dramatically increase every December for the holidays and dip every February, your data follows a wave-like pattern, not a straight line. A linear forecast would either overestimate or underestimate heavily depending on what part of the cycle you’re in. For seasonal data, explore the more advanced FORECAST.ETS function.
• Exponential Growth: If your company is a startup and user signups are doubling every month, your growth curve is exponential. A straight-line forecast will significantly underestimate your future performance.
• External Factors: The function only knows your past numbers. It can't predict an economic downturn, a new competitor entering the market, or the impact of a huge marketing campaign you're launching next month. Always layer your own business context on top of the mathematical prediction.

Best Practices for Reliable Forecasting

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

• Use enough historical data. Trying to forecast with just three data points is unreliable. The more quality data you can provide, the more accurate the trend will be.
• Clean your data first. Check for and handle any outliers—extremely unusual data points that don't represent your normal business. For example, if a celebrity tweeted about your product and caused a one-day sales spike of 10x, you might consider excluding that data point from a forecast of day-to-day sales.
• Always visualize it. Before you even write the formula, plot your data on a chart. If it doesn't look like it's roughly following a straight line, FORECAST.LINEAR may not be the right choice.

Final Thoughts

With FORECAST.LINEAR and a simple chart, you can move from guesswork to data-backed estimation. It’s an easy-to-use function that provides a quick and helpful reference point for planning, resource allocation, and goal setting, especially when you understand its assumption of a straight-line trend.

Manually fetching data from different sources and prepping it for forecasting in Excel can eat up a significant part of your day. We wanted to make getting those answers instant, so we built Graphed. By securely connecting your platforms like Shopify, Google Analytics, or Salesforce, you can skip the spreadsheets entirely and simply ask "create a dashboard projecting our sales for the next quarter." Graphed generates real-time, shareable dashboards so you can focus on making decisions, not updating formulas.