How to Use FORECAST.LINEAR in Excel

Predicting future trends is easier than you think, especially when you have historical data and a trusty spreadsheet. Excel’s FORECAST.LINEAR function is a straightforward tool that uses linear regression to project future values based on what’s already happened. This article will walk you through exactly how to use this formula, from understanding its syntax to building your own sales forecast and visualizing the results.

What Exactly Is FORECAST.LINEAR?

At its core, the FORECAST.LINEAR function calculates or predicts a future value along a linear trend line. It works by analyzing two sets of corresponding data - like sales numbers over a series of months - and finds the best-fitting straight line through those points. Once it establishes this trend, it can extend the line to predict a "y" value (like future sales) for a future "x" value (like a future month).

This function is perfect for situations where your data shows a relatively consistent upward or downward trend over time. Here are a few great use cases:

• Sales Forecasting: Predicting next quarter's revenue based on the last four quarters.
• Website Traffic Projections: Estimating unique visitors for the next month based on historical traffic data.
• Inventory Management: Forecasting the demand for a product in the upcoming weeks.
• Budgeting: Projecting future expenses based on spending patterns from previous periods.

It's important to remember that this function assumes a linear relationship. If your data has seasonal spikes and dips (like an ice cream shop's sales peaking every summer), other functions like FORECAST.ETS might be more appropriate. But for simple, straight-line trends, FORECAST.LINEAR is a fantastically simple and powerful tool.

Understanding the Formula's Syntax

The FORECAST.LINEAR formula is surprisingly simple and only requires three pieces of information, which Excel calls "arguments."

The syntax looks like this: =FORECAST.LINEAR(x, known_y's, known_x's)

Let’s break down each part:

• x (required): This is the data point for which you want to predict a value. It's the future "x" value. For example, if you have sales data for months 1 through 6, and you want to predict sales for month 7, your x would be 7. This must be a numerical value.
• known_y's (required): This is your range of known dependent data points. These are the historical outcomes you want to project, like your past sales figures or website traffic numbers. In a spreadsheet, this would be the range of cells containing an array of values (e.g., B2:B12).
• known_x's (required): This is your range of known independent data points. These values correspond to your known_y's. Usually, this is your time series - like dates, months, or years (e.g., A2:A12).

A crucial rule to remember: The known_y's and known_x's ranges must be the exact same size. If your sales data spans 11 cells, your date range must also span 11 cells, or Excel will return an error.

A Step-by-Step Example: Forecasting Monthly Product Sales

The best way to learn is by doing. Let's create a forecast for hypothetical monthly sales data. Imagine we have the following data showing the number of units sold over the last year.

Step 1: Set Up Your Data in Excel

First, organize your data into two columns. Column A will be our known_x's (Month Number), and Column B will be our known_y's (Units Sold).

You can also use actual dates, as Excel treats them as sequential serial numbers, but using simple month numbers (1, 2, 3...) can make the formula easier to read for this example.

Here’s what our data looks like:

Step 2: Identify Your Inputs for the Formula

We want to forecast the "Units Sold" for the next three months: Month 13 (next January), Month 14 (February), and Month 15 (March).

Based on our data table, our inputs are:

• x: The future month we want to predict. For our first forecast, this will be the value in cell A14, which is 13.
• known_y's: Our historical sales data. This is the range B2:B13.
• known_x's: The historical time period. This is the range A2:A13.

Step 3: Write the FORECAST.LINEAR Formula

Now, click into the cell where you want your forecast to appear (in our example, it's B14). Type the following formula:

=FORECAST.LINEAR A14, B2:B13, A2:A13

When you put the formula together:

• A14 is the x-value we want to predict for (Month 13).
• B2:B13 is the range of our known sales figures.
• A2:A13 is the range of our known months.

Press Enter, and Excel will calculate the forecasted value!

Step 4: Extend the Forecast

One of the best things about Excel is how easy it is to apply a formula to multiple cells. To forecast for Months 14 and 15, we need to make a small adjustment to our formula to "lock" the ranges for our known data.

Modify the formula in cell B14 to use absolute references (dollar signs) for the known_y's and known_x's ranges. This prevents the range from shifting down as you drag the formula.

=FORECAST.LINEAR A14, $B$2:$B$13, $A$2:$A$13

Now, click on the small green square at the bottom-right corner of cell B14 and drag it down to cover cells B15 and B16. Excel will automatically apply the formula to predict the sales for months 14 and 15.

Visualizing Your Forecast in a Chart

A forecast is much easier to understand when you can see it. Creating a line chart in Excel that combines your historical data with your forecasted data provides an excellent visual of the trend.

1. Select Your Data: Highlight all your data, including the headers and the new forecasted values (in our case, the range A1:B16).
2. Insert a Line Chart: Go to the Insert tab on the Ribbon, click on the Insert Line or Area Chart icon, and select the first 2-D Line chart option.
3. Format Your Chart: Excel will generate a chart for you. You can customize it by adding a chart title, axis labels, and adjusting colors to differentiate between actual and forecasted data.

A helpful tip is to add a trendline to your historical data. Right-click on the blue line representing your historical "Units Sold" data series and select Add Trendline... In the panel that opens, choose Linear. This visual line is exactly what FORECAST.LINEAR is using to make its predictions.

Common FORECAST.LINEAR Errors and How to Fix Them

Like any formula, you might run into an error. Here are the most common ones and what they mean:

• #N/A!: This is the most frequent error. It almost always means your known_y's and known_x's ranges are not the same size. Double-check your ranges (e.g., B2:B13 and A2:A13) to ensure they have the same number of rows.
• #VALUE!: This error appears if any of your data points are non-numeric. Check your ranges for any text that might have been entered by mistake. While dates are okay (Excel reads them as numbers), cells with text qualifiers like "Approx. 520" will cause a problem.
• #DIV/0!: This error occurs if the values in your known_x's range are all the same. The formula can't calculate a slope if the independent variable never changes, leading to a division-by-zero error. Ensure you have variance in your known_x's data.

Final Thoughts

Mastering FORECAST.LINEAR in Excel gives you a quick and effective way to make data-driven predictions for any metric that follows a clear trend. It’s a great first step into the world of forecasting, allowing you to move beyond gut feelings and use your historical data to plan for the future, right from your spreadsheet.

While Excel is powerful, setting up these forecasts across multiple datasheets and different platforms often means hours spent downloading CSVs and manually wrangling data before you can even start. To simplify all of that, we created Graphed . It connects directly to your data sources like Google Analytics, Shopify, or Salesforce, so you can build real-time dashboards and generate forecasts using plain English. Instead of spending time wrestling with formulas, you can just ask, "Show me a chart forecasting my user growth for the next 3 months," and get an answer in seconds.