Actual vs Forecast in Excel: Compare Results

How-to basics · Updated November 19, 2026

Actual vs forecast in Excel means comparing what happened with what was expected. Put the forecast in one column and actual results in another, then calculate variance as actual minus forecast; with forecast in B2 and actual in C2, enter =C2-B2 in D2.

What “actual vs forecast” means

A forecast is an estimate of a future result, such as monthly sales, expenses, staffing hours, or units produced. Actuals are the recorded results for the same measure and period. Comparing the two helps show where performance differed from expectations and gives a starting point for investigating why.

This comparison is often called variance analysis. A variance is the difference between actual and forecast values. It is not automatically good or bad: the meaning depends on the measure. Revenue above forecast is usually favorable, while spending above forecast is usually unfavorable. For a measure such as customer response time, whether a higher value is better depends on what is being measured.

Actual-versus-forecast reporting is also different from comparing actuals with a budget. A budget is generally an approved plan or spending limit, while a forecast is an estimate that may be updated as conditions change. Organizations sometimes use the terms differently, so label the comparison clearly and state which forecast version is being used.

Set up the Excel worksheet

Use one row per period and keep the forecast and actual figures in separate columns. A simple layout for Microsoft Excel for Microsoft 365 on Windows is:

  • Column A: Month or other reporting period
  • Column B: Forecast
  • Column C: Actual
  • Column D: Variance
  • Column E: Variance percentage

For example, enter January, February, and March in A2:A4; the corresponding forecast values in B2:B4; and the actual values in C2:C4. Use the same units and time boundaries in both value columns. If the forecast is for calendar months but actuals cover four-week periods, the comparison can be misleading even when the arithmetic is correct.

Keep the source data numeric. A cell containing the text “$10,000” may not behave like a numeric value formatted as currency. Enter 10000 and apply a number format using Home > Number, or choose a suitable currency format from the Number Format list. If data comes from another system, check that dates, decimal separators, and units were imported consistently.

Calculate variance and variance percentage

For the layout above, with forecast in B2 and actual in C2, put the following in D2 to calculate the dollar or unit difference:

=C2-B2

With a forecast of 10,000 and actual results of 10,800, the result is 800. A positive answer means actual is above forecast; a negative answer means it is below forecast. Copy the formula down for the other periods. The formula adjusts by row, so D3 compares C3 with B3.

To express the variance as a percentage of forecast, divide the variance by the forecast. With D2 holding the variance and B2 holding the forecast, enter this in E2:

=IFERROR(D2/B2,0)

For a variance of 800 against a forecast of 10,000, the result is 0.08, or 8% after you format the cell as a percentage. Select the percentage cells and choose Home > Number > Percent Style. Increase or decrease the displayed decimal places if the report needs more precision.

The percentage formula uses forecast as its denominator, which is a common convention for variance reporting. If the forecast is zero, the percentage is undefined; the formula above displays 0 because IFERROR replaces the division error. That may look like a real zero variance, so a clearer option for reporting is to label the case “N/A.” With forecast in B2 and variance in D2, use:

=IF(B2=0,"N/A",D2/B2)

This returns N/A when the forecast is zero and otherwise returns the variance ratio. Since the result can be text or a number, apply percentage formatting to the relevant numeric cells and make sure readers understand what N/A means.

Worked example with monthly results

Suppose a team forecasts sales of 10,000 in January, 12,000 in February, and 15,000 in March. Actual sales are 10,800, 11,400, and 16,500. Applying the formulas gives:

  • January: variance 800; variance percentage 8%.
  • February: variance -600; variance percentage -5%.
  • March: variance 1,500; variance percentage 10%.

To calculate a total, add the period values rather than adding or averaging the displayed percentages. With forecast values in B2:B4, enter this in B5:

=SUM(B2:B4)

The result is 37,000. With actual values in C2:C4, enter this in C5:

=SUM(C2:C4)

The result is 38,700. If D5 is the total variance and B5 is the total forecast, this formula calculates the overall percentage variance:

=IFERROR(D5/B5,0)

The total variance is 1,700, and the overall variance percentage is about 4.6%. It is not the simple average of 8%, -5%, and 10%, because the months have different forecast amounts. Dividing the total difference by the total forecast weights each month according to its share of the forecast.

Interpret the result in context

A variance identifies a difference; it does not explain the cause. A sales shortfall might reflect lower volume, a price change, a delayed order, or a forecast assumption that no longer holds. A spending overrun could be caused by higher prices, more activity than planned, or an expense recorded in a different period. The next step is to investigate the drivers, not to assume the formula reveals them.

