TRIMRANGE
Cut the empty rows off a range.
Cut the empty rows and columns off the outside of a range
The same block with its empty outside rows and columns dropped
Syntax
=TRIMRANGE(range, [trim_rows], [trim_columns])
| Argument | Required | What goes here |
|---|---|---|
range | Required | The block to trim, written generously, like B5:B1000. |
trim_rows | Optional | 1 cuts the empty rows above the data, 2 the ones below, 3 both. 3 if you leave it out. |
trim_columns | Optional | Same three choices, applied to empty columns instead of rows. |
How to use TRIMRANGE
A formula written over exactly the rows that are there today has to be rewritten the moment a row is added. A formula written over a generous block, B5 to B1000, keeps working and drags nine hundred empty rows into every calculation.
TRIMRANGE settles that. It takes the generous block and hands on only the part with data in it, working inwards from the edges.
Only whole empty rows and columns on the outside go. A gap left in the middle of the data stays exactly where it is, which is what you want: a missing amount is a fact about the ledger, not something to tidy away.
The dot operator is the same thing in less space. B5:.B1000 trims the trailing edge, B5.:B1000 the leading one, and B5.:.B1000 both. The dot sits on the side of the colon it trims.
Which to write is a matter of taste. TRIMRANGE names itself in the formula and reads more clearly to somebody who has not met the dot before.
It changes nothing about a plain SUM, because SUM was ignoring the blanks anyway. It changes everything about counting gaps, and about any formula that produces one result per row.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=COUNTBLANK(E2:E40)A completeness check written over a generous block. It reports thirty-one gaps, every one of them a row that does not exist yet. | 31 |
=COUNTBLANK(TRIMRANGE(E2:E40))The same check with the empty rows trimmed off first. Nought gaps, which is the true answer, and it stays true as the ledger grows. | 0 |
=COUNTBLANK(E2:.E40)The dot operator saying the same thing. The dot goes after the colon, on the side of the range being trimmed. | 0 |
=SUM(TRIMRANGE(E2:E40))A total is the same either way, because SUM never counted the blanks. This is why trimming looks pointless until you meet a formula that does count them. | 7850 |
=COUNT(E2:.E40)/COUNTA(A2:.A40)What proportion of the rows carry a figure. Neither half of this works over a fixed range once rows are added, and neither works over an untrimmed generous one. | 1 |
Notes
- Trim every range in a formula or none of them. A SUMIFS whose totalling column is trimmed and whose tested column is not will refuse, and that refusal is doing you a favour.
- TRIMRANGE takes an optional code for each axis: 1 trims the leading edge, 2 the trailing edge, 3 both, 0 neither. It is 3 unless you say otherwise.
- Both the dot operator and TRIMRANGE arrived in Excel in 2024, so a file using them will not open in an older version.
Related
A reference tells you what it does. A unit makes you use it.