How to Forecast in Excel

Predicting future sales, revenue, or customer demand is essential for smart business planning, and you don't need a complex statistical software suite to do it. Microsoft Excel is an incredibly powerful tool for creating reliable forecasts right from your spreadsheet. This article will walk you through four effective methods for forecasting in Excel, from simple formulas to automated tools.

First, Let's Prep Your Data for Forecasting

Before you can predict the future, you need a clear picture of the past. All Excel forecasting methods rely on historical, time-series data. This simply means you need two columns:

• A Timeline Column: This contains your time periods (e.g., dates, months, years, or quarters) in sequential order.
• A Values Column: This contains the historical data points you want to forecast (e.g., Sales, Website Sessions, Units Sold).

For best results, make sure your data is clean and consistent. Check for missing values or strange outliers before you begin. Having at least 12-15 historical data points is a good starting point for identifying a meaningful trend.

Example Data Set: Let's say we want to forecast sales for the next quarter based on the last 12 months. Our data would look something like this:

Method 1: The FORECAST.LINEAR Function for Simple Predictions

If your historical data shows a relatively straight-line trend, linear regression is a great place to start. The FORECAST.LINEAR function projects a future value by essentially drawing a straight "line of best fit" through your existing data points and extending it.

Step-by-Step Guide for FORECAST.LINEAR:

Imagine our sales data is in cells B2:B13, and the corresponding months (represented as numbers 1-12) are in A2:A13. We want to forecast sales for month 13 (January of next year).

1. Click on the cell where you want your forecast to appear (e.g., cell B14).
2. Type the formula. The syntax is: =FORECAST.LINEAR(x, known_y's, known_x's)
3. Breakdown of the formula parts:
4. Our final formula would be: =FORECAST.LINEAR(A14, B2:B13, A2:A13)

When you press Enter, Excel will calculate the forecasted sales for month 13 based on the linear trend of the previous 12 months. You can then drag this formula down to forecast months 14, 15, and so on.

When to use it: This method is best for simple data sets that show a consistent upward or downward trend without any significant seasonal fluctuations.

Method 2: Visual Forecasting with Chart Trendlines

Sometimes, the best way to understand a trend is to see it visually. Excel's chart trendlines allow you to do just that, projecting future performance right on a graph.

Step-by-Step Guide for Adding a Trendline:

1. Select Your Data: Highlight your historical data, including both the timeline and values columns (A1:B13 in our example).
2. Insert a Chart: Go to the Insert tab and choose a Scatter chart or Line chart. A line chart is often more intuitive for time-series data.
3. Add the Trendline: Right-click on the data line inside your chart and select "Add Trendline..." from the context menu.
4. Configure the Forecast: A "Format Trendline" pane will appear on the right side of your screen.

You'll now have a visual representation of your forecast, helping you and your team quickly grasp the expected trajectory.

When to use it: Perfect for presentations and reports where a visual forecast is more impactful than a table of numbers. It's also great for quickly testing different trend types (linear, exponential, etc.) to see which one best fits your data.

Method 3: Forecasting with Seasonality Using FORECAST.ETS

What if your business has predictable seasonal patterns? Think retail sales peaking in Q4 or an ice cream shop's revenue climbing in the summer. A simple linear forecast won't capture these cycles. For this, you need a more sophisticated method like Exponential Triple Smoothing (ETS), made easy with Excel's FORECAST.ETS function.

This function weighs recent data more heavily and can automatically detect seasonal cycles to create a much more accurate forecast.

Step-by-Step Guide for FORECAST.ETS:

Let's use a dataset with three years of quarterly sales. Here, the timeline needs to be actual dates.

1. Click on the cell where you want your forecast to appear.
2. Type the formula. The syntax is: =FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
3. Breakdown of the key parts:
4. For our quarterly data, the formula to predict the next quarter's sales would be: =FORECAST.ETS(A14, B2:B13, A2:A13, 4)

The result will be a forecast that accounts for both the overall trend and the recurring quarterly patterns observed in your historical data.

When to use it: This is a powerful, go-to method for any dataset that exhibits seasonal or cyclical patterns. It's significantly more accurate than linear forecasting for most real-world business data.

Method 4: Let Excel Do All the Work with the "Forecast Sheet"

If you want a detailed forecast complete with confidence intervals without writing a single formula, Excel's "Forecast Sheet" feature is your best friend. This tool automatically analyzes your data, chooses the best ETS algorithm, and generates a new worksheet with a comprehensive forecast table and chart.

Step-by-Step Guide to Create a Forecast Sheet:

1. Organize & Select Data: Ensure your data is in two columns (timeline and values). Select the entire data range.
2. Click the Magic Button: Go to the Data tab in the ribbon. In the Forecast group, click on Forecast Sheet.
3. Customize Your Forecast: A dialog box will pop up showing you a preview of the forecast chart. Here you can tweak the settings:
4. Create the Sheet: Click "Create." Excel will instantly generate a new worksheet containing:

When to use it: This is the fastest and most comprehensive method for most forecasting needs. It's perfect when you need a detailed, presentation-ready output that accounts for seasonality and clearly shows potential best and worst-case scenarios with confidence intervals.

Final Thoughts

Mastering forecasting in Excel transforms a simple spreadsheet into a powerful tool for strategic planning. Whether you're using a straightforward function like FORECAST.LINEAR for basic trends or employing the robust Forecast Sheet for complex seasonal data, you have everything you need to make more informed, data-driven decisions for your business.

Running forecasts in Excel or Google Sheets is effective, but it often starts with hours of manual work - exporting CSVs from your various sales, marketing, and analytics platforms, then cleaning and stitching the data together before you can even begin. At Graphed, we automate that painful first step. You connect your data sources like Shopify, Google Analytics, and Facebook Ads once, and all your data is instantly available for analysis. Instead of building manual reports, you can just ask questions in plain English like, "create a dashboard forecasting our monthly revenue for the next 6 months," and get a live, auto-updating dashboard in seconds.