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
| Row | A | B |
|---|---|---|
| 6 | Input | Cash flow |
| 7 | — | 1 |
| 8 | 10 | 2 |
| 9 | 20 | 3 |
| 10 | 30 | — |
| 11 | — | — |
=INDEX(TRIMRANGE($A$7:$A$11),B7)Illustrative output10
See the inputs and instructions for all 3 examplesSyntax
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 |