How to Forecast Sales in Excel

Building a reliable sales forecast is one of the most powerful things you can do for your business, but it often feels locked behind expensive software or complex statistical models. The good news is you already have an incredibly capable tool for the job: Microsoft Excel. This article will walk you through several practical methods to forecast your sales in Excel, from simple techniques for steady growth to more advanced functions that account for seasonality.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

First, Get Your Data in Order

Before you can forecast the future, you need to understand the past. The foundation of any good sales forecast is clean, organized historical data. Without it, you're just guessing.

What Data Do You Need?

The most crucial piece of data is your past sales performance over a consistent time interval. This could be daily, weekly, monthly, or quarterly. For most businesses, monthly sales data provides a good balance between detail and clarity. You'll need at least a year's worth of data to spot any meaningful trends or seasonal patterns, but two or three years is even better.

You'll want to gather either:

• Revenue: Total dollar amount of sales per period.
• Units Sold: Total number of products sold per period.

How to Structure Your Data in Excel

Keep it simple. All you need are two columns to get started. Don't worry about cramming everything into one sheet. Create a new, clean worksheet specifically for your forecast.

1. Column A: Your time period. This should be a date format that Excel recognizes (e.g., 1/1/2023, 1/2/2023, etc., for daily, or Jan-23, Feb-23, etc., for monthly).
2. Column B: Your sales data (revenue or units sold) corresponding to each time period.

Your setup should look something like this:

Pro Tip: Once your data is entered, select your two columns and click Insert > Chart > Line Chart. Visualizing your data right away helps you spot obvious trends, major outliers, or repeating patterns before you even start forecasting. Is the line generally trending up? Does it spike every summer? This visual check gives you a feel for which forecasting method might work best.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: Moving Average for Short-Term Trends

The moving average is one of the simplest and most effective ways to forecast sales. It works by smoothing out short-term fluctuations in your sales data to reveal the underlying trend. This method is perfect for businesses with relatively stable sales that don't have dramatic seasonal swings.

How a Moving Average Works

It calculates the average of your sales data over a specific number of recent periods - for example, the last three months. This "average" then becomes the forecast for the next period. As you get a new month of sales data, the oldest month is dropped from the calculation, so the average is always "moving" forward.

Step-by-Step Instructions:

Let’s say you have monthly sales data for the last year and want to create a 3-month moving average forecast.

1. Set up your data as described above, with dates in Column A and sales figures in Column B.
2. Click into cell C4 (the first period where you can have a 3-month average from Jan, Feb, and Mar).
3. Type in the following formula:

=AVERAGE(B2:B4)

This formula calculates the average sales of the cells B2, B3, and B4. This result is your forecast for April.

1. Press Enter. Now, click back on cell C4, grab the small square on the bottom-right corner (the fill handle), and drag it down to the end of your data. Excel will automatically adjust the formula for each row, always calculating the average of the previous three months.
2. To forecast the next month (after your historical data ends), simply drag the formula down one more row.

Method 2: Use Linear Regression with the FORECAST.LINEAR Function

If your sales data shows a consistent trend (steadily increasing or decreasing over time), then a linear regression forecast is a more powerful option. This method finds the best-fitting straight line through your historical sales data and extends that line into the future.

Don’t let the term "linear regression" scare you. Excel has a built-in function that does all the heavy statistical lifting for you: FORECAST.LINEAR.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

When to Use FORECAST.LINEAR

This method is ideal when your sales chart looks like it's following a relatively straight path, whether it's going up or down. For example, a new subscription business that’s been adding a similar number of customers each month would be a great fit.

Step-by-Step Instructions:

Let's use a 12-month sales history to forecast the 13th month.

1. Have your data ready: Dates in cells A2:A13 and sales in cells B2:B13.
2. First, make sure your dates are formatted as actual dates that Excel can recognize numerically. The date "1/1/2024" is a number to Excel, but the text "January 2024" is not unless formatted properly.
3. In a new cell where you want your forecast to appear (let's use C14), you'll enter the formula. The date for the period you want to forecast should be in A14 (e.g., the date representing your 13th month).
4. Click into C14 and type in the following formula:

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

Let's break down that formula:

• A14: The single date point you want to forecast for (the future).
• B2:B13: Your "known_y's" or your range of known historical sales data.
• A2:A13: Your "known_x's" or your range of historical dates that correspond to your sales data.

Visualizing Your Linear Forecast

The best way to see how well your forecast fits is to add a trendline to your line chart.

1. Create a line chart with your historical data (A2:B13).
2. Right-click on the sales data line within the chart.
3. Select Add Trendline... from the dropdown menu.
4. In the "Format Trendline" panel that appears, ensure "Linear" is selected.
5. Further down, you can check the box for "Display R-squared value on chart". This value (from 0 to 1) tells you how well the line fits your data. A value closer to 1 means a very strong fit.

Method 3: Forecasting with Seasonality Using FORECAST.ETS

For many businesses, sales aren't a simple straight line. They have predictable peaks and troughs throughout the year. Ice cream sales boom in the summer, retail sales spike during the holidays, and tax services are busiest once a year. If this sounds like your business, Excel's FORECAST.ETS function is your best friend.

What is FORECAST.ETS?

ETS stands for "Exponential Triple Smoothing." This sophisticated algorithm is built to analyze data that has both a general trend (like growing 10% year-over-year) and a seasonal pattern (like demand being 30% higher every December).

You need at least two full seasonal cycles of data (e.g., two years of monthly data) for this function to work effectively.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

How to Use FORECAST.ETS for a Seasonal Business

Imagine you run an online store that sells garden supplies, and your data runs from January 2022 to December 2023. You want to forecast sales for January 2024.

1. Your worksheet has dates in A2:A25 and sales data in B2:B25. The date you want to forecast for (Jan-24) is in cell A26.
2. In cell C26, enter the following formula:

=FORECAST.ETS(A26, B2:B25, A2:A25, 12)

Breaking down this formula:

• A26: The future date you're predicting.
• B2:B25: The range of your historical sales data.
• A2:A25: The range of your historical dates.
• 12: This is the seasonality parameter. We put "12" because our seasonal cycle is 12 months long. If you had quarterly data, you'd use "4." If you had weekly data and saw an annual pattern, you'd use "52." You can also leave this blank and let Excel detect it automatically.

You can drag this formula down to forecast February, March, and beyond, and Excel will create a forecast that respects both the overall growth trend and the seasonal patterns it detected in your past data.

Final Thoughts

Building an accurate sales forecast doesn't require a degree in statistics or expensive, specialized software. With clean historical data and the right built-in formulas, you can create powerful, insightful forecasts directly within Excel. By choosing the right method - whether it's a simple moving average, a trend-based linear forecast, or a seasonality-aware model - you can transform your historical data into a strategic business asset.

While mastering these functions in Excel is a fantastic skill, the initial drudgery of hunting down data from Shopify, Google Analytics, your CRM, and ad platforms can be a major time sink. We created Graphed to solve this exact problem. By connecting your tools to our platform just once, you get a unified, real-time view of your performance. You can then use plain English to ask for forecasts, trends, and sales dashboards, letting AI handle the data wrangling so you can focus on making decisions, not updating spreadsheets.