Calculating Moving Average in Excel: Formulas and Tips

Formulas & functions · Updated November 21, 2026

To calculate a moving average in Excel, average a fixed-size group of consecutive values, then copy the formula down one row at a time. For example, with observations in B2:B20, a three-period moving average starting in C4 is =AVERAGE(B2:B4); filling that formula down updates the range to B3:B5, B4:B6, and so on.

What a moving average shows

A moving average replaces each point in a series with the average of a chosen number of nearby observations. It smooths short-term ups and downs, making it easier to see the broader direction of sales, website visits, production, prices, or other time-based data. A three-period average, for instance, combines three consecutive observations for each result.

In most spreadsheet contexts, “moving average” and “rolling average” mean the same thing: the calculation window advances as you move through the data. A simple moving average gives every observation in that window equal weight. It is a descriptive smoothing measure, not automatically a forecast or a promise about what the next period will be.

For a conventional trailing average, each result uses the current observation and the observations before it. That makes the value available as soon as the current period is recorded. A centered average instead uses observations both before and after the point being smoothed. Centered averages can be useful for examining historical patterns, but they cannot be calculated for the latest periods until later observations exist.

Calculate a basic moving average with a formula

Suppose column A contains monthly dates and column B contains monthly sales, with the first month in row 2. To calculate a three-month trailing average, put the result beside the third month, in C4. With dates in A2:A6 and sales of 120, 150, 90, 180, and 210 in B2:B6, enter this in C4:

With dates in A2:A6 and sales in B2:B6, enter the first three-month average in C4: =AVERAGE(B2:B4). The result is 120, because (120 + 150 + 90) / 3 = 120.

This is a trailing average for April if the five values represent January through May: it uses January through March in the row aligned with March, then the window advances for each subsequent result. The key is that each range contains the same number of adjacent observations and shifts forward by one row.

For the next two rows in this example, the ranges are B3:B5 and B4:B6. Their averages are 140 and 160: (150 + 90 + 180) / 3 = 140, and (90 + 180 + 210) / 3 = 160. Those values let you check that the window has moved one row each time rather than accidentally reusing the original three cells.

Fill the formula down and check the alignment

After entering the first formula, select C4 and drag the small fill handle at the lower-right corner of the cell down the column. Excel adjusts ordinary cell references as the formula moves: the range in the next row becomes B3:B5. You can also copy C4, select the cells below it, and paste. The formula should stop where there are not enough observations to complete another full window.

Keep the result aligned with the final observation in its window when you want a trailing average. In the example, the value based on B2:B4 belongs beside the third observation, in row 4, not row 2. Label the result column clearly, such as “3-month moving average,” so readers know both the measure and its window size.

If your data has a header in row 1, start the formula from the first actual observation, not the header. If you insert a row or change the data range, verify that the formula still covers the intended periods. A quick check is to inspect the first, second, and last moving-average formulas and confirm that each range has the same number of cells.

Use the Analysis ToolPak instead

Excel for Microsoft 365 on Windows also offers a Moving Average command in the Analysis ToolPak. It can be handy when you want Excel to generate a separate output range without manually filling a formula. If the command is missing, enable the add-in through File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, choose Go, and select Analysis ToolPak.

  1. Open the Data tab and select Data Analysis.
  2. Choose Moving Average and select OK.
  3. Set the Input Range to the cells containing your observations. Include the header only if you select Labels in First Row.
  4. Enter the interval, such as 3 for a three-observation average, and choose an Output Range or a new worksheet.
  5. Select OK, then inspect the output and its alignment before using it in a chart or report.

The first interval minus one positions do not have enough prior data for a complete trailing average, so the output may show #N/A for those positions. The ToolPak is not available in Excel for the web. On Excel for Mac, add-ins are managed through Tools > Excel Add-ins; if the Analysis ToolPak is available and enabled, use Data > Data Analysis. The ordinary AVERAGE formula works across these Excel versions and is often easier to audit.

Choose a window that fits your data

The window is the number of observations included in each calculation. A shorter window reacts more quickly to changes but leaves more short-term variation visible. A longer window smooths the series more strongly, but it can conceal a recent shift or make turning points appear later than they happened.

  • Use a short window when recent changes matter and the underlying measure can move quickly.
  • Use a longer window when the data is noisy and the goal is to reveal a broad pattern.
  • Match the unit to the question: a 7-observation average is a week only if each row represents one day with no missing days.
  • Try more than one reasonable window and compare the results, rather than choosing a size simply because it produces a smooth-looking chart.

