 What's going on everybody? Today we're going to be looking at formulas in Excel. Now I know what you're thinking. There's absolutely no way that you're going to be able to show us every single formula in Excel and you're absolutely right. But I am going to show you some of my favorites and the ones that I found the most useful and then you can go ahead and practice those and try those out. And if there are ones that you really want me to do and you think that I missed, put it in the comments below and I will see those and I'll try to make a list of those and make another video on formulas and include all of those as well. And now before we jump into the actual tutorial, I want to give a huge shout out to the sponsor of the series and that is Udemy. You guys already know if you watched any of my videos that I absolutely love Udemy. I mean, honestly, they were the ones who got me started and were able to give me affordable courses for me to get started as a data analyst. I learned SQL and Excel and Python all through Udemy courses. And so if you were looking for a platform to take a course, I absolutely recommend you look at Udemy. They have fantastic sales going on right now, especially during the holiday season in this new year. And so if you're looking to take a full fledged Excel course, I have some of my favorites in the description below. And now without further ado, let's jump on to my screening and get started with the tutorial. All right, now before we start, I want to say that this is not like every other tutorial that I have created. This one is very streamlined. Okay, so I already know exactly what I'm going to do. There's not going to be much messing around. I'd love little notes here and there. And I'm going to try to get through it because there's a lot of them to get through. So all these ones at the bottom. Now these are ones that I use a lot that I think are useful. Again, if you know other ones that you use a lot, the things I should be using, which I know there are ones that I left out of here, you know, put it in the comments. I'll see the ones that people are liking and I will, I will create more videos on these because I know there are so many. I also will save this Excel in on the GitHub. So you can go and download it. It'll be exactly what you're looking at right now. I highly recommend trying these formulas out for yourself. So you can get a feel for how they work and how they're actually used and you can mess around with it yourself. So as you can see at the bottom, we're going to start with Max Min. And then we're going to go on to some more, I think a little bit more difficult things. And all these things are super useful. I'll try to talk about how you can actually use it as we go through it. Some are super self-explanatory, but some may not be. So this one I think is super self-explanatory, but again, one that you're going to use all the time. And so what we can do is we can say equal, and that's how you kind of start off saying this is going to be a formula. In this cell, equal means I am now creating a formula. And we're going to say MAX, and I'll hit Tab, and so it'll kind of populate it. And right here, if you've never seen a formula before, it'll kind of give you what the inputs need to be. So it's going to say Max of number one, number two, et cetera, et cetera. What we're going to do is we're going to give a range. So we're going to go from here down to here. You don't have to close the parentheses, but you can. I'm going to, and then you hit Enter. And so for this date, it's going to give us the Max date. Now these are the start dates for these people right here. And so if we just kind of glance through here, we can see that 2013 was last year, and this one is actually the latest in that year. And so it gave us the correct one. The min is going to do the exact opposite. It's going to give us the smallest. And so we'll give it the same range. We'll close the parentheses. And it's going to say December 7th of 1995. And we can see that that is correct. So Michael Scott started in 1995, the earliest of all the employees. And you can do the exact same thing for really any of these columns. We can see who's making the most money, or at least what the highest salary is. So we'll do Max. And then we'll do the salary range. And so this is, this one again, whoops, what did I do? Oh, I did the wrong range, didn't I? No, I didn't do the wrong range. It's just, there it goes. This column was a date range, or a date column for whatever reason. Let me get rid of that. And then we can do equals min. And we'll do again, we'll do the salary. And at a quick glance, we can see that Pam Beasley is making the least. And 65,000 is Michael Scott, who's making that. So super simple. It shows the max, it shows the min. You can select a range. There you go. Let's move on to if and ifs. Now, if is, I think, pretty straightforward. So all you're going to do is you're going to say, if this, then that. Ifs is a little bit different. So ifs is, you can, you can put multiple conditions. And as we're writing it, I'll show you kind of what it's, the conditions that need to be met. All right. So we're going to click right here. We're going to say equal, we're going to do if, hit tab. And we need a logical test. And so we're going to give it a range, or something, we're going to say if it's equal, greater two, something like that. Then we're going to say, if the value is true, what's the, what is going to be the output? Or if the value is false, what's going to be the output? So let's do this right here. We'll do this age range. And so if they are greater than, let's say, let's do 30. If they're greater than 30, we're going to do a comma. And so if the value is true, what should be the output? If they're greater than 30, we're going to call them old. And then if it is false, so if they're younger than 30, what should it say? And we're going to say young. And we'll close the parentheses. And there you go. So if they're over 30, then they are going to have, or if they're a younger than 30, they're going to have young. Now, this is something where you need to specify if you want 30 and over or over 30, we chose over 30. So 30 is not included in that. So they're going to be young. Now, let's get, we don't actually need two of these, that's pretty self-explanatory. The ifs is a little bit different, right? You can have multiple conditions. So let's open that up real quick. So ifs, and now we have a logical test value, if that's true, then you can do logical test two, value if that's true. So you can have multiple, multiple, multiple things. Now, this one is a little bit different. In this one, oops, let me get out of this. In this one, you had a value of true, a value of false. Ifs does not have that. Ifs is going to give you different ranges and different specific conditions. And you can't say if this one's false, you're just going to have multiple conditions. So let's do equals and ifs tab, and we'll do our first logical test. So let's do if the salesman, or if that equals to salesman, we're going to say, we're going to respond with sales. So that's if the value is true. That's what we want the output to be. Now we're going to go on to our logical test two. So you're going to see this pattern, right? If this is our conditional or logical test. So if this is true, this is what's going to be returned. So you'll notice that's just a pretty simple pattern. We can just do random things. So if it's equal to sales, I will just do the same one. If that is equal to say HR, we can say fire immediately. And now we're going to say, if it's equal to regional manager, I'm going to say, give Christmas bonus. And we'll close the parentheses and let's see what we get. So as you can see, there's no default value for true or false. Like this one, there was a logical test. And if it was true, there was a value. And if it was false, there was a value. So for every single one, you'll get a value. For this one, that's not exactly going to happen. As you can see, there are these NAs. Now, when that happens, it just means nothing met that condition. So we never said anything about supplier relations. We never said anything about accountants. But if it was part of that if statement, then it got something. And so that is how the ifs works. Now, let's move on to length. This is exactly what we're going to do. But some of the uses for this, for the length, I've used it for a lot of different things. One thing that I've used it for in the past, and max and ifs, you can use it for almost anything. Length is, there's a lot of different use cases. One, I used to work with a lot of customer data or patient data. They have social security numbers. And if there was bad social security numbers, we didn't want to include that. And so we do like the length of that. And if a social security number was, let's say, 10 numbers or 11 numbers, where it should only be nine, or however many there are, I think it's nine, then we know that that social security number is incorrect. And then we can get rid of that or discard it from our results. That's just an example, right? So for this, oops, what did I do that? I did control z to undo that, if you didn't know how to do that. So we're going to do equals len, which is length. And again, if you didn't see that, it returns the number of characters in a text string. So let's go right here, and let's go to their last name. And we'll give it a range. So it's going to tell us how many characters are in that string. So for Halpert, it's seven characters. For Flenderson, it's 10 characters. And we're able to see a length. And so again, there are a lot of different use cases for this. The social security number was one. Another one is phone numbers, right? If you look at the length of the phone numbers, and there's ones that are like 12 numbers long, you know, those might not be ones that are accurate. And you need to go look at them and see if you want to include them in your results or your output. So that is how length is done. Let's move right over to the left and right. I might be going a little fast, but, you know, I'm keeping it, I'm keeping it alive, I'm keeping this on our feet. So let's keep going. Left and right are kind of like substrings. If you've taken the sequel tutorial series that I've done, substrings are where you can choose a certain part of the text string, and you can extract data from that. And you usually have to reference a certain number, so a certain amount of characters. That's the exact same thing, except unfortunately, there's no substring. There's substitute, but there's no substring. Left and right is really the closest thing that we have. So let's kind of take a look real quick and see what we can do. So we're going to do left, and it's going to say return to specified number of characters from the start of a text string. So we're starting from the very far left, and we need to choose our text, and then choose the number of characters that we're going to be looking over. So let's go over here, and let's just choose, you know, start simple. We'll get a little bit more advanced. So we have, this is our text range. So these are the ones that we want to look at. And then how many characters do we want to look forward? And we'll just choose three as an example. And so you can see that it takes the first three characters from every single thing. Now, you can also do this with numbers. It doesn't just have to be, you know, name with actual words or letters. You can do the exact same thing. So you can say right, and we're going to choose our string. And let's do this one. So, you know, all of them start with 100. And we'll just say we want to take the last one. So this one is going to start from the very far right, and go over one character. So right here, you can see this is our range, and I just chose one. So starting from the very far right, we go over one character, and that's what we take. And so that can definitely be useful. Another one that you can do, and this one is one that I have used so many times. I mean, honestly, countless times in actually using this in my job. So we're going to go from the right, and we're going to look at a date. So, you know, sometimes you have these date structures, month, month, day, day, year, year, year, or year, you know, day, month, year, all these different, and sometimes you just want to extract the month or the year or something like that, the day. And so we want to come in here, and we're just going to extract the oops, I want to make that arrange, we want to extract the year of the start dates. So we're going to do that. And then we're going to go over four, because we want to take the first four characters from the right to give us the entire year. Let's do that. And now we can see exactly the year. And this can be just super, super useful. This is, again, one that I've used a lot. And so that is one that you might want to remember in case you're ever doing analysis on, you know, start and end dates or anything with date data. Again, one that I highly recommend remembering. Let's go over to date to text. I actually probably should have included that before, because I actually used it in this one. If you notice right here, this is a text. So in this one we just did that was a text. You can't do this right on start and end dates when it's a date format. And let me show you. So this is a date. Now, if I do equals, and you know, we just did this, let's do on the end date. And I'll do the whole range. Give me a second. And we'll do four. It's giving us completely random numbers. Why is that? Because underneath the date range, there are numbers, right? So if I go right here, and I make this general, it's gonna have a numbers and look, these are the first four characters from the right. And so it's doing what it's supposed to do, but it's not doing what we actually want. And that's the issue. So how can we convert this? Now, there are a ton of different ways. But the quickest, probably the easiest, besides actually writing it out like this, like 11-2-2001, which then converts it to a date format. But what you can do, you know, just so you know, you can create it as a text. You can do 11-2-2001. And now it will stay a text string. And as you can tell, these are a little bit different because this one is format or situated on the right. And this one's on the left. That's how you can tell the difference. Now, if you don't want to do it by hand, completely manually and waste hours of your time, you can do it in a very simple way. So we're gonna do text. So this is the exact formula that we're going to use. So let's get rid of that one. There we go. So we're gonna do equals, we're gonna do, oops, text. It says, converts a value to text in a specific number format. So for a date format, we can choose a date format and then it'll convert it to a text for us, which saves so much time, I promise you. Let's do all of these just like we did. And then we need to tell it what the format is. If we don't, if we tell it something incorrect, it's gonna give us a completely terrible output or just give us an error altogether. So this is a day-day, month-month, year-year, year-year format. And that is what we're gonna do. So we're gonna dd slash mm slash y, y, y, y, and close that up. And there you go. And now because it's in a formula, what we need to do is be this and paste it right over here. And now you can see that is a general. This is something that we can use as a string. And let's just check it just to make sure. We're gonna do right. We're gonna do this one. Let's do all of them. And we'll do four. And there you go. So now it works. That is what we are looking for. And you can do that. Imagine doing that with millions of rows or, you know, let's say 10,000 rows. It's gonna be a breeze, right? It's gonna take you two minutes or a minute to do everything that you want to do instead of having to just do a bunch of mess to convert it to a string, which I promise you I've done and it just takes forever. It's terrible. So that is date to text. Super helpful formula. Let's go over to trim. Now I purposefully messed up this column. Now why do I mess it up like this? Because when you're working with real data, you're gonna get data like this. It's messy. It's dirty. It just has random spaces at the end for no reason. Because sometimes you're gonna be working with data that is inputted by a user. It's not like a drop down option. So imagine somebody's typing this in, they accidentally put a space. So they actually put an enter or something. And then they submit it. And this is how it's gonna look in the database. And if you're a data engineer or, you know, you're working with the raw data, if they don't clean that up, then you're gonna be working with that dirty data. And I guarantee you, if you're working as a data analyst, you're gonna see stuff like this, not with maybe a last name, but all sorts of data. So we're gonna go right here, we're gonna say equals trim, do open parentheses. Actually, this says, removes all spaces from a text string, except for a single space between words. So like, you know, if it said Halpert space, or Jim space Halpert, it won't take this space in between there, because it kind of understands that in normal language, a space is supposed to be there. So it won't do that. But we'll take that, we'll give it this range, close that up, and there you go. Now it is nice and clean, much more usable. Now let's look at concatenate. One that I have used just way, way, way too many times. And something that I've used concatenate for, and you'll see this one in a lot of demonstrations, for a good reason, is because a lot of people use it for this. So what you can do is you can say equals, and well, let me tell you what concatenate does real quick. So what concatenate does, oops, I'm totally messing up here. But it joins two or more text strings into one string. It basically joins things together, and adds them together. So let's do concatenate, and we're going to add this first and last name again, one that gets used all the time, but that's because it really is useful. So you can do this, and you can say, now I want to include this. So concatenating this and this, and let's take a look. So this is Jim Halpert, but it's all connected. And that's typically not how people write their names. So what we can do is you can go back in here, and we can do what my demonstration up here already tells us to do, which is we're just going to add another thing in here. And if we add two parentheses, we can include anything in here. We can include a dash, we can include an exclamation point, or we can just include a space. So let's just include a space really quick. And just like that, it works perfectly. And so now we have the full name. Now, something that you could use it for is something like generating an email. This is something that you absolutely could do. And it's pretty simple. So I'm going to do it like this. I'm going to say, oops, what did I do? I'm going to say dot. And then at the end, I'm going to say at, oops, comma quotation at gmail.com. And now I've created emails for all of these people. So just something that you can do with this and something that it absolutely is used for. And you'll see that demonstration almost everywhere because honestly, it gets used a lot by data analysts. And so just a good one to know understanding how that concatenation works. Let's go over to the next one. So we are going to do substitute. Now, substitute's really interesting. There are different ways you can do it. I'm going to show it to you on these dates real quick. That's what we're going to look at. So changing a date format, changing how, what it's supposed to look like is absolutely something that happens all the time. And, you know, sometimes you'll even get it like this where it'll look like it'll be messy. It'll be different, a different, I guess, format. So this one has, all the other ones have slashes where these ones have dashes. And, you know, what you can do is if you want to, well, let me actually go with the no instances real quick because this one is actually makes the most sense. So we'll do equals and we're going to say substitute. And let me say substitute replaces existing text with new text in a text string. So if we do an open parentheses it says we take the text, we have the old text, we have the new text, and then we have what instance or how many times or what instance are we looking at it. And I'll explain that in a little bit. So the text that we're going to be looking at is this one right here. So let's take this range and the old is we're going to take this dash. And so let's take the dash and then what do we want to replace it with? We want to replace it with this slash right here. I think it's a forward slash. Isn't that what it's called? It's called a forward slash. Am I crazy? And we're not going to put an instance. Notice that that's in a bracket. That means it's optional. We're going to do none of that. And what it's going to do is it's going to fix this. So this one is now in the correct format that we want. And that's fantastic. That's what we tried to accomplish given what we had. Now let's fix that. If we want to do the exact same thing we can say what are we doing? Substitute. We can do substitute. We can do open parentheses. We'll give the range. And now let's say we want to change all of them to a different format. So instead of the forward slash, I'm going to keep calling it that if that's correct. We want to give it a dash. So then we close that and now all of them are in this new format. So it's able to substitute a specific value for a new value. And if you don't include an instance, then it'll do it to every single one in there. So let's go over here and we're going to actually use the instance num. And I'll show you what that does. And so really quick we'll do the exact same thing that we just did. We'll do the forward slash. And we want to replace it with this one again, this dash. But we only want to do it on the first instance of that forward slash. And so as you can see, all the ones that were replaced are the very first instance, whereas the second instance, which is the second time it appears in this string, does not get touched. So if we take this, and we put it right over here, and we move it to two, it's kind of the opposite. So the first one wasn't touched, the second one was. So we're choosing which instance or which time it shows up in that string. And then it replaces it. If you do not choose an instance, it chooses all of them. So this can be super useful if you want to do like a bulk replace. But you only want to do it on any specific column. And you just want to use a formula really quick, right? And so you can use this in a lot of different ways. So that's how you're able to actually do it with the first instance, the second instance, and if you don't include an instance at all. Let's go over to the sum. This is one I think everyone knows how to use, but I'm going to show you two other ones as well. So let's go to the sum and we're just going to do equals the sum. I hope you know what this is. Well, not hope. If you don't know what this is, it just adds up all the numbers in range. So we're going to add some means add. So we're going to take this, and it's going to give us the what all these salaries are together. So super, super simple. Some is one of probably the most basic formulas that you can do. Some if is a little bit different. You can add an if statement, which we learned right back here. You can add an if statement and then add it if it meets a certain criteria. All right. So we're going to do equals sum if, and then you're going to need to give a range and criteria, and you can include a some range if you would like. So we're going to do the salary again, we're going to do a comma. And now here's our criteria. Let's do if they have greater than 50,000 for their salary and close up parentheses. So now it's only going to add up if their salary is greater than 50,000. Now his is 50,000 exactly. So that won't count. But we have 63 and 65,000, which does equal 128,000. So it just gives a specific criteria or an if statement, then it does the addition. So super useful in that one. So that is how you do a some if and some ifs is kind of the same thing as we did back here. There's the if and the ifs. So the ifs is going to be if it has, it meets multiple conditions. So let's take a look at that one. So let's do equals some ifs. Now the syntax for this one is going to be a little bit different, you'll see that in just a second. This adds the cells specified by a given set of conditions or criteria. Close to no parentheses. We'll give the some range. So let's do the same one as before. Then we have our criteria range. So what are we looking at? This is the area that's going to be added after all these if statements are done. So we have to initially set that. Now we're going to say, okay, what criteria are we basing this off of? So let's put a comma. And we're going to base it off of let's do this one. We'll say if the gender, so we'll do comma, if that's female, if that's female. And then we'll give another one, we can say if they're female and let's say they are greater than, oops, greater than 30. We'll close that up. And it's going to give us 88,000. So female, female, there's one, two right here. So it's going to be this one and this one, that equals 88,000. So that's how that works. You're able to incorporate several different conditions into the sum formula. So again, I know this one's super simple, but you can use it in a much more complex way if you use the some if and the some ifs. Almost the exact same thing for this count. I'm not going to go super in depth into this one. I'll just kind of show you because count is count and some are kind of on the same level of difficulty. They're both pretty beginner. This is just going to give you a count of how many cells are there. So let's give this range. And so it's not going to add it. It's just going to give us a count. So if we do right here and scroll over them, like highlight them, this count down here, oops, this count down here is nine. And so it's going to give us that count. But we can do a count with conditions exactly how we did it in the sum. So if we do count if oops, I did not spell it right. If we do count if we're going to give a range and a criteria exact same as we did before. So let's do this. I mean, you can do this on basically any of these. It doesn't really for this demonstration. It doesn't really matter. But we'll say if their salary is greater than 45,000. So how many people this is going to give us how many people have a salary over 45,000? And that's five. So before in the sum if if we did that, we did 50,000, it adds everything together. The count is just going to count the amount of cells that meet that criteria. And again, count ifs, we're going to have a criteria range. And then we will specify what if statements we want to be to occur in order to count those cells. So let's do we want, you know, we want to count it can be any range or it can be any of these will do the ID this time. And now we can say, you know, we want it to be as our criteria one, we can say we want it to be greater than what their ID to be greater than 1005. And let's say we want them to be male. So they have an ID over a certain certain range. And then they are a male. There's only three people that meet that criteria. And so it'll be Michael, Stanley, and Kevin, those are three people. And so it gives us a count, very useful to give quick numbers like this, something I genuinely use a lot. And I know I've said that a lot during this tutorial, but that's because everything I'm showing you are things that I've used a lot. So I don't feel like, you know, I'm speaking at a turn here. Let's look at this one. This one is very has some specific use cases. Notice that this is a text right now. If you do it when it is in a date format, it actually will not work. I mean, I can you can test it out yourself. You just got to trust me, it's not going to work. So what this does is it's going to give you the range from this day to this day. That's what it's going to do. So let's do, oops, days. It's going to we want to choose our end date. So this is our end date. That's kind of backward from what you think. End date to start date, you think start date to end date. So you have to start with this one. And then we're going to choose start date. And now it's going to tell us how many, how many days was it from here to here. And this one it's 5056. So network days is extremely similar, except it takes out holidays and it takes out weekends. And you can see how many working days as this person, how many working days or network days as this person worked, not including, you know, weekends and holidays, how they actually worked since their start date and their end date. So let's do network days. And I mean your start date or end date and you can specify extra holidays if you'd like, but there are a already standard set holidays in there that it takes out. So, you know, if you want to do that you can. So we're going to do the start date. Again, this one's different. This one says start date, end date. And then we're going to give the end date. And if you notice, they are going to be different numbers, dramatically lower because it's taking out weekends and holidays. So this is how many days, calendar days they've worked. And this is how many days they've actually been in the office and worked. And that is it. Again, there are so many formulas. I mean literally hundreds of formulas that you can utilize and use and are out there for you to try out yourself. If there are specific ones that I did not cover in this video, please put it in the comments below so that I can, you know, show you how to do these things. I will say I probably used a majority of the ones that you're going to put in the comments already. And if I haven't used it, I'll take a look at it and see if it's really useful. And I'll show you that. So thank you guys so much for watching. I hope that this has been helpful. I feel like a lot of these things are not things that I learned before I started. Almost all these are ones that I learned while I was on the job. And so I'm hoping that you can get ahead of the curve and you can learn these things before you actually start so that when you get in there, you're just like killing it with the formulas. And people are like, whoa, this guy is like, this guy knows what he's doing in Excel. Give him all the Excel work. And then you become like, you know, just the Excel guy. And everyone, you know, loves you for it. So with that being said, thank you so much for watching. I really do hope this helped. If you liked this video, be sure to like and subscribe below. I'll see you in the next video.