A running maximum shows the best value reached up to each row — handy for record highs, peak demand, or high-water marks. The trick is an expanding range that grows as you fill down.
The locked start ($B$2) and relative end (B2) make the window expand one row at a time.
The example
Daily sales, with a column showing the highest day reached so far.
| A | B | C | |
|---|---|---|---|
| 1 | Day | Sales | Running max |
| 2 | Mon | 12 | 12 |
| 3 | Tue | 9 | 12 |
| 4 | Wed | 15 | 15 |
| 5 | Thu | 11 | 15 |
| 6 | Fri | 20 | 20 |
The formula
An expanding MAX from the first data row to the current row:
How it works
How the expanding range works:
$B$5is locked with dollar signs, so it always points to the first value.B5is relative, so it moves down as you copy the formula.- Row by row the window grows ($B$5:B5, $B$5:B6, …), and MAX reports the best value in it.
- The result never decreases — it only steps up when a new high appears.
In Excel 365 you can do the whole column at once with =SCAN(0,B5:B9,LAMBDA(a,b,MAX(a,b))).
Try it: interactive demo
Enter today's value and the previous high — the high-water mark updates.
Variations
Running minimum
Swap MAX for MIN to track the lowest value so far.
Whole column with SCAN
One dynamic-array formula builds the entire running-max column.
Pitfalls & errors
Forgetting the dollar signs on the start cell breaks the expanding range — both ends would move and you'd only ever see one cell.
Blank cells in the range are ignored by MAX, so gaps don't throw off the running peak.
Practice workbook
Frequently asked questions
Why does my running max reset?
Can I do this without filling down?
Does it ignore text or blanks?
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