TRIMRANGE Referencing in Excel

The Problem: Create a range that automatically expands as data is entered in it.

The Solutions:

1. A formatted table automatically expands as data is added to it. Whilst formatted tables are a powerful feature, they do have some drawbacks that can limit their use across all solutions.

2. The OFFSET function has been used to create flexible ranges. The solutions tend to be limited in their application as they have issues when it comes to blank cells and extra data below the ranges.

3. The INDEX function is also used to create flexible ranges and has similar limitations to the OFFSET function.

TRIMRANGE referencing

Standard vertical range reference.

A1:A10

(I am going to ignore the use of $ signs with references.)

There are three ways we can change this reference to work with the new TRIMRANGE functionality.

Flexible end reference – the last non-blank cell in the range becomes the last cell reference. Insert a full stop after the colon.

A1:.A10

Flexible start reference – the first non-blank cell in the range becomes the first cell reference. Insert a full stop before the colon.

A1.:A10

Flexible start and end reference – the first non-blank cell in the range becomes the first cell reference and the last non-blank cell in the range becomes the last cell reference. Insert a full stop on either side of the colon.

A1.:.A10

Examples

In the image below I have an example of each of the four references.

The yellow cells are for input. Currently each input cell has an entry. Currently all the references return the same references.

If we delete a couple of entries at the end of the input range things start to change.

The formulas in columns D and F change to reflect the amended range. They now only reference 8 cells. Those two references use the flexible end reference.

Let’s delete entries at the start of the input range.

The ranges are different now. The standard reference (column C)  hasn’t changed at all.

Column D has the flexible end reference and is referring to the range A1:A8.

Column E has a flexible start reference and is referring to the range A4:A10.

Column F has the flexible start and end reference and is referring to the range A4:A8.

If we delete entries in the middle of the input range, the ranges selected are not affected – see image below. The values displayed have changed but the ranges are unaffected.

This new functionality simplifies creating flexible ranges that can expand or contract based on the entries in the range.