There's a wide new set of features and functions that they release that simply make our lives easier when using Excel. So in this next hour, an hour and a half or so, we will be covering quite a bit new functionalities, new functions, and some old ones; talk about the enhancements they have for them. Let me show everyone the topic list that we have in store. [silence] Now, in this course,.
We're going to focus. It's a pretty expert-- We call it expert, but it's pretty focused on functions, on Advanced database functions. We're going to start right off learning about nesting, how to put functions together. And from there, learning about the basic database functions, like SUMIF and AVERAGEIF. And right after that, we start getting into some interesting topics, where interesting, I mean, new. Microsoft released an XLOOKUP..
VLOOKUP still there, but XLOOKUP has alleviates a lot of those small little rules and guidelines we have to follow when filling out the VLOOKUP. Things about the lookup value having to be a place to certain area, things about your range of lookup having to be set to true or false depending on your exact match mode. A lot of that has been alleviated for us. There are also some really cool array functions that I just want to spend some time talking about. The unique sort, sort by and filter functions,.
Where I have a wide variety of examples that we're going to go through using these functions to find a unique list, to sort a list, to sort a list by particular column of values, or to filter a list. Now, towards the end, we're going to start learning about some other Advanced functions. One of them being LET and that essentially allows us to build aliases within functions. We have a value, we can give it a name..
And we can reuse that name in other parts of the formula. Now, once we cover functions, I do want to talk about some functionalities and commands that we have like the consolidate tool. It's been there for quite a while but it's a good little refresher that we'll talk about. When we use it and how we can use it. And we'll wrap up this session talking about macros. A quick intro to them, how you can turn on the developer tab, how you can record your first macro, and run your first macro..
Now, if you do have any questions you want answered by me, Faz, or any other of our instructors, don't forget to join off-site. It's a great community and I'll have the link for that inside the description as well. Now, as always, keep in mind, if you do need to download the exercise files to this course, so that you want to practice and get a little hands-on with these topics that were covering, you'll find them in the video description as well. So I am pretty excited here to get started..
We're going to be covering Excel expert with these set of topics. Don't forget to download the exercise file inside the description before we get started. But let's start. I'm going to go ahead and open up my exercise file and begin. [silence] I'm over here inside of the Excel workbook. That's provided with the course. And I'm looking at a particular worksheet right now. I'm looking at a worksheet called IF Functions. The very front to the workbook..
So, if you've open the workbook and you notice you're not on the front, make sure you're on the IF Function worksheet before you do begin. But let me start by describing what I'm looking at here, because I'm actually looking at some sales data from February of 2021. And it's tracking these weekly sales from all of our sales people that we have. And it's calculating all their totals. It looks like we found some metrics here.
About the biggest sale, the smallest sale. And whoever designed this sheet is actually using it to figure out whether or not these sales people have met their quota and have reached the monthly goal of 34,000. Now, depending on whether or not, they do reach that goal, these sales people do get a 5% bonus. And this all done using the IF function,.
Where they're comparing 2 cells. And depending on if the answer is true, It's going to say one thing in a cell, and depending on if the answer is false, it's going to say something else inside of a cell. Very common function, we use in Excel. But on this sheet here, I have one example left that I haven't filled out..
And it's asking me to check whether or not the department,
This is our department here, has reached and met the qualifications for the $850 bonus per person. Now, there are 2 qualifications they have to meet. Everybody has to have a total sales of 200,000 and they have to have both. The group as a whole has to have 9000 or more, per week in sales..So an average of 9000 or more per week in sales. Now, in order to solve this, this is another IF, where we put an equal sign in the cell, and we type in the letter I and I'm just going to click tab, [mouse clicks] to fill in the rest of the function. We'll be doing that for most of our functions today. But the IF function essentially allows us to do 3, what they call arguments. The Logical test that checks whether.
Something can either be true or false. And if it ends up being true, we have a value IF True argument that we can fill out. And a value, IF False, Now, this logical test will only let you compare one thing. So right now, this Department as a whole must have a total sales of at least 200,000. [mouse clicks] Greater than or equal to 200,000. That's filled up my position for the argument. So the IF function only allows me to do one logical test..
In fact, if I try to do another one, [typing sound] and I end up putting the word [typing sound] Pass if its true, and the word Fail, if it's false. If I end up doing this it's going to give me a message saying, That 'You've entered too many arguments' Because the IF function, expect 3, and here I am giving it 4. [typing sound].
To 4 arguments. So I want to introduce a concept. Known as nesting. [typing sound] Where instead of actually putting in the argument that you have, like the logical test argument, we're going to replace that with another function. This function that I want to talk about is called AND It checks whether all the arguments are true, and returns true if all of the arguments that you've put inside of the function are true..
So, it allows us to do multiple logical tests that allows to do multiple of those things that the IF function needs. So if I go ahead and compare this cell, the F12 is greater than or equal to 200,000. [typing sound] I can hit comma and my AND function will allow me to continue and do another logical test. [typing sound] I can close out the AND function parentheses. And now, I've completely filled out.
A logical test with two arguments in it, but in the eyes of Excel, it sees it as one. I'm going to hit comma here. And now for the value, IF True, I'm going to put the word Pass [typing sound] and hit comma here. And since these are words which we call strings, they have to be put in quotes. And I'm going to close the final parentheses there. So now that I have my 3 arguments, I have my big old logical test that I've nested, they call this nesting..
And I have my pass argument, [typing sound] and if anything doesn't pass, it will give me the fail. So I've satisfied all the requirements including mine of having both of those logical tests to determine whether or not they've met the total sales and the average sale requirement. Now, if I were to hit enter here, I'm going to see that It says fail, simply because of the fact that I did not, breach my average sales of 9,000..
However, though, I do want to mention- I'm going to go into this formula. And inside the formula, I'm going to zoom in here, instead of using an AND, [typing sound] we can replace it with an OR. And the OR will just check if one argument is True. So, if one of these are True, [typing sound] it will still determine the Pass. That's the OR. [typing sound] But if we switch this back to the AND that we originally had,.
[typing sound] it actually needs to see both of these True.
Otherwise it's going to give a Fail. [typing sound] I do recommend you give this a try. Yeah. I'll leave the formula on the screen if you want to pause the video here. Try out the formula, see if you can do it without actually copying the formula, So you can figure it out, logically. And if you need a little guidance, come back to the screen and take a poke at it..And once you filled it out go ahead and press play and we'll talk about some more database functions. Now that we're getting in the mindset of filling out functions, learning of the syntax, of how to do that in Excel. Go ahead and have fun. Don't forget to come back. [silence] Now, just to get started with this expert session, I want to start talking about some database functions that we've always had. [mouse clicks] I'm over here, looking at these quarter 1 expenses.
For yet again the Pear Company. We were tracking all the divisions, and all the categories, and how much our total expenses were. Now, I want to use this sheet to figure out and look up my total expenses, for all of the software that I've been purchasing. Let's start out with that. So I actually want to sum up the total expense, every time I see the word software.
Going down this entire table or list of information. Now, I want to sum this up, if it's in the category software. So let me use a very known function. I'm gonna put an equal sign in here. Any time we use a function we have to use an equal sign but I'm going to use a sum IF. This is actually going to add cells specified by a criteria or a condition that I choose..
[mouse clicks] Lucky for us, there's only 3 arguments that I have to fill out. A range, a criteria, and an optional argument. It's in brackets, it means optional, A sum range the values you want added. Now, I do admit, it's only optional when the range itself are numerical values, but in my case, it's not. So I get to essentially give Excel a range of options that I can search through..
So, I wanted to search through my entire category column. .And I'm going to do that using keyboard shortcuts. [typing sound] I'm going to use the keyboard shortcut, Ctrl shift and a down arrow to select the entire column for myself. Really easily. You never know how large these things are. [typing sounds] Great. So I end up getting the range. That's the list of all of my possible options. And then I hit comma.
To move over the syntax to search for criteria. That's what I want next. And when I end up clicking on the cell that holds the criteria I want, I 14. It selects that as the criteria. Now it ends up specifying, what column or what values do you want me to sum once I find this value, the criteria value category software. In our case, we want to do the total expenses. [typing sound] Now keep in mind.
Practice your shortcuts here. [typing sound] Ctrl, shift, and a down arrow. That will go ahead and grab the entire column or it will grow until it meets a blank. Nice. So it looks like we do have all 3 of our arguments filled out. [typing sounds] Where we have a range, a list of all possible options. We have a criteria, it's kind of covered now but the word software is in there..
And then we have something we want to sum up. So there's quite a bit going on here, 3 things going on here. And if I were to hit enter, [typing sound] It's going to go ahead and show me that I've spent about 17 grand on software in quarter one. Now, of course, I'm a big fan of these keyboard shortcuts. [typing sound] If you wanted to add the dollar sign there, you can do that by hitting Ctrl, shift, and the number 4. It'll change the data type of the cell to currency or accounting [typing sound] So if I go over to my home tab, you'll see that.
It's now at currency. You know, before you practice this, I want you to see how similar these database functions are, because in this next example here below it actually want to find the average cost for rent. So, I want to find the average of the total expenses. If, if it's in the category rent. So, there's another formula that is very similar in nature, known as AVERAGEIF..
I'm going to hit tab here and the AVERAGEIf essentially returns well it asks for 3 things. same 3 things that we asked for last time, but it returns the average of the expense. So this time my range is going to be the category. Again ctrl, shift, down, comma, and of course, my criteria this time, it's going to be I20. You're going to have a hard time clicking on that cell. It's covered, but I can still type the word I20. And it'll get picked up,.
And I can go ahead and hit comma here and I can select a total expenses. I'm going to close this parentheses here. This time, I'm going to hit Ctrl, enter. So that it puts the value into the cell without leaving the cell. The green cell address box doesn't move down when you hit Ctrl, enter. Now I can hit Ctrl, shift, 4 [mouse clicks] to quickly add the currency format to that value..
Doing good, doing good. I still have one more to show you. And that's this top example or I'm saving for last because I want to look up the expenses for my category software if it's in the East Division. So I essentially want to return this one single value. [typing sound] In this cell here, but we will just pretend that we didn't see it right there. We''ll pretend that it was hidden..
Another example of using the SUMIF is using SUMIFS with an S at the end. This allows you to do a set of conditions or criterias It's kind of backwards now. It's going to ask for the total expenses first. I'm going to hit Ctrl, Shift, down, and when we hit comma to move over to the next argument, that's where it starts asking for the first range, the first list of options,.
And the criteria you're looking for. So, I'm going to pick the first range as division. Ctrl, Shift, down the entire column. And when I hit comma, I can select the criteria to be East, I8. And now, when I hit comma here, I have a range number 2, that I can fill out with a criteria number 2 afterwards, which will be for my categories. [typing sound] And I believe that is J8 since it's covered by a virtual wall..
I'm going to have to type it in J8. If I were to go ahead and hit enter here, I guess I already left the currency format for this one. Notice how Enter moved down a cell this time If I would've hit Ctrl, Enter it would've have stayed on the same cell. But now, I got that one value spitting out as my total expense for software in the East Division. So these are database functions and up next, we're going to learn about the XLOOKUP database function..
But I do want you to pause the video and give this a try. I want you to try filling out these 3 database functions. If you're wondering what the names of these are, the first one was SUMIF [typing sound] AVERAGEIF, and the final one was SUMIFS. [typing sound] Come on and give this a try and don't forget to come back. We'll talk about the XLOOKUP function. [silence] So I'm over here on a worksheet that I've labeled as XLOOKUP,.
And I'm going to zoom out just a bit. Because it's a master list of employee information that is listed on this sheet and on the top is a Lookup. Now I've already done one single Lookup where I've actually used a employees ID number to retrieve their last name Smith. Now, in order to do this, I used a VLOOKUP. Where with the VLOOKUP, I used a Lookup value..
An employee ID, and I used a table array to search through, and I returned the second column of the table array. With an exact match. That's a VLOOKUP. There's a lot of little rules that we have to remember. But it's been around for quite some time. [mouse clicks] Where I can actually use a lookup value. The thing about the lookup value is it has to be in the very first column of the data, otherwise the VLOOKUP doesn't work. So now, that I have the lookup value, let me highlight the data.
Known as the table array. Ctrl, shift to the right. [typing sound] And ctrl, shift, down. Now it's going to ask for what column I actually want return. And in our case will, in my case, I want to return the last name. Let's call him number 2 based off the highlight that we have. [typing sound] So if we can't add any more columns or rows now because as soon as I add.
A new column, it's going to move all my column index numbers. And then I have to actually add in false otherwise it defaults to true and we'll find an approximate match. So that's a VLOOKUP. It works, but there's a lot of rules to it. [silence So instead what they released, I'm going to delete this now. Is an XLOOKUP function. For all Office 365 subscribers.
And now office 2021 subscribers as well. And I believe Office 2019 also has this one. So if I do an Equal and I type in XL, I'll see that XLOOKUP function pop up for my database of functions that I have. And when I click on it, it's actually going to show me the arguments that I need to fill out. There are six of them. [typing sound] But the last three are optional parameters, and we don't need to fill.
Out every time. [mouse clicks] [typing sound] Now, the very first argument is very similar to the VLOOKUP where it looks for a lookup value. But this time, it doesn't matter if the lookup value is located in the middle of the table, and the first column of the table, at the end of the table. It just does not matter. There's no rule or guideline for that, but it will ask you for once you do choose, a lookup value. To actually highlight the column. It can use to search through.
To find that lookup value. So I'm going to actually highlight this first column. Ctrl, shift, down. So I've given it a lookup value and I've also given it a column to look through to find the lookup value. So it will find this value in the first position of the column, that's what's going to happen. It's going to find it in the inside of A10. First position is A10. Now, when you hit comma, it's going to ask for a return array. What column do you want me to return.
With the matching row value? So if it found row number one, what column of row one do you want me to return? You want me to return the last name, the first name, department? So on and so forth. But in my case I wanted last name. [typing sound] And if I go ahead and close the parentheses right here, I've already filled out all three of the required arguments. This will do exactly what my VLOOKUP did and will return an exact match of Smith..
I know gets exciting. I'm sure we all appreciate this much more than the VLOOKUP. But let's do another example here. Because what if we have an employee ID that doesn't exist? This N/A. That means not available. [typing sound] So if I do a XLOOKUP here and I use this as a lookup value, it will never find it in this list. And it will never return a first name because it will never find one. But there is a argument that's optional called.
If not found that I can write not in the list. [typing sound] It will return that phrase if it doesn't find a match. [mouse clicks] I also can do a different match mode. By default it will do an exact match but there are exact match or the next smaller item, what the negative one. Or you can put a 1 for an exact match or the next larger item compared to the lookup value. And you can also do a number two for a wildcard character match where you put a star or a question mark, or a tilde for a certain amount of characters that you're.
Searching for. But you don't really know what the characters are You'll see more about wildcards in my Excel 2021 XLOOOKUP video as well. We will compare XLOOKUP and VLOOKUP together. For this one, I'm going to put exact match the default. And if I ever needed to, I can have the search start from the top or the bottom of the list or even do a binary search by default. It's a first to last there, I'm going to go ahead and close out this parentheses and that's actually an XLOOKUP with all six of the arguments filled out..
[mouse clicks] We'll do one more for good. Keeping here. The department XLOOKUP. But this time before I do it, I'm going to change it back to the employee ID 1054. [typing sound] Now my lookup value of course, is my employee ID, doesn't matter what I use for the lookup value as long as it's a unique value and now I have to specify where it can find it from..