How to Calculate Forecast Error in Excel

Building a sales or demand forecast is one thing, but knowing whether it’s any good is another. Forecast error tells you exactly how close your predictions are to reality, and calculating it in Excel is a straightforward way to start measuring and improving your accuracy. This tutorial will walk you through setting up your data and calculating the most common forecast error metrics step-by-step.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What is Forecast Error and Why Does It Matter?

Forecast error is simply the difference between the forecasted value and the actual value that occurred. A small error means your forecast was close to the mark, a large error means your prediction was way off. It's a simple concept, but tracking it consistently is fundamental for any data-driven business.

Why should you care about this number? Because it lets you:

• Quantify Accuracy: Stop guessing if your forecasts are "good enough" and start putting a number to it. This allows you to set accuracy targets and track performance over time.
• Improve Future Forecasts: By analyzing your errors, you can identify patterns. Are you consistently over-forecasting in a certain season? Or under-forecasting for a specific product line? Understanding where you go wrong is the first step to getting it right.
• Make Better Business Decisions: Accurate forecasts directly impact inventory management, staffing levels, marketing budgets, and cash flow. If you know your forecast has an average error of +/-10%, you can build that buffer into your plans, avoiding stockouts or overspending.

Setting Up Your Data in Excel

Before you can calculate anything, you need to organize your data properly. A clean setup will make the formulas much easier to manage. All you need are three columns:

• Column A: The time period (e.g., Day 1, Week 1, Jan, Feb, etc.).
• Column B: The actual values (e.g., 'Actual Sales').
• Column C: The forecasted values (e.g., 'Forecasted Sales').

Your spreadsheet should look something like this:

For this tutorial, we will assume your data starts in row 2, with headers in row 1.

Calculating Key Forecast Error Metrics in Excel

Once your data is set up, you can start calculating the different metrics. We’ll add a few helper columns to our spreadsheet to make the process clear.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 1: Calculate the Individual Forecast Error

First, we need to find the error for each individual period. This is the foundation for all the other metrics. A positive error means you under-forecasted (the actual was higher), while a negative error means you over-forecasted (the actual was lower).

In column D, label the header Error. In cell D2, enter the following formula:

=B2-C2

Click on the small square at the bottom-right corner of cell D2 and drag it down to apply the formula to all your rows. Now you have the raw error for each month.

Step 2: Calculate Mean Absolute Error (MAE)

The Mean Absolute Error (MAE) tells you the average magnitude of the errors in your forecast, ignoring their direction. To put it simply, it answers the question: "On average, how far off was our forecast?" It’s one of the most straightforward and widely used metrics.

How to Calculate MAE:

1. Calculate the Absolute Error: Since MAE ignores whether an error was positive or negative, we need to find the "absolute value" of each error. In Column E, label the header Absolute Error. In cell E2, use the ABS formula:

=ABS(D2)

Drag this formula down for all your rows.

1. Find the Average: Now, simply calculate the average of your Absolute Error column. Pick a cell below your data (e.g., E14) and enter:

=AVERAGE(E2:E13)

(Adjust the cell range E2:E13 to match your data.)

The resulting value is your MAE. If your MAE is 12, it means that on average, your forecast was off by 12 units (e.g., 12 sales, $12, etc.).

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Calculate Mean Squared Error (MSE)

The Mean Squared Error (MSE) is another way to measure accuracy, but it has a specific characteristic: it penalizes large errors much more severely than small ones. This is because it squares each error before averaging them. If being off by 20 is significantly worse than being off by 10 twice, MSE is a useful metric.

How to Calculate MSE:

1. Calculate the Squared Error: In Column F, label the header Squared Error. Here, you’ll square the individual error values from Column D. In cell F2, enter:

=D2^2

Drag the formula down to apply it to all your rows of data.

1. Find the Average: Next, find the average of the Squared Error column. In a cell below (e.g., F14), enter:

=AVERAGE(F2:F13)

(Again, adjust your cell range as needed.)

The main drawback of MSE is that the unit is squared (e.g., "dollars squared"), which doesn't make intuitive sense. That's why we often use its close relative, RMSE.

Step 4: Calculate Root Mean Squared Error (RMSE)

The Root Mean Squared Error (RMSE) is the square root of the MSE. It serves the same purpose as MSE—heavily penalizing large errors—but has the advantage of being expressed in the same units as your original data (e.g., dollars, not dollars squared). This makes it much easier to interpret.

How to Calculate RMSE:

1. First, you need to calculate the MSE as described in the previous step.
2. Take the Square Root: Once you have your MSE value (let's say it's in cell F14), you can calculate the RMSE. In a new cell (e.g., F15), use the SQRT formula:

=SQRT(F14)

The resulting number is your RMSE. Like MAE, it tells you the typical size of an error in your forecast, but with more weight given to the bigger misses.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 5: Calculate Mean Absolute Percentage Error (MAPE)

The Mean Absolute Percentage Error (MAPE) expresses the average error as a percentage of the actual values. This is incredibly useful for communicating accuracy to stakeholders or for comparing the forecast accuracy of different products with very different sales volumes (e.g., forecasting for a $10 pen versus a $1,000 office chair).

How to Calculate MAPE:

1. Calculate the Percentage Error: In Column G, label the header Absolute Percentage Error. In this formula, you will divide the absolute error by the actual value. We’ll do this in one step by referencing Columns E and B. In cell G2, enter:

=E2/B2

Drag this formula down for all rows.

1. Format as a Percentage: Select your "Absolute Percentage Error" column, right-click, and select 'Format Cells'. Choose 'Percentage' to display the values correctly.
2. Calculate the Average: Finally, find the average of that column. In a cell below your data (e.g., G14), enter:

=AVERAGE(G2:G13)

Your result is the MAPE. A MAPE of 15% means your forecast is, on average, off by 15%.

Note: MAPE cannot be calculated if any actual values are zero, as this leads to division by zero, and may produce extreme values with very low actuals.

Choosing the Right Error Metric for You

You have four different metrics, so which one should you use?

• Choose MAE if you want a simple, easy-to-understand metric that represents the average error in your forecast's units.
• Choose RMSE if large forecasting errors are particularly damaging to your business. This metric will highlight them more than MAE.
• Choose MAPE if you need to report accuracy as a percentage or compare forecasts for items with different scales.

Final Thoughts

Calculating forecast error gives you a powerful tool for validating and refining your predictions. By walking through these steps using metrics like MAE, RMSE, and MAPE, you can leave guesswork behind and start quantifying the accuracy of your efforts right inside Excel.

As you connect data from various sources - like your Shopify sales, website traffic from Google Analytics, and ad spend from Facebook ads - these manual Excel calculations can become a weekly chore. At Graphed, we automate this process by unifying your data sources into one place. You can simply ask questions in plain English, like "compare my monthly sales forecast versus actuals" and get back an interactive, real-time dashboard, freeing you from endless formula-building and letting you focus on the insights.