Excel like a finance pro.
Dynamic array function
TRIMRANGE
Trims the empty outer rows and columns from a range so whole-column references stop dragging blanks along.
When to use it
Trims empty rows and columns from the edges of a range, so a whole-column reference only covers the used cells. The dot operator (A.:.A) is the shorthand.
The shape of it
- Syntax
=TRIMRANGE(range, [trim_rows], [trim_cols])
- Example
=SUM(TRIMRANGE(A:A))
Worked examples
Whole column, trimmed
=SUM(TRIMRANGE(A:A)) → same as SUM(A:A), faster
Only the used rows are evaluated.
Spill the used range
=TRIMRANGE(A:C) → the used block
Spills just the rows and columns with data.
Dot operator
=SORT(A.:.A) → sorted used values
The dot operator trims both ends.
Worth knowing
- The dot operator does the same thing inline: A.:.A trims both ends.
- Trim mode 2 removes trailing blanks only, which keeps a header row in place.
- Makes FILTER and SORT over whole columns practical.
- Requires Microsoft 365 (2024).
Where it goes wrong
- #NAME? in older versions.
- A stray value far down the column keeps the range long.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.