What is FORECAST.LINEAR in Excel?

Predicting future performance is essential for any business, whether you're estimating next quarter's sales or projecting website traffic. Microsoft Excel offers a set of powerful tools for exactly this, and one of the most straightforward is the FORECAST.LINEAR function. It's designed to help you make informed predictions based on your existing data.

Graphed

Still Building Reports Manually?

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

Watch the Demo

This tutorial will guide you through exactly what FORECAST.LINEAR does, how to use it step-by-step, and when it's the right tool for the job. We'll break down the syntax and walk through a practical example to make forecasting simple and accessible.

What Does Linear Forecasting Mean?

Before using the function, it helps to understand the concept behind it: linear regression. It sounds complex, but the idea is simple. Linear regression in data analysis is about finding a straight-line pattern in your data. It looks at the relationship between two sets of numbers and tries to draw a single straight line that best fits the trend.

Imagine your company’s revenue has grown by about $5,000 each month for the past year. Visually, if you plotted this on a graph, the dots would form something close to a straight line trending upwards. A linear forecast would use this line to predict that revenue will increase by another $5,000 next month.

To do this, the function works with two variables:

• The Independent Variable (x-values): This is the variable you control or that moves forward predictably, like a timeline. Common examples include months (1, 2, 3…), years, or advertising spend.
• The Dependent Variable (y-values): This is the variable you are measuring and want to predict. Its value depends on the independent variable. Examples include sales figures, website visitors, or conversion rates.

FORECAST.LINEAR is perfect when you have historical data that shows a relatively consistent, straight-line trend. It uses this past trend to extend the line into the future and predict what might happen next.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The FORECAST.LINEAR Function: Syntax Explained

The syntax for the function is clean and simple, requiring just three arguments to make a prediction. Understanding what each part does is the first step to using it effectively.

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

Breaking Down the Arguments:

• x (Required): This is the specific data point you want to predict a value for. It's your future "x" value. For instance, if your existing data covers months 1 through 6, and you want to predict sales for month 7, your x would be 7.
• known_y's (Required): This is the range of your historical dependent data. These are the outcomes you already know. In our example, this would be the cell range containing your sales figures for months 1 through 6.
• known_x's (Required): This is the range of your historical independent data. This range corresponds directly to your known_y's. This would be the cell range containing the numbers for months 1 through 6.

Important Note: The known_y's and known_x's ranges must have the exact same number of cells. If one range is longer than the other, Excel will return a #N/A error because it can't match each 'y' point to an 'x' point.

How to Use FORECAST.LINEAR: A Practical Example

Let's walk through a realistic scenario. Imagine you're a marketing manager, and you want to estimate website traffic for the upcoming third quarter (Q3) based on performance from the first half of the year.

Step 1: Set Up Your Data

