Rolling Standard Deviation (Volatility)

Excel Formulas › Statistics

All versionsSTDEV

Track how spread — or volatility — changes over time with a rolling standard deviation. Compute STDEV over a trailing window that slides forward each period.

Quick formula: rolling 5-period std dev ending at the current row:
=STDEV(OFFSET(B6, 0, 0, -5, 1))
OFFSET grabs the last 5 values; STDEV measures their spread. Copy down for a rolling volatility series.

Functions used (tap for the full reference guide):

STDEV

The example

Volatility over a trailing window.

AB
1PeriodRolling SD
253.2
364.1

The formula

The formula:

=STDEV(OFFSET(B6, 0, 0, -5, 1)) // 5-period rolling spread

How it works

How it works:

  1. OFFSET(currentCell, 0, 0, -5, 1) defines the last 5 rows (negative height counts upward).
  2. STDEV measures their spread — the rolling standard deviation, or volatility.
  3. Copy down; each row’s window slides forward, building a volatility series.
  4. A rising rolling SD means the series is getting more erratic; a falling one, more stable.

Sturdier alternative: with a date column, a fixed-range STDEV(B2:B6) filled down avoids OFFSET’s volatility. Plot the rolling SD as a line to see volatility regimes — the basis of risk dashboards.

Try it: interactive demo

Live demo

Series; rolling SD (window 5).

Latest rolling SD:

Variations

Fixed-range (sturdier)

No OFFSET:

=STDEV(B2:B6)

Rolling average

Trend, not spread:

=AVERAGE(OFFSET(B6,0,0,-5,1))

Coefficient of variation

Relative volatility:

=rollingSD / rollingAvg

Pitfalls & errors

Needs a full window. The first few rows can’t form a 5-value window — blank or skip them.

OFFSET is volatile. On big models it’s slow; the fixed-range fill-down is leaner.

Sample vs population. STDEV (sample) vs STDEVP — pick to match your convention.

Practice workbook

📊
Download the free Rolling Standard Deviation (Volatility) practice workbook
A rolling-volatility sheet with the fixed-range, rolling-average, and CV variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate a rolling standard deviation in Excel?
Use =STDEV(OFFSET(currentCell, 0, 0, -windowSize, 1)) to measure spread over a trailing window, or a fixed range like =STDEV(B2:B6) filled down.
What does a rising rolling standard deviation mean?
The series is becoming more volatile — values are spreading out more within the window.
How do I make a relative volatility measure?
Divide the rolling standard deviation by the rolling average (a rolling coefficient of variation).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Standard deviation · Moving average · Rolling 12-month total

Function references: STDEV