Super Sale WeekClaude Skills — 20% OFF
Tips

How to Calculate a Moving Average in Excel: 4 Easy Ways in 2026

Powerdrill Bloom·
How to Calculate a Moving Average in Excel: 4 Easy Ways in 2026

To calculate a moving average in Excel, type =AVERAGE() over the first few values, such as =AVERAGE(B2:B4). Then drag the formula down the column. Each row then averages the latest three values. Excel's Analysis ToolPak and chart trendlines can also add one for you.

This guide covers four ways to do it: the AVERAGE formula, the Analysis ToolPak, a chart trendline, and a weighted average with SUMPRODUCT. It also walks through a full example and a faster way to smooth trends in large files. Details come from Microsoft's support pages, read on November 12, 2026.

What a moving average is

A moving average, also called a rolling average, is the average of the most recent few values in a series. As each new period arrives, the oldest value drops out and the newest one comes in. The window "moves" along the data.

Microsoft explains why that helps. It says "A moving average provides trend information that a simple average of all historical data would mask."

A plain average of a whole year flattens everything into one number. A three-month moving average shows whether the last quarter is rising or falling. It smooths out one-off spikes without hiding the direction.

Common uses include:

  • Sales trends. Smooth weekly sales to see the real direction.
  • Inventory planning. Microsoft lists sales, inventory, and other trends as uses for its Moving Average tool.
  • Website traffic. Remove day-of-week swings with a seven-day average.
  • Stock prices. Compare a short average with a long one.

The size of the window matters. A short window reacts fast but stays noisy. A long window is smooth but slow to show a turn.

Set up your data in Excel

A moving average needs values in time order, with no gaps. Put the period in one column and the value in the next.

Here is a small example of eight weeks of unit sales:

Week Sales
1 120
2 135
3 128
4 150
5 162
6 155
7 170
8 181

Put Week in column A and Sales in column B, with headers in row 1. The values then sit in rows 2 to 9.

Before you start, check three things:

  • Sort by date. Oldest first, newest last.
  • Fill gaps. A missing week will shift every average after it.
  • Use numbers. Microsoft notes that AVERAGE ignores text in a range, which can quietly change your result.

Way 1: Use the AVERAGE formula and drag it down

This is the simplest way, and it works in every version of Excel.

  1. Decide on a window, such as 3 weeks.
  2. In cell C4, type =AVERAGE(B2:B4).
  3. Press Enter.
  4. Drag the fill handle down to C9.

Excel shifts the range by one row each time. C5 becomes =AVERAGE(B3:B5), C6 becomes =AVERAGE(B4:B6), and so on.

The first two rows have no average, since there are not yet three values. Leave them blank, or label them so readers know why.

Microsoft defines AVERAGE simply. It "Returns the average (arithmetic mean) of the arguments." For the window to work, the cell references must stay relative, with no dollar signs.

This method is easy to audit. Anyone can click a cell and see exactly which values it averages.

To keep the column growing on its own, format the data as an Excel table with Ctrl+T. When you add week 9 in a new row, the table copies the formula down for you.

Way 2: Use the Analysis ToolPak Moving Average tool

The Analysis ToolPak has a built-in tool for this. Microsoft says it "projects values in the forecast period, based on the average value of the variable over a specific number of preceding periods."

First, turn on the ToolPak if you do not see Data Analysis on the Data tab. Microsoft's steps on Windows are: select File, then Options, and then select Add-Ins. In the Manage box, select Excel Add-ins and then select Go. Then select the Analysis ToolPak check box and select OK.

Then run the tool:

  1. Go to the Data tab and select Data Analysis.
  2. Choose Moving Average and select OK.
  3. In Input Range, select the Sales values.
  4. Check Labels in First Row if you included the header.
  5. In Interval, type the window size, such as 3.
  6. Choose an Output Range, such as C2.
  7. Check Chart Output if you want a chart, then select OK.

Excel writes the averages into the output range. The first rows show #N/A, since the window is not yet full.

The tool writes values, not formulas. If your data changes, run the tool again.

The tool suits a one-off analysis, such as a quarterly review. It also adds a quick chart if you checked Chart Output. For a sheet you update every week, the formula in Way 1 is easier to maintain.

Microsoft's page also shows how each value is built. It describes N as "the number of prior periods to include in the moving average." The tool simply averages those N values for each row.

Way 3: Add a moving average trendline to a chart

A trendline adds the smoothed line straight onto a chart. It is the fastest way to show the trend visually.

Microsoft's steps start with a chart of your data:

  1. Select your data and insert a line chart.
  2. Click anywhere in the chart.
  3. On the Format tab, in the Current Selection group, select the trendline option in the dropdown list.
  4. Select Format Selection.
  5. In the Format Trendline pane, under Trendline Options, select Moving Average.
  6. Set the Period to your window size, such as 3.

If the chart has no trendline yet, add one first from the chart elements menu.

Microsoft notes one quirk about the line. The trendline has fewer points than the series. Microsoft says the count equals "the total number of points in the series less the number that you specify for the period."

A trendline only draws the line. It does not put the averages in cells. Use Way 1 if you need the numbers for a report.

Way 4: Calculate a weighted moving average with SUMPRODUCT

A simple average treats every week in the window the same. A weighted version gives recent weeks more say. That helps when recent changes matter most.

Use SUMPRODUCT to multiply each value by its weight, then divide by the total weight. Microsoft says SUMPRODUCT handles arrays "whose components you want to multiply and then add."

For a 3-week window with weights of 1, 2, and 3:

  1. Put the weights 1, 2, and 3 in cells F2 to F4.
  2. In cell D4, type =SUMPRODUCT(B2:B4,$F$2:$F$4)/SUM($F$2:$F$4).
  3. Drag the formula down to D9.

