- 4 weeks ago
Five of the most popular Excel functions take center stage here, with clear examples of how they work on real data. The lesson walks through Date Diff for finding the difference between two dates in years, months, or days, then shows how Text Split can separate addresses and other strings into useful parts like street, city, state, and ZIP code. It also covers IF statements for simple logic and bonus calculations, COUNTIF for counting records that meet a condition, SUBSTITUTE for cleaning up unwanted characters, and XLOOKUP for pulling matching values from another range.
Along the way, the video focuses on practical spreadsheet tasks like data cleaning, lookup formulas, conditional logic, and working with dates in Excel. It is especially useful for anyone learning Excel for data analytics, office work, or everyday reporting, and it highlights why these newer functions can save time compared with manual copying and formula workarounds.
Created for viewers searching for Excel tutorial, popular Excel functions, XLOOKUP, IF statement, COUNTIF, SUBSTITUTE, TEXTSPLIT, and Date Diff examples. Ideal for Excel beginners, data analysts, and anyone who wants faster spreadsheet workflows, cleaner data, and better formula skills for real-world Excel tasks.
Along the way, the video focuses on practical spreadsheet tasks like data cleaning, lookup formulas, conditional logic, and working with dates in Excel. It is especially useful for anyone learning Excel for data analytics, office work, or everyday reporting, and it highlights why these newer functions can save time compared with manual copying and formula workarounds.
Created for viewers searching for Excel tutorial, popular Excel functions, XLOOKUP, IF statement, COUNTIF, SUBSTITUTE, TEXTSPLIT, and Date Diff examples. Ideal for Excel beginners, data analysts, and anyone who wants faster spreadsheet workflows, cleaner data, and better formula skills for real-world Excel tasks.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're gonna be taking a look
00:03at some of the most popular functions within Excel.
00:12Now, being the most popular is very subjective,
00:14so I'm just gonna tell you right off the bat,
00:15these are the ones that I think are the most popular.
00:17These are the ones that when I'm out in the real world,
00:20working with real people and real data,
00:21these are the ones that I see people use a lot.
00:23They may not be the most common Excel functions,
00:26but I think they're very, very popular
00:28amongst a lot of Excel users.
00:30Most likely, if you're new to Excel,
00:31a lot of these functions are probably gonna be ones
00:33that you haven't used before
00:34because they aren't really the basic, simple functions
00:37that you'll see every single day.
00:38But without further ado,
00:39let's jump onto my screen and take a look.
00:41So let's jump right into it.
00:42Let's get started with this function called DateDiff.
00:45Now, DateDiff is gonna give you either years, months, days
00:49of the difference between two different dates.
00:52And this is a really, really good function to use,
00:55especially if you're working with something like transactions
00:57or diagnoses or something like this,
01:00where you have a hire and a termination date,
01:02and you're looking for maybe the average time
01:04someone spends at your company.
01:06And so this can be a really powerful function to use.
01:10And so we're gonna come in here,
01:11and first one that we're gonna look at
01:12is how to do this with years.
01:14How many years have passed from this date to this date?
01:18So we're gonna come in here,
01:20we're gonna say equal to, and we're gonna say date.
01:23And it's not even showing up down here,
01:24but I promise you it's in there.
01:26We're gonna say DateDiff.
01:28Now, there are three arguments that we need to pass through,
01:31or three parameters if you wanna call it a parameter.
01:34First, we need the starting date.
01:36Then we need the end date.
01:38Then we need to specify our date measurement,
01:41and that's gonna be years.
01:42So we're gonna do a year just like this,
01:44and we're gonna close our parentheses.
01:46And it's gonna tell us that this is an 11-year difference.
01:49Now, we can roll this all the way down.
01:51You can see this is a five-year difference,
01:54a 10-year difference.
01:56And that may not look immediately right,
01:59because we have 2008, and we have 2019.
02:01But notice that this is on 130, and this is 11-12.
02:05Here, it's looking at full, complete years
02:08that have elapsed between the first date
02:10and the second date.
02:11So this is only 10 years,
02:14and let's say, two and a half months.
02:16So it hasn't been a full 11 years,
02:19so it's only gonna have 10 right here.
02:22Now, this exact formula is going to work,
02:25or this exact function is going to work for months as well,
02:28except all we have to do is change this to an M.
02:31And when we run that, you can see this is 133 months,
02:35all the way down with this, and this is in months.
02:38And we can do the exact same thing for days as well.
02:41So we're gonna come in here,
02:42and for days, we're just gonna put a D there.
02:45And we're gonna scroll that all the way down.
02:46So this is a super popular function within Excel,
02:50especially when you're working with multiple dates,
02:53and you're trying to find the difference between them.
02:54You don't often just wanna say is equal to,
02:58let's do this minus this,
03:01because it's gonna give you the day,
03:02but what if you don't want just the days?
03:04You want the months, or the years,
03:05or some other measurement.
03:07And so that is a really, really good one to know how to use.
03:11Let's go on to our next one,
03:13which is gonna be text split.
03:15I don't know about you,
03:16but I see data like this all the time.
03:19I used to work in healthcare,
03:20and within healthcare,
03:21we would have a lot of patient addresses.
03:23And oftentimes, we wanna do some type of analysis
03:25on their zip code, or maybe their state or city.
03:28And we needed to split out this data,
03:31and that's where this text split function comes into play.
03:35So we can come in here,
03:36and we can say is equal to text split.
03:39It's gonna split text into rows or columns using delimiters.
03:43So when we come in here,
03:45we have different parameters.
03:46You have text, and the column delimiter,
03:49and we even have additional stuff as well,
03:51if we want to specify them.
03:53But in the most simplest sense,
03:55we just pass through our text,
03:56and then we say, okay, our delimiter,
03:58it's mostly a comma,
04:00but we have a space between these,
04:02and we'll see that in just a sec.
04:03We can do a comma,
04:05then we specify we have a comma as our delimiter.
04:08And just like that, we have 123 Main Street,
04:12Springfield, Illinois 62701.
04:15And we can drag this all the way down.
04:18Now, you'll notice it kind of have this
04:19little light blue line around this.
04:21That's because we wrote the function right here.
04:23It's in this column.
04:25But as we go over, you'll notice there is no function
04:29in this column or this column.
04:31That's because it all took place in this first one.
04:33Now, we can still use this data.
04:36We can come over here, and we can split this one
04:38into the zip code.
04:39We can do equals, and we'll do text split.
04:43And let me go back in.
04:44We'll do text split, and we'll reference this cell.
04:47We'll do comma with a space this time.
04:50And if we scroll over, it's over here because
04:55you can see in the data really quick.
04:57Let's zoom in really close.
04:58There's a space here.
05:00And so that is just a slight issue,
05:03I guess you could say, with it.
05:04But it did split it properly based off of the actual data.
05:09But either way, the data, whether it sits in there or not,
05:12we're still able to split it, and we're able to get all the data.
05:15Now, typically, when I get it like this,
05:18because we have all these cells where it's kind of like
05:22phantom populated, is that I'll take all this data,
05:25and I will then paste it in as a value.
05:29And so then that value, this actually becomes the text that was in there.
05:34So then I can come in here, and I could clean up this data further with something like trim.
05:38And I can trim down that data.
05:40And for this one, I can still bring it all the way down.
05:43And we'll take all of this.
05:45And this is a little bit trickier, actually, because if we try to put this in the state and zip
05:49code,
05:50if you watch this, we're going to paste this as values.
05:52It's not going to work.
05:54And that's because we still have, let's do Control-Z real quick.
05:57We still have this formula in here reading off of this data.
06:01So we're overwriting it, and then the data doesn't work.
06:05So all we're going to do is we're going to take this,
06:07we're going to paste it as values right underneath, at least for this example.
06:11So then we can delete all of this, and we can copy this through.
06:17There are probably more efficient ways to do this, but knowing how to use paste as values
06:23is actually quite useful as well.
06:25So that is how text split works.
06:28Again, it can be very handy when you're working with wanting to split out names or locations
06:33or addresses, very, very, very helpful and popular within Excel.
06:38Next, we're going to take a look at an if statement.
06:40Now, if statements allow you to specify a condition that needs to be met
06:44in order for something to happen.
06:46So let's say, for example, we have our sales right here.
06:50This is our employee name.
06:52Then we have how much they actually sold.
06:54Now, the target, let's say, was 5,000.
06:58They needed to sell more than 5,000 to meet their target.
07:00So we're going to say is equal to, and we're going to say if.
07:04Now, real quick, the if says checks whether a condition is met,
07:07returns one value if true and another value if it's false.
07:12So we're going to open up this logical test says this number needs to be greater than 5,000
07:19in order for this value of true to be populated.
07:23So if they did, we're going to say, yes, their target was met.
07:27If it was false, if it was less than 5,000, if it didn't meet this criteria,
07:32then we're going to say no.
07:34And there you go.
07:35This person did not meet that criteria, but we had multiple people who did.
07:40And you can write almost anything.
07:41And in fact, you can have if functions within if functions.
07:44You have nested if functions for a ton of multiple criteria need to be met.
07:49And if this happens, then do this.
07:51Another way that an if function can be used is for doing some type of simple calculation.
07:56So let's say, you know, if they met their goal,
07:59we're going to give them 10% of the sales that they made.
08:01So we're going to say is equal to, and we'll do if just like we did.
08:05So if this is greater than the 5,000, if that is true,
08:10then we're going to take the sales number times and we'll give them 10%, which is 0.1.
08:15This will be their bonus.
08:16If it's false, they get zero.
08:19And so this person, they got zero as a bonus.
08:23And you can see, we have some people who made some good money because they did meet
08:27their target that they were trying to achieve.
08:30Now, there's one other function that I want to show you.
08:32And that's these if functions combined with aggregate functions.
08:37Now, if we just type in here, we're going to say equal.
08:40Let's say we wanted to do a count if.
08:43A count if says counts the number of cells within a range that meets the given criteria.
08:49So count is very common.
08:51It's just an aggregate function.
08:52It gives us a count of how many cells are within the range.
08:55But what if we only want to count the ones that meet a criteria?
08:59That's where this count if comes in.
09:01So we can give them the range.
09:02We can say, here's the range.
09:04And our criteria is the people who met it.
09:06So we're going to say is greater than 5,000.
09:10But because this is count if, we actually have to put this within quotation marks.
09:14If we don't, it will not work.
09:16You can go ahead and try it.
09:17So we're going to do count if greater than 5,000.
09:20And you can see that we have three people who have met their target of $5,000.
09:26And there's also ones like average if, count if, all these different ones.
09:29In fact, I think if we put in, maybe not, I was going to say average if.
09:34But we have average if, we have some if.
09:37We have all different types of these combinations where it's this logical statement or conditional
09:43statements combined with an aggregate function.
09:46And they're very useful.
09:47The next one we're going to look at is substitute.
09:50Now, substitute is really popular because oftentimes when you're working with real data,
09:55the data doesn't come in perfect format.
09:58For example, this one right here, we have these dashes.
10:02And if you know anything about certain databases, certain databases doesn't like different types
10:07of characters.
10:08Maybe it's a comma.
10:10Certain databases don't like commas.
10:12Certain databases do not like dashes and so on and so forth.
10:15But underscores tend to be pretty safe throughout all of them.
10:19And so what if we wanted to change this?
10:21Maybe our database is reading this in when we, you know, connect it into a database or
10:25something.
10:26It's reading this in incorrectly.
10:27It's maybe separating out these values because of this dash.
10:31We can come in here and we can say equal substitute.
10:35Substitute is going to replace existing text with a new text in a string.
10:40So it has to be a string.
10:43So we're going to do substitute.
10:44Here's our text.
10:46Our old text is going to be a dash.
10:50Our new text is going to be, let's do an underscore, just like that.
10:55And then we're going to close it.
10:56We'll hit enter.
10:57And you'll see that we've changed this entire thing.
11:01So now we can apply this to all the rows.
11:04And this is really, really helpful, not just for, you know, changing it like this.
11:08It's also really good for cleaning up data that's not, you know, correct.
11:12For example, if we come in here, there'll be times where, you know, data will accidentally
11:16start with a comma, super, super common.
11:19Unfortunately, we can say is equal to substitute and we're going to go and get rid of that comma.
11:25So we're going to say within a eight, we had a comma and now we're going to want nothing.
11:31And we're just going to leave that blank.
11:32So right here, this is where we're specifying the comma.
11:35This is where we're specifying.
11:36We want to replace it with nothing.
11:38Hit enter and it gets rid of that completely.
11:41Now, what's so great about substitute is you can actually stack these on each other.
11:45So you can also do a substitute on this text.
11:50And so now we can then do it again.
11:52So now this is our text right here, this one where we just got rid of it.
11:56So it's the SKU dash 789.
11:59Now that becomes our text.
12:00And we can say the old text is a dash, just like we did above.
12:05And now we want an underscore.
12:07There we go.
12:08And so you can stack these and you can really filter out a lot of bad data or bad things
12:14that you don't want in there.
12:15And you can replace it with something that you do want.
12:18So that is really common, really popular to do within Excel, especially for things like
12:23data cleaning.
12:24I found that to be extremely helpful.
12:26The last thing that we're going to take a look at is XLOOKUP.
12:30Now, you may have heard of VLOOKUP.
12:32You may have heard of HLOOKUP, which is vertical lookup and horizontal lookup.
12:37But XLOOKUP to me is just much better than those.
12:41And it's becoming extremely popular.
12:43And you're seeing it a lot more in the actual workplace.
12:45Even people who are a little bit older who've been using Excel since like the 90s, they're
12:50starting to, you know, get around to the times and starting to use XLOOKUP.
12:53And if you're older and you're still using VLOOKUP, that wasn't an attack on you.
12:57I love you.
12:57So let's come up here.
12:59We're going to choose XLOOKUP.
13:01Let's look this up real quick.
13:03It says it searches a range or an array for a match and returns a corresponding item from
13:08a second range or array.
13:11Now, if you don't know what that means, let's actually exit out of here real quick.
13:14So say we want to get, we have this data in one area of Excel.
13:19We have this data in another area of Excel.
13:22But we want to have this county right here.
13:25But we can't just copy and paste this because it's going to be wrong.
13:30Because maybe Springfield and let's see, Odinville, oh, that is green.
13:35Let's see.
13:36Springfield is supposed to be Haverbrook.
13:38And it's green because we just copied and pasted it over.
13:41And this capital city is supposed to be green.
13:44And so not everything lines up exactly as it needs to be.
13:48And so what we can do is we can populate this data properly
13:52according to how the data actually sits over here.
13:54And this is really, really powerful.
13:57Let me hit Control-Z real quick.
13:58Really, really powerful.
14:00This is just an example.
14:01But imagine you're working with, you know, 50,000 rows.
14:04This can save you weeks worth of manual work.
14:08And so we're just going to, we're going to use this real quick.
14:11Now, XLOOKUP is very simple to use.
14:15And I'm not even going to compare it to VLOOKUP or HLOOKUP because those are a little bit more confusing.
14:19I'm just going to show you the power of XLOOKUP.
14:22So what's our lookup value?
14:23What are we trying to search for?
14:24We're searching for this.
14:26Where do we want to look up that array?
14:28Right down here in this city.
14:30And then what do we want to return?
14:33We want to return the county.
14:35That's it.
14:36That's all we need.
14:37We're going to hit enter.
14:38And this one was correctly populated with Haverbrook.
14:42So let's see.
14:43We have Springfield.
14:44There's Haverbrook.
14:45So that gets populated.
14:46So it looks in here.
14:48It says Springfield is right here.
14:50This is our return array.
14:52And so it just says, OK, let's go over here.
14:54We're going to take this one.
14:55It's as easy as that.
14:56Now, one thing to note is we can't just pull this down like this
15:01because what happens is as we go down, this lookup array and return array go down with it.
15:07And so as you get, whoops, as you go all the way down, you'll notice they're way far down
15:11because as we shift these downward, all of our reference cells are also going down.
15:17So what we need to do is we need to come up here and we actually need to anchor this.
15:20We have to say F4 and F4.
15:23You notice these dollar signs right here.
15:27That means that that is not going to change.
15:29That is going to be anchored in.
15:31So now when we hit enter, that one's going to work.
15:33But as we drag it down, every single one is going to work
15:37because when we come in here, this lookup array, this return array didn't change
15:41and we didn't want it to change.
15:43The only one we wanted to change was the city as we dragged it down.
15:47So these are some of the most popular Excel functions,
15:52one that as you really start getting into Excel, you're going to be using a lot,
15:56especially if you're working with a lot of data.
15:58You're going to be using these ones all the time.
16:01So I hope that that was helpful.
16:02I hope that you learned something.
16:03If you did, be sure to like and subscribe below.
16:05I have a ton of Excel videos and I also have a full Excel course over on analystbuilder.com,
16:10along with all of my other courses on SQL, Python, Tableau and more.
16:14With that being said, that is the end of the video.
16:16I hope you enjoyed it and I hope to see you in the next one.
16:19I hope you enjoyed it and I hope you enjoyed it.