what's going on everybody welcome back to another video today we're going to be taking a look at some of the most common functions within [Music] Excel these functions that we're going to take a look at are ones that I use almost every single time I'm in Excel and so these are going to be really important for you to know especially as you start using Excel more so without wasting any more time let's jump onto my screen and take a look so let's get started and the first one that we're going to look at is aggregate functions and I use these almost every single time I'm in Excel so these are extremely extremely common to use these are probably ones out of all the ones that we're going to look at today that you probably have used the most and aggregate functions are quite simple they're going to take an array or in this case a column of numbers and you're going to be able to perform an aggregation on them so you're either going to sum them up you'll take an average of the number or we can get a count of how many numbers are in here so really quickly let's come in here we're going to say is equal to and we're going to use our first one this is the sum this sum aggregation is going to add all the numbers in a range of cells so we're going to open the parenthesis we're going to pass through this range of cells right here and we're going to hit enter and so now it's going to sum up all of these numbers that's going to be 5,220 so we're just adding all of these together with this sum function next we're going to do is take an average now to write this we're going to say is equal to and you have to spell out average we're going to open this parenthesis and we're going to take all of these values just like we we did before we're going to close our parenthesis and what it's doing is it's actually summing up all of these and then it's dividing it by how many numbers or the count of how many uh cells are in our range of cells and so it'd be the same as doing this we could say is equal to we could do the sum of all of these numbers and then we would divide it by the count and we'll look at count in just a little bit but we'll do the count of all these numbers so we'll take this right here and we're going to hit enter and you'll notice they're the exact same thing so it's a fairly simple uh equation or calculation in order to get the average so you can either write it out yourself or you can just use the average function the next one is probably the simplest out of all of these it's count it's going to count the number of cells in a Range that contain numbers so if we come in here and we say we want to get the count of all of these in this range we'll get a count of 10 now if we come up here and we delete one you'll notice that because this is now blank this changes to nine and so it's only the populated cells that get counted let's move on to our next most common Excel functions and that's going to be year month and day whenever I'm working with any type of date data type I often want to extract data out of it and this happens all the time and so I don't just want to keep it in its current state where it has the day month and year I just want to extract out the year or I want to extract out the month or the day and that is very common for me to do and so these are very common functions for people to use all you have to do to use them is you're going to say equal then you're going to say year and it says this is going to return the year of a date an integer in the range of 1900 to 99,999 now if you didn't know and this is just a little extra fact is Excel can't do things that are before 1900 so uh let's actually write this real quick we're going to choose this one it extracts that 2024 and we can drag that down and that's going to work beautifully and if we copy this and we change this to uh let's do 1901 change it just like this if we drag this down it's going to work beautifully but what happens if we change this to I don't know the year uh 1472 now you'll notice it's no longer acting like a date where it's on the right side of this cell it's now acting as a string so then if we pull this down and we try to get it to work it's not going to work because it's viewing it as a string that is just one of those nuances within Excel uh they don't like historical events in Excel so you know don't do anything historical ever in here U the next one that we can do is the exact same way we did year we can do month and all we have to do is type month and then this is going to return the month within this date cell so then we're going to select our date and it's going to extract the actual month so we have 1 2 3 4 5 five and if we bring it all the way down we're only taking out the month that is within this uh date over here next one we can do is the actual date we're going to say is equal to day this is going to return the day of the month and again we'll pass through our date cell we'll drag this down and there we go so now we have this all broken up and we can better perform certain aggregation on the year month or day depending on what we're actually wanting to do with the data and again this is done all the time so it's really common to use these specific functions let's head over to formatting now I'm calling this formatting but it's more like text string manipulation uh there's already formatting within Excel but let's take a look at some of these so we have this name right here and it's happens all the time with real data where the data isn't all in the same format sometimes things just go wrong and you want to format it all the same and so it's very common to take data like this and format it all in the same way using one of these uh string functions so we have upper which right here it's going to convert a text string to all uppercase letters so again if we pass this through or uh you know this is a fun one because we can pass through just this one cell or we should be able to pass through this entire array or this uh cell reference and then it's going to populate all the way down and so we can do the same for lower it's going to be the exact opposite of upper where we're going to take this entire uh reference cell it's going to make it all lowercase or we can do proper and I personally love typically either upper or proper I don't personally use lower that much I just don't like how it looks so for a lot of my work I usually make it upper or proper depending on uh the actual use case but I'm going to type proper in and this one's unique because it's going to convert a text string to proper case the first letter in each word to uppercase and all of the letters to lowercase and this would kind of be what you would expect for a name column we'll pass through that whole array and this just looks uh really clean really nice and I do this on a lot with names or locations or things like that where I know it should look like this and you know it doesn't have to exactly look like an upper or lower is that's just the one that I uh personally prefer now let's come over here to this logical tab in here we have some information on student name so we have our student name we have math English and Science and these are their scores and what we want to create is some type of logic maybe we want to say if they have a certain score within their math then they're going to pass and if not then they're going to fail now we could easily come through here and say okay this guy this guy failed this guy passed uh this lady failed this this guy uh ped you can do that manually and that's going to take forever but what you want to do is you want to say there's thousands of these you just want to create a logic that you can apply to every single row so we can come in here and we're going to write an if function this is going to check whether a condition is met and return one value of true and another value if false and if we open up this parentheses you'll see we have a logical test value of true and value if false so we're going to say if this is greater than let's say 60 then our value of true if that is a true statement then that means that this is a pass if it is false if it's less than 60 that means it's a fail and then we're just going to pass this along all the way down and you can see fail pass fail pass and fail now there's another type of logical functions within Excel and that's where you combine an aggregate function with an if statement and for this one we can do a count if so it's an IF function like we just did but within a count aggregation and this is going to count the number of cells within a range that meet the given criteria so we have count if and for our range it's going to be these cells right here now when we hit comma we have to specify our criteria now this is just a little bit different than this over here we can still do greater than 60 but we have to put it within quotes So so we have to do quote greater than 60 quote and then we close it and you can see we have two that had greater than 60 AKA to that passed and so if we didn't use uh let's just come up here real quick if we didn't use this it doesn't read it in right and so for all these count if functions you just have to put the criteria in quotes and there's average if there's some if there's all of these uh different ones so just as a super quick example we could do uh the average if and we can do the exact same thing so we have the range and criteria so we're going to do here's our range our criteria is if it's greater than 60 and then we're going to close it so you can see of all the people that passed this 88 and the 61 the average of all the people who passed was a 74.5 so that's how you can use these logical uh functions as well as these aggregate logical functions both are super common to use and when I got into my first data analyst job I saw these almost every single day very very uh popular the next one we're going to look at is these lookups now lookups just in general are extremely extremely popular and that typically comes from the fact that you have data that's not all in one place you have data up here and you have data down here and what you want to do is you say okay this person's in North Carolina and we want to know how much of a discount this person is going to get and so the data down here we have North Carolina we know it's a 10% discount but there's so many of them we don't want to go through and manually uh transfer this 10% and copy it and put it up here okay next one's South Carolina let's come down here this will take forever in fact uh should be %. this will take forever and so we want to use something called a v lookup well there's a vlookup and then we'll look at X lookup and I'll kind of explain the difference but the vlookup we're going to write like this we're going to say equal to we'll say V lookup it says looks for a value in the leftmost column of a table now remember that because that does come into play with something I'm going to talk about in a little bit but then it returns a value in the same Row from a column you specify so we're going to do vlookup now we have to have our lookup value what value do we want it to search for we have right here then we want to specify the table array that's this information right down here so we want to specify this entire table is what it's calling it and then we have to specify within this table what number of the index do we want to be returned now that's actually the second in Excel this would be one and this is two so we need to specify the second and then if you want to go even further you can do uh the last option which is a range lookup which says it has to be an exact match or an approximate match now we want to do an exact match we don't want it to be approximate so we're going to close this and we can apply this all the way down or can we uh no we can't because the issue is is that this is changing this table array is changing every time we go down so look as we go down let's go down one you'll notice this went down one and as we go down to the next one it went down again we need to do something called anchoring and so we need to Anchor this and let's hit Escape we need to come up here we need to Anchor this table array because this doesn't change we want it to change as it goes down this uh table right here this data right here but we don't want this to change so we need to hit F4 that's going to be uh right above my numbers on my keyboard there's a little F4 button you click on that and now it has these little dollar signs that means it's not going to change so we're going to hit enter and as we drag this down you'll notice that it populates correctly and if we go into any of these it's going to uh be anchored in there not changing and that is really important for using these now V look up is great um it it works for what it's good for and honestly a lot of you know people you may work with may use vlookup and so it's good to know how to use it but I personally haven't been using vlookup ever since xlookup came out uh because X lookup is like a just an easier version of vlookup and has less issues and I'm going to kind of explain the limiting factor within V lookup in just a second so we're going to do X lookup so we're going to come over here we're going to say x lookup now this is going to search a range or an array for a match and return the corresp and return the corresponding item from a second range or array and this one the syntax is much easier we have our lookup value just like before we have our lookup array that's this one and we have our return array and that's it that's all we need then we can close this and it's going to give us our discount now again we need to Anchor these in place otherwise uh we're going to have some errors we can do this we'll drag this all the way down and it looks like this one is being read as a percentage this is just uh General data type so they're the exact same thing they're just uh formatted differently if we come in here we change this to let's say percentage you'll notice it's the same thing so we can uh we can do it that way too now the limiting factor on vlookups is that little piece that you may have noticed a second ago the leftmost column of a table you have to be looking for in the leftmost column so down here I just reversed this uh State and discount that's all I did and so what we're going to do is we're going to come in here and we're going to rewrite the exact same thing and we're going to try to write this so we have our lookup value we have our table array and then we have our column index num so it should be just one instead of two so then we type in a one right here and we can even do a false as well to make sure it's an exact match but when we hit enter it's not going to work and that's because it automatically is defaulting to needing to look at that leftand column for the value that you're passing through so North Carolina has to be on this left-and side you cannot put other values in the left- hand side and that does matter because when you start working with uh larger data sets and you know more complex data the data doesn't always sit perfectly just like this and so you then have to rework all of your stuff and switch columns around to make it work and it can just get you know a little bit confusing and so I personally love X lookup now there's also one called H lookup it's essentially the exact same thing for V lookup except horizontal because V lookup stands for vertical like this and an H lookup is horizontal like this but the exact same thing and so all of these functions Excel are ones that you're going to see almost every single day if you're working in Excel I know that I have used and seen these a million times uh within Excel over my years of working in it and they're just crazy common to use so I highly recommend learning all of these different types of functions with that being said that is the end of the video thank you guys so much for watching if you liked it be sure to like And subscribe if you haven't already check out analyst builder.com I have a full Excel course on there as well as a ton of other stuff like SQL Excel pandas Tableau and more thank you guys for watching and I will see you in the next one [Music]