How to Use the FORECAST Function in Google Sheets

Trying to predict where your sales, website traffic, or project costs are heading next can feel like guesswork. Fortunately, Google Sheets has a powerful built-in tool that uses historical data to give you a surprisingly accurate glimpse into the future. This article will walk you through how to use the FORECAST function, provide a step-by-step example of how to use it, demonstrate how to visualize the results, and identify common mistakes to avoid.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly Is the Google Sheets FORECAST Function?

The FORECAST function calculates a future value based on existing values using a method called linear regression. That might sound technical, but the concept is simple. It looks at your past data, imagines a straight “line of best fit” running through those data points, and then extends that line to predict where the next point is likely to land.

Think about it like this: if your monthly sales have been increasing by about $100 each month, the FORECAST function will notice that trend. When you ask it to predict next month's sales, it will add approximately $100 to your last known sales figure. It’s perfect for spotting and projecting straightforward, linear trends in your data, whether you're looking at user growth, revenue, or inventory levels.

Back to Basics: Understanding the FORECAST Syntax

Before you start making predictions, it's helpful to understand how the formula is structured. The good news is, it's simpler than it looks and only has three required parts (or arguments).

The syntax for the function is:

=FORECAST(x, data_y, data_x)

Let’s break down each piece:

• x: This is the specific point on the timeline for which you want to predict a value. For example, if you have data for months 1 through 6 and you want to predict the value for month 7, your x would be 7. This is the "what if" part of your forecast.
• data_y: This is your range of known dependent values - the numbers you want to forecast. In a sales report, this would be your column of historical sales figures (e.g., $5,000, $5,200, $5,500).
• data_x: This is your range of known independent values - the timeline or sequence that corresponds to your data_y. This could be a series of month numbers (1, 2, 3), years (2021, 2022, 2023), or specific dates.

A critical rule to remember is that the data_y and data_x ranges must be the same size. If you have six months of sales data, you need six corresponding month numbers or dates. An imbalance here is the number one reason the formula returns an error.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Putting It Into Practice: A Step-by-Step Example

Let's walk through a common, practical example: forecasting quarterly sales for a small online store. Imagine you have sales data from January to June and want to predict sales for July, August, and September.

Step 1: Organize Your Historical Data

First, set up a clean table in Google Sheets. It's a best practice to use numbers for your timeline (the data_x range) because linear regression works directly with numerical values. Instead of typing "January" and "February", use a "Month Number" column.

Your data might look something like this:

• Column A (Month Number): 1, 2, 3, 4, 5, 6
• Column B (Sales): $12,500, $13,100, $12,900, $13,800, $14,200, $14,800

Now, add the month numbers for the period you want to forecast. In this case, add 7, 8, and 9 to Column A for July, August, and September.

Step 2: Write Your FORECAST Formula

Now you're ready to build the formula. Click on the cell next to your first future month number (in this example, C8, next to month 7).

Based on our syntax and data, here's the information we need:

• x: The future month number we are solving for, which is in cell A8 (the number 7).
• data_y: Our known historical sales figures, which are in the range B2:B7.
• data_x: Our known historical month numbers, which are in the range A2:A7.

So, the formula for July's forecast is:

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

When you press Enter, Google Sheets will run the calculation and return a forecasted sales value for July.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Interpret and Extend Your Forecast

The formula produced a result, but we're not done yet. We also want to forecast for August and September. You might be tempted to just drag the fill handle down from cell C8, but you’ll run into an issue. Google Sheets will automatically adjust your ranges, breaking the formula.

To fix this, you need to lock the historical data ranges using absolute references (dollar signs). By putting a $ before the column letter and row number, you tell Sheets to always use that exact range, even when you copy the formula elsewhere.

The revised, draggable formula is:

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

Now, enter this formula in cell C8, press Enter, and then click and drag the small blue square (the fill handle) down to cover the cells for August and September. Google Sheets will correctly forecast the values for months 8 and 9 because the historical data ranges are locked, while the x value correctly shifts to A9 and A10.

Seeing is Believing: Visualizing Your Forecast

A list of numbers is useful, but a chart makes your forecast much easier for everyone to understand. Creating a visual representation of your data and the forecast allows you to see the trend at a glance.

How to Add a Trendline to Your Google Sheets Chart

Highlight all your data - both the historical and the forecasted values (in our example, a range like A1:C10 which includes your headers). Then follow these steps:

1. Navigate to Insert > Chart in the top menu. Google Sheets will likely suggest a line chart, which is perfect for this.
2. In the Chart editor sidebar that appears on the right, go to the Customize tab.
3. Click on the Series dropdown section.
4. Scroll down and check the box next to Trendline. This will overlay a dotted linear trendline on your historical data points.

You’ll immediately see how your forecasted values are an extension of this trendline. It’s a great way to confirm that your logic is sound and to present your findings in a clear, compelling way.

Tips and Common Pitfalls to Avoid

The FORECAST function is powerful, but it's important to be aware of its limitations and common errors to get the most accurate results.

• Mismatched Data Ranges: The most frequent error is having a different number of cells in your data_y and data_x ranges. If you see a #N/A error, double-check that your ranges are perfectly aligned.
• Relying on Non-Linear Data: The FORECAST function assumes your data follows a straight line. If your business has strong seasonality (e.g., a huge sales spike every December followed by a dip in January), a linear forecast will be misleading. It will average out those peaks and valleys, not predict them. For those cases, an exponential function like GROWTH may be more appropriate, or you may need more advanced forecasting methods.
• Not Enough Historical Data Points: Forecasting from just two or three data points is unreliable. The trend line is easily skewed by a single outlier. As a general rule, the more high-quality historical data you can provide, the more reliable your forecast will be.
• Using Text Instead of Numbers: The function works best when your data_x (the timeline) consists of numeric values. While it can sometimes interpret dates, it's more reliable to use clear numerical series like 1, 2, 3... or proper date values recognized by Sheets.

Ready for More? Advanced Forecasting Functions

Once you've mastered the basic FORECAST function, you might want to know about a few related functions that can add more nuance to your analysis.

Graphed

Still Building Reports Manually?

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

Watch the Demo

FORECAST.LINEAR

This function is the modern replacement for FORECAST in both Excel and Google Sheets. In Google Sheets today, they work a bit differently where the FORECAST.LINEAR function does not actually have much functionality and may give inaccurate results. Because of that, it may be safest to stick to =FORECAST for now but also verify your prediction is accurate.

GROWTH Function

This function is designed to forecast exponential trends. Instead of fitting a straight line to your data, it fits a curved, exponential growth line. This is ideal for modeling scenarios like compounding interest, viral social media growth, or user adoption for a fast-growing startup, where the rate of growth increases over time.

TREND Function

While FORECAST predicts one value at a time, the TREND function can output an array of values along a linear trendline. It’s useful for calculating multiple forecast points at once, smoothing out existing data, or filling in missing values within your historical data set.

Final Thoughts

Using the FORECAST function in Google Sheets can transform your data from a simple record of the past into a powerful tool for planning the future. By organizing your data, applying the formula correctly, and visualizing the outcome, you can generate valuable insights with just a few clicks.

While manual forecasting in spreadsheets is powerful, it can consume a lot of time once you start pulling in data from multiple sources like Google Analytics, Shopify, or your CRM. At some point, you end up spending more time exporting CSVs and updating formulas than acting on them. That's why we created Graphed. We connect directly to all your marketing and sales accounts, allowing you to create live, self-updating dashboards and get forecasts just by describing what you need in plain English. This automates the busy work and lets you focus on what really moves the needle.