TRIMRANGE

Welcome to this post, where we’ll dive into Excel’s TRIMRANGE function and its companion Trim Refs operators. These recent additions make working with dynamic ranges and calculated columns more efficient and intuitive.

Note: as of the time of this post, these are in limited release and not all versions of Excel have them. For availability and more information, check out this announcement from Microsoft.

Video

Step-by-step Tutorial

We’ll break this down into three exercises to explore their uses step-by-step. Let’s jump in!

Exercise 1: The Problem with Dynamic Ranges

Imagine you’re working with a dataset and you’d like to create a calculated column. For example, you might want to subtract values in column C from those in column D.

The formula in E1 may look like this:

=D1-C1

You’d then drag the formula down to apply it to all rows. But here’s the challenge:

• What happens when you add new rows later? Will the formula automatically adjust?
  • If your data is stored in an Excel Table, the formulas expand automatically.
  • If not, Excel may or may not detect and extend the formula depending on your version.

Common Workarounds

Pre-fill the Formula: Drag the formula down far enough to cover potential future rows.

Use Whole Column References: Instead of targeting specific cells, use entire columns like this:

=D:D-C:C

This approach ensures all rows are calculated, even new ones:

Drawbacks

Using whole column references, however, can cause performance issues. It calculates for every row in the column, which could slow Excel down and increase file size unnecessarily.

Enter the TRIMRANGE function: a smarter way to handle this scenario.

Exercise 2: Introducing the TRIMRANGE Function

The TRIMRANGE function ensures calculations are limited to only the rows with data, even when using whole column references. Here’s how it works:

Let’s modify the earlier E1 formula to this:

=TRIMRANGE(D:D)-TRIMRANGE(C:C)

What does this do? The TRIMRANGE function trims out any blank rows before and after your data.

The result? Excel calculates only for the rows with data, optimizing performance:

How It Handles New Rows

Let’s say you add a new row at the bottom of your dataset. The TRIMRANGE function dynamically updates to include it. No manual adjustments are needed!

Optional Arguments

The TRIMRANGE function also has optional arguments to customize its behavior:

=TRIMRANGE(range, row_trim, col_trim)

• row_trim: Defines how blank rows are trimmed:
  • BOTH (default): Removes blank rows before and after the range.
  • LEADING: Removes blank rows above.
  • TRAILING: Removes blank rows below.
• col_trim: Works similarly for blank columns.

These options give you control over how TRIMRANGE handles your dataset.

Exercise 3: The Trim Refs Operators

If you prefer to avoid using the TRIMRANGE function, Excel introduces a new set of Trim Refs operators for similar functionality.

Here’s how they work:

Write your formula with whole column references, like:

=D:D-C:C

Add dots (.) around the colon (:) to specify the trimming behavior:

To trim leading rows: Place a dot before the colon:

=D.:D - C.:C

To trim trailing rows: Place a dot after the colon:

=D:.D - C:.C

To trim both: Use dots on both sides (which is the equivalent of the default TRIMRANGE function):

=D.:.D - C.:.C

This approach trims the blank rows dynamically while maintaining a compact formula.

Why This Matters

The TRIMRANGE function and Trim Refs operators make it easier to work with dynamic datasets. They eliminate the need for manual updates when rows are added, and they optimize performance by avoiding unnecessary calculations for empty rows.

Conclusion

The new Trim Range function and Trim Refs operators are game-changers for anyone working with dynamic datasets in Excel. They simplify your workflow, optimize performance, and ensure your formulas stay up-to-date as your data grows.

Sample File

I’ve prepared a sample file showcasing the TRIMRANGE function and Trim Refs operators in action.

FAQs

1. What version of Excel includes the Trim Range function?
The TRIMRANGE function and Trim Refs operators are part of Excel’s beta channel updates. Check this announcement from Microsoft for availability and more info.

2. Can I use TRIMRANGE in older Excel versions?
No, these features will not be ported backwards into legacy versions of Excel.

3. How does TRIMRANGE handle blank cells within a range?
Blank cells within a range are not removed. Only rows or columns before or after the data range that are entirely blank are trimmed.

4. Will using TRIMRANGE impact my file size?
Using TRIMRANGE or Trim Refs reduces file size compared to using whole column references, as calculations are limited to non-blank rows.

5. Can I use TRIMRANGE with structured references in Tables?
Tables already expand automatically, so TRIMRANGE is useful for ranges outside of Tables.

6. Are there performance differences between TRIMRANGE and Trim Refs?
Both methods are efficient, but TRIMRANGE offers more flexibility with optional arguments, while Trim Refs keep formulas compact.

7. How do I decide between TRIMRANGE and Trim Refs?
Use TRIMRANGE for more customization and flexibility. Use Trim Refs when you want a quick, concise solution.