How to Forecast in Google Sheets

Trying to predict next quarter's revenue or future website traffic can feel like gazing into a crystal ball. But you don't need psychic powers - you just need your historical data and a Google Sheet. This article will show you how to use simple, built-in Google Sheets functions to create reliable forecasts for your business.

First, Let's Talk About Forecasting

Forecasting is simply the practice of using past data to make informed predictions about the future. For example, by looking at your monthly sales from the last year, you can make a reasonable estimate of what your sales will look like for the next few months. It's a fundamental part of financial planning, inventory management, and marketing strategy.

There are many complex forecasting models out there, but for a huge number of business scenarios, two simple methods available in Google Sheets are more than enough to get started:

• Linear Forecasting: Assumes that the value you are predicting will continue to grow (or shrink) at a steady, consistent rate. Think of it as a straight line moving up and to the right. This is great for mature businesses with stable growth patterns.
• Exponential Forecasting: Assumes that your value will grow at an accelerating rate. The growth itself compounds over time. This is more common for startups, product launches, or campaigns that are just starting to scale.

Step 1: Prep Your Data for Forecasting

Before you can use any formulas, your data needs to be organized properly. Your spreadsheet can’t make sense of messy, unstructured information. The most important rule is the "garbage in, garbage out" principle - clean source data leads to a reliable forecast.

You need two clear columns of data:

• The Independent Variable (Column A): This represents your time period. It must be a numerical series, like months (1, 2, 3), quarters (1, 2, 3, 4), or even a sequential list of dates. This is your "known_data_x."
• The Dependent Variable (Column B): This contains the historical data points you're analyzing, such as sales figures, user sign-ups, or website sessions. This is your value, or "known_data_y."

Here’s what a clean dataset for forecasting monthly sales revenue might look like:

Let's say this is your sheet for the year 2023. Month 1 is January, Month 2 is February, and so on. Putting "1", "2", "3" instead of "January", "February", "March" is important because the forecasting formulas work best with numeric values for both the x and y axes.

You’ll also want to add the future time periods that you want to forecast. In this example, if we want to predict sales for the first quarter of 2024, we would add Months 13, 14, and 15 to Column A, leaving their corresponding cells in Column B blank for now.

Step 2: Linear Forecasting with the FORECAST Function

If your business growth has been relatively stable and consistent, a linear forecast is your best starting point. The FORECAST function calculates a future value along a linear trend line fitted to your historical data.

The syntax for the function is:

=FORECAST(x, data_y, data_x)

• x: The single new data point (time period) for which you want to predict a value. For example, 13 for the 13th month.
• data_y: The range containing your known historical values (your sales data). In our example, this would be B2:B13.
• data_x: The range containing your known time periods (months 1-12). In our example, this would be A2:A13.

How to Use It:

To predict the sales for Month 13, click on cell B14 and enter the following formula:

=FORECAST(A14, B2:B13, A2:A13)

Press Enter. Google Sheets will calculate the predicted sales value based on the straight-line trend of the previous 12 months. In our example data, this gives us a forecast of $15,103.03.

You can then drag the fill handle (the little blue square in the corner of the cell) down to cell B16 to automatically calculate the forecasts for months 14 and 15 as well. Google Sheets will intelligently adjust the 'x' value in the formula to reference cells A15 and A16.

Step 3: Exponential Forecasting with the GROWTH Function

What if your growth isn't a straight line? If you're running a new campaign or entering a growth phase, your numbers might be curving upwards. This is where the GROWTH function comes in handy, as it fits an exponential curve to your data.

The syntax is slightly different but follows the same logic:

=GROWTH(known_data_y, [known_data_x], [new_data_x])

• known_data_y: Your range of known historical values (B2:B13).
• known_data_x: Your range of known time periods (A2:A13). This is an optional argument but highly recommended for accuracy.
• new_data_x: Your range of future time periods you want to predict (A14:A16).

How to Use It:

The GROWTH function is best used as an array formula, meaning it will output all of your forecasted values at once. To use it:

1. Clear any values you had in your forecast cells (B14:B16).
2. Click on cell B14.
3. Enter the formula:

=GROWTH(B2:B13, A2:A13, A14:A16)

After pressing Enter, Google Sheets will automatically populate the forecasts for all three future months (13, 14, and 15), calculating the values along an exponential growth curve.

Notice that the GROWTH function in our example predicts higher values than the FORECAST function. This is because it assumes growth is accelerating based on the historical trend, whereas FORECAST assumes steady, additive growth.

Step 4: Visualize Your Forecast with a Chart

The numbers are helpful, but a visual chart makes the trend instantly clear to anyone who looks at it. A line chart is perfect for this.

1. Select all your data: Click and drag to highlight all three columns, including headers and both historical and forecasted data (A1:B16).
2. Insert Chart: Navigate to Insert > Chart from the top menu. Google Sheets will likely recommend a Line Chart by default, which is what we want.
3. Distinguish Forecast from History: The chart will show a single continuous line. To make it clearer, we can style the forecasted section differently.

This chart now tells a clear story: here's what happened, and here's what we project will happen based on that trend.

Best Practices and Other Considerations

While these functions are powerful, keep these tips in mind to improve your accuracy:

• Seasonality: The simple FORECAST and GROWTH models don't account for seasonality (e.g., higher sales during Q4 holidays, lower traffic during the summer). If your business is highly seasonal, consider breaking your forecast down into smaller periods or using more advanced techniques like a moving average to smooth out the data.
• The Trendline Tool: For a super quick visual forecast without formulas, you can insert a chart with just your historical data. Then, in the Chart editor > Customize > Series menu, scroll down and check the box for Trendline. This will superimpose a trendline over your data, and you can even choose between Linear and Exponential types.
• Forecast vs. Actuals: A forecast is a guide, not a guarantee. As new data comes in each month, create a new column called "Actuals" next to your "Forecasted" column. Comparing the two will show you how accurate your model is and help you refine it over time.

Final Thoughts

Forecasting in Google Sheets doesn't have to be intimidating. By setting up your data correctly, you can use built-in functions like FORECAST and GROWTH to transform historical performance into a powerful guide for future decisions. Visualizing this data with charts makes it easy to communicate your direction and align your team.

Of course, the first step is always getting the right data into that spreadsheet. For many marketing and sales teams, that involves hours of downloading CSVs and manually copying data from a dozen different platforms. We built Graphed to remove that friction completely. Instead of building spreadsheet models, you connect your tools like Google Analytics, Shopify, or Salesforce once. Then, you can simply ask, "forecast our revenue for the next 3 months based on last year's trend," and get an automated, real-time dashboard instantly. This gives you back the time to act on your forecast instead of just building it.