Formulas in Excel | Excel Tutorials for Beginners

what's going on everybody today we're going to be looking at formulas in excel [Music] 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've 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 onto my screen 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 love little notes here and there um 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 thing that i should be using which i know there are ones that i left out of here you know put 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 um 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 gonna start with uh max min and then we're gonna go into some more i think a little bit more uh difficult things um 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 the 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 um and you can do the exact same thing for really any of these columns we can see who them 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 uh whoops what 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 uh 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 um 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 if you can you can put multiple conditions and as we're writing it i'll show you kind of what it the conditions that need to be met all right so we're gonna click right here we're gonna say equal we're gonna do if hit tab and we need a logical test uh and so we're gonna give it a range or or something we're gonna say if it's equal greater two um something like that then we're gonna 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 gonna do a comma and so if the value is true what it 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 yeah or if they're younger than 30 they're gonna 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 um so they're going to be young now uh 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 a value if uh that's true then you can do logical test two value if that's true um 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 if's does not have that ifs is going to give you different ranges in 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 if's tab and we'll do our first logical test so let's do um if the salesman or if that equals to salesman we're gonna say we're gonna 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 2. 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 it's just a pretty simple pattern we can just do random things so if it's equal to sales um 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 like this one there was a logical test and if it was true there was a value and if it was false there's 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's 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 you know some of the uses for this for the length i've used it for a lot of different things um one thing that i've used it for in the past and you know max and ifs you know 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 had like social security numbers and if you know 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 or you know however many they 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 um so for this oops why'd i do that i did ctrl z to undo that if you didn't know how to do that um 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 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 ten characters and we're able to see a length and so again there are a lot of different use cases for this uh 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 um i i may be going a little fast but uh you know i'm keeping it i'm keeping it live i'm keeping this on our feet so let's keep going left and right are kind of like sub strings if you've taken the the sql tutorial series that i've done uh substrings are where you can choose a certain part of the text string and you can extract data from that um and 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 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 with actual words or letters you can do the exact same thing so you can say write 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 gonna go for the right and we're gonna look at a date so you know sometimes you have these date structures month month day day year year year or year um you know day month year all these different and sometimes you just want to extract either 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 so 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 uh 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 uh 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 um numbers right so if i go right here and i make this a general it's going to have the 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 um but the quickest probably the easiest besides actually writing writing it out like this like 11-2-2001 which then converts it to a date format um 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 uh formatted 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 going to 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 going to do equals we're going to 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 going to give us a completely terrible output or just give us an error altogether so this is a day month month year year year year format and that is what we're going to do so we're going to dd slash mm yyyy and close that up and there you go and now well because it's in a formula what we need to do is copy 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 um 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 going to 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 it's terrible so that is date to text super helpful formula let's go over to trim now i i purposefully messed up this column now why do i did i mess it up like this because when you're working with real data you're going to 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 going to 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 going to 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 going to be working with that that dirty data and i i guarantee you if you're working as a data analyst you're going to see stuff like this not with maybe a last name but all sorts of data so we're going to go right here we're going to say equals trim do open parentheses actually this says removes all spaces from my text string except for a single space between words so like you know if it said halpert space uh or gym space helper it won't take the space in between there because it kind of understands that in normal language 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 um 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 um so what you can do is you can say equals um and well let me tell you what concatenate does real quick so what concatenate does oops i'm totally messing up here um 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 it says jim halpert but it's all connected and that's typically not how people write their names so what we can do is we 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 you know pretty simple so i'm going to do it like this i'm going to say oops what i do i'm going to say dot and then at the end i'm going to say at oops comma quotation gmail. com and now i've created emails for all of these people so just something that you can do with this um and something that it absolutely is useful and you'll see that demonstration almost everywhere because honestly it gets used a lot um by data analysts and so uh you know just a good one to know understanding how that that concatenation works um let's go over to the next one so we are going to do substitute now substitute's really interesting um there are different ways you can do it i'm going to show it to you on these dates real quick and 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 these 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 uh actually makes the most sense um so we'll do equals and we're going to say substitute and oops and let me say substitute replaces existing text with new text in a text string so if we do an open parenthesis 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 so called a forward slash am i crazy um 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 you know 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 uh we can say uh 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 um forward slash i'm going to keep calling it that if that's correct we want to give it a dash and 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 gonna actually use the the um the s uh the instance num and i'll show you what that does uh 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 um all the ones that were replaced are the very first instance whereas the second instance which is the second time it appears in the 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 a 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 gonna show you two other ones um as well so let's go to the sum and we're just gonna do equals the sum and i hope you know what this is well not hope i 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 sum 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 ranging criteria and you can include a sum range if you would like so we're going to do the salary again we'll do a comma and now here's our criteria let's do if they have greater than 50 000 for their salary and close out parentheses so now it's only going to add up if their salary is greater than fifty thousand now his is fifty thousand exactly so that won't count but we have sixty three and sixty five thousand which does equal 128 thousand 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 sum 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 um equals some ifs now uh oops now the syntax or this one's going to be a little bit different you'll see that in just a second but this adds the cells specified by a given set of conditions or criteria close to no brain for open parenthesis we'll give the sum range so let's do um the same one as before then we have our criteria range so what are we looking at what's this is the area that's going to be added after all these if statements are done right 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 oops 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.

and 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 you can use it in a much more complex way if you use the sumif and the sum 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 sum 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 countdown here oops this countdown here is 9 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 that right if we do count if we're gonna give a range in 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 gonna give us how many people have a salary over forty five thousand and that's 5. so before in the sum 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 countifs 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 let's it can be any range or it can be any of these we'll 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 want 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 um a certain range and then they are a male so there's only three people that meet that criteria and so it'll be michael stanley and kevin those are our three people so it gives us a count very useful to give quick numbers like this something i i genuinely use a lot um 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 out of turn here let's look at this one this one is very um has some specific use cases notice that this is a text right now if you do it when it is uh in a date format it actually will not work i mean i can you can test it out yourself you just gotta trust me it's not gonna work so what this does is it's gonna give you the range from this day to this day that's what it's to do so let's do uh 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 just start date you think start date and date so you have to start with this one and then we're going to choose the start date and now it's going to tell us how many um how many days was it from here to here and this one it's 5056.