The dollar signs lock the weight cells, so only the sales range moves. The newest week in each window gets a weight of 3, and the oldest gets 1.

Microsoft adds one rule. It says "The array arguments must have the same dimensions," or SUMPRODUCT returns #VALUE!. Keep the window and the weights the same size.

A moving average example

Here is the 3-week simple average for the eight weeks of sales, using Way 1.

Week Sales 3-week average
1 120 –
2 135 –
3 128 127.67
4 150 137.67
5 162 146.67
6 155 155.67
7 170 162.33
8 181 168.67

Look at week 6. Sales dipped from 162 to 155, but the 3-week average still rose, from 146.67 to 155.67. The dip was noise, and the trend held.

Week 8 shows the weighted version too. Its 3-week weighted average is (155 × 1 + 170 × 2 + 181 × 3) ÷ 6. That is 1,038 ÷ 6, which equals 173.00.

The weighted figure, 173.00, sits above the simple average of 168.67. That is because the latest week, at 181, counts three times.

How to choose the window size

The right window depends on the rhythm of your data. A few rules of thumb help:

  • Match the cycle. For daily data with weekly swings, use 7 days. For monthly data with yearly seasons, use 12 months.
  • Short for fast decisions. A 3-period window shows turns quickly but stays bumpy.
  • Long for strategy. A 12-period window shows the big picture but reacts late.
  • Compare two windows. When a short average crosses above a long one, the trend may be turning up.

Try two or three windows on the same chart before you settle. Pick the one that removes noise without hiding the turns you care about.

Write the window size next to every chart and table. "3-week average" is clearer than "average." It also stops two readers from comparing a 3-week line with a 6-week one by mistake.

Show the trend in a report

The numbers are only half the job. Most readers will judge the trend from a chart, so the chart needs care.

Plot the raw values and the smoothed values together. Use a light gray line for the raw weekly sales and a bold colored line for the 3-week average. Readers see both the noise and the direction at once.

Label the smoothed line directly on the chart, such as "3-week average," rather than relying on a legend. Start the smoothed line where the window first fills, so it does not drop to zero at the start.

Add a short title that states the finding. "Sales kept rising through the week 6 dip" tells the reader what to look for. "Weekly sales" does not.

If you share the chart in PowerPoint, paste it with a link to the workbook. When next month's data arrives, you refresh the chart instead of rebuilding it. Our guide on how to update PowerPoint charts from Excel covers the options.

For a dashboard, place the latest value and the latest average side by side. A small note such as "above average" or "below average" helps busy readers act on the number.

Calculate a moving average in Excel with AI

When you track many products or regions, building a column of averages for each one takes time. An AI tool can smooth whole datasets and chart them in one pass.

Here is the method in three steps, using Powerdrill Bloom.

Step 1: Upload your Excel file

Upload the workbook with your time series. The AI Forecasting tool asks you to upload "historical metric with timestamps in any granularity."

Keep one row per period and one column per product or region. Clear headers help the AI understand each series.

Uploading an Excel time series to calculate a moving average in Powerdrill Bloom

Step 2: Ask for the moving average in natural language

Describe what you need. For example: "Add a 3-week moving average for each region and flag weeks where sales fell below it."

The Excel Calculations page says you can "Input your desired calculation request in text." You can then ask follow-ups, such as switching to a 4-week window.

Step 3: Check the results and chart the trend

Check one average by hand against Way 1. Then show the trend. The Line Graph Creator says it can "convert your raw data to stunning, interactive line graphs."

For a broader look at patterns over time, see our guide on how to do a trend analysis.

Checking moving average results and charting the trend

Common mistakes to avoid

These are the errors that come up most often with rolling averages in Excel.

  • Unsorted dates. An average of shuffled rows is meaningless. Sort oldest to newest first.
  • Missing periods. A skipped week shifts every window after it. Fill gaps or mark them.
  • Locked references. Dollar signs on the sales range stop the window from moving. Keep it relative.
  • Text in the range. AVERAGE ignores text, so a number stored as text silently drops out.
  • Zeros for missing data. Microsoft notes "cells with the value zero are included." A zero typed in as a placeholder for missing data drags the average down.
  • Reading the average as a forecast. A rolling average describes recent levels. It lags turns by design.
  • Static ToolPak output. The Moving Average tool writes values, so rerun it when data changes.
  • Unlabeled windows. Always say how many periods each average covers.

When you are ready to smooth trends across a whole dataset, try Powerdrill Bloom with your last six months of weekly figures.

Frequently asked questions

What is the formula for a moving average in Excel?

Use AVERAGE over a window of cells and drag it down. For a 3-period window starting at row 2, type =AVERAGE(B2:B4) in row 4, then fill down. Each row averages the latest three values.

How do I add a moving average line to an Excel chart?

Select the chart, open the Format Trendline pane, and choose Moving Average under Trendline Options. Then set the Period to the number of points you want in each average.

Where is the Moving Average tool in Excel?

It is in the Analysis ToolPak. Turn on the add-in, then go to Data > Data Analysis and choose Moving Average. Set the input range and the interval, then select OK.

What is the difference between a simple and a weighted moving average?

A simple version gives every value in the window equal weight. A weighted version gives recent values more weight, often using SUMPRODUCT with a set of weights.

How many periods should a moving average use?

Match the window to your data's cycle. Use 7 for daily data with weekly swings, or 12 for monthly data with yearly seasons. Shorter windows react faster but are noisier.

Sources: Microsoft Support, Use the Analysis ToolPak to perform complex data analysis · Microsoft Support, Add a trend or moving average line to a chart · Microsoft Support, AVERAGE function · Microsoft Support, SUMPRODUCT function.