- 2 months ago
Excel’s most common functions become easy to understand when you see them used in real data. This walkthrough covers aggregate functions like SUM, AVERAGE, and COUNT, then moves into date extraction with YEAR, MONTH, and DAY, plus text cleanup using UPPER, LOWER, and PROPER.
The lesson also shows how to build simple logic with IF and COUNTIF, including criteria written in quotes, and how to use VLOOKUP and XLOOKUP to pull matching values from another table. Along the way, it explains why anchoring cells matters, how exact matches work, and why XLOOKUP is often easier than VLOOKUP when working with larger Excel data sets.
If you’re learning Excel for data analytics, this is a practical guide to formulas, lookup functions, date functions, and text functions you’ll use again and again. It’s especially useful for beginners, analysts, and anyone who wants a clear refresher on everyday spreadsheet formulas.
Perfect for searches like Excel functions tutorial, common Excel formulas, VLOOKUP vs XLOOKUP, IF and COUNTIF examples, date functions in Excel, and Excel for data analysis. A helpful Excel guide for building faster workflows, cleaning data, and understanding the formulas that show up in real-world spreadsheet work.
The lesson also shows how to build simple logic with IF and COUNTIF, including criteria written in quotes, and how to use VLOOKUP and XLOOKUP to pull matching values from another table. Along the way, it explains why anchoring cells matters, how exact matches work, and why XLOOKUP is often easier than VLOOKUP when working with larger Excel data sets.
If you’re learning Excel for data analytics, this is a practical guide to formulas, lookup functions, date functions, and text functions you’ll use again and again. It’s especially useful for beginners, analysts, and anyone who wants a clear refresher on everyday spreadsheet formulas.
Perfect for searches like Excel functions tutorial, common Excel formulas, VLOOKUP vs XLOOKUP, IF and COUNTIF examples, date functions in Excel, and Excel for data analysis. A helpful Excel guide for building faster workflows, cleaning data, and understanding the formulas that show up in real-world spreadsheet work.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're going to be taking a look
00:03at some of the most common functions within Excel.
00:11These functions that we're going to take a look at
00:13are ones that I use almost every single time I'm in Excel.
00:16And so these are going to be really important for you to know,
00:18especially as you start using Excel more.
00:20So without wasting any more time,
00:21let's jump onto my screen and take a look.
00:23So let's get started.
00:24And the first one that we're going to look at
00:25is aggregate functions.
00:27And I use these almost every single time I'm in Excel.
00:30So these are extremely, extremely common to use.
00:33These are probably ones out of all the ones
00:35that we're going to look at today
00:36that you probably have used the most.
00:38And aggregate functions are quite simple.
00:40They're going to take an array, or in this case,
00:42a column of numbers,
00:43and you're going to be able to perform an aggregation on them.
00:46So you're either going to sum them up,
00:48you'll take an average of the number,
00:49or we can get a count of how many numbers are in here.
00:52So really quickly, let's come in here.
00:54We're going to say is equal to,
00:55and we're going to use our first one.
00:58This is the sum.
00:58This sum aggregation is going to add all the numbers
01:01in a range of cells.
01:03So we're going to open the parentheses.
01:05We're going to pass through this range of cells right here,
01:09and we're going to hit Enter.
01:10And so now it's going to sum up all of these numbers.
01:13That's going to be 5,220.
01:15So we're just adding all of these together
01:18with this sum function.
01:20Next, what we're going to do is take an average.
01:22Now to write this, we're going to say is equal to,
01:24and you have to spell out average.
01:26We're going to open this parentheses,
01:27and we're going to take all of these values,
01:29just like we did before.
01:30We're going to close up parentheses.
01:32And what it's doing is it's actually summing up all of these,
01:36and then it's dividing it by how many numbers,
01:39or the count of how many cells are in our range of cells.
01:44And so it would be the same as doing this.
01:46We could say is equal to, we could do the sum of all of these numbers,
01:52and then we would divide it by the count.
01:56And we'll look at count in just a little bit,
01:58but we'll do the count of all these numbers.
02:00So we'll take this right here, and we're going to hit Enter.
02:04And you'll notice they're the exact same thing.
02:06So it's a fairly simple equation or calculation
02:09in order to get the average.
02:10So you can either write it out yourself,
02:12or you can just use the average function.
02:13The next one is probably the simplest out of all of these.
02:17It's count.
02:19It's going to count the number of cells in a range that contain numbers.
02:22So if we come in here and we say we want to get the count of all of these
02:27in this range, we'll get a count of 10.
02:30Now, if we come up here and we delete one,
02:33you'll notice that because this is now blank, this changes to nine.
02:36And so it's only the populated cells that get counted.
02:40Let's move on to our next most common Excel functions.
02:43And that's going to be year, month, and day.
02:47Whenever I'm working with any type of date data type,
02:51I often want to extract data out of it.
02:53And this happens all the time.
02:55And so I don't just want to keep it in its current state,
02:57where it has the day, month, and year.
02:59I just want to extract out the year.
03:02Or I want to extract out the month or the day.
03:04And that is very common for me to do.
03:06And so these are very common functions for people to use.
03:10All you have to do to use them is you're going to say equal.
03:12Then you're going to say year.
03:13And it says this is going to return the year of a date,
03:16an integer in the range of 1900 to 9,999.
03:20Now, if you didn't know, and this is just a little extra fact,
03:23is Excel can't do things that are before 1900.
03:26So let's actually write this real quick.
03:29We're going to choose this one.
03:31It extracts that 2024.
03:33And we can drag that down.
03:35And that's going to work beautifully.
03:36And if we copy this and we change this to, let's do 1901.
03:42Change it just like this.
03:43If we drag this down, it's going to work beautifully.
03:46But what happens if we change this to, I don't know, the year 1472?
03:53Now you'll notice it's no longer acting like a date,
03:56where it's on the right side of the cell.
03:58It's now acting as a string.
04:00So then if we pull this down and we try to get it to work,
04:03it's not going to work because it's viewing it as a string.
04:07So this is just one of those nuances within Excel.
04:10They don't like historical events in Excel.
04:12So, you know, don't do anything historical ever in here.
04:15The next one that we can do is the exact same way we did year.
04:18We can do month.
04:20And all we have to do is type month.
04:22And then this is going to return the month within this date cell.
04:26So then we're going to select our date and it's going to extract the actual month.
04:32So we have one, two, three, four, five, five.
04:34And if we bring it all the way down, we're only taking out the month that is within this date
04:41over here.
04:42Next one we can do is the actual date.
04:44We're going to say is equal to day.
04:46This is going to return the day of the month.
04:49And again, we'll pass through our date cell.
04:52We'll drag this down.
04:54And there we go.
04:55So now we have this all broken up.
04:56And we can better perform certain aggregations on the year, month or day,
05:01depending on what we're actually wanting to do with the data.
05:04And again, this is done all the time.
05:05So it's really common to use these specific functions.
05:09Let's head over to formatting.
05:11Now, I'm calling this formatting, but it's more like text string manipulation.
05:15There's already formatting within Excel.
05:17But let's take a look at some of these.
05:19So we have this name right here.
05:21And it happens all the time with real data, where the data isn't all in the same format.
05:28Sometimes things just go wrong, and you want to format it all the same.
05:32And so it's very common to take data like this and format it all in the same way using one
05:38of these
05:38string functions.
05:39So we have upper, which right here, it's going to convert a text string to all uppercase letters.
05:45So again, if we pass this through, or, you know, this is a fun one,
05:49because we can pass through just this one cell, or we should be able to pass through
05:54this entire array or this cell reference.
05:58And then it's going to populate all the way down.
06:00And so we can do the same for lower.
06:03It's going to be the exact opposite of upper.
06:05We're going to take this entire reference cell.
06:08It's going to make it all lowercase.
06:11Or we can do proper.
06:12And I personally love typically either upper or proper.
06:16I don't personally use lower that much.
06:19I just don't like how it looks.
06:20So for a lot of my work, I usually make it upper or proper, depending on
06:24the actual use case.
06:26But I'm going to type proper in.
06:28And this one's unique because it's going to convert a text string to proper case.
06:31The first letter in each word to uppercase and all of the letters to lowercase.
06:35And this would kind of be what you would expect for a name column.
06:39I'm going to pass through that whole array.
06:41And this just looks really clean, really nice.
06:44And I do this on a lot with names or locations or things like that where I know it should
06:50look like this.
06:51And, you know, it doesn't have to exactly look like an upper or lower is that's just
06:55the one that I personally prefer.
06:57Now, let's come over here to this logical tab in here.
07:01We have some information on student names.
07:03So we have our student name.
07:05We have math, English and science.
07:07And these are their scores.
07:08And what we want to create is some type of logic.
07:12Maybe we want to say if they have a certain score within their math,
07:16then they're going to pass.
07:18And if not, then they're going to fail.
07:20Now, we could easily come through here and say, OK, this guy, this guy failed.
07:25This guy passed.
07:27This lady failed.
07:29This guy passed.
07:31You can do that manually.
07:32And that's going to take forever.
07:33But what you want to do is you want to say there's thousands of these.
07:36You just want to create a logic that you can apply to every single row.
07:39So we can come in here and we're going to write an if function.
07:43This is going to check whether a condition is met and return one value of true and another
07:47value if false.
07:49And if we open up this parenthesis, you'll see we have a logical test, value of true and
07:54value if false.
07:55So we're going to say if this is greater than, let's say, 60, then our value of true,
08:02if that is a true statement, then that means that this is a pass.
08:06If it is false, if it's less than 60, that means it's a fail.
08:11And then we're just going to pass this along all the way down.
08:15And you can see fail, pass, fail, pass, and fail.
08:20Now, there's another type of logical functions within Excel.
08:24And that's where you combine an aggregate function with an if statement.
08:28And for this one, we can do a count if.
08:32So it's an if function like we just did, but within a count aggregation.
08:38And this is going to count the number of cells within a range that meet the given criteria.
08:43So we have count if, and for our range, it's going to be these cells right here.
08:48Now, when we hit comma, we have to specify our criteria.
08:51Now, this is just a little bit different than this over here.
08:54We can still do greater than 60, but we have to put it within quotes.
08:59So we have to do, quote, greater than 60, quote, and then we close it.
09:04And you can see we have two that had greater than 60, AKA two that passed.
09:09And so if we didn't use, let's just come up here real quick.
09:13If we didn't use this, it doesn't read it in right.
09:16And so for all these count if functions, you just have to put the criteria in quotes.
09:21And there's average if, there's some if, there's all of these different ones.
09:26So just as a super quick example, we could do the average if, and we do the exact same thing.
09:32So we have the range and criteria.
09:34So we're going to do, here's our range.
09:36Our criteria is if it's greater than 60, and then we're going to close it.
09:42So you can see of all the people that passed this 88 and the 61,
09:47the average of all the people who passed was a 74.5.
09:51So that's how you can use these logical functions as well as these aggregate logical functions.
09:58Both are super common to use.
09:59And when I got into my first data analyst job, I saw these almost every single day.
10:04Very, very popular.
10:06The next one we're going to look at is these lookups.
10:09Now lookups just in general are extremely, extremely popular.
10:13And that typically comes from the fact that you have data that's not all in one place.
10:18You have data up here, and you have data down here.
10:22And what you want to do is you say, okay, this person's in North Carolina,
10:26and we want to know how much of a discount this person is going to get.
10:31And so the data is down here.
10:33We have North Carolina.
10:34We know it's a 10% discount, but there's so many of them.
10:37We don't want to go through and manually transfer this 10% and copy it and put it up here.
10:43Okay.
10:44Next one, South Carolina.
10:45Let's come down here.
10:46This will take forever.
10:48In fact, it should be 50%.
10:50This will take forever.
10:51And so we want to use something called a V lookup.
10:55Well, there's a V lookup.
10:56And then we'll look at XLOOKUP, and I'll kind of explain the difference.
10:59But the V lookup, we're going to write like this.
11:02We're going to say equal to, we'll say V lookup.
11:05It says, looks for a value in the leftmost column of a table.
11:09Now remember that, because that does come into play with something I'm going to talk
11:12about in a little bit.
11:14But then it returns a value in the same row from a column you specify.
11:18So we're going to do V lookup.
11:19Now we have to have our lookup value.
11:21What value do we want it to search for?
11:23We have right here.
11:25Then we want to specify the table array.
11:28That's this information right down here.
11:30So we want to specify this entire table is what it's calling it.
11:34And then we have to specify within this table,
11:38what number of the index do we want to be returned?
11:42Now that's actually the second.
11:43In Excel, this would be one and this is two.
11:47So you need to specify the second.
11:49And then if you want to go even further, you can do the last option, which is a range lookup,
11:54which says it has to be an exact match or an approximate match.
11:58Now we want to do an exact match.
12:00We don't want it to be approximate.
12:01So we're going to close this.
12:04And we can apply this all the way down.
12:07Or can we?
12:08No, we can't.
12:09Because the issue is, is that this is changing.
12:13This table array is changing every time we go down.
12:15So look, as we go down, let's go down one, you'll notice this went down one.
12:20And as we go down to the next one, it went down again.
12:24We need to do something called anchoring.
12:27And so we need to anchor this and let's hit escape.
12:30We need to come up here.
12:30We need to anchor this table array because this doesn't change.
12:34We want it to change as it goes down this table right here, this data right here.
12:39But we don't want this to change.
12:40So we need to hit F4.
12:42That's going to be right above my numbers on my keyboard.
12:45There's a little F4 button.
12:47You click on that.
12:47And now it has these little dollar signs.
12:49That means it's not going to change.
12:51So we're going to hit enter.
12:53And as we drag this down, you'll notice that it populates correctly.
12:57And if we go into any of these, it's going to be anchored in there, not changing.
13:03And that is really important for using these.
13:06Now, if you look up as great, it works for what it's good for.
13:10And honestly, a lot of people you may work with may use VLOOKUP.
13:14And so it's good to know how to use it.
13:15But I personally haven't been using VLOOKUP ever since XLOOKUP came out.
13:20Because XLOOKUP is just an easier version of VLOOKUP and it has less issues.
13:26And I'm going to kind of explain the limiting factor within VLOOKUP in just a second.
13:31So we're going to do XLOOKUP.
13:33So we're going to come over here.
13:34We're going to say XLOOKUP.
13:36Now, this is going to search a range or an array for a match and return the corresponding
13:42item from a second range or array.
13:45And this one, the syntax is much easier.
13:47We have our lookup value, just like before.
13:49We have our lookup array.
13:51That's this one.
13:53And we have our return array.
13:55And that's it.
13:56That's all we need.
13:57So we can close this and it's going to give us our discount.
14:02Now, again, we need to anchor these in place.
14:05Otherwise, we're going to have some errors.
14:07We can do this.
14:09We'll drag this all the way down.
14:11And it looks like this one is being read in as a percentage.
14:14This is just a general data type.
14:16So they're the exact same thing.
14:18They're just formatted differently.
14:20If we come in here, we change this to, let's say, percentage.
14:24You'll notice it's the same thing.
14:26So we can do it that way, too.
14:28Now, the limiting factor on VLOOKUPs is that little piece that you may have noticed
14:34a second ago, the leftmost column of a table.
14:37You have to be looking for the leftmost column.
14:39So down here, I just reversed this state and discount.
14:44That's all I did.
14:46And so what we're going to do is we're going to come in here.
14:48I'm going to rewrite the exact same thing.
14:50And we're going to try to write this.
14:51So we have our lookup value.
14:53We have our table array.
14:55And then we have our column index num.
14:57So it should be just one instead of two.
15:00So then we type in a one right here.
15:04And we can even do a false as well to make sure it's an exact match.
15:07But when we hit enter, it's not going to work.
15:10And that's because it automatically is defaulting to needing to look in that
15:14left-hand column for the value that you're passing through.
15:18So North Carolina has to be on this left-hand side.
15:21You cannot put other values in the left-hand side.
15:23And that does matter because when you start working with larger data sets and, you know,
15:28more complex data, the data doesn't always sit perfectly just like this.
15:32And so you then have to rework all of your stuff and switch columns around to make it work.
15:37And it can just get, you know, a little bit confusing.
15:39And so I personally love XLOOKUP.
15:42Now, there's also one called HLOOKUP.
15:44It's essentially the exact same thing for VLOOKUP except horizontal.
15:48Because VLOOKUP stands for vertical, like this.
15:52And an HLOOKUP is horizontal, like this.
15:54But the exact same thing.
15:56And so all of these functions in Excel are ones that you're going to see almost
16:01every single day if you're working in Excel.
16:03I know that I have used and seen these a million times within Excel over my years of working in
16:09it.
16:10And they're just crazy common to use.
16:11And so I highly recommend learning all of these different types of functions.
16:16With that being said, that is the end of the video.
16:18Thank you guys so much for watching.
16:20If you liked it, be sure to like and subscribe.
16:21If you haven't already, check out analystbuilder.com.
16:24I have a full Excel course on there, as well as a ton of other stuff,
16:28like SQL, Excel, Pandas, Tableau, and more.
16:31Thank you guys for watching.
16:32And I will see you in the next one.