So Office 2021 has just launched and there are so many new features you'll find. whether it's co-authoring features, or thinking tools or Data types, or functions in Excel, Translations and Outlook There's so many features and they're really easy to use. So today, we're going to talk about quite a few of them..
We're going to get into some Excel PowerPoint, Outlook and Word. We're going to have a lot of fun. The first thing we're going to talk about in this video is the visual refresh. Any of your office applications that you open have now had a major visual overview..
So if you see, if I open up word for instance, you'll see that even the starting really changed. I'll try to pause that there for you real quick. Right here, you'll notice that it does have a new little style, even on the startup as well. Now once it opens up, you may not see much in terms of changes on your startup page..
But the many you open up into a document, you will see the new and improved ribbon. Now, the ribbon still has the tabs. You'll still see command groups. You'll still see the commands.
But you'll see a more rounded corners there and just a simpler, just coloration monotone view to it So I really do like the new design. It does take some getting used to when you first start but I do love it, makes it very simplistic..
We now have the autosave and the save that is automatically on the top of the ribbon where the quick access toolbar used to be. So now when you click the little drop down, and you show it below the ribbon It just makes more sense for me, and it doesn't take up as much space as it does with the older versions, and I just really like it a lot..
So visual overview here. It looks great. They did an amazing job and what we're going to talk about next once we come back is a couple of the new Excel features and functions that came up We'll show you X look up, the sort function. We're going to go into the unique function, more these.
Dynamic array functions, and also the sequence function. So pause your videos, come back and we'll have some fun. Many of you have probably used V lookup or H lookup, or even index and match in the past. Today, I'm going to show you a new function called X look up. Now, X look up is an amazing function that replaces both V lookup and H look up.
Because it allows us to get rid of those requirements that V lookup and H hook up placed on our data sets For instance, in Vlookup, we would have to have the most leftmost column to look up value and it would have to match what we're looking up by. It would also have to be an ascending order as well..
Otherwise, it wouldn't work properly. The X lookup, it looks both left and right. So we don't even have to worry about any of those limitations that vlookup and H lookup gave us So right here. I have some data. I have a master list here with the start date. We have the department in the name of the person.
And what I want to do, is I simply want to look up
What department these group of people are in, from my master list. So to do this, normally we would have to go in and use Vlookup or Hlookup but the problem is.I can't even use Vlook up based off the limitation because I'm looking up by the name and the name is in the rightmost column, not the leftmost column So right away, I won't be able to use it. Another thing is, it's not an ascending order..
So there's your second limitation right there. But once again with X look up, you don't even have to worry about that. So the first thing I'm going to do I'll zoom in a little here is I'm going to go into my H2 cell.
And I'm going to start typing out, my equal sign and the new lookup function, X lookup. Now, there's quite a few little things we can do here. The first thing we can do, is search for lookup value. It's asking us what are we looking up by and I'm looking up by simply the name,.
So I'm looking up, Julius. I'll put my comma. The next thing it's going to ask me, is to lookup array. Where are we looking for this name? Well, it's going to be found in one of these listed names here..
So I'm going to just simply start with C2 and then I'm going to use my keyboard shortcut which is control+shift+down arrow to select the rest of the data. Then, I'm going to put my comma.
It's going to ask me, what do I want to return? and I'm going to say, Well, once you find this name in this list, I'm simply looking for the Department during. So I'm going to select from B2 all the way down to be B34..
So now it's saying Okay, we're going to look for this name, in the list of names and when we find that name, we're going to return what department they're in. and I'm like, that's exactly what I want. So really you only need these three major arguments.
Lookup value, look up array, and what you want to return Now, there are other arguments as well. We have, if not found. So that means that if I put a random name in here instead of it giving me an error,.
It'll give me whatever I write in here,
For instance. I can write something like doesn't exist [keyboard] and then the match mode.The match mode means that we try to find the closest match? or are we trying to find an exact match? Now by default, it's going to turn on to exact match, and then the last thing that it's going to ask us if we wanted to, is the search mode. Do we want to return the first search, or the second search..
So for instance, if Julius was working in sales but then switch to research. Which one do we want? Do we want the sales to be returned? Or do we want research to be returned? So we can look up the first or last value..
But once again, these three are totally optional. So, I will just simply put an, if not found statement and I'll put, "Doesn't Exist" just like that..
Now, I can close my parenthesis, press enter and it will tell me that Julius was in sales and now I can simply just autofill down, and there we go. Now, if I did add a random name here Since we filled out that, "If not Found".
If I put in Joe Patti, which is my name and will give me a big salary. Why not? and then, I want to just simply see what department I'm in. It'll say it doesn't exist Instead of giving you that weird error that you.
Would normally get if we didn't fill out "doesn't exist". So if I took this out, and I just simply press entered. It would give you an NA which means not applicable which means that I'm just not part of the list, but when people see NA, they tend to get nervous..
So, that's why I like to always add the doesn't exist or maybe not available, things like that. We can do the same thing with our bonus as well if I want to just pull out the bonus, you'll see here that they're all in different order. We're going to search by salary in here but this time, we need to make sure.
That we're searching for the actual salary, that's in between. We don't want an exact match because if we do an exact match, I don't have any salaries that are exactly these numbers So we do want to make sure that we do a closest match So, for instance, I'm going to do =XLOOKUP.
What am I looking up by? Well, the yearly salary. Where am I looking for it? Well, here in the salary. What do I want to return? Well, I want to return the bonuses. How much much percentage they get..
Now, I'm going to skip "If not found so I'll just put a comma and a comma. Now, it's asking me for the match mode and gives me four options: Exact match, Exact match or next smaller, Exact match or next larger or Wild card.
And I'm going to do an exact match or next fall So, I'll double-click on that. My search mode. I'm just going to keep it blank as well. So I'll just close this off and press enter. and then it's going to just simply give me what bonus I want..
Now, of course, I'm going to put a percentage on this one so that it shows me 10% I'm going to change the rest of these to a percentage formatting. So that when we do finally go like this. Whoo. But you see what happened here?.
It's telling me that there's some sort of issue It's saying. Well Joe, the 51 return 10% because the lowest one next to it was 10%. The 47 though, didn't return anything. Neither did the 58 and so on and so forth. And the reason why, is because if you look through all of these at the top,.
Nothing was actually set in stone. So even though the G2 is changing, which is nice because we want that to change. We don't really want these other formulas to change which they are. So if we go through, you'll see F15 through F-18, F15 through F19, and it goes up out 16 F20..
So you want to be sure that when you type out your formulas, you do make sure to absolute reference it and you can do this by using the F4 key on your keyboard. And it will automatically put your dollar signs into absolute reference so they don't change..
Now, when we press enter, we can go through and look at that, cleans it up perfectly. So looks great. I love X look up. It's one of my favorite features. Vlookup was always a little bit of a limitation. So this replaces that, I hope you enjoyed that. Try it out on your own..
The next function I'm going to show you is what's known as "the sort function" and it's part of our dynamic array functions Now, I really do like this function a lot and it allows you to sort all of your data in a way that you want. So for instance, if I come over here and I do equals sort..
I can just simply sort arrange an array of data Now, the first thing it's going to ask you is, what do you want to sort? And I actually want to sort all of this information. So I'm going to select from A2 all the way down. So I'm going to use Control+shift+right..
Ctrl+shift+down to D38 and this will give me all of my data. Now not including my headers here because I don't want it to be included in this sorting. So I'm just going to sort it from A2 to D 38. and then if I press enter right now,.
It's going to automatically sort it, by the first name column here and then it's going to sort it in the ascending. So that's the default. So you'll see the minute I press enter, if I add no other arguments, it will just simply sort it by the main. but there are other arguments here..
We have like a sort index. So if I put my comma, I can now choose what I want to Index this by. So what do I want to sort by? You want to sort by the first Column, the second, or the third. And I want to sort by hiring date..
So I'm going to put a 3 there, and it's going to know now that I'm sorting by the hiring date column because it's in the third column. I'll put another column and it's going to now, ask me. What do you want to sort by ascending or descending?.
And I'm going to say let's sort it ascending, so I'll put a one Now, the last option is by column. So, it's asking me if my data is sorted by the column or by the row By default, It's going to be false by row. So you don't even have to fill that out..
When I press enter. It's now sorting everything, but it's sorting it by date of hire. Since we've put that index as 3 So this is a really great way to start to sort your data without affecting your original data The next function we're going to talk about is called "Unique".
And what it allows you to do is go through a list or a dataset. And it allows you to return the distinct amount of items within that column. So for instance, if I wanted to see how many departments I have overall, or how many divisions I have overall,.
And you'll see there's many repeats, and I don't really know what's in there. I can use the unique function to, just simply return everything that's unique. So let's say, for instance, I want to see how many divisions do I have. I can do =Unique, and it's going to say, what is the array?.
What are you looking at? and I'm going to save the divisions. So I'm going to select from C2 use my control+shift+down arrow to select the rest of my data from C2 to C95. and when I press enter, it tells me that, out of all of this list of data,.
There's actually only 4 unique values, which is Maine, Connecticut and New Hampshire, and Vermont. Not only that, but I can actually do two at once. So if I wanted to see the vision and Department how many unique values I have, in each of those..