For monthly seasonal data, a 12-month window can smooth a full annual cycle, but it also responds slowly to changes. For daily data, a 5-observation average represents five recorded rows, not necessarily five calendar days. If weekends, holidays, or missing dates matter, decide whether you want an average of observations or an average over a defined period of time.

Handle blanks, zeros, and missing periods

Excel’s AVERAGE function ignores blank cells and text values in a referenced range, but it includes numeric zeros. This difference can substantially change a result. A zero may mean that sales were genuinely zero; a blank may mean that the value was not recorded. Decide what each represents before calculating an average, and do not replace missing values with zero unless zero is the correct observation.

AVERAGE also returns an average of the available numeric values if some cells inside the range are blank. That means a nominal three-row window might be based on only two numbers. If every result must use three observations, check the source data for gaps or add a separate completeness check. Likewise, if rows are not evenly spaced in time, the formula still averages adjacent rows; it does not account for the length of the gaps between dates.

Sort the records chronologically before filling a trailing average. Duplicate dates, out-of-order rows, or text stored in place of numbers can make a technically valid formula answer the wrong question. Filtering a list does not change the cells included in an ordinary range-based AVERAGE formula: hidden rows are still part of its calculation. If that is not intended, use a method designed to include only visible records or create a clean analysis range.

Weighted and exponential moving averages

A simple moving average gives all observations in its window equal influence. A weighted moving average gives selected observations different weights, often assigning more influence to recent values. Put the observations in B2:B4 and their corresponding weights in D2:D4; for values 150, 90, and 180 with weights 0.2, 0.3, and 0.5, use:

With observations in B2:B4 and matching weights in D2:D4, enter =SUMPRODUCT(B2:B4,D2:D4)/SUM(D2:D4). The result is 147: (150 × 0.2 + 90 × 0.3 + 180 × 0.5) / 1.0 = 147.

Keep each weight beside the observation it belongs to, and make sure the weights add to a positive total. In the example, the most recent observation receives the largest weight. A weighted calculation is useful only when the weighting rule has a reason behind it; it is not automatically more accurate than a simple average.

An exponential moving average is another common smoothing method. It gives more weight to recent observations while allowing older observations to continue influencing the result, with their influence declining over time. It is recursive: each new result depends on the current observation and the previous exponential average. Excel does not provide a worksheet function named “exponential moving average”; it can be built with a recurrence formula or calculated with another suitable tool, but it is different from the simple AVERAGE window described above.

Interpret the result and avoid common mistakes

A moving average smooths the series; it does not remove the original data or explain why values changed. Keep the source measurements visible and label the smoothed series separately. When graphing both, use a line chart and clear legend labels so a reader can distinguish actual observations from the calculated average.

A frequent mistake is calling a moving average a forecast. A trailing average describes the recent window and can be used as a simple baseline, but it does not model seasonality, growth, one-off events, or other causes. Another is choosing a period count without checking the data frequency. A 12-row window over weekly data covers 12 weeks, not 12 months.

Finally, do not assume that a smoother line is a better analysis. A large window can hide abrupt changes that are important for inventory, staffing, or service monitoring. Compare the moving average with the raw observations and state the window, frequency, and treatment of missing data whenever you share the result.

Frequently Asked Questions

What formula calculates a 3-month moving average in Excel?

With monthly values in B2:B4, enter =AVERAGE(B2:B4) in the cell beside the third month, then fill down. Each copied formula should use the current month and the two preceding observations.

How do I calculate a moving average in Excel without the ToolPak?

Use AVERAGE over the first complete window, then copy the formula down. For a three-observation window, start with the first three numeric cells; the range will shift as the formula is filled into subsequent rows.

Does Excel have a moving average function?

There is no worksheet function named MOVINGAVERAGE. Use AVERAGE with a shifting range, or use the Moving Average option in the Analysis ToolPak where that add-in is available.

Should a moving average include the current period?

A trailing moving average usually includes the current observation and earlier observations in its window. A centered average uses observations on both sides of a point and is mainly useful for historical smoothing, since future values are not yet known at the series endpoint.

Why is my moving average showing #N/A?

If you used the Analysis ToolPak, the first few output positions may show #N/A because there are not yet enough observations for the selected interval. Check that the interval is correct and interpret only results based on a complete window.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.