Microsoft Excel Tutorial: Excel New Range Reference For Dynamic Sized Ranges and TRIMRANGE function.

In today’s video, we’re diving into an exciting new feature in the Excel beta that’s bound to change the way we work with dynamic ranges. Microsoft has introduced a groundbreaking method for referencing ranges using a simple dot notation, or by leveraging the new TRIMRANGE function. Say goodbye to inefficient full-column references and hello to cleaner, more efficient formulas. Stick around as we explore how these new features can streamline your Excel workflows!

First up, let’s talk about the TRIMRANGE function. This new function allows you to specify a range and trim out any unwanted empty cells—either at the top, the bottom, or both! For example, =TRIMRANGE(B:B,3,3) removes both leading and trailing blanks, effectively analyzing a smaller dataset without those pesky empty cells slowing things down. This is a great improvement for those looking to optimize their Excel models.

But the excitement doesn’t stop there. There’s a new range notation that uses dots to specify which cells to include or exclude dynamically. With B:.B, you can remove trailing blanks, while B.:B gets rid of leading blanks. And if you use B.:.B, you eliminate both. This new notation provides a flexible way to handle expanding or contracting data ranges without over-complicating your formulas.

These new tools even work with data validation in Excel! Imagine having a data validation range that automatically adjusts as your data expands. Simply use a range like A2:.A30 in your data validation source, and it will grow or shrink as needed. While it works great for data validation, there are some limitations in pivot tables and charts, but it’s a promising start!

Overall, this new dot notation and the TRIMRANGE function are fantastic additions to the Excel toolset, providing more control over dynamic ranges. Whether you’re managing large datasets, creating dynamic data validations, or just want cleaner spreadsheets, these new features are a game-changer. Make sure to try them out and let me know in the comments how you’re planning to use them in your workflows!

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

Table of Contents
(0:00) New way to refer to Excel ranges of unknown size
(0:38) Syntax of TRIMRANGE function in Excel
(1:02) Example of TRIMRANGE in Excel
(1:13) New Colon – Dot Reference is shortcut for TRIMRANGE
(1:47) Data Validation drop-down lists that expand
(2:18) Charts and pivot tables mixed results
(2:30) Examples of adding columns to expanding range
(3:24) Examples of Trailing and Leading Empty Cells
(4:00) Does not remove interior empty cells
(4:12) Using TRIMRANGE instead of colon dot references.
(4:40) Period is hard to see. Tilde? Umlat?
(5:05) Slowly flighting to wider audience

This video answers these common search terms:
Excel TRIMRANGE function
New Excel Beta features
Dynamic range formulas in Excel
Excel dot notation for ranges
How to use TRIMRANGE in Excel
Excel range reference tricks
Data validation with dynamic ranges in Excel
Optimize Excel with TRIMRANGE
Advanced Excel range formulas
Excel tips for managing empty cells