ExcelFlux

TRIMRANGE function

Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns.

Lookup and reference · Microsoft 365 · Guided Excel example

Where can you use it?

Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.

Scenario in the workbook

Trim blank rows from both ends. Interior blanks are outside the scope of trimming.

A worked example from the workbook

RowAB
6InputCash flow
7—1
8102
9203
1030—
11——
Record 1
=INDEX(TRIMRANGE($A$7:$A$11),B7)

Illustrative output10

See the inputs and instructions for all 3 examples

Syntax

TRIMRANGE(range,[trim_rows],[trim_cols])

Worked examples

Trim blank rows from both ends. Interior blanks are outside the scope of trimming.

Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.

Row A B
6 Input Cash flow
7 — 1
8 10 2
9 20 3
10 30 —
11 — —
Trial Expression Sample output
Record 1 =INDEX(TRIMRANGE($A$7:$A$11),B7) 10
Record 2 =INDEX(TRIMRANGE($A$7:$A$11),B8) 20
Record 3 =INDEX(TRIMRANGE($A$7:$A$11),B9) 30
Microsoft Excel reference