7 Super Excel Formulas | Excel at Microsoft Excel

1. Introduction: What if I told you that you could be more efficient in the work that you do save a lot more time and energy and ultimately stand out to your boss as a top performer? If this is something that you're interested in, then you've come across the right course. My name is Stephen with mine favor, and in this course I'm gonna show you how to use Microsoft Excel formulas to help make you more productive in the work that you're doing. And we're gonna start with some simple formulas and then start to progress up to more complex, really advanced formulas. And the cool thing about this course is I've actually laid it out. So that way you can follow step by step, every single formula that were covering. So that way you can get the practice along the way. I develop this course after realizing from first hand experience how powerful Microsoft Excel can really be. And over the course of a couple of years, I ended up getting several promotions in a big role in that waas being just a little bit more productive than everybody else. My team being just a little bit quicker than everybody else and a lot of it really came down to hard work, but also using tools at my disposal like excel to save time. So that way, in the bigger picture, I could be quicker than everybody else, and I could be more efficient, have a higher quality. I'm not sure you step by step some good formulas for you to be aware of, how to use them, how you can start implementing them today and reduce any type of confusion. Or you may be uncertainty you have about Microsoft Excel. So that way you're walking away from this crystal clear on how to use formulas. With that being said, I'm very excited. You've decided Stop by and let's go ahead and jump right into the course. 2. Section 2 - Average | Count | Sum: again. Thanks so much for checking out the class. I'm really, really excited. The first thing you're gonna want to do is go to the class description or the notes. It's more around there. And you should find a copy of this file that I have pulled up called formulas Underscore Project. And I've actually designed that the class in a way where? In each section as I go through the different formulas, as you can see each tab on this Excel document, we're gonna cover it. And that way you can follow along step by step. And actually, as I'm showing on the screen, you can actually type the formula yourself. So that way you can get the practice as well. Once you have that pulled up, you're gonna wanna go to Section two average, count some, and with this, we're gonna ease it into the course by come earned some of the more simple type of formulas , I say Simple, because these are actually formulas that are automatically built into excel. So you don't really have to type them. You definitely can. But I want to show you sort of both ways on how they work. So 1st 4 average average is used to do exactly what it sounds like. It finds the average of, ah, group of different numbers. So, for instance, this sample data that we're taking a look at here this is all just made up information about sales agents in a company, so as their name, how long they been there, their location and because their sales agents were tracking how much sale, how much they've sold in January, February and March. Their phone number, which is obviously fake, and then their annual salary so that before get started, there's no specific rhyme or reason to the numbers. They don't necessarily have to make sense. They're just a bunch of random data. So for average, the way Excel it's built in is you could simply take the cursor and drag it all the way down. Now, if we take a look towards the bottom, Excel gives us three different things. It gives us the average, so it's giving us the average of the highlight. It sells so across all the agents. For the month of January, the average sales was $5732.57 right next to it gives us the count, which was the next formula we're gonna talk about and then this Some. You could do this with multiple things. So let's say you wanted to see the average sales for all agents for all three months. Well, all you do is you just highlight all those and it shows you down here shows that account shows with some. So excel makes it really easy to do that. But I do want to show you how you can actually type this in yourself. So let's say we want to find the average of January. You just type average you notice when you start typing a valid formula, it's gonna give you this drop down box to sort of show you. Hey, you're on the right track. This is a good formula and then open parentheses and then we're filling out. Or formula. There's a couple ways we could do it so we could either take our mouse, dragged all the way down, close the parentheses, and so we're taking the average of all this out from cell F six top one through F 26 down. Click enter and notice how it gives us the exact same number as if we just selected it all like that, right? $5732.57 exact. Same there. And we will do that quickly with some and account as well. So as we see, the numbers match up literally the exact same way. And it's really up to personal preference on how you want to handle this. But I figure out, start the course office something a little easier now if you're trying to do this and when you're selecting stuff. If nothing's popping up on the bottom here, one thing you may have to do is enable it on your Excel document. And the way you do that is move your mouse down just towards the bottom of the field here, like sort of this bar, right click, and then notice how it gives us different options to check her on check so in mind right now, average count in. Some are checked, but notice these air, other possible formulas or features. So if I uncheck average and unchecked count now, if I try and highlight stuff on Lee, some is gonna pop up. So if you're trying to go through this and you're highlighting it, and it's not showing up down here. Just go ahead and right click and then get it added. And that's one thing I really like about Excel is when they designed it. They understand that people are going to use it for different things, right? What you need in your job role may be very different from what somebody else wants. It may be annoying if you every time you select numbers that shows you the some because you don't need to. Some you only need the count or you only need the average right. And then this does kind of get into other possible formulas here, which we won't cover in this course. But just to be aware that they exist, right, so you could add the minimum, you can add the maximum. So let's say that was a relevant feature to your job, could highlight it, and then instantly it's gonna tell you what the lowest value is than what the highest value is. That's it for this one. The next section. We're also gonna cover some basic formulas, still kind of easing into this, and then after that, we'll get into some more complex formulas 3. Section 3 - Small | Large: for this next section we're gonna cover to pretty cool formulas. They're called small and large. So again, going back to that last section sort of towards the end. If you remember, we have the ability to select minimum and maximum. So that way, if we highlight of row of information, you know, automatically and actually I'm gonna before move forward, I'm gonna remove the account average and some. So that way we keep this bottom bar a little cleaner. So you remember in the last section we could add the minimum and maximum to the to the bar here. So that way it automatically shows what the lowest in the biggest is. That can be hopeful. But let's say we wanted to know the second biggest or the second smallest Well, there's an formulas that can help us out with that. And with this section, I also want to show you that multiple formulas can do the exact same thing. And so it really becomes helpful to just know how to use the different formulas. And then that way you can use them in whatever context working in. So first, let's take a look at the annual salary of all the employees. Let's say we wanted to find the person who is currently being paid the least because we think that we need to give them a race or bump up their salary. So there's a couple ways we could do it. One we could again highlight it, find the minimum, and then we see it's Chris Smith. We could also type in equals men. So, like the row give us the same thing under the way we could do it. Focusing on the formulas in this course is first small. So do equals small. Now it's asking for first in array, an array that simply means the list of data. So we're only taking a look through the annual salaries, and then it the next thing K, which is asking for. It's asking for how, what number of smallest you want. Do you want the absolute smallest? If so, we would type of one if you want the second smallest we type of to. If we wanted the fifth smallest of this whole list, we type of five, right? So it's starting at the smallest, and then whatever report after there is counting from the smallest upward so for this first will put a one close parentheses. Enter and notice how it gives us the exact same number and better level is here. Now let's say we wanted to find the fourth smallest salary of everybody here. Well, yeah, We could manually take a look through all 21 different employees. Or we could use the power of excel and create a formula to find that a lot quicker until this 57,000 in 95. So now if we really wanted to check our work, something we could do is highlight this top header section, go to data filter, and then we could filter the annual sales as sending order. So now we automatically filtered all the salaries from smallest to largest right to. Since we did that, we could just count down for 1234 So the 4th 1 $57,095 match their formula to go over and our employees there is the person, so I will undo everything. So it's not back in order, and then a similar to small large is just the opposite. It's going to start with the biggest number first and then work its way downward. So if we wanted to find so if we wanted to find the person with the third biggest salary, you would be this 11 18 3 64 So pretty straightforward. Not really complicated, but you can to see how this would be helpful, especially if you're looking for something that's more in the middle range of data instead of simply of the biggest or the smallest. 4. Section 4 - Concatenate: This next section is one of my personal favorites. It's using the formula called Concoct Nate, which is really a fancy word for combining multiple cells. So there may be situations where it would help save you a lot of time and energy to I have Excel automatically combined the values of two cells and put them together. So for this example, I took the information for the different states, abbreviations and state capitals for all 50 states in the US Let's say we needed to take the state capital and then combine that with the state abbreviation. So let's say we're running a database or some project where we need to be listening out the state capitals. So there's a couple ways we could do this. We could do it manually, which would take a lot of time and energy right, typing out 50 different combinations. This could also be a problem because we're increasing the likelihood of a possible typo or error. So instead, let's have excel work for us, help save us that time. The way we do that is by using that. Could Katyn eight formula, So to do that will type equals and it's kind of a strange word eso if you have trouble spelling it or remembering how to don't worry. The nice thing is, as you start typing out Excel automatically give you suggestions for formulas, says we see gives us this one here, click it then the next thing is so tells us. First text one. And this is basically the first cell that you're they want to combine that you want to appear on the far left. So since we want us to appear as the city and then comma state abbreviation, we're gonna click this city first and then we're do comma. Then it gives us text to we're gonna click the abbreviation Now notice. With this formula, we have options. We could end it right here. Or it could let us just keep on going. Just keep contaminating, keep adding and sells. Right. So this is where can Katyn eight is powerful because you're not limited. You can use this for large data sets. So just for to be kind of silly an example sake, I'll show you what this would look like. This isn't what we're going for, but you click Enter. Notice how all the cells we selected. You just threw him altogether. Montgomery L. Alabama, Arkansas CEO. So it's not what we're trying to accomplish. We'll delete that. Do this again. Comma ale. Enter. Don't combined it. But it didn't put it in the formula that we wanted. The way we want it to look like is right having that comma between the two. That's a pretty common convention. And then also having a space between that Montgomery and the abbreviation. That way they're not running together. The way we do that is after this first cell after Montgomery, we're gonna want to put a space. The way we do that is, we add quotation marks and then we're gonna want to add a comma. Then a space quotation marks, and then a calm again, we'll click enter and then notice how that matches. So I want to go back to that, cause that could be a little confusing. So the first thing is, we were doing the contaminate formula. Just show you was something right below it here by default, right? We have to use a comma between the cells that were combining so notice in this example. Phoenix comma Arizona. We do enter it doesn't actually display a common here. So that's why if we want a comment actually show up, that's why I had to add an extra one. So we did Montgomery comma than quotation marks. This is sort of ah, good way. If you wanted to add additional features, text something else almost like freeform and add in something unique. So let's say we wanted to say hi, um quotations. Hi. Enter now it says Montgomery High, Alabama. Now let's say we wanted a space between Montgomery, so notice within these quotation marks, I'm adding a space before the high and after the high. Now, if we click, enter noticed, there's a space there. So that's all the quotation marks is. It's You could think of it as kind of, ah sort of cheat code, or it lets you input custom text or symbols or whatever you want to dio. So with this, you know, going back to this because it didn't give us a comma. But we need one to be in it there, over doing his and nickel Tate chin marks, then adding the comma, but understanding that we're gonna be adding spaces in there as well, so that way in this example. I'm actually only gonna add a space after the fact. So that way three next Montgomery Space Alabama. So now that we have the formula, we can just take that I got all the way down. But that's it for that contaminate feature. I feel like I was a pretty good introduction into it in The cool thing about it is, it lets you combine multiple cells. So you really do have a lot more power and control when it comes to editing data in the way that you need to. 5. Section 5 - Left | Mid | Right: Now that we got the state capital and abbreviation, let's go ahead and build upon that by learning the left, made it and write formulas. So make sure you're on section five tab here and again. All we did was took the information we did from the last section. Copied it over. And we now have this. So I left mid and right. They're not gonna be helpful all the time, but they can help save you a lot of time and energy in certain situations, and they are pretty straightforward. So left do just equals left parentheses, text. She'll select all the text on this case will do. Ah, just the cell. And then it says number of characters. So let's say we wanted to only get the first letter from the city or get the first letter from this cell. So if we only wanted to return the M, what we do is we click one. So from C four, it's starting at the left, and it's taking just the first letter so we click. Enter gives us an M. Let's say we wanted the 1st 2 letters m. O. So it's kind of a cool feature as you can see three. This would give us M o n. We drive got down. Then we'll do this start number. So this is gonna be where do we want it to start counting? Because the middle of every single cell is gonna be different depending upon how many characters air in there. Right? So Montgomery, Alabama, that has more characters in Juneau, Alaska. So first we need to tell Excel, where do we want to start looking from? So let's say we're going to start with the fourth character, and then we only want to return. The next two said By doing it this way, this is gonna give us the fourth and the fifth character from the left of this cell. So enter Ke Jie And then if we zoom in here a little bit so m o n, that's three t. That's the fourth G. That's the fifth again. Start with the fourth and in return, a total of two cells. So we're gonna take a look at right, and this is where this could be really helpful for this specific situation. So let's say you're working on project and you're given the data in column. See your given it in this format, but you don't need the name of the city. You only need the abbreviation. Or let's say you're trying to manipulate the data and you need to get the state abbreviation out or you need to remove it right. Instead of manually typing that out for every single one which could work, you could also do, right? It's like the text. And then we're gonna say to and we know by taking a look at this data that the two characters on the far right they're always gonna be the state abbreviation. So we know with confidence that this is gonna be the right way to give us that information . I will say right, Select it, too. Enter and notice how it automatically sort of extracts where it gives us the state of radiation. And then I got all the way down. You notice how does the same thing if we were taking a look at this through the lens of a real world situation? The left in middle probably wouldn't be very helpful because we don't need to know the left couple digits or the middle, because that's gonna vary. But knowing the right, and the ability to remove the right ones is going to save you a lot of time and energy. But it's good to know how to use the left in middle, because depending upon the data or the situation, the opposite may be the case, right. It may be more helpful to remove just the first letter or the first couple letters from the left versus the right of so. 6. Section 6 - VLookup : So now we're going to take a look at the V look up formula to make sure you're on section six of the worksheet here. And in my experience, this is one of the more common formulas I see employers asking for. And so if you know how to use this and you're really confident in it, this can really help with your job and, you know, possibly future jobs moving forward. So for this, we have a list of state capitals and then the state that they're in. Then we also have a couple sample questions as well. So let's say we needed to find what state is the capital Boise located in? Well, again, we could go through and manually find it. But imagine if we had thousands of records and not just 50. So we need to learn how to use V. Look up. So, V Look up. I'm actually gonna zoom in a little bit here first. Okay, So, V now the first thing is asking for is the look of value. So we want to find Boise in this whole section of data. We want to first find Boise for this. I use the parentheses it's kind of similar to that example before, with heading into comma and in the contaminate for formula. So since I'm Frito freeform typing something in, I want the formula to basically go through all of this and find something that matches exactly to what I typed here. Boise. They will do comma table array. So that means the whole table of information. Notice how the boxes slept around all that. That will do. Comma column index number. So this means what column or what corresponding value do we want it to return? So we're taking a look for Boise in all of this. Then we're saying, Once we find Boise, do we want to take a look at the first column or the second column? Well, once we find Boise, which is in this first column, we wanted to return the value, which is in the second column, Right, because there are Hogle. Here's what status Boise in. We want to know the state that corresponds, or that's right next to Boise. We're gonna do to because Capitals, the first column states the second column comma. Then it gives us options. You can either do an approximate match or you could do an exact match. Typically, when I do this, I try to use the exact match so it false and the seas enter and gives us Idaho. Now we could go take a look. Boise start next Tyto. So we'll go through a little bit faster with this next one here. Where is Baton Rouge located? And it gives us the Louisiana, which is correct. Okay, now let's do one other example to show you sort of a different way that you could use V. Look up. Okay, so where's Atlanta located in our past couple examples for the thing we were looking up. We manually type it out, but let's say we already have that here. What we could do, instead is instead of typing it out, you know, quotations. Atlanta all that. If that's by itself, we could just select that cell comma and then just like we did before, so that also work for us. One thing to be aware of when you're going about it like this, though, if you aren't typing it, but you're just using the settle and it's not working for some reason, something you should do is take a look of the cell and make sure there's no spaces after the word right, because in this, if you look up, it's looking for the exact ah value of this cell. So if Atlanta here is spelled Atlanta with no spaces afterwards, it's also looking for Atlanta here with no spaces. So, for instance, if we go into this cell in this type of space, enter now. The formula is not working again. Because in our formula we told that, Hey, let's find Let's find Atlanta space. Where is here? There is no Atlanta space. It just Atlanta by itself. Now, if we added a space to Atlanta care, it would cause it to, um, find it that that is a common sort of road block or hurdle. Some people may come across when using Excel is being aware that it seems very small, but making sure you don't have that extra space when you're searching for something or for searching for something, make sure it's literally the exact same 7. Section 7 - IF : as we're making it towards the end of the course here. I first want to thank you for making it this far and hope some of the information has been really helpful. One thing I did want to cover, just going back to the previous section, which I forgot to mention before. When it comes to the lookups, the way they're set up in structured is the information you're looking for has to be in the first column, and then we're looking for it has to be in the second column, right? So if you wanted to flip it around, if you wanted to see what if we were to do that, the look up and look for Alabama and then returned the values to the left? Unfortunately, it doesn't work that way to effectively use of you. Look up. Basically, the value you're looking for has to be on the left, and then the information has to be directly to the right of it. So that was something that I feel like I didn't cover in the last section, and I just wanted to clarify if you have any questions, be sure to leave him in the course. Notes below, and I'd be happy to answer, clarify, but didn't want to leave it stranded in terms off that concept. So moving on to Section seven, we are onto our last formula here, and this is a little bit more on the advanced side. So I think this is a good place to end the course, but given introduction into how powerful and customizable a lot of thes formulas can really be, So with this, we're back to our sales agent information, the employees, all the information. No, let's say we're a manager and we're taking a look over all the numbers, and we want to figure out who are the top performers. Who should we possibly be promoting in the way we're going to determine who should be promoted is the people who are selling the most right so away we could do This is by going to this who will be promoted column. We're gonna do an if statement. So if statement is exactly what it sounds like, if certain criteria is met, then it's going to return a value or then it's gonna do something. But if it doesn't or if it doesn't meet that criteria, then it will do something else for this example. We're going to say if an agent in these three months, if they sold at least $25,000 for the company, we're gonna consider promoting them. So going to say if the summer, if the total of all three months sales close parentheses, comma, actually. So if this some we'll say is greater than 25 123 greater than $25,000 comma, then we'll return the value of maybe promote. And since we're free forming text kind of similar to before, do parentheses, maybe promote comma. Now, if the value is false, If the sum of all the sales data isn't greater than $25,000 we're gonna have it be blank in a way to do that, too. Not return anything is by using too quotation marks like that. Then, lastly, we're going to close out the formula because again, when doing these formulas, you before we kid and it, we need to close it out. So if we take a look through, you know this some this some function is closed out. Its has an open parentheses, close parentheses, but we need to close out the entire if statement, right? So that starts with an open parentheses. So we need to make sure it's closed. So that's a good general rule when you're using any type of itself formula to enter and nothing happened, but it didn't air out. So now what we could do is take this formula and drag it all the way down and look So there are two people, two agents, and for better clarity here, I'm gonna change the color so they stand out a little bit. Okay, so these two people been there 16 years, they live in Oklahoma City and they've been here three years in Miami, Florida These are the two top performers. And if we wanted to just compare, we could highlight those notice on the bottom there. Average sales for the past three months is a little over $9000. There's is 8500 highlighted. Again. Their total sales is 27,000. Their total sales is little over 25,000. So this person here is currently the top performer. But as you can see, that's kind of a cool way to do the whole conditional formatting. If this then this happens, let's go ahead and run another example of this. So let's go ahead to lead all that out. If an employee has been here more than 10 years, we're gonna give them an award for being here more than 10 years. If their tenure is greater than 10 years, then give them in a word. And then, if not this time, said returning, nothing will say will give them simply a good job. Keep trying and closed. Parentheses. Enter. Okay, so this 1st 1 good job. Keep trying. They haven't been here greater than 10 years. Okay, so as we see Award award a word and then really, what we could do is go back, Teoh filter data, and then we could filter it simply by the award. So now this is all the people who've been here more than 10 years. We take a look that obviously matches up to the information there. We give all them awards and then we tell all these other people keep trying. You only got a couple more years so you can get a word to when first getting started. It can be a little bit intimidating, but I would really encourage you to start small. Start with very basic simple ones. So you understand each. As you're going through each step, you understand why you're typing it, and once you understand this type of syntax, then it makes it much easier to build upon. 8. Final thoughts: Let's go ahead and put this all together and get some even more practice to make sure we really understand how to use these formulas and and put them into practice. So for this, I want you to click on the class project half down here, and I created a couple different questions. So that way you can go ahead and use this information and creature on formulas. And with each question, I sort of with each question I color coded where that question is gonna correspond to the spreadsheet, Right? So for a question one financial sales for all team members for each month of January, February, March and make it easy. I put that here. So you're supposed to create the formulas for right there, then likewise with section question to you're gonna put that in here three year and do that column for you. You're into that column and then for five, I want you to create any use, any of these other formulas that we talked about and do something different or unique, right? Anything that you want. But just to show that you have an understanding of how they work and then once the conscious project has done. Be sure to go ahead and upload it to the course. So that way we can go ahead and take a look and, you know, see a copy of what you've been able to dio. If you have any questions throughout it, please don't hesitate to reach out. You know, drop a comment. I'll be monitoring it pretty closely to help answer any questions. Or, you know, don't be afraid to reach out to other students who I'm sure will be happy to help out with any questions. Or, um, just really any insights you have to write. So maybe it's not a question, but it's something that you discovered along the way. Or maybe something clicked as you were going through and getting this practice, whatever the case may be. But please don't state to reach out and engage with the community here. That's what we're all here to do is help each other out, grow and help develop these skills. That way, we can take these from this course, bring back to our workplace and be more productive. And you know, suddenly our boss is gonna be wondering, why are we 10 or 20% more productive and you can sit back and smile and no to yourself. Well, it's because I know how to use Excel. Now I know how to use some of these formulas that are saving me a lot of time and energy. If you do want to see more Advanced Excel formulas, let me know. And if there's enough demand or if people want to see it, that's something I could definitely create. That is an idea that's already in the back of my mind, where we could sort of think of this as part one of the formulas, right? This is a good basic introduction, but then, with Section two, we could build upon that going from the F statements into even more complex formulas, which could help you do a lot, even crazier stuff. But again, with anything we need to get the basics, get that good understanding of the syntax, how that all works before we go into the more complicated stuff. So if that's something you like to see, let me know and I could definitely work on that for you guys. But with that being said, thanks so much for your time, attention and take care