Stop Writing Huge Excel Formulas. (Use ONE DOT Instead) transcript

Have you ever seen someone do this in Excel? So they have a list of names here, and they would go to another column, type in "=", and reference the entire column. They might not do it on the same sheet because it doesn't really make sense, but they might want to bring this information to another sheet. So they would go here to another sheet, type in "=", reference this column, and press Enter. Now, the reason they do this is so that whenever new names come in, they are automatically added to this list.

So if I go down here and add "Leila G" and go here, we can see it automatically added. Then they might add different columns with other information. Maybe until now, when you saw them do this, you just kept to yourself, you didn't say anything. But now you can say something. Dot. That's what you're going to say. Add the dot. They're going to be like, "What are you talking about?" They're like, "Look at this. We're going to go right here after the colon.

We're going to add a DOT. Look at what happens." Everything is cleaned up. All those zeros are gone. And they're going to be like, "What is this?" The reason I referenced the column is so that I can add new data and it comes over. And you're going to be like, "Wait a second, hold on. That... that's exactly what's happening!" If you go here and add in "Poldi king," let's take a look at our report. "Poldi king" shows up.

You're going to look at them... Okay, jokes aside, this is a new feature in Excel. It's called Trim Refs. Its purpose is simple: to cut out empty cells that appear after or before your range. So we just saw an example of cutting out empty cells after the range, but you can do the same thing for before the range as well. Let me just go and shift this range one cell down. Now we can see a zero before that range. How do we cut this out?

Take a guess. You probably guessed it. I just have to go and put a dot before the colon, press Enter, and it's gone. Now, the cool thing here is that I'm working with a clean spilled range. So I could, if I want, use this in further calculations. For example, let's just add numbering to this. I'll go and add in the SEQUENCE function. I'm going to do a COUNTA, and look at this. I'm just going to reference A1, put a hash to reference the spilled range.

Let's just close the bracket and do a "-1" because I want to exclude the header. That's it. Close the bracket, press Enter, and I get a dynamic numbered range. So if I go here and add a new name to this, let's go to our report. It's automatically added. Now, in case you're not a fan of that dot syntax and you prefer to use a proper function, you can use the TRIMRANGE function, which does exactly what the dots do, except in function format.

So if I remove these dots so we go back to what we had originally, now I'm going to put this range inside the TRIMRANGE function. Notice it popped up here. I'm going to press Tab, then I'll go to the end, add the closing bracket, and it automatically cuts out those leading and trailing empty cells. Now, if you wanted to define exactly what you want to cut out, you can take advantage of its optional arguments. So here you can define to just cut out the leading empty cells or the trailing ones.

So if I go with number 2 here, that's what I get. Now, there's some really great things you can do with this function. I'll share some cool ideas. Number one: grab a proper unique list of items. So here we have a list of different roles, and we want to get a unique list of roles. So I'm going to type "=UNIQUE" and let's say I want to reference this range, but I want to account for additional roles here. So I'm going to take in extra cells, close the bracket, Enter, and I get that additional zero popping in there.

How do I get rid of this? You guessed it. I just go here, type a dot, and Enter. If I have a new role added, let's say "Driver," that automatically gets added, but that nasty zero is not there. Next: combining data from multiple sheets in a dynamic way, minus the headache. So here, I have some staff data, their roles, and salaries. This is in a sheet called "Staff." We have 21 rows of data. On another sheet, "Management," we have the same data but for our management.

I want to combine the two on this "All" sheet, and I want to account for extra rows. We're going to start off with VSTACK. Let's go to "Staff," select this range, and account for some extra rows. I'll just go up to row 30, add the Excel separator, go to "Management," and add in some extra rows. But we don't need that many, so I'll go until row 15, close the bracket, Enter, looks great! Until, I scroll down and see those empty rows added as zeros.

To clean this up, you probably know what we need to do, right? We're going to go here, add a dot, go here, add a dot. Now, when I Enter this, look at this—beautiful. Let's quickly test it out. Let's just add a new manager down here. Okay, so now we have Walter White, joined the team. Let's go to the "All" sheet, and Walter is automatically added. Now, remember, instead of the dot, you can also use the TRIMRANGE function.

You just have to put each bit inside the function. So if I do it quickly, we're going to put this side in TRIMRANGE and this side in TRIMRANGE as well, and we get the same effect. I personally prefer to use the dot. By the way, if you're enjoying this video, you're going to love our courses, especially our Black Belt Excel Package. We've had many people tell us it's been life-changing for them. This package already includes eight of our top-selling Excel courses, and we've also added a whole new section to it that breaks down the latest features when they roll out and how to check if you have them.

And obviously, we keep this updated. So if you want to upgrade your skills, go to XelPlus.com or click the link in the description box that will take you directly to the package. So, going back to the video, I kept the best for last. This is something I needed to do in my corporate job, and that was to automatically grab the last 12 months. The formula was a horror. I mean, look at this. This was the function I used to grab the last 12 months of this raw dataset.

Here I have my date, and I have Bags in units and I just want to grab the last 12 months. So notice from July to June—that's all I want here. And if I add a new month to this, let's say the month of July, that automatically gets added here. That's the purpose of this. Doesn't this look scary? Let me just press Ctrl + Z. This was the old way. This is the new old way, because it's just been replaced. It uses the FILTER function to exclude these blanks, and once they're excluded, we use the TAKE function to grab the last 12 rows.

This is also history. This is history. This is the new way of doing things. Let's go with TAKE. We're going to select this range. We're going to take the last 12 rows, so I'm going to go with "-12." But you know what I'm going to do? I'm going to put a dot here, and boom—the last 12 months automatically added. Now, if I add July, we'll see it automatically pop up. And this is great for creating charts that are dynamic, right?

Let's just quickly insert a line chart. I have data until July. Let's go ahead and add August. Look at the chart—beautiful. Take a moment and look at this. Now, before, way before. Obviously, you wouldn't have this problem if you used tables. And you probably already know that tables are best practice anyhow. But you also probably know that there are just—sometimes– cases that, for whatever reason, we can't use a table.

That's when we can make use of this new annotation and the TRIMRANGE function. Now, I know that there are tables fanatics out there who say that we should always, always use a table. So if you're not a fan of the TRIMRANGE function, comment below–just comment "Team Table" and I'll see who you are. If you're a big fan of the TRIMRANGE and Trim Refs, comment "Team Trim." If you like to switch between the two depending on the situation, I consider you a diplomat, so comment "Team Diplomat" below.

Okay, that's it for today. Thank you for being here, thank you for watching, and I'll catch you in the next video.