Direction also depends on the metric. For revenue, actual minus forecast is often easiest to interpret: positive is above plan, negative is below. For costs, a positive difference means spending exceeded the forecast, which is commonly unfavorable. You can keep the arithmetic consistent and add a separate status or explanation column, rather than reversing the formula for selected rows without documenting the rule.

Consider whether the comparison is like-for-like. Check that actuals and forecasts use the same currency, accounting basis, product definitions, and period cutoffs. If figures are cumulative, compare the same number of days or months. A partial month’s actual sales should not usually be compared directly with a full-month forecast without identifying that the period is incomplete.

Make the comparison easier to scan

Number formatting makes a report easier to read. Use a consistent currency or unit format for forecast, actual, and variance columns, and use percentage formatting for the percentage column. Avoid showing excessive decimal places when the underlying data does not support that precision.

Conditional formatting can draw attention to large differences. Select the variance cells, then in Excel for Microsoft 365 on Windows choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than or Less Than. Set a threshold that matters for the business, not just an arbitrary value. For example, highlighting variances greater than 1,000 may be useful for a large sales line but irrelevant for a small one. Conditional formatting colors the result; it does not determine whether a variance is favorable.

A chart can show the trend as well as the gap. Select the period, forecast, and actual columns and choose Insert > Recommended Charts, then select a clustered column or line chart that suits the data. A clustered column chart puts forecast and actual side by side for each period; a line chart can make changes over time easier to see. Keep the scale and units clear, and avoid a chart that hides small but important differences.

Choose a useful level of detail

The right structure depends on the question the report should answer. A monthly summary is compact, but it may conceal differences among products, teams, or regions. A detailed table can help locate a source, yet too many rows can make a report difficult to review. A practical approach is to keep transaction or category detail in a data sheet and show a concise summary on a separate report sheet.

Choose a consistent reporting interval and level of detail. If the forecast is maintained by product and month, retaining those fields in the actuals makes it possible to compare each product in each month. If actuals are only available as a company-wide total, do not imply that product-level variances are measured accurately. Add explanatory columns such as owner, reason, or corrective action only when someone will maintain and use them.

Forecasts may be revised over time. To make a fair comparison, identify whether the report uses the original forecast, the latest forecast, or a forecast frozen at a specified date. Comparing actuals with a forecast that was updated after results became known can make the forecast appear more accurate than it was at the time. Keeping dated forecast snapshots preserves the context needed for later review.

Common problems and how to avoid them

  • Mixing up signs: State that variance equals actual minus forecast. This makes positive and negative values consistent, even if favorable status differs by metric.
  • Comparing different periods: Match dates and coverage, and flag partial periods rather than treating them as complete.
  • Using a misleading percentage: A zero forecast makes a percentage variance undefined. Display N/A or explain the chosen treatment instead of implying a meaningful 0%.
  • Taking an unweighted average: Calculate an overall rate from total variance divided by total forecast when that is the intended measure; do not assume an average of monthly rates gives the same answer.
  • Overwriting the forecast: Keep a dated copy or version of the forecast used for the comparison so later revisions do not erase the original benchmark.
  • Treating every difference as important: Set practical thresholds and investigate material variances. Small differences may be normal rounding or timing effects.

Frequently Asked Questions

What formula compares actual to forecast in Excel?
With forecast in B2 and actual in C2, subtract forecast from actual using =C2-B2. A positive result means actual is higher than forecast; the business meaning depends on the metric.

How do I calculate actual vs forecast percentage?
With variance in D2 and forecast in B2, use =IFERROR(D2/B2,0) and format the result as a percentage. If a zero forecast should be shown explicitly, use =IF(B2=0,"N/A",D2/B2) instead.

Should variance be actual minus forecast or forecast minus actual?
Either convention can be used, but actual minus forecast is common and makes positive values mean actual exceeded the estimate. State the convention in the report and consider whether above forecast is favorable for the measure.

How do I compare totals across several months?
Sum the forecast and actual columns separately, calculate total variance as total actual minus total forecast, and divide total variance by total forecast for the overall percentage. This avoids giving each month equal weight when monthly forecast sizes differ.

Can I compare actuals with a revised forecast?
Yes, but label which version you use. Comparing with the latest forecast answers a different question from comparing with the original forecast, so retain dated versions when you need to evaluate how accurate earlier estimates were.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.