First, organize your existing data in a simple table. For readability, have your independent variable (time) in the left column and your dependent variable (what you're measuring) in the right column.

For this example, your data might look like this:

• Column A: Month (Numbered 1-6 for Jan-Jun)
• Column B: Website Visitors

Let's say your spreadsheet is set up as follows:

• A1: "Month Number", B1: "Website Visitors"
• A2: 1, B2: 12,500
• A3: 2, B3: 13,100
• A4: 3, B4: 13,650
• A5: 4, B5: 14,400
• A6: 5, B6: 15,000
• A7: 6, B7: 15,700

We also want to predict for July, August, and September, so add those months to a new row:

• A8: 7
• A9: 8
• A10: 9

Step 2: Write the Formula

Now, let's predict the traffic for Month 7. You'll write the formula in the cell next to your new x-value (in this case, cell B8).

• x: The value we want to predict for is in cell A8 (which is 7).
• known_y's: Our historical visitor data is in the range B2:B7.
• known_x's: Our historical month numbers are in the range A2:A7.

Your formula in cell B8 will be:

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

Press Enter, and Excel will calculate the predicted traffic for Month 7. Based on the trend in the sample data, this should return a value like 16,400.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Extend the Forecast Using Absolute References

Now you want to predict for months 8 and 9. You might be tempted to just click and drag the fill handle from cell B8 down to B10. However, this will cause a problem. When you drag a formula down, Excel automatically adjusts the cell ranges. Your x value (A8) will correctly shift to A9 and A10, but your known_y's and known_x's ranges will also shift, breaking the reference to your historical data.

To fix this, you need to "lock" your lookup ranges using absolute references, which you do by adding a dollar sign ($) before the column letter and row number.

Modify the formula in B8 to this:

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

Now, when you drag this formula down to cells B9 and B10, the range of historical data ($B$2:$B$7 and $A$2:$A$7) will remain locked, while the x value will correctly update to A9 and A10. You'll now have your complete Q3 forecast.

Visualizing Your Forecast with an Excel Chart

Numbers in a table are useful, but a chart makes your forecast instantly clear. Visualizing the data helps you see the trend and how your prediction fits into it.

1. Organize All Your Data: Make sure your historical data and your new forecasted data are in continuous rows. Your Month Number column should go from 1 to 9, and your Website Visitors column should include your actual data and your newly calculated forecasts.
2. Create a Line Chart: Select both columns of data (in our example, A1:B10). Go to the Insert tab on the ribbon and, in the Charts group, click Insert Line or Area Chart. Choose the first 2-D Line chart option.
3. Format for Clarity (Optional but Recommended): Excel will create a chart with one solid line. To distinguish actual data from the forecast, you can format the forecast portion.

You now have a clear, professional chart showing both your past performance and a data-driven forecast of the future.

Common Pitfalls and Best Practices

FORECAST.LINEAR is a great tool, but it's not universally applicable. Knowing when to use it — and when not to — will make your predictions more reliable.

When FORECAST.LINEAR Shines

• Highly Linear Data: It's perfect for data that already follows a consistent, straight-line trend heading up or down.
• Simple Relationships: It works best when you are examining the relationship between just two variables, like time vs. revenue or ad spend vs. clicks.
• Short-Term Predictions: Linear forecasting is generally most accurate for near-future predictions (e.g., next month or next quarter).

When to Be Cautious

• Seasonal Data: If your data has predictable peaks and valleys (like swimwear sales peaking in summer and dropping in winter), a straight line won't capture this pattern. For this, you should look at Excel’s FORECAST.ETS formula, which is designed to handle seasonality.
• Volatile or Random Data: If your data points are scattered all over a chart with no clear direction, a linear forecast will be unreliable because there's no consistent trend to follow.
• Long-Term Forecasts: The further out you predict, the more external factors can influence the outcome. A linear trend that holds for six months may not hold for three years due to market changes, new competition, or other variables.

Graphed

Still Building Reports Manually?

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

Watch the Demo

FORECAST.LINEAR vs. FORECAST: What's the Difference?

If you've been using Excel for a while, you might be familiar with an older function called just FORECAST. So, what's the difference?

Functionally, FORECAST and FORECAST.LINEAR do the exact same calculation and will return the exact same result. The FORECAST function is an older "compatibility" function retained from Excel 2013 and earlier versions.

In Excel 2016, Microsoft introduced a suite of new forecasting functions with more descriptive names to handle different types of data. FORECAST.LINEAR is the official modern replacement for FORECAST. While you can still use the old version, it's best practice to use FORECAST.LINEAR to stay consistent with modern Excel and signal that you are specifically conducting a linear regression.

Final Thoughts

FORECAST.LINEAR is a powerful yet beautifully simple function for making quick, data-driven predictions in Excel. By understanding how to structure your source data and apply the formula correctly, you can easily turn historical trends into actionable insights about future performance.

While Excel is great for these analyses, the process can become tedious — exporting fresh data weekly, updating your formulas, and rebuilding charts. It often feels like you're stuck in a reporting cycle. At some point, you end up spending more time updating spreadsheets than acting on the insights they provide. We built Graphed to solve exactly this problem. Our tool connects directly to your live data sources like Google Analytics, Shopify, and your ad platforms, so your dashboards and reports are always up-to-date. Instead of being an Excel wizard, you can simply ask for a "forecast of next quarter's sales based on last year's traffic," and Graphed builds a real-time dashboard for you, keeping it fresh automatically.