How to Calculate Moving Average in Google Sheets

Key Insights

• Simple moving averages using AVERAGE with OFFSET or INDEX give you flexible, self-adjusting formulas that work as your data grows
• The MAP function combined with ARRAYFORMULA lets you calculate moving averages for entire columns in a single formula, eliminating manual copying
• Exponential moving averages weight recent data more heavily and require a recursive approach in Google Sheets, making them trickier but more responsive to trends

Why Moving Averages Matter

Moving averages transform noisy data into actionable trends. Whether you’re tracking daily sales, monitoring website traffic, or analyzing stock prices, raw data points often obscure the underlying pattern. A moving average smooths out short-term fluctuations and highlights longer-term trends.

The concept is straightforward: instead of looking at individual data points, you calculate the average of a fixed window of consecutive values. As new data arrives, the window slides forward, dropping the oldest value and incorporating the newest one.

Stock traders use moving averages to identify buy and sell signals. Sales teams use them to forecast demand. Operations managers use them to spot performance trends before they become problems. Once you understand how to calculate them in Google Sheets, you’ll find uses everywhere.

Simple Moving Average with the AVERAGE Function

The most direct approach uses Google Sheets’ built-in AVERAGE function with a fixed range. For a 3-period simple moving average (SMA), you’d write:

=AVERAGE(B2:B4)

This calculates the average of three consecutive cells. To extend this down a column, you’d enter the formula in the first applicable row and drag it down. Each row would reference its own trailing three values.

Here’s what a basic setup looks like:

The first two rows lack sufficient data for a 3-period average, so they remain blank. This manual approach works but becomes tedious with larger datasets and doesn’t adapt well when you want to change the averaging period.

Dynamic SMA with OFFSET or INDEX

The OFFSET function creates formulas that automatically reference the correct range relative to the current cell. This makes your spreadsheet more maintainable and less error-prone.

=AVERAGE(OFFSET(B5,-2,0,3,1))

Breaking down the OFFSET parameters:

• B5: The reference point (current row’s data cell)
• -2: Move up 2 rows from the reference
• 0: Don’t move any columns
• 3: Select 3 rows (the averaging window)
• 1: Select 1 column

This formula always looks at the current cell and the two cells above it, regardless of where you paste it.

For even more flexibility, use a cell reference for the period length:

=AVERAGE(OFFSET(B5,-(($E$1)-1),0,$E$1,1))

With the period stored in cell E1, changing that single value updates every moving average calculation in your sheet.

The INDEX function offers an alternative approach that some find more readable:

=AVERAGE(INDEX(B:B,ROW()-2):INDEX(B:B,ROW()))

This formula explicitly defines the range from 2 rows above the current row to the current row, giving you a 3-period average. It’s more verbose but makes the logic clearer.

Using ARRAYFORMULA for Bulk Calculations

Copying formulas down hundreds or thousands of rows is inefficient. Google Sheets’ MAP function, combined with ARRAYFORMULA, lets you calculate an entire column of moving averages with a single formula.

=ARRAYFORMULA(
  IF(ROW(B2:B100)<ROW(B2)+2,
    "",
    MAP(B2:B100,LAMBDA(cell,
      IF(ISBLANK(cell),
        "",
        AVERAGE(INDIRECT("B"&(ROW(cell)-2)&":B"&ROW(cell)))
      )
    ))
  )
)

This formula:

1. Skips the first two rows (insufficient data for 3-period average)
2. Iterates through each cell using MAP and LAMBDA
3. Calculates the average of the current cell and two preceding cells
4. Returns blank for any empty source cells

A cleaner approach using SEQUENCE and OFFSET:

=ARRAYFORMULA(
  IF(SEQUENCE(99,1,2)<=3,
    "",
    MAP(SEQUENCE(99,1,2),LAMBDA(row,
      AVERAGE(OFFSET(B$1,row-3,0,3,1))
    ))
  )
)

For very large datasets, consider whether the computational overhead of array formulas impacts your sheet’s performance. Sometimes a simple dragged formula performs better than a complex array calculation.

Exponential Moving Average (EMA)

Simple moving averages treat all data points equally. An exponential moving average (EMA) gives more weight to recent observations, making it more responsive to new information.

The EMA formula uses a smoothing factor (α) typically calculated as:

α = 2 / (period + 1)

For a 3-period EMA, α = 2 / (3 + 1) = 0.5

The EMA calculation is recursive—each value depends on the previous EMA:

EMA_today = (Price_today × α) + (EMA_yesterday × (1 - α))

In Google Sheets, you need to seed the first EMA value. The standard approach uses the first SMA as the starting point:

Row 4 contains the initial SMA. Row 5 onward uses the EMA formula referencing the previous EMA value.

To make this more flexible with a variable smoothing factor:

=B5*$E$1+C4*(1-$E$1)

Store your smoothing factor in cell E1 and adjust it without rewriting formulas.

Creating an array formula for EMA is significantly more complex due to the recursive nature. Google Sheets’ SCAN function handles this elegantly:

=ARRAYFORMULA(
  SCAN(AVERAGE(B2:B4),B5:B20,LAMBDA(acc,val,
    IF(ISBLANK(val),acc,val*0.5+acc*0.5)
  ))
)

The SCAN function maintains a running accumulator (acc) that carries the previous EMA value forward, applying the smoothing calculation to each new value.

Visualizing Moving Averages with Charts

Numbers in cells tell part of the story. Charts reveal the full picture.

To overlay a moving average on your raw data:

1. Select your date column, raw data column, and moving average column
2. Insert > Chart
3. Choose “Line chart” as the chart type
4. In the Chart Editor, ensure both data series appear

Google Sheets also offers built-in trendlines that can function as moving averages:

1. Click on your chart to select it
2. Open the Chart Editor (three dots > Edit chart)
3. Go to the Customize tab
4. Expand the Series section
5. Check “Trendline”
6. Set Type to “Moving average”
7. Adjust the period as needed

The built-in trendline approach requires less formula work but offers less control. For serious analysis, calculate your own moving averages and plot them explicitly.

Consider using different colors or line styles to distinguish raw data from smoothed data. A thin gray line for daily values with a bold colored line for the moving average creates clear visual hierarchy.

Practical Tips and Common Pitfalls

Handle insufficient data gracefully. Your moving average formulas should return blank or an error message when there aren’t enough data points. Wrap calculations in IF statements:

=IF(COUNT(B2:B4)<3,"",AVERAGE(B2:B4))

Deal with blank cells deliberately. Decide whether blanks should break the calculation or be skipped. The AVERAGEIF function can exclude blanks:

=AVERAGEIF(B2:B4,"<>",B2:B4)

Choose your period thoughtfully. A 7-day moving average smooths weekly cycles in daily data. A 30-day average reveals monthly trends. A 200-day average (common in stock analysis) shows long-term direction. Match the period to the patterns you’re investigating.

Watch for performance issues. Complex array formulas recalculate whenever any referenced cell changes. For sheets with thousands of rows and frequent updates, consider using helper columns with simpler formulas instead of one massive array formula.

Validate your calculations. Spot-check a few values manually. Off-by-one errors in OFFSET or INDEX formulas are common and can silently corrupt your entire analysis.

Moving averages are foundational tools for data analysis. Master these techniques in Google Sheets, and you’ll have a reliable way to extract signal from noise in any dataset you encounter.