TRIMRANGE Function in Microsoft Excel: Remove Blank Outer Rows and Columns

• First introduced: announced as a Windows Beta preview on August 28, 2024, for Version 2409, Build 18020.2000 or later. Microsoft introduction
• Current status: documented for Microsoft 365. Microsoft’s March 25, 2025 update announced wider Windows availability. This guide is scoped to Microsoft 365 for Windows; it does not establish other platforms or a perpetual-edition entitlement. Release update
• Last supported version: no retirement endpoint established. Check feature availability in the installed Microsoft 365 build rather than treating an introductory Beta build as the final release.
• Evidence checked: October 14, 2026. Current function help. Examples were independently calculated; no native Excel execution was performed.

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

range is required. The two optional controls independently choose which outer rows or columns to remove: 0 keeps both edges; 1 trims the leading edge; 2 trims the trailing edge; 3, the default, trims both. Official syntax

The same option means different directions depending on the argument: leading rows are above the data, whereas leading columns are left of it. Use comma argument separators for these English-locale examples, or your locale’s corresponding separator.

Start with an empty worksheet. Enter only these nine values in B3:D5:

Leave every other cell in A1:E7 genuinely empty. Do not fill those cells with spaces or formulas returning empty text. Enter output formulas at G1 or farther right.

=TRIMRANGE(A1:E7)

Expected output is a 3-row by 3-column array:

Rows 1–2 and 6–7 are outside the populated block; columns A and E are also empty. The returned rectangle therefore corresponds to B3:D5. The original data remains in B3:D5.

These scalar checks are convenient if you want to verify the dimensions independently of the spill layout:

=ROWS(TRIMRANGE(A1:E7))
=COLUMNS(TRIMRANGE(A1:E7))

Each returns 3.

=ROWS(TRIMRANGE(A1:E7,2,0))

Expected result: 5. Rows 6–7 are removed, but the two blank rows above the data remain. The last argument is zero, so columns are not trimmed:

=COLUMNS(TRIMRANGE(A1:E7,2,0))

Expected result: 5.

This is useful when the position of a heading or a fixed-width layout must be retained. Do not infer that every blank cell inside the returned rectangle disappears.

=ROWS(TRIMRANGE(A1:E7,1,3))

Expected result: 5, corresponding to rows 3–7. The formula removes the leading blank rows, retains the trailing blank rows, and trims the outer columns. Its width is 3, as shown by =COLUMNS(TRIMRANGE(A1:E7,1,3)).

The same row count as example 2 describes a different set of rows. This is why checking only the dimensions is insufficient when validating an important result.

=INDEX(TRIMRANGE(A1:E7),3,2)

Expected result: 1, the Units value for Pad. After trimming, the heading is row 1 of the returned array, Pen is row 2, and Pad is row 3. Column 2 is Units.

The corresponding untrimmed reference =INDEX(A1:E7,3,2) returns Item. Relative positions change after trimming, so adjust downstream indexing deliberately.

Microsoft also documents trim-reference punctuation: A1.:.E7 trims both sides, A1:.E7 trims trailing sides, and A1.:E7 trims leading sides. For example:

=ROWS(A1.:.E7)

Expected result for this dataset: 3. The full function is often easier to review when rows and columns require different controls. Trim-reference documentation

• A blank-looking edge remains: inspect spaces, apostrophes and formulas. These examples cover physically empty cells; they do not establish how every empty-string or error value is treated.
• A gap in the middle remains: outer-edge trimming is not row filtering. Use a deliberate FILTER condition to remove records.
• #SPILL!: check whether another value obstructs the returned array. Clear only intended output cells.
• #NAME? or unrecognized trim-reference syntax: confirm the Microsoft 365 feature is available in that installation.
• An entirely empty input is a separate edge case. Do not assume it produces a usable zero-sized array; define your empty-dataset behavior before deploying a formula.

• INDEX in Microsoft Excel
• Editorial testing standards

Verification: The retained coordinates and example outputs were independently checked using the exact nine populated cells. This is documentation-based verification, not a native Excel test.