Today, I'm going to cover important new Excel functions that you need to know in 2021. So these are new functions that were introduced recently in Excel for Microsoft 365, also for Excel for the Web. Now, if you have this 365 version of Excel, you need to know these functions. And once you go through these let me know which one is your favorite one. I'm going to share mine with you at the end of the video. Let's get moving. (upbeat music) Function number one, sort. With sort you get to dynamically sort your data set based on a column. So let's say, I have name entry dates and yearly salary and I want to get everything sorted by yearly salary. All I have to do is type in the sort function. My array is what I want to get back. Now, this is officially an Excel table, so let's say I want to get everything back, name, entry date and yearly salary. So I'm going to select the full table. Next is my sort index. This is the column number that we want to sort this by. If I completely ignore everything because these are optional arguments they're all in square brackets. If I close the bracket and press Enter I sort everything automatically by the name here. And the sort order is ascending. That's the default. Then notice the formatting didn't come with. So I'm going to grab the formatting from here and apply it to my range. And if you were planning to expand your range, you can drag the formatting down to a few cells below. Now, if I wanted to sort this by salary in descending order, I can just update my formula. What is my sort index going to become? Number three. The sort order, remember default was ascending, I want to go with descending. The last argument is whether it should sort by column or by row. Well, our data is in rows, so we're going to need false and false is the default. So I don't need to mention anything. I just close the bracket here, press Enter. And I have my data sorted by salary in descending order. All of this is dynamic. If I add a new person here and let's say this person earns 200,000, we're going to see Lilly at the top of our rank here. Now, let's update the date as well and everything updates automatically. Now, in case you ever want to sort a column by a column that's not in your end result, you can use the sort by function. So let's say, you want to get a sorted list of names based on salary, but you don't want to show entry dates or salary in your end results. Here the array is going to be the column that you want to get back. That's just going to be our name. Now, by what column do we want to sort? By yearly salary. How do we want to sort yearly salary? I want it in descending order, close bracket, press Enter. I get my names back without the other information. Number two, the unique function. With a unique function, you dynamically get a distinct list of values. So for example, let's say I want to get a distinct list for region here. All I have to do is type in unique that we can see it down here. So press Tab to accept it and select our array. The array in this case is a column because here I'm dealing with an Excel table. So it's enough if I just select the column here and I get my structured referencing. Now, if I close the bracket and press Enter, I already get a distinct list of values. You can do this for one or more columns. So let's say you want it to grab the unique combination for division and region. This time your array is going to become division and region together, close bracket and press Enter. And we get a unique combination of the two. In the unique function, you also have additional arguments that might come in handy. So for example, let's say you wanted to grab the apps that only occur once in this dataset. That's where the third argument exactly once comes into play. So the array is my list here. There is an argument in between and it's called by column. This is whether my data is organized by columns or by rows. I want to return unique rows exactly once. If it's true, it returns items that appear exactly once. Otherwise, it returns every distinct item. This is the default. This is what we get here. So in this case, I'm going to go with true, because let's say I want to get the apps that only occur once. And that's my list. Just to compare, let's get the unique list of apps close bracket and press Enter. Some of these apps occur more than once but this gives me a distinct list of values. Whereas this one gives me the items that occurred exactly once. Now, all of this is dynamic because if I end up having another division here, so let's say health and I press Enter. Notice that this one got expanded and I'm lucky that it's not going over my other spilled range because otherwise I'm going to end up with a hash spill error. Number three, XLOOKUP, XLOOKUP is the new and improved version of VLOOKUP. It's more flexible and it's super easy to use. So let's say, I want to select an app from here and I want to get the division from this list and the profit from this list. I can easily do that with XLOOKUP. My lookup value is this one right here. The lookup array is the range where this value is sitting in. So it's sitting in this column here. Next is what do I want returned? I want division returned. So notice division is to the left-hand side of the app's column. That doesn't matter for XLOOKUP. All I have to do is close bracket and press Enter. And I get game for Hackrr. In case Hackrr is missing, I am going to get an error, N/A. This is where you can make use of the next argument in XLOOKUP. If it's not found, what do you want to see? So I'm going to put missing here. And the error is automatically updated to my text here. So you don't have to put it inside the F error, or if N/A function. I'm just going to put Hackrr back here to see the correct division. Let's now get profit. I'll go with XLOOKUP. My lookup value is here. The lookup array is wherever I have my app which is sitting right here and the return array is what I want returned. And it only select this. So notice with XLOOKUP, what you want returned is the only column that you need to select. You don't have to select the entire range. This makes it much easier to write the lookup function. Now, all I have to do is close bracket and press Enter. And I get the profit for Hackrr. All of this is of course dynamic. If I switch to Accord, I get this information
updated automatically. Number four, the filter function. With the filter function, you get to dynamically filter your results based on any condition you want. So let's see here. I want to filter my results based on who earns more than 90,000. I can start off with filter. First thing I need is the array. This is the range I want to get back. So let's say I want both name and yearly salary back then is the include argument. I want to include every row where yearly salary is greater than 90,000? Now, I can't just reference 90,000 alone. I need to compare a range with this number. That range is my yearly salary range. If this is greater than the number I have in the cell. Now, when I close bracket and press Enter, I get my list spilling into the next rows. The formatting is not coming with. So I'm going to copy and paste it with the format painter. Now, this is dynamic. If I change this to 30,000, my list automatically updates. Now, you might be wondering, can we also sort this? Yes, we can put the filter function inside the sort function. The array that we want to sort is our filtered results. The sort index, let's say we want to sort by salary that's index number two, and the sort order, I'm going to go with minus one for descending, close bracket and press Enter, and I get my filtered results automatically sorted. If anything changes in my list here, so for example, James ends up earning 70,000, he's going to automatically pop up in my list. Number five, the sequence function. Sequence is an interesting function that gives you numbers in the order that you want. So let's say I want numbers ranging from one to 10 in each cell. All I have to do four rows is type in 10, close bracket, press Enter, and I get one to 10 written here. Now, if you take a look at the other arguments we have, let's say I wanted numbers to go to the next column. I'm going to put two for columns. Start is the number we want to start from. Default is one. That's why when I press Enter and it only had 10 in there, it started from one. Step is the accumulator. Default is one. So let's say, if I put a two here as my start and as my step I put a three, close bracket, press Enter. I get my start number from two, it's increasing by three. It's spreading to two columns and 10 rows. Now, how can you practically use this function in your reports? Well, let's say you have a dynamic unique list of your regions for example. Now, you want to add numbering to this. I can use the sequence function for this for the number of rows, instead of fixing it manually, I can make it dynamic and use the COUNTA function because I'm counting texts. I want to count my unique list here. That's sitting in G3 and I want it to spill to make sure everything is included. So I'm going to go with hash. Close and close, press Enter. I have my dynamic list. If my range changes to apps here, notice my sequence function automatically updates. The sequence can do a lot more than dynamic indexes. And I have a separate video on this. I'll add the link to the description. That was my list. There were more functions that were introduced recently but I just picked the most common ones which I think will help you this year when you're doing data analysis in Excel. My favorite function is the filter function. What about yours? Which one is your favorite one? And was there a function that you didn't know that was on my list? Let me know in the comments below. As usual, thank you for watching. If you like what you see, don't forget to give that thumbs up before you leave and also consider subscribing if you're not subscribed to this channel. (upbeat music)
