How to Use TRIMRANGE & Trim Ref Operators to Tidy Up an Excel Spreadsheet

Excel's TRIMRANGE Function and Trim Ref Operators: A Clean Solution for Dynamic Ranges

Tired of cumbersome dynamic range formulas like OFFSET, INDEX, or TOCOL? Excel's TRIMRANGE function offers a streamlined approach to managing expanding and contracting data ranges without the complexity. This article explores TRIMRANGE, its simpler counterpart – Trim Ref operators, and why they're valuable alternatives to structured tables in specific scenarios.

Quick Links

• TRIMRANGE Syntax
• Example 1: TRIMRANGE with SUM
• Example 2: TRIMRANGE with XLOOKUP
• Using Trim Ref Operators
• TRIMRANGE vs. Structured Tables

TRIMRANGE Syntax

The TRIMRANGE function takes three arguments:

<code>=TRIMRANGE(a, b, c)</code>

Where:

• a (required): The range to trim.
• b (optional): Row trimming (0: none, 1: leading, 2: trailing, 3: both). Defaults to 3 (both).
• c (optional): Column trimming (0: none, 1: leading, 2: trailing, 3: both). Defaults to 3 (both).

Currently, TRIMRANGE only trims blank rows or columns before or after data, not those interspersed within.

Example 1: TRIMRANGE with SUM

Let's calculate profit (Column C - Column B). Instead of a fixed range like =(C2:C200)-(B2:B200), which creates unnecessary calculations, use TRIMRANGE:

<code>=TRIMRANGE(C2:C200)-TRIMRANGE(B2:B200)</code>

This automatically adjusts as you add rows, maintaining a clean spreadsheet. The default trimming of leading and trailing rows and columns works perfectly here.

Example 2: TRIMRANGE with XLOOKUP

This example uses XLOOKUP to find jersey colors based on team (see image below). Instead of a fixed range in =XLOOKUP(B2:B22,$G:$G,$H:$H), use TRIMRANGE:

<code>=TRIMRANGE(a, b, c)</code>

Here, 2 trims trailing blanks. The result dynamically updates with new player entries.

Using Trim Ref Operators

Trim Ref operators provide a shorthand for TRIMRANGE, automatically trimming both leading and trailing blanks. Add a period (.) before and/or after the colon:

Period Placement	Blanks Trimmed
After colon (: .)	Trailing
Before colon (. :)	Leading
Both sides (. : .)	Leading and Trailing

For example: =XLOOKUP(B2:.B22,$G:$G,$H:$H) trims trailing blanks. Be mindful of these subtle dots when reviewing formulas!

TRIMRANGE vs. Structured Tables

Structured tables with structured references are generally preferred for their automatic expansion. However, TRIMRANGE offers a valuable alternative when dealing with spill arrays or LAMBDA functions incompatible with structured tables, or when you need unique formatting outside a table.

The above is the detailed content of How to Use TRIMRANGE & Trim Ref Operators to Tidy Up an Excel Spreadsheet. For more information, please follow other related articles on the PHP Chinese website!