Microsoft Excel 2021/365 Tutorial: 3.5+ Hours of Advanced Excel Training Course

[Music] simon says subscribe and click on the bell icon to receive notifications we've made the files the instructor uses in this tutorial available for free just click the link below in the video details to get these hello everyone and a very warm welcome to this course on excel 2021 advanced my name is debra ashby and i'm an id trainer and microsoft subject matter expert with over 25 years experience of using and training excel to students customers and delegates all over the world and i am very much looking forward to hosting this course for you and what a course we have for you if you're looking to up skill and move out of the catch all intermediate excel user category and become an absolute guru then this is the course for you and we're going to dive straight into the good stuff first we're going to start out by taking a look at the new dynamic array functions available in excel 2021 so things like unique filter sort sort by x lookup and so many more we'll see how to combine functions together and create complex lookups we'll delve into the world of statistical and math functions and explore functions like round aggregate rank and count blank and if your thirst for formulas is not quenched by that stage i'm going to show you two brand new game changing excel functions let and lambda the latter lets you create your own functions without the need for any programming knowledge it's truly brilliant now of course no advanced course would be complete without at least taking a minor tour through some of the more advanced features of pivot tables and pivot chart techniques and i'm going to show you how you can add more interaction into your worksheets by using things like form controls and we'll finish out this course by taking a look at importing data from external sources transforming it in power query and we'll also predict the future with forecasting and learn the basics of automating our tedious tasks using macros and vba so there is so much for us to cover now all of the files that i use in the videos are available for you to download from the course files folder so if you want to follow along with me then you can also at the end of each section there is an exercise so you can practice the skills that you've learned so with all that said and without further ado grab yourself a drink and i will see you in the first lesson the first exercise in this course is less of an exercise and more of a way to set ourselves up for success throughout this course so i just want you to check a few prerequisites before we begin now the first thing you need to make sure you do is that you download the course and the exercise files to your pc you'll find them in a folder within this course just make sure that you save them off to a folder on your pc that's easily accessible the second thing you need to check is the skill level of this course this course is very much designed for advanced excel users so we won't be covering a lot of the basic things so basic formulas things like customizing the quick access toolbar and using basic functionality like cut copy paste so make sure that you do at least have an intermediate level knowledge of excel before attempting to run through this course and if you need to we do have an excel 2021 for beginners and an excel 2021 intermediate course for you to review if required and finally ensure that you're using the correct version there are so many different versions of excel out there this course is very much designed for excel 2021 which is the latest standalone version from microsoft i will say that if you've come to this course and you have a microsoft 365 account then the course is pretty much the same because the standalone version of excel 2021 was really designed to bring it in line with the features and functionality in the microsoft 365 version of excel so once you've checked all of those things you are pretty much good to go for the next section you'll want to download the course exercise files click the link below in the video description to get these you can also scroll through the details to find time stamps for each section in this course if you're enjoying this training please leave us a comment in 2018 microsoft rolled out a version of excel with six new dynamic array functions and a new calculation engine that allows us to write one formula and get multiple results and it really is a game changer because it eliminates the need for the cse method to calculate array formulas and cse stands for control shift enter being able to write a single formula and getting an array of dynamic results means writing fewer formulas and that also means fewer errors and what it generally leads to is more trustworthy spreadsheets so dynamic arrays make formulas much easier to write and they solve some really hard problems in excel now it is worth noting that dynamic arrays and all of the new functions are only available in excel for microsoft 365 so if you have a microsoft 365 subscription you should have access to these new dynamic arrays and also in this version of excel excel 2021 and as part of the dynamic array update excel now includes six new functions which leverage the power of dynamic arrays and you can see the six new dynamic array functions listed out on the slide just here so we have sequence which will generate an array of sequential numbers rand array which will generate an array of random numbers unique which is one of my favorite new dynamic arrays which will extract unique values from a list or range we have a sort function which will sort a range by column sort by which is going to sort a range by another range or another array and finally we have the filter function which will filter data and return matching records so what we're going to focus on in this section is just going through each of these new dynamic arrays so you understand how they work and how they can be helpful to you before we begin it's important to understand spills and arrays because this is probably going to be new terminology for you in excel formulas that return multiple values will spill these values directly onto the worksheet now that might not make too much sense to you at the moment so let's take a look at a couple of examples so i'm going to show you an example of just a regular formula first of all or a standard formula so in this first little table we have some employee names and what i want to do is i'm just going to return their department from this little table that we have over on the right hand side so what i could do here is just a very simple vlookup so we're going to type equals vlookup our lookup value is going to be the employee name we're going to look up that value in this table over here and you can see that because i've named my table employee location that's what i'm getting in the formula as opposed to the cell references i need to provide the column index number so what information do i want to return well i want to return the department which is column number two and am i doing an approximate or an exact match of the lookup value the employee name well i'm doing an exact match so i can put zero or false on the end here so i'm just going to go with zero let's close the bracket and hit enter and it pulls back it as the department and what i can do here is just drag this down to fill out those other departments for the rest of the employees now this is what we call a standard excel formula when we construct it the answer just appears in one cell and if we want to copy that to other cells we have to use our fill handle or other methods in excel in order to do that dynamic array formulas work in a very different way which you're going to see in a moment before we get on to that let's just take a look at one other type of formula and that is a cse array and this might be something that you're already aware of the cse part stands for control shift enter because it's always been in excel if we want to perform multiple calculations within one formula we need to press ctrl shift enter to make that into an array formula and this is something that historically many people have found quite confusing so let's have a look at a cse array so you can understand exactly what they do if you're not sure now in this little table we just have again some basic products we have the quantity that's been sold and we have the price of each of those products so if i want to find out what the overall total is of all of the products this would involve two different calculations because i need to multiply the quantity by the price for the first product and i need to do that for all of these different products and then i need to do a sum calculation to add up all of those totals now we could do this calculation all in one go using a cse array so what i'm going to do here is we're going to say equals sum and all i would need to do is select all of the quantities multiplied by all of the prices now in order to get this to work and to calculate correctly we would need to make this a cse array which means we need to press ctrl shift enter and not just enter so let's do that ctrl shift enter and we get our total so cse control shift enter allows us to combine multiple calculations in one formula and another thing with these cse arrays you can always tell if you do have a cse formula because if you look at the formula bar you'll notice that your formula is encased in these curly brackets so that tells you that it's an array formula now bearing in mind both of these examples that we've looked at this is going to give you a better understanding of how spills work with dynamic arrays and what i'm going to show you is a very basic example of a filter dynamic array now i'm going to move through this section fairly quickly but don't worry if you're not really understanding what i'm doing because we do have an entire lesson dedicated to this filter function a bit later on and we'll go through it slowly and we'll break it down really what i'm trying to get you to understand in this lesson is just the concept of how spills work so what we're going to do is we're going to return the employee the department and the block from this table depending on what we have selected in column k so we're going to use one of the new dynamic array formulas and that is filter so let's type it in we need to select our array which is going to be everything in this table and then i need to tell excel what i want to include so i'm just going to filter for all of the employees in the marketing department so we're going to select the department array and that has to equal what we have in cell k for and that's all i'm going to do there are lots of other things we can do with filter and lots of other arguments we can use but just for this basic example let's close our fabric and hit enter notice the results spill into multiple cells so i don't just have one formula in one cell like i did when i was doing the vlookup and this is effectively a cse array because i'm performing multiple calculations in one formula and the results have spilled down into multiple cells if i was to change this department to let's say sales and hit enter those results will update so everything is fully dynamic and if we click in our results notice that we get this blue border around the outside this is effectively showing us what our spill range is that is the terminology and this spill range will expand and contract where necessary when new values are added or removed so if i was to add another name to the bottom of this table everything should update so let's do that i'm going to go down to the bottom here let's just add adam lacy and we're going to put him in sales and what we should see is that this employees table will update and there we go he's added on to the end let's put him in the east block like so let's take a look at a couple of other examples of spills i'm going to jump across to the spills worksheet and notice that we have a little table just here and that's just listing out some first names and some last names of different employees now notice that the first names are in lowercase and the last names are in what we call proper case now i want everything in this first name column to be in proper case and i also want to sort this list of names into alphabetical order a to z so the first thing i'm going to do here is put this data into a table ctrl t let's click on ok and i'm going to change the colors because those aren't particularly nice let's just change those to a purple color now i'm not going to name my table for this example we'll just leave it on table 2 and let's tidy up this data so we're going to combine two things here we're going to use another new dynamic array called sort by and we're also going to make that first name column proper text so let's type in proper as well and we're going to select everything that we have in here close off our proper i'm now back into my sort by formula so i can choose which column i want to sort by so i want to sort by the last name comma and do i want to saw in ascending or descending order well i want to sort in ascending close off sort by hit enter now take a look at those results it's converted that first name into proper case the results have spilled across both columns and it's also sorted the list by the last name a to z now one other thing to notice is if you wanted to go in and edit this formula if i click in a cell down here within the spill range note the formula is kind of grayed out in the formula bar you always must go to the first cell that contains the formula in order to edit it then you can double click and you can make your changes now one other thing to note here is if i then wanted to do something like maybe a count on these names let's type in count a i'm going to select everything in this table notice what excel does take a look at that notation it's put a pound or a hash symbol in there and what that basically tells us is that we're referencing a dynamic array now if i was to press enter here i'm going to get a result of 22 because it's counting each of these individual items now that wouldn't be what i would want in this case i just want to know how many members of staff we have so i could go in here and i can do a divided by two and that's going to give me the correct result of 11. now what about if i was to add more names to this table and you'll notice at the bottom here i just have a little list so let's drag this up and because our original data is in a table that table is going to auto expand to accommodate those new names but take a look at what's happened just here we now have a spill error in column d now why do we have that if we click on it take a look at the range if you look at the blue lines around the outside you can see that because i have another table underneath there isn't enough room for these results to spill down so this can easily be fixed simply by moving this table out of the way so if we pick this up and let's just drag it over here now i get my spill results also notice that my count has updated as well so everything here is completely dynamic so that's kind of how spills work hopefully you can now understand that and see the difference between just a standard formula csc arrays and dynamic arrays in this lesson and the next few lessons we're going to be taking a closer look at the unique function because this is such an amazing formula for extracting unique values from a range of cells and this is something which i've always thought should be a lot simpler than it is in excel previous to the release of this new unique dynamic function it can be quite tricky to get a unique list of values from a range now with the introduction of unique the process is so much simpler so let's start out in this lesson by taking a look at a couple of basic examples of how unique works so in column a i have a list of countries and one thing you'll notice about this list of countries is that those entries are not unique so we have the uk repeated a few times we have the usa repeated a few times so on and so forth so if i just want to extract a unique list of all of the values from the range in column a i can use the unique dynamic function so let's click in cell c three we're going to type in equals and then use unique so let's start to type this in notice it comes up in the list we can press add tab key to select it and we have three arguments for this particular function now notice that the last two are optional because they are in square brackets and in this first basic example we're not going to use those optional arguments we're just going to go for the first one which is array so all this basically means is select the range that you want to extract the unique values from so we're going to select range a3 to a19 let's close off our unique function hit enter and like magic we now have a unique list that is a lot shorter than our original list because we're just seeing the nine unique country names notice that this is a dynamic function so we have this blue area around the outside showing me my dynamic array range now notice here that i don't have my list of country names in a table and if we take a look at the unique formula you can see it's just referencing a standard cell range a3 to a19 now there are so many advantages to putting your data into a table for example if this country list updates so let's add another country on to the end we're going to add brazil and hit enter notice that my unique list doesn't update however if i put this into a table then it means that when i change anything in column a or i add new countries to the bottom this unique list will also update so let's put this data into a table ctrl t yes my table has headers let's click on ok i'm going to leave the formatting as it is that's absolutely fine now notice this unique list hasn't automatically updated with that new entry for brazil as i'm still referencing just the cell range as opposed to the table so let's construct a unique again i'm going to delete the formula out let's go for equals unique this time when i select my range notice it's going to give me the table name now i didn't rename my table so it's just called table 1 and then we have the column heading in those square brackets so country i now have brazil on the bottom but if i add another country let's go for argentina hit enter notice that that now updates so if you really want to make everything completely dynamic it's always worth putting your data into a table just one of the many advantages of using tables in excel definitely one of my favorite things now another little tip here this range of cells is essentially a dynamic array and it might be that you want to use this data in other calculations or maybe you just don't want to have a formula underneath this list of values what we can do is we can throw away the underlying formula so we just have a unique list as plain text effectively so how do we do that well we do it using paste special so i'm going to select my unique list ctrl c to copy and if we go up to the home tab click the lower half of the paste button i'm going to choose to paste values only so when i click this let's press escape to deselect i now just have this as a plain text list if we take a look in cell c3 i no longer have the unique formula listed there now of course this means that it's not linked to this table anymore so if i add any other country names onto the bottoms let's add thailand it's not going to update but there are many situations where you just might want to use the unique formula to extract the list but you actually just want to have those plain values so don't forget about that little paste special trick now the final thing i want to show you in this first lesson is how we can use unique and count together so maybe i want to count the list of unique entries so we can combine both functions so we're going to start out with account a because we're going to be counting text and then we want to go straight into our unique formula now i could select this unique list but i'm going to select this one over here in the table let's close off unique close off our account hit enter and it's telling me that i have 12 entries which would be correct because this unique list contains 11 entries you can see that information down in the status bar we also added thailand which would make it 12. so we can combine unique with many other functions in excel to make it more useful and get the results that we want the unique function can essentially do two things it can extract unique values or it can extract distinct values so what is the difference between these two well distinct means i want all entries in the source data one time so for example if the uk is listed out three times in a range i only want to show it once so we're getting a list of unique values whereas unique means show everything in the source data that appears only one time so a good example of that in our data would be south africa that appears only once in this list and so that would be included in the results of the unique formula now you might be thinking to yourself well didn't we just use the unique formula to extract a distinct list of values we extracted all entries from our range of data regardless of how many times they're listed in the range well yes we did but in these examples we're going to be using more of the unique arguments so we're going to start with distinct but first of all we're going to put our data into a table control t yes my table has headers let's click on ok and i'm going to choose a different table style because i don't particularly like this one let's go for a dark one this time and i'm going to give my table a name so let's call it country underscore population and hit enter so now let's extract a distinct list first and then compare it to what we get when we extract a unique list so we're still using the unique function we're going to select our array so we're going to stick with extracting the country names and notice now because i've named that table i have the table name and then the column name comma let's move on to the next argument which is by column so do we want to return unique columns or unique rows well i want to return unique rows so we're going to have false in there comma now this is where we can choose if we're doing distinct or unique so a true argument will return the items that appear exactly once whereas a false argument will return every distinct item so we're doing distinct so let's type in false close the bracket hit enter and now we get a list of all of those distinct values let's compare this list to what happens when we use a different argument so we're going to do unique again we're going to select the same range we want to return unique rows so we still want a false just here but this time we want to return items that appear exactly once in the range so we're going to use true instead and let's hit enter so this list is much shorter because it's only returning countries that appear exactly once in the selected range whereas distinct will show all of the countries regardless of how many times they appear in the examples that we've looked at so far we've been using the unique function to extract just one list of values so we've been extracting a unique list of countries but in this lesson i just want to show you how simple it is to extract multiple pieces of criteria using unique and once again we can either extract distinct values or unique values so if we take a look at our table we have our list of countries but this time we have some additional information we have the population of each of those countries in column b and then the region or the continent that those countries belong to in column c and what i want to do is i want to extract all three of these columns or all three of these pieces of information into the table on the right hand side so once again the first thing i would probably do here is put this data in a table ctrl t let's click on ok and once again i'm going to change this table style let's just change this to something fairly plain in fact i don't think i'm going to have any table style this time let's name our table we're going to call this uh country underscore population underscore region and let's hit enter so now if we use unique we need to select our array now because i want to extract multiple pieces of criteria i want to extract all three columns my array is going to be all three columns so effectively the entire table if i only wanted to extract two columns i would just select two columns comma do i want to return unique columns or unique rows well i want to return unique rows so we need a false in here and this time i want to extract every distinct item so once again we're going to add a false close the bracket hit enter and those results spill down and it's going to pull through the other pieces of criteria and of course because we've put our information into a table i could add additional countries onto the bottom so let's add spain and it's going to update so everything is still completely dynamic now let's just undo just to get rid of spain and once again you can see that that disappears now if we change this i'm going to edit the formula remember you can only edit the formula in that first cell if i click somewhere else the formula is grayed out let's double click i'm going to change this last argument from false to true press enter and now we get that much shorter list again because it's only pulling out of this data countries that appear once and once again if i was to add spain onto the bottom because that does appear only once it's going to populate in this table so super simple to use unique and extract multiple pieces of criteria it doesn't just apply to one column so far in this section we've been using the unique function to extract values by row so in this lesson i'd just like to show you a couple of examples of how we can extract information by column so let's take a look at our information first of all so at the top here i have a small table and this table represents some crayon packs and each crayon pack contains a number of different colored crayons and we can see here for pack one all of the different crayons and there are six in each pack all of the different colors of those crayons and we have eight different packs with different color combinations and notice the difference here is that our information is running horizontally across the page so when we're using unique we want to extract our information by column as opposed to by row and this pretty much works in exactly the same way as the examples we've seen in the previous lessons we're just using a different argument so let's extract the distinct crayon packs first of all so we're going to type in equals and then we're going to go with unique press add tab key our array is going to be all of the crayons and notice i haven't put this in a table so i'm getting the cell range here instead of a table name let's press comma now this is where the difference lies in previous examples we've been using that false argument to return unique rows but this time we want to look down the columns we wanted to check each color in the pack to see if that is unique or not so we want to use the true argument just here comma and we want a distinct list so we're going to do a false argument just here close off the bracket hit enter and here are our results now i'm going to select all of this dynamic range and let's just move this along just so it's a bit easier to compare with the table above so the results that we have here are basically packs that are distinct now notice it's only brought back effectively six packs so that means that i have two packs that are the same in my data and i can see here that yes i do so pack two contains these crayons black purple green yellow red and blue and i also have the same combination of colored crayons in pack eight so instead of returning this twice it's just returned it once in my formula results so that accounts for one of the missing results but what about the other one well i can see here that pack one has this color combination and pack four has exactly the same color combination so again that is why we only have six results as opposed to eight so let's take a look at the final option here this time we're going to return all of the unique crayon packs so again this works in a fairly similar way equals unique our array is going to be our different colored crayons this time we're going to return the unique columns so we want a true argument in here now remember when you're adding these true or false arguments you can represent true with a one and false with a zero so if i put a one in there it's going to work exactly the same now this time i want to return the unique list so i want to return items that appear exactly once so this is a true argument as well i'm going to use a 1 for that also let's close the fair bracket hit enter and now i get an even smaller list so these are the crayon packs that appear exactly once in this table so it's basically omitted anything that is a repeat pack so it's brought back four packs of crayons we have a total of eight so four packs are essentially duplicates and that would be correct because again pack one and pack four are duplicated as are pack two and pack eight so extremely simple to search with that by column argument as opposed to by row it's time now to move on to talking about another dynamic array function and that is sort so most excel users are used to sorting lists of data we can simply click anywhere in our data and then on the data tab we have various different sort options we can sort a to z we can sort z to a or if we want to do a more complex sort we can sort by multiple columns so bearing that in mind why is it useful to have a specific function that allows us to sort our data well with a sort function it means that we're not restricted to keeping our data in one location we can basically sort it and place that data anywhere on the worksheet we can also use the sort function with other functions so maybe we want to extract a unique list of values from a range and then sort that list of values we can combine them both together because we're using functions so let's take a look at how a basic sort works now what i have here is a small table of data and this is very much inspired by the winter olympics that are currently on tv now i will say that these are not actual athletes bib numbers or medals that they achieved this is just some dummy data but that is where my inspiration for this example is coming from so we have in column a listed out some athletes we have the country that they're participating for we have their bib number and then we have the medal that they achieved and what i'm going to do first is just a very basic sort i want to output this information in the table on the right hand side but i want to sort the results a to z by the country so let's click in cell f4 and type in equals sort now notice with the sort function we have four arguments and three of those arguments are optional because we have those square brackets so let's take a look at this in its most basic form we're just going to use the one mandatory argument first of all and that is the array so if i just wanted to do a simple sort on the athlete name i could select the range close off the bracket hit enter and it's just going to sort those names by the default sort which is a to z now we want to do something a little bit more complex than this so let's delete it out we're going to go back into sort and we're going to use all of the arguments now i want to sort all of the data so my array is going to be everything that we have in this table i can then choose the column that i want to sort this data by this is kind of similar to vlookup in a way in that the columns in the table are numbered from left to right so the athlete column is column number one country is column number two bib number is three medal is four so depending on which column you want to sort by that is the sort index that you would use so i want to sort this list by country so my sort index is column number two my next argument is the sort order so if i want to sort in ascending or descending order now i want to sort in ascending order so that is a one comma and our final argument is just do we want to sort by the column or by the row well i want to sort by the row so we're going to type in false just here close off the bracket let's hit enter and now i get those results sorted in ascending order by country so we have australia at the top going all the way down to usa at the bottom so really nice and simple and if at this stage i decided that i wanted to maybe sort by a completely different column maybe i want to sort in descending order by the bib number i can simply edit the formula so i'm going to click on this first cell let's edit it up in the formula bar the first argument here is fine that is just the array the sort index well column number two that needs to be changed because we now want to sort by the bib number so that's going to be column number three now the sort order if we want to sort in descending order that is minus one and then we still want the false argument on the end because we're sorting by row so now if i press enter all of those are going to update and you can see that it's now sorted by bib number in descending order so we have the highest number bib number at the top all the way down to the lowest so pretty straightforward now let's just go down to this second table that we need to complete now notice here it just says sort by country descending order nice and easy we've already done this so let's do this and see what we get our array again is going to be everything in this table sort index where we want to sort by the country so that is column number two as sort order we want to sort them in descending order so minus one and we want to sort by row so we have a false or a zero argument on the end now what happens when i press enter i'm getting a spill error and if you remember back to one of our earlier lessons in general we get this if there isn't enough room for those results to spill down and i can see that if i click on cell a22 take a look at that blue border it's kind of overlapping the table that i have underneath so what i really need to do here to get these to spill properly is move this data out the way so let's just grab this and move it and now all of my results update and you can see that they're sorted in descending order by country now the final example i want to show you here is how we can sort by multiple different columns so so far we've simply been sorting by one column or another so the first example we sorted by bib number the second one we sorted by country but what if i now want to sort the list by country in ascending order and then by bib number in descending order how do we add in multiple pieces of sort criteria well this is actually pretty straightforward so let's go back into our sort function our array is still going to be the same thing so we're going to select everything now we need to provide our sort index and because we want to sort by two different columns we want to sort by country and we want to sort by bib number we need to put these in curly brackets so we want our first curly bracket and then we specify all of the columns that we want to sort by so the country column is column number two but we also want to sort by the bib number column which is column number three let's close our curly bracket and then we can specify the saw order for both of these columns so again we need a curly bracket we want to sort by the country column in ascending order so ascending is 1 and we want to sort by the bib number in descending order which is minus one let's close off that curly bracket comma do we want to sort by column or by row we want to sort by row so we need a false argument on the end and we can close off as sort so let's just review that formula we're selecting the range that we want to sort we're then putting inside the first pair of curly brackets the columns that we want to sort by and then in the second pair of curly brackets we're specifying the sort order for each of those columns let's press enter and take a look at our results so we're sorting by country in ascending so i should have a to z which is correct and then we're sorting by bib number and that is in descending order so if we take a look at canada's bib numbers for example we should find that they go from the highest to the lowest and of course with this formula again if we take a look at it in the formula bar if you wanted to sort by three or four different columns you could simply add more column numbers and more sort orders in between these curly brackets so pretty nice and simple to be able to use the sort function and sort by multiple columns in this example we're going to use the sort function to sort data horizontally and in this lesson in the process of doing this we're going to be utilizing a couple of other functions that we haven't yet spoken about so before we begin let's start out by understanding the data so here i have a small table we have some movie names listed in column a and then we have actors that have been in each of these movies and i've just listed out four actors to keep this data set manageable now what am i aiming to do with this data well what i want to do is effectively find out which actors have been in the same movie together more than once now if i have a list of data that looks like this it's quite difficult to be able to determine that simply by using our eyes so we're going to need to use a formula or a couple of different formulas and techniques in excel to help us out with this and because there are a few steps i've listed those out at the bottom so the first thing i want to do is i want to take this first movie la la land and i can see the four actors that we have for that particular movie i want to take this range of actors and i want to sort the data horizontally in ascending order so effectively i should have emma stone listed first so over in this little table we're going to type in equals sort we're going to open our bracket and this time the only thing we want to sort is this first row b4 to e4 now do i need to provide a sort index here am i sorting on any particular column no i'm not so i don't have to add anything in for my saw index i can just press another comma to move on to the next argument now do i want to sort these horizontally in ascending or descending order well i want to sort them in ascending order so we need a one just here comma am i sorting by row or am i sorting by column well this time i'm sorting by column so this needs to be a true argument on the end let's close off our bracket hit enter and see what we get so now i basically have those four actors sorted a to z now what i can do is just select all of these and use my fill handle and drag down and it's going to apply that same sort to all of the actors in the rest of the movies and notice that it's sorting by the actor's first name as opposed to their last name this still isn't the solution to what i'm trying to find out which is which actors have been in the same movie together more than once so we need to do a few more things here to achieve our goal now the next thing i'm going to do i'm going to take this horizontally sorted list of actors and i'm going to basically join the text in each row together and for this i'm going to use the text join function and text join is kind of similar in many ways to something like concatenate or concat as it's now known it will join pieces of text in different cells together but we have a few different arguments that we can use now a way that i can do this is to join all of the text together so it's in one cell i can then sort that data and highlight the duplicate cells so quite a few different steps there so what i'm going to do is i'm going to edit the sort formula and we're going to join all of these actors names together so they're effectively all in one cell and we're going to do that by adding in the text join function now the first argument of text join is to specify a delimiter so do we want our actors separated with a space or a comma or a dash well i don't want them separated with anything i just want them in a horizontal list next to each other so i'm just going to press comma to move on to the next argument do i want to ignore empty cells well i don't have any empty cells in my data set but it's not going to hurt for to do a true argument just there comma now the next argument is text one now text one is going to be generated by the sort so i can click right at the end here and i just need to close off my text join let's hit enter and now all i need to do is double click to copy this down and would you take a look at that i now essentially have all of the actors together in one cell so now that i have all this text joined together i'm going to select it all ctrl c and i'm just going to paste in and i'm going to use that paste special technique to paste just the values over the top so now i've essentially thrown away the underlying text join and sort formula now that i have my data like this i can use my regular sorting options on the data tab and i'm going to sort it a to z now this makes it quite a bit easier for me to see all of the actors that have been in the same movie and because we have a fairly short list i can see that right at the bottom here those names are all the same and the same for this one just here if you wanted to make this really obvious you could apply some conditional formatting which will highlight duplicate values so let's select our data up to conditional formatting and we're going to select duplicate values i'm happy with this fill of light red fill with dark red text click on ok and now i can see the actors that have been in the same movies so these four actors have all been in the movies the interview and super bad and these four actors have all been in burn after reading and insurgent so quite a few steps to get to this place but this is something that can come in very useful when you're working with real life data in the last few lessons we've been taking a look at how we can use the sort function to sort data in excel and now it's time to move on to taking a look at another new dynamic function which is very similar to sort and that is the sort by function so what exactly is the difference between sort and sort by well if you recall in the sorts that we've done using the sort function we can either choose to sort in ascending or descending order now sort by differs from this because instead of selecting ascending or descending we can choose another range or another array to sort by instead so let's take a look at an example now the data set i'm using in this example is basically some very well known books we have the book title in column a we have the author of the book in column b we have the genre that the book falls into in column c we have when it was first published in column d and then in columns e to i we have the number of one to five star ratings that the book has received so we're going to use the sort by function to sort this data so let's take a look at sort by let's explore the arguments and let's take a look at a few different examples so let's type in equals sort by now for this one we have three arguments now there are actually more arguments than three if you take a look at the end there we have those three little dots which basically tells us that there are more now the last argument and all of the ones that we can't see are all optional arguments the only two mandatory arguments are array and by array1 so array is the data that we want to sort if you take a look just above i have two column headings book and author so that is the only information i'm interested in seeing so my array is simply going to be the book and the author columns comma now i can choose a column to sort this by so instead of ascending or descending like we provide when we're doing just a regular sort this time we can sort by a column so maybe i want to sort this data by the number of five star reviews so i'm going to choose the five star column comma and now i can choose how i want to sort this column so do i want to have the most five star reviews at the top or the least five star reviews at the top well i'm going to sort this in descending order so we have a minus one now if i was to press comma here notice that it goes on to buy array too so we can sort or we can use this function to sort our range on multiple different columns now we're going to do that in a moment for the time being let's just keep it simple and just sort by the 5 star column let's close off our sort by hit enter and now you can see what we get so at the top here we have harry potter and the prisoner of azkaban and if i take a look in my data set let's take a look at the five star reviews well i can see that this one has a thousand five star reviews and a quick look down through the rest of the numbers that is definitely the highest so i don't necessarily have to have the value the number of five star reviews output into this table in order to sort the data by this column so this makes it very simple for me to see for example the top five books based on five star reviews let's do another sort i'm going to delete this out i'm going to go into sort by again again the array is just the book and the author because that's the only information that i'm interested in we can choose what we want to sort by so maybe this time i want to sort by genre and i want to sort in ascending order of the genre comma and now i want to sort by something else so maybe i want to see which is the worst performing genre so which one has the most one star reviews so my second sword is going to be by the one star review array comma and i'm going to sort this in descending order let's close the bracket hit enter and see what we get so the first sort we did here was a zed by genre so if we take a look at she a history of adventure let's find that in the table i can see the genre is adventure and because that begins with a it's at the top of the list the next one is the catcher in the rye and if we find that in the table it's right at the bottom that is the coming of age genre which is c so i can see that these are going down in a to z order but we're then sorting them by one star reviews as well and if we take a look at she a history of adventure i can see that yes this has by far the most one-star reviews if we take a look at some books that are all by the same author so for example harry potter i can see that the harry potter book that's received the most one star reviews is harry potter and the deathly hallows and if we take a look at that it is this one just here i can see that yes this number is greater than all of the other one star reviews for the harry potter series so just remember sort by differs from sort because you're using a column to specify the sort as opposed to just selecting ascending or descending much like the sort function we can also do a horizontal sort by and we're going to use this method to tidy up this small data set so if you take a look at this data notice that these column headings aren't particularly in the correct order for example we have the first name in column a and then we have the title in column b and their last name in column d ideally we want the title in column a the first name in column b and the last name in column c i then don't really mind if i have department or salary in columns d or e now in order to tidy up this data and sort it so that everything is in the correct order we need to assign a sort order to each of the columns and you can simply do this underneath your data set so what we're going to do here is simply mark the order that we want the columns to appear in so i want the title column to be first so we're going to put a number one underneath there i then want the first name so let's put number two just there that's going to be followed by the last name so that's a number three we're then going to have the department which is going to be column number four and then finally the salary is going to be column number five and we can now use this sort order in our horizontal sort by so let's click somewhere up here and we're going to do sort by press the tab key what do i want to sort well i want to sort everything in this table so i'm going to select the entire data set a 3 to e16 remember if you want to put this in a table that is absolutely fine comma what do i want to sort it by well i want to sort it by this array just here comma how do i want to sort it well i want to sort it in ascending order let's close the bracket hit enter and now you can see like magic we have all of our columns organized so now i can go in and add the titles back in so i'm going to grab title let's control c and just paste this across we want first name last name department and salary now notice here in the salary column we're probably going to want to apply some number formatting i'm also going to widen out these columns by selecting them all double clicking so that we can see everything in each of these cells the final thing to do is just select the salary column let's go up to number and we're going to give this some currency formatting and we're going to take those decimal places down so very very simple to do a horizontal sort by where you're specifying the order of the columns it's time now to move on and take a look at our next dynamic array function and that is the sequence function now what sequence does in its most basic form is it outputs a list of sequential numbers and you can specify how many rows and how many columns and also what the step value is of those numbers now before we get into a couple of practical examples of the use of sequence let's just take a look at it in its most basic simple form so if we type in equals sequence let's take a look at the arguments so we have four arguments and three of them are optional now we are going to use all of them in this first example so what we want to specify first of all is how many rows of numbers do we want so let's say that we want four rows of numbers we can then specify how many columns of numbers we want so let's say four again i can then specify my starting number so for argument's sake let's start at number one and then i can provide the step value so do we want it to step by one two three so on and so forth let's start out with one so you can understand how this works let's close off the function hit enter and take a look at what we've got so we have four rows and four columns of numbers the numbers start at one and they increase or step by one each time now if we double click to edit this formula let's change some of these numbers so this time i'm going to say i want 10 rows we're going to have 8 columns the starting number is going to be 5 and we're going to step by 10 each time let's now hit enter and see what we get so take a look at this we're stepping by 10 so 5 15 25 so on and so forth and we have 10 rows and 8 columns so that's all well and good but how is this useful when we're working in excel well let's take a look at a couple of practical examples so let's delete this out and move to cell d4 so what we have here is a list of training courses in column a and maybe it's my new year's resolution that i'm going to complete a different training course every friday starting on february the 11th 2022 so what i basically want to list out are all of the training dates so that is every friday from this start date and then i can start assigning what courses i want to attend on those specific training dates so we can use sequence to help us with this so let's type in equal sequence how many rows of numbers do i want well the number of rows is going to be the number of training courses i have in this list now i don't particularly want to go through and count all of these particularly if my data set was a lot larger than it is so we're going to get count to do all the hard work for us now remember we need to use count a because we're counting text and not numbers so let's select all of the training courses a4 to a17 close off count a and now we're back into our sequence formula we can now specify the number of columns that we want now i'm just filling training dates down so i only want one column i can then choose my starting value well my start date is going to be february the 11th and because i have that in a cell i can simply select this cell what is my step value going to be well i want to complete a different training course every friday so that is effectively every seven days so my step value is going to be seven if i was going to complete a training course every two weeks my step value would be 14. so let's stick with seven close the bracket hit enter and now i get a whole bunch of dates now this column isn't formatted as a date column which is why we're getting these strange numbers so if we select them and just jump up to number formatting and apply the short date format you should see that the first date there is february the 11th but then the next one is february the 18th which is seven days after the start date so you can use sequence not only to produce some numbers but also to do things like create a date schedule and there are so many other ways that you can use this sequence function what i can now do is go through and simply assign which courses i want to attend so maybe word is going to be my first course i can just copy and paste that over we can also use sequence to unstack records and this is something that previous to this sequence command was actually quite difficult to do in excel now what do we mean by unstacking records well again notice in column a we have some training courses listed out and i have these listed vertically so we have the name of the training course first then we have the level we have the location of that training course followed by the date and it just repeats round so again training course level location date so on and so forth and effectively because i don't really like the way that this is laid out i want to lay them out in this order so course level location and date and i want this to be dynamic so that when new training courses are added everything updates so in order to do that we need to put our data into a table control t yes my table has headers let's click on ok and i'm just going to turn off banded rows so let's click in cell c4 and we're going to jump straight into our sequence function the first thing we need to specify are how many rows do we want well again i don't want to be counting down this list so i'm going to get count a to do all the hard work for me let's select everything in this column let's close off count a and now we're back into our sequence formula the next thing we need to specify are the number of columns well i have four columns that i need to populate course level location and date so we want four columns now for what we're trying to do here that is all we need we don't need a start or a step value so let's close off our formula and see what we get let's hit enter and we get a whole bunch of numbers now each one of these numbers relates to an item in column a for example this number one relates to the first thing that we have in this list so this is basically excel number two is beginner number three is the location and number four is the date so effectively in this column we have all of the courses then we have all of the levels all of the locations and all of the dates now i don't have 24 courses because that is how many cells i have highlighted just here and you can see that down in the status bar if you're not sure the count is 24. i only have six courses in column a so what we need to do here is edit our formula and i'm just going to click at the end of the count a and we need to divide it by four so effectively we're dividing by the number of columns so divided by four hit enter and now we have six courses six levels six locations and six dates now this still isn't giving us exactly what we want we don't really want to see these numbers we want to see the name of the course the level the location and the date so we can use index to find this out for us so we're going to type in equals index so what are we indexing what's our array well remember with index the array is always the information that you want to return so my array is going to be my list of training courses comma what is the row number well the sequence function has provided the row number for us so i'm going to select cell c4 now if i was to close my bracket just here and hit enter it's just going to return the first one excel can you remember what symbol we need to add to reference an entire dynamic array well if you said the pound or the hash symbol you would be correct so let's click at the end of c4 add a pound symbol notice it's selected the entire dynamic array and now when i hit enter i get exactly what i'm looking for the only thing left to do here is change these dates we need to apply some short date formatting and we've now unstacked those records and they're looking a lot easier to read and of course if we change anything here so maybe this second course changes to a powerpoint intermediate course it's going to also update in our results and if i add more data onto the bottom so i have some additional data down here let's just drag it up it becomes part of this table and our results also updates you can see we now have those additional items added onto the bottom and once again i just need to apply some short date formatting so you can use the sequence command in many different useful scenarios the filter function is another brand new dynamic array in excel 2021 that allows us to filter our data based on criteria that we specify i'm going to be taking a look at the filter function over the next few lessons because there's lots of different ways that you can use this function so let's start out in this lesson by understanding simply what the filter function does and how it might be useful so on the left hand side i have a reasonably large table and this table just shows a list of students it shows the exam that they took the grade that they achieved and also the date of their exam and what i want to do here is create a few different filters so that i can get a list of the students that match that criteria so maybe i want to filter for all students that sat a specific exam so maybe the french exam maybe i want to see what exams occurred on a very specific date or maybe i want to see all of the students who failed the exam so i can send out letters inviting them to resit the test i can do all of this using the filter function now before we get into the actual filter function we're going to do a couple of things to make our life easier the first thing we're going to do is you've probably guessed it we're going to put our data set into a table ctrl t yes my data has headers let's click on ok and let's immediately change that table style because it really is not very nice and i think i'm just going to choose no table style i'm also going to give my table a name so we're going to call this exam results and hit enter now the next thing i'm going to do just to make my life a lot easier is i'm going to create a data validation drop down list so i can easily select the exam from a list as opposed to having to type it into cell f4 each time so we're going to utilize our unique function to get a unique list of all of the exams and then we're going to use that list in our data validation so let's click on a cell that's kind of out of the way we're going to say equals unique let's select a list of exams ctrl shift down arrow close off the unique function hit enter and now we have a unique list now i don't want these to have the unique formula underneath so let's do control c and a little paste special trick let's paste the values directly over the top so now i have this list i'm going to create a data validation dropdown so i can simply select them from a list so up to data we're going to go into data validation we're going to create ourselves a drop down list and the source is going to be this range just here and click on ok so now that makes it a lot easier for me to switch between the different exams now i'm going to do exactly the same for the dates and also the grades the process is exactly the same so i'm going to do that off camera join me back here in a couple of seconds and we'll continue on with the filter function so now i have my three data validation drop down lists now one thing i did when i was creating a unique list of the grades to get them to display a b c d e and then fail i combined unique with sort so i'm going to get you to have a go at that we've seen how sort works and we've seen how unique works so see if you can get these to list out in exactly the same way that i have now i'm going to leave date and grade blank at the moment and just focus on the exam so i want to use the filter function to first of all show me all of the students that sat the maths exam so we're going to type in equals filter now filter has three arguments and the last one is optional now the array again the array is the information that we want to return now we want to return student exam grade and date so we need to select all of our data control shift down arrow to select that entire range comma now this is where we specify our criteria so what we want to include what is our filter well our filter is the exam column maths so what do we want to include well we want to include the exam column ctrl shift down arrow and that needs to equal whatever we have in cell f4 so at the moment that is maths comma the final argument which is the optional argument is if we want to specify some text or something if it doesn't return any results so in general i tend to put in quotation marks no records if it can't find anything close the bracket hit enter and like magic we now have a list of all of the students who took the maths exam now once again column k doesn't have any formatting applied so let's change this to short date format so now if i was to change this to french that list is also going to change so it makes it very simple for me to filter those results now currently we're just filtering by one piece of criteria and that is the exam that the students set but it might be that we want to filter by two pieces of criteria so maybe the exam and the date or maybe three pieces of criteria the exam the date and the grade it might be that we want to see a list of all of the students that sat the science exam or achieved a grade of a and this is where we start to build in logic to our filter function that's exactly what we're going to explore over the next four lessons we're going to build in logic and i'm going to show you all of the different operators you can use to really refine your filter results but in its most basic form that is how the filter function works pretty straightforward now if you're wondering about these columns over here which have the data that we used to create our data validation dropdown if you want to hide those that's perfectly fine we can right click select hide and that just removes them from view in the previous lesson we saw how to use the filter function to filter for one piece of criteria and if you recall we used the filter function to filter for all students that sat the maths exam now in this lesson we're going to move that concept on a little bit and we're going to add in some logic to our filter and there are many different types of logic that you can add to your filters and we're going to start out with the plus logic and all that means is that we're going to be using a plus operator now when we use the field function with the plus operator we're basically doing an or formula so what does that exactly mean well it might be that i want to filter for all students that sat the maths exam or achieved a grade of c or maybe i want to filter for all students that's sat the french exam or sat a test on a specific date so we use the plus operator to separate our criteria and we're effectively applying or logic so let's see how that works now i'm just going to switch this filter back to maths for this first example and we're going to type in equals filter now the array argument as we've already seen is what we want to return now we want to return the student the exam the grade and the date so our array is pretty much everything that we have in this table i'm going to highlight that first row and use my shortcut keys ctrl shift down arrow to select everything now we get to specify what to include now in the previous example when we were just filtering for students that sat the maths exam we simply selected the exam column and check that it was equal to maths but now we want to add two pieces of criteria because i'm going to filter for all of the students that sat the maths exam or achieved a grade of c now when you have multiple pieces of criteria you need to encase each piece of criteria in brackets so let's open a bracket and select what we want to include first so i want to include all students that sat the maths exam so we're going to select the exam range ctrl shift down arrow and that has to equal whatever we have in cell f4 so that is the first piece of criteria let's close the bracket now if we want to do an or we then need to add the plus operator and then we can specify as second piece of criteria so i'm filtering for all students that sat the maths exam or achieved a grade of c so my second piece of criteria is going to be the grade range control shift down arrow and that needs to equal what we have in cell f12 close the bracket now if i had more pieces of criteria that i wanted to add to my or logical formula i could carry on going i could add another plus and then specify my next piece of criteria now for this example we're just going to leave it at two pieces of criteria the only thing left to do here is finish off this filter formula now the last argument here is an optional argument because it's in square brackets and always specifying here is what we want it to return if it doesn't find any results so in quote marks we're just going to say no records and then we're going to close off the filter function let's hit enter now the first thing to note here is the column k doesn't have any formatting applied so let's click the drop down and change this to short date format so now what we're getting here are all students that sat the maths exam or achieved a grade of c so we can see here for ming sia he sat the maths exam this girl sat the mass exam these two so on and so forth but we also have in here any students that achieved a grade c and of course if we have these as data validation drop-down lists we can simply select different pieces of criteria so now i'm filtering for all the students that set the french exam or achieved a grade of d so that is how that plus operator works you're essentially applying or logic to the filter function in the previous lesson we saw how we could use the filter function to filter data based on logic and we use the plus operator to do an or logical filter so we were filtering for all students that sat the maths exam or achieved a grade of c so now let's take a look at using the asterix operator because effectively when we use that we're doing an and logical formula so for example i might want to filter for all students that sat the french exam and achieved a grade of d and this works pretty much in exactly the same way we're just changing the operator so let's jump into cell h4 and type in equals filter the first thing we need to choose is the array so we want to return everything the student the exam the grade and the dates so we need to select all of the information in the table remember ctrl shift down arrow to select everything comma what do we want to include well we're going to include two pieces of criteria again and each piece of criteria needs to go in brackets so the first thing that we're filtering for are all students that sat the french exam so we need to select the exam range ctrl shift down arrow that needs to equal whatever we have in cell f4 let's close the bracket now we want to apply our asterisk operator because we want to say and and then we can specify as second piece of criteria so our second piece of criteria is the grade so we want to select the grade range ctrl shift down arrow and that needs to equal what we have in cell f12 we can then close the bracket now the final thing to specify here is what we want it to display if it doesn't find any records so again we're going to say no records and put that in quote marks we're going to close off our filter hit enter and take a look at that immediately i know that we have no students that sat the french exam and achieved a grade of d so what about if we start changing some of these things so let's go for grade e now i can see three students who sat the french exam who achieved the grade of e what about if i change the exam to maths we have one student who got an e now remember if you want this table to dynamically update you need to make sure that your original data is in a table itself now currently my data isn't in a table how can i tell that well when i click inside the data if we take a look at the ribbons at the top i don't get my table contextual ribbons when you're clicked inside a table you will see a table design ribbon at the top so let's press ctrl t and put this data in a table yes my table has headers let's click on ok and once again i'm going to change this to no specific format now if i was to go and add a name onto the end here my filter isn't going to update because i'm currently using a very specific cell range a4 to d41 so i would need to really redefine my arguments for this particular formula so let's delete it out and i'm going to give my table a name so on the table design tab let's call this we'll just call it students now if we then do our filter again our array is the entire table and notice now it's picked up the name of the table as opposed to the cell range then we specify what we want to include so let's open our bracket we're going to select the exam range and that needs to equal what we have in cell f4 close the bracket we're doing an and so we want an asterisk in here and then we can specify our second piece of criteria so that is going to be the grade ctrl shift down arrow and that needs to equal whatever we have in cell f12 let's close off our criteria let's specify what we want it to save it doesn't find any records and then close off our filter and if we hit enter we get the same result now the difference here is that if i go and add another student to the end of this table i should find that my filter formula updates so i'm going to add another student that's at the maths exam and achieved a grade of e currently we only have one result i would expect this to change as soon as we add somebody else so i'm just going to add my name to the bottom here and maths we want a grade e and let's just say the 8th 2022 so if we scroll back up now you can see that that's automatically added that to my results so once again i hope you're taking note of the importance of putting your data into an excel table if you want everything to dynamically update in this next example we're going to take a look at using the filter formula with the equals operator now equals is a little bit different as it will produce a result that contains either both criteria or neither criteria so we're going to use a different example here now in the table on the left i have a small little table that i've named venues and we have a list of 10 or so different venues so this might be that we're looking to book a venue for an event or maybe a wedding reception or something like that we can then see the capacity of each venue in column b and if they provide a dj in column c and what i want to do here is i want to use the filter function to get a list of venues that have a capacity of greater than or equal to 400 and a dj or they have a capacity of less than 400 and no dj so let's take a look at how we would construct this so we're going to type in equals filter the first argument is our array so again this is what do we want to return now i want to return the venues the capacity and the dj so i need to select all of the information and because i've named this table you can see the table name as opposed to the cell references comma what do we want to include well we have two pieces of criteria so let's put the first one in brackets and our first piece of criteria is that the capacity is greater than or equal to 400 so we want to select the capacity column and that needs to be greater than or equal to 400 let's close off our first piece of criteria now we need to use the equals operator and then enter a second piece of criteria so let's open a bracket the second piece of criteria is that we have a dj so we need to select the dj column and this needs to equal yes and we need to put that in quote marks because it's text let's close off our bracket and specify what we want it to say if it returns no records so this time we're going to say no venues and close off our filter formula let's hit enter and see what we get so what i should be seeing here in my results are all of the venues that have a capacity of greater than or equal to 400 and a dj so i can see here grand ballroom that fits and these two here are both greater than 400 and they also have a dj but what i'm also seeing in this list are all of the venues that have a capacity of less than 400 and no dj so that is basically these three just here so it might be that if we have a very large venue i want them to provide a dj but if we have a smaller venue maybe it's in a small pub or something like that we don't require a dj so this is where that equals operator comes in really useful and of course if we put this in a table if i add another venue onto the end maybe i'm still scoping out different venues then our results are going to update so let's add the magic room onto the bottom and we're going to say that this has a capacity of 500 and yes it has a dj and now you can see that because that meets our criteria of being over 400 and having a dj it's adding it to my list of results so that is the equals operator it's basically that both criteria are true or neither criteria are true in the final example of using logic with the filter function we're going to take a look at the minus operator and the minus operator works in a similar way but what we're going to do here is we're going to produce a list of venues that have a capacity of greater than or equal to 400 or a dj but not both and as you would expect this works in a fairly similar way to all of the other operators that we've been looking at so we're going to construct a filter function so let's type in equals filter our array as always is going to be everything that we want to return and because i have that in a table it's coming up with the table name and then we specify what we want to include now with this example i'm hard coding greater than or equal to 400 into the formula now that in general isn't the best way to put together your worksheets so maybe once i've put together the invite list i find that i have more guests than i was expecting if i want to change this to greater than or equal to 500 or 600 it means i'm going to have to come in and change the formula where i've hard coded that value so let's lay this out in a different way that kind of adheres more to the rules of good spreadsheet design so let's delete out filter and what i'm going to do over here is we're just going to add a heading that says capacity and we're going to add into here 400. let's also add another column for the dj and i'm going to put in here yes so now instead of hard coding these values into the actual filter formula i can just reference the cell so if any of this information changes maybe i change my mind on the capacity or decide that i don't want to dj i can simply change it in one cell and everything else is going to update that's a much better way of working with your spreadsheets so now let's construct our filter formula so equals filter our array is going to be what we want to return which is everything comma what do we want to include let's open a bracket for our first piece of criteria so our first piece of criteria is that the capacity is greater than or equal to and now we can choose our cell reference i four let's close the bracket to close off our first piece of criteria now we're going to use the minus operator and then we're going to put in our second piece of criteria and that is that the dj is equal to yes so we can select cell j4 let's close off the bracket let's specify what we want it to say if it finds no records so we're going to say no venues close off our filter and hit enter and would you take a look at that so this one is a little bit harder to understand this minus operator what it's showing us are all of the venues that have a capacity of greater than or equal to 400 or a dj but never both so this first one here ritz is that has a capacity under 400 but it does have a dj so it's appearing in our results the same for this one and the same for this one now when we get to these three just here these venues have a capacity of greater than or equal to 400 but they don't have a dj so one of these is true that's basically what will appear in the search results one of those conditions needs to be true but one thing that we don't see is when both conditions are true so what we won't see in here are any venues that have a capacity of greater than or equal to 400 and a dj so that is the difference with this operator and as i said if at any point details change for example if i change the capacity to greater than 600 and hit enter you can see that automatically my results update and i haven't had to go in and start messing around with this filter formula if i change the dj to no again i'm going to get a different set of results so outputting your criteria into separate cells and using the cell reference is a much better method than hard coding it into the actual formula another new dynamic array available in excel 2021 is the rand array function and rand array allows us to generate or randomize data so let's take a look at it in its basic form and then we're going to see how we can apply this in a real world scenario so i'm going to click just somewhere on this worksheet and we're going to type in rand array now notice here that we have five different arguments and all of them are optional but we're going to use all of these five arguments so you understand what each one does now as i said render a will generate a random list of numbers so the first thing we need to do here is specify how many rows of numbers we want so i'm going to say that i want four rows of random numbers i can then choose how many columns so let's go for four again and then if i want to i can specify a minimum and a maximum value now i don't have to utilize minimum and maximum because none of these arguments are mandatory if i don't specify a min or a max then excel is just going to generate some random numbers of its choosing but maybe i do want to be a little bit more granular about the numbers that i'm generating so i'm going to say that i want to generate numbers between five and let's say a thousand and the final argument is if i want those numbers to be decimals or integers so let's take a look at decimals first of all we're going to use the false argument let's close the bracket and hit enter and that is exactly what i get now of course if you want to you can take these decimal places down just by using your number formatting and there you have your list of random numbers and this is particularly good if you're just trying to generate some dummy data to use in maybe training videos like this one or maybe if you just want to practice your excel skills it's good to know a few techniques just to get some random or dummy test data into your worksheet and of course because this is a rand array formula you could throw away the underlying formula and just have the values in the cells by doing ctrl c to copy paste special and paste those values so really nice and straightforward let's take a look at rand array again but this time we're going to use the integer argument so let's choose render a this time let's do 10 rows and we're going to do five columns the minimum value is going to be one and the maximum value is going to be 900 and this time we're going to use the true argument so that we get integers instead let's hit enter and there we go so that final argument really just specifies if you want decimal numbers or if you want integers so really nice and straightforward now that's all well and good but how can this be useful in practical examples well let's take a look at this first set of data now we're going to pretend that we work for a company called big drop bungie and we have the list of all of the people who are going to be doing a bungee jump today and we want to assign them to a jump order so who's going to go first because if we were to ask all of these people they're probably going to look towards everybody else to do the first bungee jump so we want to assign a random order for these jumpers and we can use rand array to help us do that so what we're going to do here is we're going to type in equals render a now how many rows do we want to generate random numbers for well i want to generate random numbers for all of these jumpers and because my list is fairly short it would be quite easy for me to count the number of jumpers but i'm going to get excel to work this out for me by going straight into account a let's select our list of jumpers close off our count a and now we're back into our rand array formula now i don't actually want to specify a minimum or a maximum and i'm not going to specify the integer and if you don't specify what integer you want to use by default you're going to get a decimal which is fine in this case so let's close this off hit enter and this is what we get now notice that one of these looks slightly shorter than the other and that's just because there is a zero on the end of this so just keep things consistent i'm going to use my increase decimal button just to make that the same as all of the others so now that i have this list i'm going to use my copy and paste trick to throw away the underlying formula so let's select the numbers ctrl c and then we're going to paste special and paste the values over the top so now we have these random decimal numbers i'm going to sort these into order so let's click in the jump order column and then on the data tab i'm going to sort these smallest to largest so what we could say here is that whoever has the smallest number is going first and whoever has the largest number is going last so effectively we've generated a random jump order for all of these different jumpers so it looks like lonnie has drawn the short straw and she's going to be doing her bungee jump first so that is a very practical use of how you can use rand array now before we move on from this lesson i want to show you one more function now technically this function isn't a dynamic array function but it is very similar to rand array so i thought it would be a good time to introduce it into the course because we are going to use this quite a bit later on now if we jump across to the next worksheet i want to show you how you can use randbetween and i will say that randbetween is one of my most used functions in excel because of the type of job that i do i'm always running training sessions and i need to be able to generate dummy exercise files and practice files with random data and randbetween really helps you do this so let me show you an example of how it can be useful so let's just say that these people are employees and i want to generate a worksheet that shows some random employees some random salaries and the dates that they were hired and i want to use this to maybe showcase sorting and filtering so instead of going through and just typing in a salary for each of these people very manually i want to use ram between to generate some random numbers which i can use as salaries so we're going to type in equals rand between now round between just has two arguments bottom and top so you just specify the bottom number and then the top number and the formula will generate random numbers between those two numbers so let's say the bottom salary is going to be 25 000 and the top salary is going to be 50 000. close the bracket hit enter and now we can double click to copy that down let's control c and then paste just the values over the top and now all i need to do is apply some currency formatting and effectively i very quickly created myself some random salaries i can also utilize round between to generate random dates so let's take a look at this now again we just have these two arguments but because we're using dates we want to go straight into the date formula so now we can specify our bottom date so let's see we're doing dates that people have been hired so we're going to say that everybody was hired between 2005 and 20 21 so the bottom date is going to be 2005 for the year and we'll say the month is january and the day is the 1st of january let's close the bracket comma we now need to specify our top date so we go straight into the date formula again so my top date is we're not going to have hired anybody after december 31st 2021 so the year is 2021 the month is 12 and the day is 31. let's close off the date function close off round between and if we hit enter we're going to get a number let's double click to copy that down and we need to apply the correct date formatting so let's jump up to here and select short date and like magic we've now generated ourselves some random dates between 2005 and 2021 so randbetween can be so helpful if you're quickly trying to create data sets to practice with or maybe do some training with another great addition to dynamic array functions is the x lookup function and this is the perfect function for anybody who has ever struggled using index and match to perform lookups the index and match formula in itself is a reasonably complicated formula and it can be quite difficult to remember particularly if you don't use it all that often with the introduction of x lookup not only is it more powerful and more flexible the notation is a lot easier so let's take a look at a couple of examples of how we can use x lookup now in this table on the left hand side i have some apps listed out i have the category that the app belongs to the app name the type of app it is the revenue and the profit and effectively what i want to be able to do is select an app from the data validation drop down list and have it return the category the type the profit and the revenue now normally you would look at using vlookup or something like index and match for this and in fact in order to return the first result of the category you would have to use index and match because remember one of the big limitations of vlookup is that you can't use a lookup value that's to the right of what you're trying to return so in this case google docs is to the right of the category so this would need to be an index and match now just so you get a good comparison as to how simple x lookup is let's do an index match first of all so we're going to type in equals index and the first thing we need to select here is the array now the array is what you want to return so in this case we want to return the category so we need to select the category column control shift down arrow to select that comma we now need to provide the row number and remember we can use the match function to help us automate the finding of that row number and lookup value is whatever we have in cell h3 and our lookup array is wherever we're going to find that lookup value so that lookup value exists in the apps list control shift down arrow the final argument that we have here is the match type and for this we want to do an exact match of the app name in the apps list so we want a zero argument on the end we're going to close off match close off index and hit enter and that's going to give us our result so let's just double check this we need to find google docs here it is in row 9 and the category is productivity so that's all working and if you can remember this formula that's fine you can still carry on using index and match but now let's take a look at how we can do the same thing but this time using x lookup because i think you're going to find this a lot easier so we're going to type in equals lookup and press the tab key now the x lookup function has six arguments and you'll notice that the last three are optional arguments because they're in square brackets so you don't necessarily have to use all of these arguments the first argument that we need to provide is the lookup value so that's fairly straightforward we're looking up whatever we have in cell h3 we then need to provide the lookup array so where are we going to find that app we're going to find it in the apps list control shift down arrow to select that column the only other thing we need to provide is the return array so what do we want to return we want to return the category so we need to select the category column control shift down arrow now if i wanted to i could just stop right there i don't have to go ahead with the final three arguments so if i close my bracket and hit enter it's going to give me the correct result so now let's do the same thing but for the type we're going to use some of the other arguments so let's type in equals x lookup our lookup value again is whatever we have in cell h3 and we're looking it up in the apps list we then specify the return array so this time we want to return the type so we need to select the type column comma now let's take a look at the first optional argument if not found so this is where you can specify what you want it to say if it can't match the app in the table now if it can't find it i just want it to say not found and we need to put this in quote marks the next optional argument is match mode so this is the type of match you want to do so much like the match function we can choose to do an exact match but we also have some other options here we can do an exact match or next smaller item we can do an exact match or next larger item or we can do a wildcard character match so the wildcard character match that would be relevant if i had for example maybe google asterix as my lookup value so we can do wildcard character matches and these other two where we're looking for an exact match or next smaller or larger item i'm going to show an example of that in a moment it's very similar to when you do a vlookup approximate match now i want to do an exact match i want to match the words google docs exactly in the table so the next argument is a zero comma now i can choose how i want to search through this list so this is something else that you can't really do when you're performing a lookup with index and match i can choose to search first to last or last to first and i can even do a binary search so if i have my item sorted in ascending or descending order i can specify that here too now if you don't provide any argument just here the default is searching first to last so it's going to start at the top of the table and search through the entries top to bottom so let's do first to last close the bracket hit enter and now i get the type let's double check to make sure that that is correct i can see yes the type here for google docs is free ads let's just finish these off let's do the profit so we're going to do x lookup again the lookup value is google docs comma the lookup array is where we're going to find that that's in the apps list the return array this time is the profit column and i'm not going to use those optional arguments let's just close this off and hit enter and we get a result let's just do the final one because practice does indeed make perfect lookup value is h3 we're looking it up in the apps list and we want to return the revenue this time so we want to select the revenue column close the bracket hit enter and we get our result let's just make sure that we are still all correct and i can see that yes everything seems to be working so now if i change the app that we're looking up in the drop down everything should update nicely so x lookup is not only a lot easier in its notation it's a lot easier to understand and remember it also allows you to specify a few different pieces of criteria let's take a look at another example of x lookup because remember x lookup is part of the dynamic array set of functions which means that it can spill and return multiple results so let's take a look at a quick example of that i have a small employee table just here we have some employee ids some employee names and their department and what i want to be able to do is type in the employee id and have it return the employee name and the department so we're effectively returning two pieces of information from this table so we're going to type in equals x lookup and lookup value is going to be the employee id we're looking it up in the employee id range and we want to return the employee name and the department so when it comes to the return array we simply just need to select everything that we want to return i'm going to add a piece of text if it's not found so we're going to say just not found in here i want to do an exact match and i want to search first to last close the bracket hit enter and take a look at that if i now change this employee id to 1005 you can see that everything updates it looks like terry's in the same department let's choose something else one zero zero eight there we go now if i was to enter in an employee id that doesn't exist so let's say 2000 i'm just going to get that if not found text in the cell so you can use x lookup to return multiple results because it is a dynamic array formula let's take a look at our last example and this is where we're going to focus on that match mode argument and this is very similar to how vlookup works when you're doing an approximate match as opposed to an exact match so what we have over here are some tax rates and some salaries and what i want to be able to do is type in a salary and get it to return the tax rate now what if the salary that we have in cell d6 doesn't exactly match a salary that we have in the table so maybe i have in here 30 000. now i want to apply some formatting to that so let's just apply some accounting format and take those decimal places down and i want it to return the tax rate now because we don't have 30 000 listed exactly in this column i effectively need to do an approximate match so i want it to return the tax rate that it's nearest to so i can choose if i want to return the next highest or the next smallest so let's type in equals x lookup our lookup value is the salary we're looking it up in the salary column we want to return the tax rate i'm going to skip over the if not found option because we want to go straight to match mode so this is where i can choose if i want to do an exact match or i match it to the next smallest item or the next largest item now i can't do an exact match here because 30 000 doesn't exist in the table so do i effectively want to round it down to the one below or round it up so i'm going to say i want to do an exact match or next larger item so that's going to be our one argument let's close the bracket hit enter and it's going to give me a tax rate of 25 so if we look at the table just here it's picked this one out because 30 000 if we were to round it up the next tax rate would be 25 if i was to change this to let's go for 14 000 it's giving me a result of twelve percent because again it's rounding it up to fifteen thousand effectively so that match mode is very similar to vlookup approximate match the final new dynamic array formula that we're going to look at in this section is x match and this is like match on steroids because we can do so much more with x match than we can do with match so the best thing to do here is just dive in and take a look at a few different examples now i have three different examples here that we're going to run through and this first example we just have a very simple list of different applications and what i want to do is i want to search for the application and return its position in the list because that's basically what the match function does it's going to provide you with the row number or wherever the lookup value is located and what you'll notice about the application that we're looking up is that i'm using a wildcard character just here so let's take a look at how we would construct this x match so we're going to type in equals let's choose x match and we have five different arguments here once again the last two are optional so we don't need to complete those if we don't want to now the first argument is the lookup value so we're looking up whatever we have in cell c5 where are we going to look it up well we're going to look it up in this list just here what type of match do we want to do are we doing an exact match of the word g-o-o asterix do we want to match exactly or to the next smaller item or to the next larger item well it's going to be none of those because we're using a wildcard character so for this one we're going to choose number two and then finally we get to specify the search mode so if we want to search first to last last first or if we've sorted our list in ascending or descending order so i want to search first to last so we're going to add a 1 on to the end there close the bracket hit enter and i can see here that using this wildcard character it's picked out of this list that i'm looking for google docs and it's returned position of one so x match shares many similarities with the x lookup function let's take a look at another example now this time we just have a very small list of employees and we have the monetary value of the sales that they've generated and what we're going to say here is that any of these sales agents that have generated more than seventeen thousand dollars in sales are going to get a bonus and what the x match formula will do is it will basically do a count because we're using numeric values and it's going to tell us how many employees in our list will receive a bonus so let's type in equals x match our lookup value is going to be our threshold where are we looking this up well we're looking it up in the sales column now what type of match do we want to do here well i don't want to do an exact match because we're not matching exactly 17 000 in that column what i want to say is that everybody who's achieved greater than that amount of sales is going to get a bonus so i want to do an exact match or next larger item so we want a one in there and we're going to search first to last let's close the bracket and hit enter and it's telling me that two employees have exceeded 17 000 in sales and are going to get a bonus if i check the table over here i can see yes it's only these two people adam lacy and lucy jones who will receive a bonus now the third example here is an example of how you can use index with x match so you're probably more familiar with using index with the match function but you can expand on that by using x match so let's return the department and the location for the employee that we have in cell g29 so we're going to start out with an index we need to choose our array so remember our array is is the information that we want to return so in this case that's going to be the department comma next we need to find the row number now normally if we were doing just a regular index and match this is where we would go straight into a match formula and we're going to do exactly the same thing here but we're just going to use x match instead we can now specify our lookup value and our lookup value is the employee we're looking up the employee name in the employee name range and now we can choose match mode and search mode so i want to do an exact match of the employee name and i want to search through the list first to last so we want a one on the end let's close off x match close off index and hit enter let's do that one more time for the location so we're going to type in equals index this time we want to return the location so this is our array we want to automate the finding of the row number by going straight into x match our lookup value is the employee we want to look that up in the employee range we want to do an exact match and we're searching first to last so we have a few more additional pieces of information that we can add into our formula when we're using x match as opposed to match let's close off both of our formulas hit enter and let's double check our results so adam lacy he's in the marketing department and he's located in the west block if i was to change this to let's say ming sia and hit enter all of those details update so x match is pretty much the same as match it will return the position of a lookup value and you can combine it with index to perform a powerful and complex lookup it's time now to complete exercise two and in this exercise i'd like you to practice some of the skills that you've learned in this section related to dynamic array functions now notice at the bottom we have four different worksheets that i want you to work through we're going to start out on the first worksheet where we're going to practice using the unique function so the first thing i'd like you to do is extract a list of unique months into column g i'd then like you to extract a list of distinct months into column h and then finally i'd like you to count the number of unique months once you've done that you can click over to the filter and sort worksheet now for this exercise i'd like you to first create a data validation drop-down list containing unique values for the sales agent and unique values for the month and what i'd then like you to do is use the filter function and create a filter that filters for the sales agent ben i'd like you to ensure that the text no records is displayed for zero results once you've done that and it's working i'd like you to then modify the formula to filter for sales agent ben and the month of february and once you have those results i'd then like you to modify the formula again to filter for sales agent ben or the month of february which should give you a longer list once you have that longer list of results i'd then like you to modify the formula and sort the results in ascending order by sales agent and i'd like you to use the sort function as opposed to sorting utilities on the home or data ribbons and then a final thing i'd like you to do is enter the total profit generated by claire for the month of march and i'd like you just to input that into cell p4 once you've completed that it's time to move on to the x lookup worksheet now for this exercise once again i'd like you to create a data validation drop-down list in cell i4 containing the unique values for the sales agents and then i'd like you to construct an x lookup formula to return the team the revenue and the profit for whichever sales agent is selected in cell i4 and then the final part of this exercise is practicing the rand array function so i'd like you to use rand array to add some dummy data into columns b and c for revenue and profit and i'd like you to use the count a function to work out the number of rows required i'd like you to use a hundred as the minimum value and a thousand as the maximum value and output the values as integers not decimals once you've done that i'd like you to use paste values to just throw away the underlying formulas so quite a bit of work to do in this exercise have a go at it and if you'd like to see my answer then please keep watching so the first thing we need to do here is extract a list of unique months into column g so this is fairly straightforward we're just going to select the unique function our array is going to be the list of months and that is it close the bracket hit enter and that is our unique list now remember distinct is slightly different because that will return only the months that appear exactly once so we're going to go for unique again we still want to extract the months we want to return the unique rows so we need a false in here but we want to return items that appear exactly once so we're going to add a true on the end here and hit enter and then finally i asked you to count the number of unique months so because we're counting text we need to use count a we can then go into unique and we can select a months close off unique close off count a hit enter and the result there is seven which should basically be the same as the number of values returned in column g and if we take a look down the status bar you can see that the count is in fact seven let's jump across to the next worksheet now this is where we get to practice the filter and the sort functions and the first thing i asked you to do was to create a data validation drop-down list containing unique values for the sales agent and months so the first thing i'm going to do is just somewhere down here is i'm going to use unique again to get a unique list of the sales agents and also a unique list of the months so now i can use these to create my data validation dropdowns so in cell g5 let's jump up to data into data validation we want to create a list and our source is going to be these values just here we need to do exactly the same for the month so up to date of validation we want to create a list and our source are our unique months so let's make a couple of selections the next thing i asked you to do was to create a filter to filter for sales agent ben and ensure that the text no records is displayed if there are no results so we want to come over here and put together our first filter now we want to return everything so our array is going to be everything in this table we now need to specify what we want to include now we're only including sales agent ben so we only have one piece of criteria so we're going to include the sales agent when it's equal to what we have in cell g5 and in a moment i'm going to switch this to say ben comma what do we want it to say if it doesn't find any records well we want it to say no records let's close our filter and hit enter at the moment we're seeing the results for claire but we need to switch this to ben and there we go this time we want to modify the formula to filter for sales agent ben and the month of february so let's switch our month to february and let's edit our formula i'm going to edit this up in the formula bar so if we delete out what we have there and just take it back we've selected our range now we need to specify what we want to include now remember if you have more than one piece of criteria they need to go in brackets so first piece of criteria is that the sales agent needs to equal ben let's close the bracket and if we're doing an and filter we need to add the multiplication symbol because our second piece of criteria is that the month is equal to february let's close the bracket we want it to say no records if it doesn't find anything let's close and hit enter and now we get two results for sales agent ben in the month of february next i want you to modify the formula to filter for sales agent ben or february so this is where we're changing that operator so let's go back up to our formula and instead of an asterix in here we just need to switch this out for a plus instead so now we're getting a slightly longer list of results because we're filtering for sales agent ben or the month of february then i asked you to modify the formula and sort the results in ascending order by sales agent so once again let's jump up to the formula bar and we're just going to add sort on to the front the array is generated by the filter formula let's go to the end as sort index well we want to sort by the sales agent which is column number one and we want to sort in ascending order so we need another one on here let's close the bracket hit enter and we get our new list of sorted results the final thing i asked you to do was to enter the total profit generated by claire for the month of march in cell p4 so let's switch our filter to claire and we're going to change our formula so this time we're going to say filter we want to select everything we have two pieces of criteria the first piece is that we want to filter for sales agent claire so sales agent must equal claire and so we need an asterisk in there we want to filter for the month of march so let's select month that needs to recall what we have in cell g8 and again i'm going to change this to march in a moment let's close the bracket let's add add text for no records close the bracket again and hit enter now let's make sure that we switch this to march because the final thing we needed to do was enter the total profit for claire in cell p4 so this is a very straightforward sum calculation let's just type it in we're looking for the total profit so let's select both of those cells hit enter and there we go five five five let's move on to the next part of this exercise the first thing we need to do here is create a data validation drop-down list containing unique values for the sales agents now all of these sales agents are unique which makes this a little bit simpler we can go up to data validation we don't need to do unique first of all we're going to create a list and our source is going to be this range just here let's click on ok and i'm just going to select a sales agent from here because now we want to use an x lookup formula to return the team the revenue and the profit so let's type in x lookup a lookup value is whatever we have in cell i four i'll look up array well our lookup array is the sales agent range and our return array is going to be the team close the bracket hit enter and that gives us our result we're going to do exactly the same to look up the revenue and the profit so our lookup value is i4 we're looking it up in the sales agent column and this time we're returning the revenue close the bracket hit enter and finally we need to do the same for the profit lookup value is i4 we're looking that up in the sales agent range and this time we're returning the profit close the bracket hit enter and there we go and we should find that when we select somebody else from this list everything updates the final part of this exercise was to practice using the rand array function to add some dummy data into columns b and c so let's click in cell b5 and type in rand array now the first argument here is how many rows of numbers do we want well i want the number of rows to be equal to the number of sales agents so i'm going to use the count a function to work out how many rows i have how many columns of numbers do i want well i want to fill revenue and profit which is two columns i want the minimum value to be 100 the maximum value to be a thousand and i want to show these as integers as opposed to decimals so we need a true argument on the end let's close the bracket hit enter and like magic i get my dummy data the final part of this exercise was just to throw away the underlying rand array formula so that these are just values so let's select them all ctrl c to copy let's go to the home tab and we can just paste special paste values and that is it so quite a long exercise for this section i hope you got on okay with that and i will see you in the next exercise in this section of the course we're going to take a look at some more advanced lookup and reference functions and we're going to start out in this lesson by taking a look at how we can perform two-way lookups and i'm going to show you two different methods we're going to use index and match first of all and then i'm going to show you how you could do the same thing but using the new x lookup function now what exactly do we mean by a two-way lookup well effectively what we want to do is we want to look up information using two pieces of criteria as opposed to one if we take a look at the table that we have on the worksheet this basically just lists out a few different travel companies we then have the months january to december listed across the top and then in the middle here the main bulk of our data is some sales figures that each of these companies have generated by month and what i'm aiming to do is i want to be able to select a month and a company and have it return the sales figure so we're doing a two-way lookup because we need to use two lookup values we need to look up the month and then we need to look up the company to get our result now before we get into constructing the formulas there's some prep work that we need to do here and all of this prep work is really just to make your life a lot simpler and easier now the first thing i'm going to do is put my data into a good old excel table control t yes my table has headers let's click on ok and immediately i'm going to change that to no particular format let's give this table a name so let's call this travel underscore data now one thing i don't really need here are these filter buttons in the column heading so if we go up to table design we can simply deselect filter button to get rid of those now the next thing i want to do is i want to create two data validation drop down lists so i can simply select the month and the company i don't really want to spend time typing each one in to get my result now what i found in the past is that if i create a data validation drop down list and i use this range running horizontally across the page it doesn't tend to work so what i'm going to do is i'm just going to output january to december and i'm going to flip them around so we have them in a vertical list and then i'm going to use that list for my data validation now a quick way of just flipping around data is to use the transpose function now this is something you may have used in paste special previously but there is also a function to do the same thing and all we need to do is select our range press enter and it's going to give us a vertical output now i want to throw away the underlying formulas so ctrl c and then we're going to paste special and paste the values so now that i have this list i'm going to create a data validation dropdown so up to data into data validation we want to create a list and our source is going to be this over here and click on ok so now i have all of those values listed in a drop down we're going to do the same for the companies now i have these in a vertical list already so this is straightforward data validation we're going to create a list and our source is just going to be these values here and click on ok now the final step of preparing this data that i'm going to do is i'm going to create some named ranges to make our formula a lot simpler to understand so i'm going to create a named range for the company's range for the months and also for the data so let's start out with the months so i'm going to select all of those cells and then in the name box we're just going to call this months let's select the companies we're going to call this companies and then let's select our data set and we're just going to call this data so now let's go in and let's build our first two-way lookup and the first method i'm going to use is index and match so let's type in equals index the first thing we need to choose is our array now remember the array is where we're going to find our answer so our answer is going to be somewhere within these numbers now instead of selecting the cell range because i've named my ranges i can simply press the f3 key and that's going to show me all of the ranges that i have in my workbook so i can choose the data range we need to automate the finding of the row number using match what is our lookup value well we have two different lookup values we need to look up the month but we also need to look up the company so let's deal with the month first of all my lookup value is going to be b13 where am i going to look that up well i want to look it up in this month range at the top now again because we named that range we can simply press the f3 key and select the months range comma we want to do an exact match of that month name in that range so we want a zero on the end there let's close off our first match now because this is a two-way lookup we need to go straight into another match and this time we're going to look up the company so our lookup value is a14 where are we looking it up well if we press f3 we're looking it up in the company's range and we're doing an exact match let's close off our match close off our index and then when we hit enter we should find we get the correct result so let's double check we're looking at may sun and sales which is this value just here and i can see that yes that is the same let's select something else let's go for january and go explore there we go that's exactly right so a few steps there to achieve a really nice two-way lookup now that is the method using index and match but of course we can do exactly the same thing using the new x lookup function so let me show you how this works let's delete it out we're going to type in equals x lookup so what is our lookup value again we have two different lookup values let's deal with the months first of all we're looking up january where are we looking it up well we can press that f3 key we want to look it up in the months comma the next argument is the return array now when we're using x lookup we specify the return array last so we want to go straight into our next x lookup our lookup value is the company and we're looking that up in the company's range f3 companies comma now we can specify what our return array is so our return array is going to be those numbers f3 let's select the data and i could carry on going so if i want to specify a value if it's not found a match mode or search mode i can definitely do that so let's do that i'm going to add in not found if it can't find what i'm looking for the match mode is going to be exact and we're going to search first to last let's close off both of our x lookups and hit enter and now take a look at what we get that looks good so far if we switch to april and let's say footsteps i'm getting the correct value the choose function in excel returns a value from a list using its given position or index and choose can be useful in many different scenarios and its power increases when it's combined with other functions so let's take a look at choose in its most basic form first of all so what i could do here if i just click in a random cell somewhere on the worksheet if we type in equals choose notice we have three arguments just here now there are actually more than three arguments again because we have those three dots at the end now the first argument is the index number now i'm going to type in a 2 here this won't make too much sense to you at the moment but just bear with me because it will all become clear comma value number one now i'm going to enter in three values we're just going to use colors so let's go for red as our value 1 we're going to say blue is our value 2 and green is our value 3. now if i press enter what result do you think i'm going to get here it's going to return blue and that's because it's using this index number here the number 2 to pick the second item that i have listed out if i was to change this index number to 1 i'm going to get red and as you would expect if i change this to 3 i'm going to get green i could even do this using cell references instead so if i have red blue and green listed out in cells i could say equals choose index number let's say three this time and instead of typing the values out i can use the cell references so we have value one i'm going to lock that cell reference value two and finally value three close the bracket hit enter and because we're selecting the third item it's returning the word green so choose can help you choose a value based on an index or a position number now that's all well and good but how can this be useful well let's take a look at a couple of examples now if we take a look at this first table we just have some employee names and we have a code next to them and we need to complete the department for each of these employees then over on the right hand side we have the code and then we have the department that that code relates to so effectively what i want to do is i wanted to look at the code i wanted to match it over here and return the corresponding department now we could use a vlookup to do this so let's do that first of all and then i'll show you why there is an advantage to using choose instead so i could do equals vlookup the lookup value is going to be the code the table array is this over here f4 to g7 and we need to make sure that we lock that the column index number so what do we want to return we want to return the department which is column number two and we're going to do an exact match which is a false argument hit enter and then i can copy that formula down and that's going to give me the correct result everything corresponds to this little table however what happens to this formula if i was to delete this table over here well if i delete it out i suddenly get n a errors because this vlookup doesn't have anything to refer to so this is where choose has an advantage because we're actually specifying all of our arguments within one function when we're not referring to an external table or external data so let's undo and we're going to do this again but this time we're going to use choose so let's type in equals choose our index number is going to be the code and then we can specify our values so i could type directly into here sales then we have hr then marketing and then finally we have finance let's close the bracket hit enter and then when i copy this down it's giving me exactly the same results as the vlookup but if i was to delete this data over here it's not going to affect the choose formula because we're referring to the different departments directly within the formula now of course if we were to use cell references in the choose formula for sales hr marketing and finance instead of actually typing out the words we're going to have the same situation as we do with the vlookup because the formula would be referring to cells over here if we were to delete this data then that's going to give us some errors in our formulas but we can use choose in a similar way to vlookup and then it doesn't matter if we delete the data or not now another way that you can use choose and this is something again that i do frequently and this is particularly useful if you want to create some data that you can just use for training or practicing excel so what i'm going to do is i'm going to grab these departments ctrl c let's just paste those i'm going to paste them all the way over here because this formula can get quite long now i'm going to add a few more departments onto the end here so let's say projects development and i t now it might be that for this list of employees i want to assign a random department to them i can use a combination of choose and rand between to do that so we can type in equals let's go for choose and this time we're going to go straight into rand between now remember with this formula we need to define a bottom and a top number and round between will generate random numbers between those two numbers so my bottom number is going to be one and my top number is basically going to be the count of the number of different departments that i have now i only have a short list i could type in seven or i can get excel to do the hard work for me by using account a selecting the range and we need to lock that range if we're going to copy this formula down so we can close off our count a close off our round between and now we're back into our choose formula so now i can specify my different values so my first value is cell j23 let's lock that the next one is hr then we have marketing next is finance projects development and finally we have it let's close off atchu's formula if we hit enter we can then copy this down and we're basically getting this list of departments put in a random order and the way that this function works is brand between is basically assigning a number between 1 and 7 randomly to each of these names and then that number is being used to reference one of these departments 1 to seven and of course if you want to throw away the underlying formulas you can control c and paste values over the top so this is something i use all the time when i'm generating random data sets for practice purposes now let's look at our final example of using choose now here i have a list of dates and what i basically want to do is i want excel to look at the date in column a and i want it to tell me what quarter this date falls into now there are a couple of different ways that you could do this we could do this using the round up function so if we type in round up we're then going to go straight into the month function let's select the month and then we want to divide it by three which is going to give us the quarters the final thing to specify is the number of digits now i don't want any digits on the end so let's close the bracket hit enter and then if i copy this formula down it's going to tell me that these ones are all parts of quarter one these ones are quarter two these are called three so on and so forth now another way that you could do this is by using choose so let's type in equals choose then we want to go straight into the month function again and our serial number is going to be the date now we can specify our values so the way that this works is that the first quarter is made up of three months so january february march the second quarter is made up of april may june so on and so forth throughout the year so we're going to assign a value to each of the months so january is one so is february so is march we then have april may june july august september october november december so we're assigning a value to each of those months hit enter and we should find that we get exactly the same thing now some people find this choose formula easier to remember than this round up formula and i will leave that down to you to determine which one you want to use but i just wanted to showcase there a few examples of how you can use choose in real life scenarios the switch function is very similar to the choose function and can be a good replacement for something like vlookup or x lookup so in this lesson we're going to take a look at an example of how you can use switch now what we have here in column d are some movies listed out in column e we have a ratings column which is currently blank and then in column f we have the imdb score for that movie and what i effectively want to do here is complete the ratings column using the imdb score and if we take a look over on the left hand side we have a little table here that shows the rating and the score that's attributed to that rating now of course this is a scenario that is kind of perfect for some kind of lookup function like vlookup or x lookup and it's worth noting that the switch function despite the fact that it is really a lookup function you're not going to find it under the lookup and reference group of functions in the functions library you'll actually find switch underneath logical now before we get on to working with switch let's take a look at how we would do this if we were using x lookup and then we can do a good comparison between the two so we're going to type in x lookup now the lookup value is going to be the imdb score comma where are we looking this up what is our lookup array well our lookup array is going to be wherever we're going to find this score so this is our lookup array b3 to b7 now we're going to copy this formula down so we don't want those cells moving so f4 to lock what is our return array well we want to return the rating so we need to select a3 to a7 f4 to lock and then we close off our x lookup let's hit enter let's copy this down and we get exactly what we're looking for now of course the disadvantage with this again is if we were to delete this table or delete the values out we're going to get n a errors in column e so one advantage that the switch function and the choose function also have is that we don't necessarily have to have what we're looking up in a table or a list because we define our values from within the formula so let's do the same thing but this time we're going to use switch so ctrl shift down just delete out everything that we have in there and this time we're going to use switch our first argument is the expression so this is basically what are you looking up so i want to look up the score comma now i get to define the different values so the first value i need to define is a score of 9 if it finds a score of 9 it's going to return the word excellent if it finds a score of 8 it's going to return the words very good seven is going to be good six is okay and finally five is poor and close the bracket so now if i hit enter and copy this formula down it's going to produce exactly the same result as the x lookup the only difference here is that if this data gets deleted out of this table it has absolutely no effect on the values in column e because we're defining our values that we're using as opposed to referring to them via cell references in an external table or some kind of list elsewhere on the worksheet so that is the advantage that switch and to a lesser extent choose have over x lookup and vlookup so you might find there are certain scenarios where switch is going to be extremely useful to you particularly if you don't want big long lists of data or tables of data on your worksheet you just want the results it's time now to do exercise three and in this exercise we're going to practice some of the skills that we've learnt in this section so the first thing i'd like you to do in this exercise is practice two-way x lookups so i'd like you to replace the words month in cell k4 and team in cell a3 with data validation drop-down lists that contain the months and also the teams i'd then like you in cell l4 to add a two-way x lookup formula to look up the month and the team and return the sales total and then i'd like you to test this formula is working by checking that your results are correct by finding them in the table once you've done that it's time to move on to the next part of this exercise and in this exercise we're going to practice using choose and rand between so i have a list of team members just here and what i want to do is assign them to a team based on colors and i want the color assignment to be random for each of the team members so i'd like you to combine the choose and the rand between functions to assign team members randomly to a team based on the colors that we have listed just here and then in the final part of this exercise i'd like you to practice using the switch function so i'd like you to use the switch function to search in this table up here for the job ratings for each employees but instead of the job rating i'd like you to return the grade that's attributed to each of the job ratings and i'd like you to use the switch function here as opposed to the probably slightly easier option of using a lookup function so quite a few things to do there if you'd like to see my answer then please keep watching so in the first part of this exercise i asked you to replace the words month and team with data validation drop down lists so let's get rid of the word month and do our first drop down so into data validation we're going to choose list and we need a list of the months so our source is going to be a4 to a15 that looks perfect we're going to do exactly the same for team let's go into data validation we want to create a list but this time as source are the teams just here let's click the drop down looking good now that we have these two drop downs i want to create a two way x lookup formula that's going to find the value in this table based on my selections in the drop-down lists so we're going to type in equals x lookup so our first lookup value is going to be the month comma where are we looking it up well we're looking it up in the month array comma now we need to go into our second x lookup this time we're looking up the team we're looking this up in the team array what do we want to return well our result could be anywhere in our table so we need to select all of the values let's close off the first x lookup close off the second and hit enter and now we get our result let's just apply a little bit of accounting format and take those decimal places down and let's check that our result is correct so if we take a look at march team 4 this is the answer 3 2 5 4 and that's exactly what we have if we change the month to let's go for january and we'll say team two let's check that in the table that should be our result which it is so once you've completed that part of the exercise let's move on to the choose worksheet now for this i asked you to use the choose and run between functions to assign team members randomly to a team based on color so in here what we want to do is we want to type in equals choose and then we want to generate some random numbers so we're going to go straight into rand between now the random numbers that we want to generate are going to correspond to these colors in the list so what is the bottom random number that we want to generate well it's going to be number one what is the top well i have four items so the top number is going to be four now i'm back into my choose function and i can assign meaning to the random numbers that i'm generating by assigning them to colors so whenever we get a number one generated it's going to output red and we're going to lock this because we want to copy it down whenever there's number two we're going to get the next color whenever there's a number three we're going to get green and whenever there's number four we're going to get white let's close off choose hit enter and now if we double click to copy this down we have a bunch of random colors generated for each team member the final part of this exercise was to use the switch function to search in the table for the job rating and return the corresponding grade so we have the job ratings up here and we have exactly what those job ratings mean so excellent very good okay or poor and in the table below i have a list of employees the job rating that they've achieved but i want to return the grade now of course we could do this using some kind of lookup function but i wanted you to use the switch function so let's type in equals switch our expression so what are we evaluating here we're evaluating the value in cell b12 what is our value well if the value in cell b12 is one then we want it to output the word excellent if the value is two we want the word very good if the value is 3 then we want it to say okay and if the value is 4 we want it to say poor close off the bracket let's hit enter that looks good so far double click to copy down and there we have our results remember the main advantage of doing it this way over lookups is that i could now freely delete out this table at the top and it's not going to affect my formula so that's the end of this exercise i hope you got an okay with that i will see you in the next one in this lesson we're going to take a look at how we can use median and mode and both median and mode are statistical functions and if you're looking for them on the formulas ribbon you're not going to see statistical functions listed out here you're going to need to click on more functions and you'll find a group called statistical and both of them are in here if we scroll down to the m section we have median and we have mode now one thing to note here is that we have two mode functions mode dot mult and mode dot single in previous versions of excel i'm talking pretty old versions of excel sort of pre-2010 we only had a mode function and mode definitely had its limitations which meant that microsoft implemented two new functions mode.mult and mode.single i'm going to take a look at both of these so you understand the difference now the first thing we're going to do is we're going to find the average i'm going to use this little table over here which basically registers the steps per day of a particular person so in general they do between 5 000 and 6 000 steps per day over roughly a month and a half so if i want to find what the average number of steps per day for this person were and remember an average is also known as the mean we can simply use our average function we should all be very familiar with this we can simply select our data a3 to e12 close off the bracket and we can see that the average number of steps per day is 5617. now the median is similar to average but it finds the middle value in the list and a lot of the time you'll find that the median value is different to the average value remember with an average excel will basically add up all the numbers and divide it by the number of values to get the average whereas median will effectively sort all the numbers into order and find the middle value so let's take a look at the median for this data set so we're going to click in cell h3 type in equals median and we're just going to select our data range close the bracket hit enter and we have a completely different number now this number is also affected by whether you have an odd number of values or an even number of values if you have an odd number of values then excel will take the middle value whereas if you have an even number of values it will take the two middle values and average them to get the median so that's how it calculates this number now let's move on to mode now if we type in equals mode notice that we have three functions in this list mode multi mode single and then we have mode and what you'll notice about mode is that we have this little warning triangle next to it and that basically means that this is the compatibility version of mode so this is a formula that's available in older versions of excel so excel 2010 or previous mode has since been replaced in later versions of excel with the two functions that you can see above and both of these do slightly different things it is worth noting though that if you do use the two new functions in your spreadsheet and you send this spreadsheet to somebody using a very old version of excel they might not be able to see these calculations because these two functions won't be available to them in their version now hopefully that won't be too much of a problem there aren't that many people these days who are still using excel 2010 or excel 2007 but it's definitely something to be aware of so let's take a look at mode single first of all now mode single works pretty much the same as the old mode function and what mode single will do is it will look through our data and it will produce a result or it will tell us which one of these values occurs most frequently so i have quite a few values in this data set that repeat so i want to find out which one occurs most often so we're just going to select our data set one more time let's close the bracket and hit enter and i can see that 5 7 1 3 occurs the most now i'm just going to check that by applying some conditional formatting and i'm just going to highlight the cells that match 5713 and i can see that i have four occurrences of this value now there are other numbers that occur multiple times in this data set and if i highlight all of the duplicates you can see all of the numbers which occur more than once and this is pretty much what mode multiple does it will find the value that occurs the most so in this case it's going to be five seven one three we know that this occurs four times but mode multiple will also show us all of the other values that also occur four times and this is something that the old mode function in earlier versions of excel couldn't handle hence why we now have two new formulas so if we do mode dot multi and select our cell range let's close the bracket and hit enter notice that these results work like a dynamic array they spill down into the other cells now i'm going to take the decimal places down all the way on these numbers and i can now see that these three values occur four times in my data set so you use mode multiple if you want to extract multiple results most of us are used to using min and max to find the largest and the smallest values in a range of data but we also have a couple of functions called large and small which will help us find not the largest and the smallest values but the second third fourth fifth largest and smallest values in a range of data so let's take a look at how this works now the data that we're using here are just some album sales we can see the year that the album was released the artist the album name the genre that that album belongs to and then we have the sales of that album so in this first table i want to find out what the top five sales totals were so if i want to find out the top one this is fairly straightforward we can simply use max let's select our sales range close our bracket hit enter and i can see that the top selling album sold four and a half million copies but what if i want to find the second third fourth and fifth best-selling albums by sales total well this is where i would use the large function now if we type in equals large notice that we have two arguments array and then we have k now array is basically going to be what we want to return which is going to be one of these sales values and k is where we specify whether we want to return the second largest the third largest the fourth so on and so forth so if i want to return the second largest value i can simply type a 2 in there now if i close my bracket and hit enter it's going to tell me that the second best-selling album sold 3 million 300 000 copies and actually looking at this these shouldn't really be monetary values this is the number of copies so let's change that to number and take those decimal places down and we'll also modify our formatting just here as well we need to take that to number and take those down and let's add a comma separator perfect now if i want to copy this formula down like that it's not really going to work because for each formula it's attempting to pull back the second largest and also because we didn't lock the table range those cell references are moving down each time so these three values that it's returning is not correct so this is where using a cell reference for that k value can really help so if we do large again and let's select our array and i'm going to lock it this time by pressing f4 and for our value because we want to return the third largest i'm going to use the cell reference that contains three let's close the bracket and hit enter and now i should be able to drag this value down and i'm going to get the correct results similarly we can use the small function to tell us what the second lowest third lowest fourth lowest values are now if i want to find the lowest i could use min so we're going to select our range close the bracket hit enter and there we get a min now once again i'm just going to change these to numbers now if i want to find the second lowest i can use small we can select our array this is exactly the same as using large press f4 to lock and then i'm going to use the cell reference g5 as that k value close the bracket hit enter and now i can copy these down so really nice and straightforward now what if i want to move this on a little bit and maybe find the highest sales in the pop genre now if i want to find the absolute highest i could use the max ifs function for this so let's type in max ifs my max range well i want to return the sales again so e4 to e32 and we're going to press f4 to lock that and now we need to specify our criteria and our criteria range well we only want to look for sales for albums in the pop genre so our criteria range is going to be the genre and we need to lock that and our criteria is going to be pop now i don't have that written out in a cell so i'm just going to type it in quote marks into the formula let's hit enter and that is the result that i'm getting so let's apply the same formatting and if i take a quick look 4500 well i can see that sitting just there and yes that is in the pop genre now if i want to find out the second largest third largest fourth and fifth in the pop genre i could use large in a slightly different way so we're going to do a large but we're going to combine it with an if so my logical test is if the genre f4 to lock is equal to pop if that's true we want to return the sales values and we want to return the second largest so i'm going to use a cell reference just here and i also have to remember to lock this one f4 and let's close off our large formula let's hit enter we can copy this down and then apply our formatting let's do that again but this time for the lowest sales in the hip-hop genre so once again we can use min ifs to find the very lowest value so our min range is what we want to return so that's going to be the sales we need to do f4 to log our criteria and our criteria range well our criteria range is going to be genre f4 to lock and this time we're looking for hip hop so we want to put that in quote marks close the bracket hit enter and there we go and if we want to find out the second lowest we can use small and combine it with if a logical test is if the genre at 4 to lock is equal to hip hop then we want to return if that's true the sales f4 to lock but we want to return the second largest close off as small function hit enter and then we can safely drag this down last thing to do is just to apply some formatting so let's add a comma separator and take those decimal places all the way down so that is how you can use large and small and also a little bit of a look at max ifs and min ifs as well if you weren't familiar with those two useful functions in this lesson we're going to talk about ranking data within a list and the example that we're going to use is a list of movies in column a and then in column b we have the number of five star reviews that those movies have received and what we want to do in column c is rank those movies according to the number of five star reviews so effectively i want to see which movies have the highest number of five star reviews all the way down to the bottom now there are a couple of ways that i could do this and the most obvious one would be to simply sort this column column b and have the highest value at the top going all the way down to the lowest but the issue that you have when it comes to sorting is that it's going to sort the movie titles as well and i want to keep these in the order that they're currently in so instead what i could do is i could have another column column c where i just list out the rank of each movie which position in the list it falls into according to the number of five star reviews so for this we're going to use the rank function in excel now the first thing you'll notice here when you type in rank is that you get a number of different options come up in excel's intellisense menu and we're only really interested in the top three now notice rank which is the third one in this list this function is the one that we used to use in older versions of excel and it's still there as a legacy feature for anybody who's still using a very old version of excel the functionality of the rank function has since been replaced with the function above rank dot eq so this is the one that we're going to use for this first example let's press the tab key to select it now the rank function has three arguments number reference and then we have a third optional argument order and this is a really straightforward formula to use the first thing we need to provide is the number that we want to rank so that is going to be cell b4 the reference is what we want to rank it against so i want to rank this value in cell b4 against the other values in column b now because i don't have any other data other than the data that i'm currently using in column b i could select the entire column simply by clicking at the top of the column heading if i did have additional data further down here it would work out better for me to highlight the exact cell reference but for this example we can use the column let's close the bracket hit enter and now i can double click to fill that formula down so i can see that the movie 12 angry men is ranked 12th by the number of five star movie reviews and if we scroll down and look through this list i can see here if i'm looking for the number one reviewed movie i can see that it's the departed now when we typed in this formula there was an additional optional argument on the end which is order now if i press the comma here notice that we can choose if we want to rank these in descending if we want to rank these in descending or ascending order and the default here is descending but if i change this to a 1 on the end and press return and then double click to copy that down 12 angry men if we're working from the bottom of the list is in the 39th position and effectively the movie that's ranked 1 will be the one with the fewest 5 star reviews and that one would be seven samurai now i'm going to just edit this formula and i don't want to sort them in ascending order let's put that back to descending and double click to copy that formula down now if i was to sort these five star reviews this is going to make them a little bit easier to see so let's go to data and let's sort these largest to smallest now what would happen if i had two movies that achieved exactly the same number of five star reviews so let's cheat a little bit here and change the one underneath the green mile to the same value so nine seven one four take a look at what happens to the rank it's going to assign it the same number also notice that now it's skipped over number five also notice that we don't have a movie ranked in the fifth position because we have two movies occupying the fourth position so just something to be aware of when you're working with rank eq let's take a look at another example this time i'm going to show you rank average which is the other function that we have in that drop down list now what i have here are some dates and we have some calls locked so maybe this is some information from a call center and we're just reviewing how many calls they logged on any particular day so what i can do here let's do a rank first of all i'm going to do rank eq again let's just select that one my number is going to be the number of calls logged and my reference well i want to compare it to the rest of the values in column b i'm happy with the default of descending order so i don't need that final argument let's hit enter and then i can double click to copy that down and this is going to show me the days that achieve the highest number of calls and once again i could sort this list to make it a bit easier but i can see that this is in the number one position so on january the 4th 2022 we received the highest amount of calls of 197. now what about rank average what does that one do so let's type in equals we're going to go for rank again and the top option there is rank average now this has exactly the same arguments so let's do the same thing we're going to rank cell b4 against everything else in column b and we're going to sort in descending order so we'll just accept the default i'm going to do ctrl enter to stay in the same cell and then i can double click to copy that down now i'm going to sort these calls in descending order to make this a bit easier to see so let's sort largest to smallest and you'll find that the rank average is the same for many of these so you can see here for three four five six seven eight nine ten so on and so forth it's pretty much the same but take a look at these first two the rank average is 1.5 whereas the rank is 1. so what's occurring here well effectively the way that excel works out the rank average is it says this value is in position 1 and this value is in position 2. so if you add one and two together you get three and what would be the average of three over these two items well it would be 1.5 so that is where it gets its average from the same thing down here this value is in position 16 this value is in position 17. so if you add 16 and 17 together you get 33. what's 33 divided by 2 well it is 16.5 so that is the difference between rank and also rank average count blank is one of those formulas that pretty much does exactly what it says on the tin it counts all blank cells in a selected range and sometimes this can be really useful in many different scenarios so let's take a look at how count blank works now in this first example we have a table on the left hand side that just has some order ids some customer names and then we have email addresses for some of our customers now it appears that some of the customers haven't left their email address when they've placed an order so i've been tasked by my manager to go through all of our customers and tell him how many haven't left an email address and this is really going to determine if we're going to spend any money on developing our order system and making email addresses mandatory prior to placing an order so i can use the count blank function for this and it works exactly as you would expect we're going to type in equals and it's this one just here count blank and we have one argument for this and that is the range of cells so all i need to do here is select this range c4 to c23 and it's going to tell me that out of 20 customers seven of them have not left their email address now currently this data that i'm reviewing isn't in a table so as i add more customers to the bottom of this list my count blank formula isn't going to update so again this is another reason why using tables is so important let's press ctrl t click on ok and i'm going to leave the table style as it is let's give our table a name we're going to call this orders and now i'm going to update my count blank formula so let's replace the cell reference with the table name this time and hit enter so now if we add a new customer onto the bottom so let's say the order number is one zero two zero and i'm just going to use my name why not notice that my formula has updated so now we have eight people in the list who don't have an email address now if i was to add my email address in here and i will say that this isn't my actual email address but let's add one in and hit enter that count blank formula goes back down to 7 because it's no longer a blank cell so make sure that you put your data in a table and then your count blank formula will constantly update now another quick tip here notice as i've entered in my email address it's automatically turned into a hyperlink now the other email addresses in here are not hyperlinks so so if you just want to remove that hyperlink and just have it as plain text just right click on the cell and choose remove hyperlink let's take a look at another example of using countblank now what i have in the table on the right hand side are a list of employees and i have days of the week across the top and then i have a y which tells me on which days these employees are scheduled to work and the first thing i want to do here is i want to just do a count of how many days each employee is working so this is just a straightforward count a i'm going to select this row close the bracket and hit enter and then i can copy that formula down and that just lets me know how many days each of my employees is scheduled to work now what about if my manager comes to me and he's looking at rearranging the scheduling around a little bit because we need more cover on certain busier days what would be really useful to know is how many employees are off on mondays how many are off on tuesdays wednesdays so on and so forth and this is where the count blank function can come in really handy in a practical example so let's go for count blank our range we just want to select monday close the bracket hit enter and then i can drag this formula across and i can see how many employees we have off each day so on saturday we have the most amount of employees off but friday and saturday are our busiest evenings so this is really going to help my manager when it comes to analyzing how he wants to reorder the scheduling of stuff so count blank can be used in many different situations and is a really useful formula to have in your excel toolkit it's time now to do exercise four and in this exercise we're going to practice some of the skills that we've learned in this section so in the first part of this exercise i'd like you to practice using average median and mode and i'd like you to find the average median mode for the numbers in the table and i want you to ensure that when you're calculating the mode you return all values that occur more than once in the second part of this exercise i'd like you to practice using the large and small functions so first i'd like you to find the top three largest values in the table and then i'd like you to return the corresponding product so think about the formula you might need to use in order to do that i'd then like you to find the top three smallest values in the table and then return the corresponding product and when you're doing this i'd like you to ensure that you use cell references instead of hard-coded values wherever possible and also ensure that you apply the correct absolute referencing and in the final part of this exercise i'd like you to practice using the rank function so i'd like you to rank the scores of each student in descending order and then finally i'd like you to sort the table by rank so the highest scoring students are at the top so give that a go if you'd like to see my answer then please keep watching so the first thing i asked you to do here was find the average median and mode for the numbers in the table so this is pretty straightforward we're going to go into an average calculation we need to select all of the numbers in the table and that's going to give us the average next we need to find the median so again this is a straightforward calculation we're going to select all of the numbers again and hit enter and that is our median value again note that the median value is different to the average finally i asked you to find the mode of the numbers in the table and i added a little note here to ensure that when calculating the mode make sure you return all values that occur more than once so the reason why i specified that is because when we go into mode we have two options here mode dot mult or mode single so if we want to return all values that occur more than once we need to use mode dot mult let's select our values once again close the bracket hit enter and i can see that those values occur more than once in the table let's move on to the next worksheet so the first thing i wanted you to do here was to find the top three largest values in the table and then return the corresponding product now when it comes to finding the largest i could use the large function or i could still use max here so if you used either of those that's absolutely fine i'm going to go with large let's select it i'm looking for the largest value so this is our array and i want to find the largest so that is my second argument now i also mentioned in the notes that we need to use cell references wherever possible so instead of typing in here 1 to return the largest i'm going to use the cell reference that contains the number one let's close the bracket and hit enter now because i want to copy this formula down i need to make sure that i'm locking the sales array in the table so let's make sure that we f4 just there hit enter and then i can copy this down now in order to find the product i can do a simple index and match so let's type in equals index what do we want to return we want to return the product name this time so that's our array again we need to lock that because we want to drag this formula down we now need to find the row number so let's go straight into a match we're going to use the sales value as the lookup value comma where are we looking that up while we're looking it up in the sales array f4 to lock and we want to do an exact match close off match close off index ctrl enter and now we can drag this down let's do exactly the same to find the top three smallest values in the table so this is a very similar process we're just using the small function instead so we're looking for the smallest sales value in this array f4 to lock and we want to find the smallest i'm going to use a cell reference close the bracket ctrl enter and then we can copy down now once again to find the product i could use an index of match i could even use an x look up here if i wanted to so let's just switch it up and go for that instead so let's say x look up our lookup value is f 11. we're looking it up in this array just here we need to lock that f4 and our return array is going to be the product f4 to lock close the bracket control enter and now we can copy that down let's move on to the final part of this exercise which was using the rank function so i wanted you to use rank to rank the scores of each student in descending order so let's do this part first of all i'm going to click in column d and we're going to use our rank function now remember we have a few to choose from here we want to choose rank eq now the numbers that we want to rank are in this array just here and again i want to copy this down so we're going to do f4 what's our reference what am i ranking them against well i'm ranking them against each other so my second part is going to be this cell reference again f4 to lock i can then specify if i want to rank them in descending or ascending order and i asked you to rank them in descending order so we want a zero on the end here let's close the bracket ctrl enter and take a look at that i now have a rank assigned to all of those scores now in the final part of this exercise i asked you to sort the table by rank so the highest scoring students are at the top now if you try to click in rank and then use your sort commands so sort in ascending you'll find that you get this message just here now the way that we can overcome that is simply to throw away the underlying formula so let's select ctrl c to copy and then we can simply paste the values now we can go to the data tab we can sort a to zed and we can see our top ranked student at the top in this lesson we're going to talk about rounding values and specifically three functions round round up and round down and these three functions are excellent functions to know but are particularly important if you work in the financial sector where being extremely accurate with money is the highest priority so let's take a quick look at how rounding works now in this first table i just have some order ids i have a price and then i have the price plus tax and you can see over here we have a sales tax rate of 2.5 now the price plus tax i've already added in the calculation for this this is a very straightforward calculation that most of us probably know how to do by this stage we're doing b4 which is the price multiplied by the sales tax 2.50 and then we're adding on the price again because if we don't add on that price if i was just to remove this it's just going to give us the amount to increase by so we do need to make sure that we are in addition adding on the price again to the sales tax amount to get the total so for this first order order 1000 the price is 488 the price plus tax is 5 now what about if this customer just here decided that they wanted to order a thousand items what is the price going to be well this is a fairly straightforward calculation that most of us can do in our mind because it's simply going to be the price multiplied by one thousand five multiplied by a thousand is five thousand so effectively when we do this calculation we should get a result of five thousand let's see what happens i'm going to type in equals let's do c4 multiplied by the volume and hit enter take a look at that i don't get 5000 i get and 5002. now why am i getting five thousand and two and not five thousand well this is because of this number just here notice that i'm displaying two decimal places now the mistake that a lot of people make when they're looking at numbers is that they think that this is the actual real value of the number but because we've minimized this down to two decimal places if i was to increase these decimals and reveal a few more of those numbers underneath you're going to start to see why we're getting a result of five zero zero two as i increase the decimals and i've just increased them by one the actual number is five zero zero two which is why when we multiply it by a thousand we're getting a result of five zero zero two because increasing and decreasing these decimal places doesn't actually change the underlying value you're just changing the way that the number is displayed now how can we get this to be accurate because we do want this to say 5000 well this is where we can use rounding so we're going to use the round function to make sure that we're rounding to an even number of pennies so let's click on this cell we're going to jump up to the formula bar and we're going to use the round function now notice in the drop down list there we have a few different options we're going to choose the first one the first argument here is the number now the number is going to be generated by the formula that we already have in this cell so if we just click at the end and press comma the only other argument we have here is the number of digits so how many decimal places are we rounding to so i want to round to the penny so i'm going to round to two digits if i wanted to round to the dollar i would have a zero in here so let's go for two let's close the bracket and hit enter and would you take a look at that now our answer has changed to 5000 and if i start to copy this formula down some of these will change some of them may not and i can then double click to copy this formula down now it might be that you want to override the standard rounding rules and round up or round down to a specific number of decimal places well as you might have guessed we have two functions that can help us with that and they are called roundup and round down so let's edit our formula and this time we're going to use round up i'm going to keep it rounding up to two decimal places and if we hit enter notice that now it's rounded it up to the next penny if i wanted to round it up to the next dollar i would put a 0 in here and it's going to round it up to 6. now i don't want that i want to round it to the penny so let's put that back to two and once again i can double click to copy that formula down and you may have noticed that some of these numbers also changed and of course we can do the opposite of this we can use the round down formula instead i'm going to keep it on two decimal places let's hit enter it's rounded down to five if i double click again some of these are changing and rounding down to the nearest penny so just be aware of that when you're working with rounding make sure you understand the different options that you have and whether you're rounding to the nearest penny the nearest dollar so on and so forth now before we leave this lesson there's just one final thing that i want to show you now in column a we have some salaries and all of the people who have these salaries have got a bonus of 2.15 and we have their new salary including that bonus in column b but do you notice one difference between column b and column a in column b all of these salaries are nice and neat they're rounded nicely to the dollar whereas the original salaries aren't now the way that you can do that and i've already written this formula out in this column is i'm using round up we're doing our calculations so we're doing a 29 which is the salary we're multiplying it by the percentage of bonus and then we're adding the salary again but take a look at this last argument we're doing a minus 2 for the number of digits and this method will basically round to the nearest 100 if i was to replace this with a minus one it's going to round to the nearest ten dollars so let's change that and copy it down and you'll see that all of these will change so again just be aware that you can use minus figures as well as this last argument for the number of digits in this lesson we're going to explore some specialized rounding functions and these are all in the rounding wheelhouse but they do slightly different things so the functions that we're going to be looking at in this lesson are m round ceiling and floor so let's take a look at the data that we're going to be using so in column a we have some order ids in column b we have price and then in column c we have the price plus tax so pretty much the same as the last example we were looking at and there is the sales tax that we're using to perform this calculation and we already have rounding applied to this to two decimal places now what if i want to do something slightly different maybe i want all prices to be multiples of 5 cents or 10 cents or 20 cents or maybe 50 cents well if we want to do that then we can use the m round function which is going to allow us to round to a variety of different multiples so instead of round let's choose m round and i can simply add an m onto the front here now m round has two arguments number and multiple and once again the number is going to be generated by the formula that we currently have now i want to get rid of the multiple on the end there because we want to specify what multiple we want to round by so maybe i want to round to the nearest 50 cents so i'm gonna put in here point five oh and let's double click to copy this down and this now looks a lot neater because we've rounded to the nearest 50 cents if i wanted to round to the nearest 20 cents i could change that to 0.2 and hit enter and then when we copy this down you're going to see those figures change 0.1 for the nearest 10 cents once again we can double click to copy down and note our changed values so enround is a really flexible function which will just allow you to round to different multiples now what are ceiling and floor well these work along the same lines but ceiling means we're rounding up whereas floor means we're rounding down so ceiling works like m round where we can specify arrow multiple but it's going to round up and floor lets us specify our own multiple but it's going to round down so if i was to change m round to ceiling notice that the ceiling function is one of those legacy functions so it's since been replaced with the ceiling.math function so let's select that we have three arguments number significance and mode now the number once again is going to be generated by the formula that we already have let's delete out our previous multiple the significance argument is basically the multiple it's just been named something slightly different in this function so if i want to round up to the next 50 cents i would do exactly the same thing put point five zero in here and then there is a third argument which is also optional and that is mode a mode is really there to deal with negative values that you have in your data and you can specify if you want to round towards or away from zero now we don't have any negative values so we don't need that argument let's press enter and you can see what happens it's now rounding it up to the nearest 50 cents and i'm sure you can guess what floor does pretty much the same thing but the opposite way around let's replace ceiling.math with floor again we have the same situation here floor is an old function that has since been replaced with floor.math the same arguments here so this time let's round down to the nearest 10 cents press enter and let's double click to copy that down and there we go so three additional rounding functions there which are really handy to know particularly if you work in the financial industry the aggregate function is a great little function to know what it does is it enables us to calculate aggregates like sum average count min max whilst ignoring things like errors hidden rows subtotals and one of its main uses that i find is to perform some calculations and ignore any errors that we have in cells now aggregate if you're looking for it in the functions library if we click on the formulas tab you'll actually find it under the math and trig group and it's this one just here and if we take a look at the screen tip it says it returns an aggregate in a list or database so let's take a look at a quick example of how this works on the spreadsheet i just have a very simple list we have just some items and then we have the price for those items now notice that i have a couple of cells in this price column that contain errors i've got some div errors in there now it might be that instead of taking up time trying to work out why i'm getting these div errors i just want to perform a calculation on the rest of the prices well aggregate is going to allow me to do that because it's going to let me ignore any error values that i have in cells so let's type in equals aggregate now the first argument is producing a drop down list so that we need to select here what kind of calculation we want to do do we want to do an average or a count or a min or a max well i want to do a sum so i can simply type in number 9 or i can select it from the list by pressing the tab key comma to move on to the next argument now this is where i can specify what criteria i want to ignore and it's definitely worth taking a look through some of these number three in particular is pretty hardcore because it will ignore hidden rows error values nested subtotals and also other aggregate functions now in this first example we're going to start off with a very basic number six we wanted to ignore those div errors that we have in the cells so once again i could just type in number six or i can select it from the menu and press the tab key now notice underneath where we can see our arguments we have two different ways that we can put together an aggregate now the one that we're currently doing is this bottom one we've provided the function number the options and now we need to provide the reference so this is basically just the column that we want to aggregate so i'm going to select b4 to b14 let's close off the function and hit enter and it's adding up all of those numbers and ignoring these two now let's take a look at another example what i'm going to do here is i'm going to apply some filters to these headings and i'm going to hide some rows so let's say that i'm only interested in blouses dresses hats and let's go for trousers and sportswear click on ok so now my data contains both hidden rows and also we still have this error value in here so i want to use the aggregate function so i can do a count but i wanted to ignore the hidden rows and i wanted to ignore the errors as well so let's type in aggregate this time we're going to do a count so that is number two comma now we want to ignore hidden rows and error values and that is the option that we have all the way at the bottom option number seven comma the last thing we need to provide is the cell range which is b5 to b14 let's close the bracket and hit enter and i just need to change the formatting on this cell and take those decimal places down is telling me that we have four items and notice that we do once we're ignoring this item just here so we have blouse hat dress and sportswear so aggregate is a super useful function if you want to perform some kind of aggregation but you want to ignore certain values it's time now to complete exercise five where we're going to practice some of the skills that we've learned in this section of the course so the first thing i'd like you to do is to practice rounding so i'd like you to calculate the total including tax in column d for each of the invoices and you can see we have the sales tax amount this did in cell g3 and i'd like you to ensure that when you do this calculation you round up to two decimal places in the next part of this exercise i'd like you to practice more specialized rounding so i'd like you to calculate the bonus for each employee in column c i then like you to calculate the new salary for each employee in column d and then i'd like you to use specialized rounding to round the new salary to the nearest 100 so think about which function allows you to do that and then in the final part of this exercise i'd like you to practice using the aggregate function so i'd like you to use the aggregate function to complete the total and the average of all prices including tax and when you perform this calculation i'd like you to ignore errors and hidden rows see how you get along with that and if you'd like to see my answer then please keep watching so the first part of this exercise is fairly straightforward the first thing we need to do here is calculate the total including tax in column d so we're going to do a sum here and we're going to say c4 multiplied by g3 and we need to lock that cell reference because we want to copy this down and then we want to plus the amount now currently i haven't applied any rounding to this at all and that was the second part of this exercise i wanted you to ensure that you rounded up these totals to two decimal places so we need to make a modification to this formula and we need to specify that we want to round up the number is going to be generated by our sum calculation so we can jump straight to the end and just specify how many digits we want to round up to so in this case that is two digits close the bracket control enter and then we can double click to copy down nice and straightforward let's move on to the second part of this exercise where i asked you to use specialized rounding to calculate the new salary so the first thing we need to do here is we need to calculate the bonus for each employee in column c so we just want the bonus amount so we can just do b3 multiplied by g2 remember f4 to lock control enter let's double click to copy this down now we need to work out what the new salary is so that's going to be a straightforward sum calculation of adding salary and bonus together so let's do that first of all we're going to say equals sum and we just want to do b3 plus c3 control enter and if i copy this down take a look at these numbers they're kind of a bit all over the place we want these to be nice multiples of a hundred so what we can do here is use the m round function so let's go up to the formula bar and edit this first one we're going to use m mround the number will be generated by the sum calculation and then we just need to specify the multiple now i want to round to the nearest 100 dollars so i'm just going to type in 100 close the bracket ctrl enter and now when i double click to copy this down you can see we get nice rounded numbers to the nearest 100 the final part of this exercise was to use the aggregate function to complete the total and average for all of the prices inclusive of tax and i wanted you to ignore errors and hidden rows so if you take a look at my data i have some calculations going on here but i also have some errors in my data as well so i still want to perform a total and an average but i want to ignore those errors and also any hidden rows and i can see that i've got a hidden row here row 16 is currently hidden so let's do the total first of all we're going to go into aggregate we now need to choose our function now i want to use sum so i could just type in 9 or use my arrow keys and select it now i can choose what i want to ignore so i want to ignore hidden rows and error values so the one that closes fits that is the bottom one just here comma we now need to select the array that we want to find the total of so that is this array just here and that is pretty much all we need close the bracket hit enter and we get our total we need to do exactly the same but this time we're going to do an average so let's select aggregate we're doing an average let's type in number one and we want to do the same thing so number seven to ignore hidden rows and error values let's select our array close the bracket and hit enter and now i can apply my number formatting so i'm going to take the decimal places for this one down to two and let's add a comma separator i think that looks a lot better if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to get the course exercise files and follow along with this video click over there and click over there to watch more videos on youtube from simon says it

Channel: Simon Sez IT

Views: 263,686

Keywords: Excel 2021, Excel 365, Advanced Excel, Excel Advanced, excel tutorial, excel 2021 new features, excel tips and tricks, excel 2021 xlookup, advanced excel tricks, excel online course, excel dynamic array, randarray, randbetween, sortby, xlookup, xmatch, countblank, median and mode, excel statistics, excel mode, excel median, excel switch, excel choose, aggregate function, excel, office 2021, office 365, dynamic arrays, dynamic array function, xmatch function, simon sez it, median

Id: PlPgYOFJROI

Length: 223min 40sec (13420 seconds)

Published: Tue Jul 19 2022