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.
- Open the Data tab and select Data Analysis.
- Choose Moving Average and select OK.
- Set the Input Range to the cells containing your observations. Include the header only if you select Labels in First Row.
- Enter the interval, such as 3 for a three-observation average, and choose an Output Range or a new worksheet.
- 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
- Audit Checklist Excel: Build and Use a Reliable Template
- Audit an Excel Sheet: A Practical Review Checklist
- Attendance Tracker Excel Template: Free Setup Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query VLOOKUP: How to Match Tables in Excel InsteadPower Query does not have a VLOOKUP function; use Merge Queries to match rows from two tables and…
- Excel NOW Function Not Updating? Causes and FixesExcel’s NOW() function does update, but it does not run like a live clock: it returns the current…
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
- Midpoint of Two Dates in Excel: Formula and ExamplesTo find the midpoint of two dates in Excel, enter the start date in A2 and the end…
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- Excel Column Addition Formula: Sum a ColumnTo add every number in an Excel column, use =SUM(B:B), replacing B with the column you want to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.