TRIMRANGE and Range Names in Excel

In the image below we have a list of colours. We want to name this list and have it expand, as new colours are added.    

If you select the range A1:A5 and press Ctrl + Shift + F3 you open Create Names From Selection dialog. This allows you to use the label in cell A1 to name the range beneath.

Click OK with only the Top row check box ticked. This will create a new range called Colours referring to the range A2:A5.

This range is set to A2:A5. Adding a new colour in cell A6 won’t expand the named range to include the new colour.

TRIMRANGE references

A recent update to Excel has added a new way to refer to ranges. In the link below you can see more information.

https://www.excelyourself.com.au/blog/trimrange-references-in-excel

This new reference defines range based on the used cells in the range. We can define a long range and ignore the empty cells beneath our list.

We can use this new range referencing in the Refers To reference for a name.

In the Formulas tab click the Name Manager icon or press Ctrl + F3.

In the dialog that opens select the Colours name. In the Refers to: box edit the name to.

=Sheet1!$A$2:.$A$500

I added two zeroes on the end and added the full stop after the colon. Click the Tick icon and click the Close button.

The range name is now dynamic. Adding new colours to the bottom of the list automatically updates the range name.

Creating flexible name is much easier now with this new range referencing.