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: MrExcel 2024 Igniting Excel
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
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: MrExcel 2024 Igniting Excel
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
Transcript of the video:
Hey, just down below the video, if you click “Like”, like that'll make sure that YouTube shows this video to more people.
Thanks. Oh, this is wild.
Brand new in Beta - a new way to have expanding dynamic size ranges.
A new way to reference ranges with an extra dot or two extra dots.
Check it out. Hey, we got something really new and exciting today. A new better way to refer to ranges that are going to expand or contract. In the past we might've done something crazy where you just referred to the entire column. You don't know how many rows there were going to be. And that's inefficient because Excel has to look and all of those rows. So we have this new thing called TRIMRANGE.
Let's take a look at the function argument there. =TRIMRANGE(.
So we specify a range. And then the row trim mode.
So the choices there are to get rid of any trailing blanks.
Empty cells at the bottom. Leading is the empty cells at the top.
Three is the default to get rid of both at the top and the bottom.
So you can see right here this example. If we ask for the TRIMRANGE of B:B.
I don't have to specify the ,3,3 because I'll take either the top or the bottom.
Then that is going to analyze a smaller dataset. Alright? Ah.
But here's the amazing thing, right? They have a new formula range notation.
Check this out. If we say B colon and then a period or a full stop or dot, I'm just going to call it a dot. This B colon dot B says get rid of all of the values at the end - the trailing right? If we would change the.to put the dot before the colon, then it would get rid of the leading blanks.
If you put a dot both before and after the colon, then it's going to get rid of leading and trailing. Now here, this is an amazing thing.
We said, “well what about Data Validation” Right?
Data, Validation. In the Data Validation source, allow anything from A2 colon dot down to A30. So that means as this range grows, the data validation is going to grow. So right here we can choose and it goes down to Guava. I'll add something else down here.
And when I go back, that range should have automatically grown, right?
So hey, it's working with Data Validation. Now I tried it with charts.
I can't get it to work with charts. With pivot tables, it's only half working.
It kind of takes a look at that range and locks it into the pivot table as hard coded right now.
So here's one where we're going to add more columns or rows - we don't know.
So you can see that the B2:. down to E10. But instead of just taking an average here, which returns one number. I said let's just divide everything by two.
So that gives us a spilled range. It shows us what's being returned.
So we want everything from B2 to E10. But you see that all of these empty here and empty cells here are not being returned. It's actually shrinking it down, which is perfect.
But if I would add a number here, 1234, you see that, then it extends the entire range.
And if I would add a number here, let's just add 123s, Control Enter.
And they get added in, right? Pretty cool how this is working.
Now with the whole thing with the trailing and the leading, these values down here have the dots in various spots. So B2 to B15 divided by two.
And you see that because I'm asking for it to trim the trailing empty cells.
Everything after 385 is not being returned. But these two blanks at the top are being returned to zeros. If I would switch that formula to put the dot before the colon. Then that's going to get rid of the two leading blanks like that. And we'll have all the trailing empty cells.
And then the one that you might want to use if you want to get rid of both leading and trailing blanks would be the dot colon dot. So it's there.
So even if we have the dot colon dot, that's going to get rid of the leading blanks and the trailing blanks. But any blanks in the middle will continue to be returned. And of course, if you don't like this dot construct - the colon dot. You can do this, all of this, with the TRIMRANGE.
So here the two is getting rid of trailing here, the one is getting rid of leading.
And then here the three, three is getting rid of both leading and trailing.
So whether you want to use TRIMRANGE or just the shorter colon period notation, there's lots of different ways to go. There's been a lot of discussion about the period.
Some people say they want something bigger than the period.
Some people thought of maybe a till day, but there's some countries where the till day's not on the keyboard. Someone in Germany wanted to use the two dots above the u. I don’t know what that's called, that accent key.
But I don't have that on my keyboard. So at least with the dot everyone has it.
I realize it's hard to see. You have to be kind of careful when you're looking for that. What a cool new thing.
You just have to hope for the flight to get expanded.
So you can give this a try. Thanks to my friend Suat in Turkey who managed to get it on his laptop. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, just below the video, click this Store icon. We have a whole bunch of new mugs and shirts.
Professionally designed. All kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
Thanks. Oh, this is wild.
Brand new in Beta - a new way to have expanding dynamic size ranges.
A new way to reference ranges with an extra dot or two extra dots.
Check it out. Hey, we got something really new and exciting today. A new better way to refer to ranges that are going to expand or contract. In the past we might've done something crazy where you just referred to the entire column. You don't know how many rows there were going to be. And that's inefficient because Excel has to look and all of those rows. So we have this new thing called TRIMRANGE.
Let's take a look at the function argument there. =TRIMRANGE(.
So we specify a range. And then the row trim mode.
So the choices there are to get rid of any trailing blanks.
Empty cells at the bottom. Leading is the empty cells at the top.
Three is the default to get rid of both at the top and the bottom.
So you can see right here this example. If we ask for the TRIMRANGE of B:B.
I don't have to specify the ,3,3 because I'll take either the top or the bottom.
Then that is going to analyze a smaller dataset. Alright? Ah.
But here's the amazing thing, right? They have a new formula range notation.
Check this out. If we say B colon and then a period or a full stop or dot, I'm just going to call it a dot. This B colon dot B says get rid of all of the values at the end - the trailing right? If we would change the.to put the dot before the colon, then it would get rid of the leading blanks.
If you put a dot both before and after the colon, then it's going to get rid of leading and trailing. Now here, this is an amazing thing.
We said, “well what about Data Validation” Right?
Data, Validation. In the Data Validation source, allow anything from A2 colon dot down to A30. So that means as this range grows, the data validation is going to grow. So right here we can choose and it goes down to Guava. I'll add something else down here.
And when I go back, that range should have automatically grown, right?
So hey, it's working with Data Validation. Now I tried it with charts.
I can't get it to work with charts. With pivot tables, it's only half working.
It kind of takes a look at that range and locks it into the pivot table as hard coded right now.
So here's one where we're going to add more columns or rows - we don't know.
So you can see that the B2:. down to E10. But instead of just taking an average here, which returns one number. I said let's just divide everything by two.
So that gives us a spilled range. It shows us what's being returned.
So we want everything from B2 to E10. But you see that all of these empty here and empty cells here are not being returned. It's actually shrinking it down, which is perfect.
But if I would add a number here, 1234, you see that, then it extends the entire range.
And if I would add a number here, let's just add 123s, Control Enter.
And they get added in, right? Pretty cool how this is working.
Now with the whole thing with the trailing and the leading, these values down here have the dots in various spots. So B2 to B15 divided by two.
And you see that because I'm asking for it to trim the trailing empty cells.
Everything after 385 is not being returned. But these two blanks at the top are being returned to zeros. If I would switch that formula to put the dot before the colon. Then that's going to get rid of the two leading blanks like that. And we'll have all the trailing empty cells.
And then the one that you might want to use if you want to get rid of both leading and trailing blanks would be the dot colon dot. So it's there.
So even if we have the dot colon dot, that's going to get rid of the leading blanks and the trailing blanks. But any blanks in the middle will continue to be returned. And of course, if you don't like this dot construct - the colon dot. You can do this, all of this, with the TRIMRANGE.
So here the two is getting rid of trailing here, the one is getting rid of leading.
And then here the three, three is getting rid of both leading and trailing.
So whether you want to use TRIMRANGE or just the shorter colon period notation, there's lots of different ways to go. There's been a lot of discussion about the period.
Some people say they want something bigger than the period.
Some people thought of maybe a till day, but there's some countries where the till day's not on the keyboard. Someone in Germany wanted to use the two dots above the u. I don’t know what that's called, that accent key.
But I don't have that on my keyboard. So at least with the dot everyone has it.
I realize it's hard to see. You have to be kind of careful when you're looking for that. What a cool new thing.
You just have to hope for the flight to get expanded.
So you can give this a try. Thanks to my friend Suat in Turkey who managed to get it on his laptop. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, just below the video, click this Store icon. We have a whole bunch of new mugs and shirts.
Professionally designed. All kinds of great fun slogans for you or your favorite Exceller. If you like these videos, please down below, Like, Subscribe and Ring the Bell. Feel free to post any questions or comments down in the comments below.
