Excel: How to Calculate 7 Day Moving Average

In time series analysis, a 7-day moving average is simply the average value of the 7 days leading up to and including a certain date.

The following example shows how to calculate a 7-day moving average for a dataset in Excel.

Example: Calculate 7 Day Moving Average in Excel

Suppose we have the following dataset that shows the total sales made by some company during 20 consecutive days:

To calculate the 7-day moving average of the sales values, we can type the following formula into cell C8:

=AVERAGE(B2:B8)

We can then click and drag this formula down to each remaining cell in column C:

Excel calculate 7-day moving average

The values in column C represent the 7-day average of the values in the sales column.

For example, the 7-day average of sales on 1/7/2023 is 10.8571.

We can confirm this is correct by manually calculating the average of sales for the seven days leading up to and including this date:

7-Day Sales Avg. on 1/7/2023: (8+10+12+12+13+10+11) / 7 = 10.571

This matches the value calculated by our formula.

Since we clicked and dragged this formula down to each cell in column C, the formula automatically updated to use the most recent 7 days to calculate each 7-day moving average.

For example, cell C21 uses the range B15:B21 to calculate its 7-day moving average:

Note: We had to type our formula starting in cell C8 because this represented the first date that had 7 days to use for calculating the 7-day moving average.