Microsoft Excel Formulas You Need to Know in 30 Minutes!

1. Introduction: Welcome to Excel formulas. You need to know my name Julian. And for the past 10 years I've been working with Excel on a daily basis and teaching others how to use it in this class. We're going to cover basic formulas and functions to get things done in excel. So if you're completely new to excel or you've used it before but want to better understanding or read fresh of some of the basics this classes for you at the end of the class, you'll understand how to use many of the most popular commonly use formulas, helping you to analyze information in Excel quickly and easily recovering functions such as some some if Countess V look up on more as well as going over the basics of formula writing and sell referencing the course is made up of a series of short videos that cover even one formula or topic or a group of similar formulas. The same Excel workbook that's used in the videos is available for you to download so you can practice the formulas yourself as well. Thanks for enrolling in this class on Let's get Started 2. Why Excel: So before we get started, I want to run over Why, in my opinion, you should learn excel on what you'll get. Learning anything requires some investment of time. But to reach a competent level in Excel, it doesn't take long classes like this. You can very easy pick up many of the basic essentials in a matter of hours, not even days or weeks. When you've got those skills, there's a range of benefits to having them. So what are those benefits? The first Off Excel skills are absolutely essential for many jobs. And it's not just the obvious jobs that involve numbers look, counters dean or financial analyst worlds. Virtually any job that involves computer may end up involved. Microsoft Excel, Even if it isn't something you use every day, employees will often pacify. They want a certain level of mike stuff, Excel schools, and sometimes we even specify the exact functions and features of the program that you need to be able to use to qualify as a candidate for a job. So the sooner you get started in learning Excel, the less hurdles you'll have to jump later down the line. Secondly, Excel make you faster and more efficient when dealing with data, even if its tax base data there will usually be a way in which Microsoft excel can help you get things done faster. The more you know about the software, the more short cuts you'll be able to take in processing and analyzing data. Thirdly, Excel making more organized where it's at work and you to analyze some sales data or even something outside of work, like planning a road trip. You can use Excel to do things in a more organized way. That'll mean you're less likely to misinterpret something, forget something or misplaced something forcefully. It's easy to learn how to use it. Compared to Microsoft Word or Microsoft Power Point, Excel can seem quite intimidating. There's a huge amount you could do of it, and it's easy to think its software that's difficult to learn. But the truth is is actually all pretty straightforward. You don't need to be a technical person to Atlantic South and start using it more often. Lastly, it can seem like a super dull program, and in many ways it kind of its. But once you start learning how to use it and you see that it can automate things that you might have otherwise wasted time doing manually, you'll probably begin to like using it and find it fun on realize that it's something that's there to make your life easier. 3. Arithmetic and Basic Formulas: Okay, so we're going to start off just by looking at arithmetic in excels a very basic formalist . You just do things. The addition, subtraction, multiplication on division. Um, well structured all the worksheets in a way that we've got the data over here on the left hand side, and then we've got the formulas written out. So what you'll notice here is that they've got a little apostrophe before the equal sign on what this means is that the formula just displays in the cells That's visible few to see if I was to remove the apostrophe, then just go into the actual archer of that formula. So five plus five and there's your answer. But they're they're gonna be really not like that just so you can actually see the way they look. Um And then we'll use the space here for examples. So let's just quickly run through this So simple. Case off equals five plus five. Enter at your answer. 10 minus five. Multiplication. Just know that we used the Asterix symbol there, so g o. And then for division, it's using the forward slash. So that's a basic example of how you can use excel for simple arithmetic and how to write kind of very basic formulas like Fight plus five or whatever else 4. Cell Referencing: Okay, so in this tutorial, we're gonna look at the topic off cell referencing now. So referencing is something that you really want to understand. A zits enables you to do to write formulas in a much quicker way on. It's very important to understand the basics as you use it throughout. Lots of the things you do in Excel. There's two types of cell reference relative and absolute, and you can also have what's called mixed referencing, where you have a mixture off relative and absolute, which will come onto now. We've got our data over here to illustrate this, and we're going to use these two columns over here, too, illustrating the different types of cell reference. Now let's say we want to calculate the total price off all these shirts and the belts and socks and shoes and sandals. So, as you can see, we've got the quantity of each item down here on. Then we've also got the price, so simply weaken reference on these cells just by clicking on them. So this one here, times by price it enter, and then that gives us our total price of $83.96 now these are what's called relative references. So agile. See here, detailed them out. Here's we've got relative when you don't have any dollar signs and we'll come onto what the absolute stuff means. But because it's relative, that means that when we drag this down, everything moves relative to the change in the cell. So because we've come down around from 6 to 7, these have changed from 6 to 7. So you can see here we had six and six, and now we've got seven and seven on DSO on and so forth. And that's really useful because it means we can quickly fill out all those formulas and get all those answers without having to manually right out the quantity times the price again. So you'll use relative referencing a lot now on the discount column. However, that's where it gets a bit tricky, because if we were to use relative referencing like we've just done, it's not gonna work, and I'll show you why. So let's say we take this total price equals that, and then we on many times it by the 10% discount to figure out what the discount is going to be Now that's correct. Overseas. Eight. Those 40 on our total price here. But when we drag this down, you know, is that the numbers start to go a bit wrong. So here we've got this zero, even though 10% of that is not zero on here, we've got an error. And what's happening here is because we've dragged it down. This cell is also coming down to so here it's referencing nothing. And then on this one here, it's actually referencing this word which doesn't make any sense. $15 time discount. So that's where absolute referencing comes in and what we were. What we need to do here is actually lock this cell in place so that when these moved down, this stays in the same place. So these they're different types off absolute referencing you can do now here, we need to lock this row in place when you we need to make sure that when we drag this down , this particular cell doesn't move from both three. As it happens, we also wanted to stay in the e column. However, because we're going down, that's not going to change anyway. So what that means is that we could just lock the road and use the e dollar sign three. But we could also just lock the column on the road as well. So either of those of work. However, if we locked the column but didn't lock the blow, that wouldn't work. So let's just run through those examples. First of all, if we do this and then we times this by the discount and then if we lock that completely now, you can manually enter the dollar signs or you can actually just click F four on once you press that for your cycle through all the different options going from just locking the row , the column back to relative and it starts off with the everything. Lots of an absolute reference. So let's just do that for now. And as you'll see here, we get correct answers because what's happened is this is locked in place was this is still moving, so we're getting what we want on. Then we could also just go back here, and we could have just the locked on again. This would work fine as well, because the column as we go down, we're not changing column anyway. If We had a formula that is going in the other direction than we need to make sure that the column was locked. Andi In that particular case, the vote would need to be locked, so that's basically absolute and relative referencing. In a nutshell. Advocacy ITT's really useful. Really important on it can save you a lot of time, cause it means you can have more complex formulas that reference cells that may not new with your formula, and it will still work just fine when you're pulling down your formulas or putting them across. 5. SUM, COUNT, COUNTA, AVERAGE, MIN, MAX: So in this tutorial, gonna look a few Basic Excel functions. We've got our data that we're gonna be using as an example up in this top left here. So we've got a few different items fruit, that quantity on the cost per item on, we'll start off with some function. So sums really basic but really important and really useful. All it does is basically just add stuff up. So we type in some and then we highlight these cells here, we'll get the total so 14 and then leaving once accounts counts also really useful. However, unlike adding stuff up, it counts the number of individual items. So here we can see there's 1234 items, obviously four across all these. If we highlight all of these, we'll get full counter is very similar to count. Except the difference is is that rather than counting three instance of numbers, what it does is it counts any cells that are empty. And that means it's really useful if you want account text value. So what we're gonna do it will use it that account of these words here. So let's just do that counter, and if we highlight over there again, we get four on. If we If we have done the count formula, it wouldn't it wouldn't have given us that value. It would give us zero. And the reason is is because these are numbers. So we just got back, um, and then moving on to average. So Average will just provide the average off a series of numbers on again Really useful because it means you don't have to write out the individual items of each form. It's You don't have to manually add up numbers and then divide them by the total count of them. So let's just take a look at these prices. We do an average of these prices. We then get 0 65 So that's an average here on again, real quick, really useful. Minha max, you might know, always use these. However, they're really simple, and they're also useful to know men what it does. It just brings back the lowest value in a Siri's or numbers. So we go over these prices enter, we get 30 which, as you can see it, is the lowest one, and then Max. All that does, as the name suggests, brings back the highest value, so high life of those and then we go $1 that's just a basic introduction to some really important but simple functions in Excel. 6. SUMIF, COUNTIF, AVERAGEIF: Okay, so we've gone over some count counter An average formulas on those former is really useful . But what they can't do is some or count based on a certain criteria on often you might have a data set of lots of different items in it, lots of different data points on. Perhaps you're only interested in one particular type of data and you want to retrieve the information, just relates to a specific item or specific territory or whatever the criteria is depending on your data set. And that's where the kind of range of if formulas come in, come into play. So there's a few ones were going over here and that some if count if on average, if so, we go with the first of these some. If so, we've got our example. Data. Andi. As you can see, we've got a Siris of furniture, items like chairs, desks, tables all in different colors. And then we've also got their quantities on. Their cost brightens. So let's say we wanted Teoh. Look at the number of desks that we've got in total, so we can see there is item to the arm just desk. So that's where the summit formula would come in handy. So what you do is it's equals some if press tab and then what you see is he's got the options of range criteria on some range. Range relates to data where the values are specified, so based on the criteria values. So let's say we want Teoh, you know, add up the death, so we need to go to the item list. So we're gonna highlight this range where all off the items listed. Now, the criteria that we're interested in in this particular example is the desk. So what you do here is with the quotation mark opening up and then you write desk and then close it off. So then we specified which items to look at what the criteria is that we're interested in. And then now we need Teoh put some range, which is basically the actual numerical values that are going to be added up some. We're gonna highlight the quantity section here and then enter. And then we get six on. As you can see in this formal date set, that is indeed the case. We've got two desks here and then we go down here We've got another four deaths kits of six . So what it's doing is is looking for all of these counting weather the desk and then looking across and going. Okay, that's too here on for here. That's basically how the formula works. Will visit formulas, work. We'll go through the counting on average if as well, which also really use force account. If and we tired and then let's say we're interested in the colors this time. So let's go with range as the color right. And this is a bit simpler because because it's counting. We don't have a separate some range because we're actually just going to count the items from this list. So we selected a range and then let's say we're interested in yellow items. So what we'll do is we'll write yellow there on in. It's just simple case hitting enter and then we've got to. So along here we can see that that's true. We've got yellow, red, blue, green, blue, green, yellow, red. So there's two yellow, and that's what it's identified. This counted up those now we move on to average if which is also really useful, so we'll just go down there get average If and it's worked the same way of some range, the average range and the average it formula, it's exactly the same. So you just select the range. So let's this time, let's have a look for chairs on. Then we'll go there. We'll go to criteria. So for that we're going to enter chair. Then we're going to select the average range She's here and then Great. We got sick so we can see there. We've got Chair five there, and seven, we add those two together. It's 12 so divided, but to six. And that's average. If that's just a basic run through off some of the really useful if formulas that you can use an excel. 7. SUMIFS, COUNTIFS, AVERAGEIFS: So in this video tutorial, we're going to look at some ifs, count ifs and average ifs. Now these kind of Samos some if counted on average if, except the reason it's called ifs rather than some, if some ifs, is because you've got multiple criteria. So rather than summing or counting ravaging based on one criteria, you can add in 2345 Except so we've got a data table here. As you can see, it's a bit bigger than previously, so we're going down the road 30 on. We've got sort of three main ways in which the data is divided up. So it's the item chair debt table, etcetera, color pink or yellow or whatever else. And then material, whether it's metal, plastic or wood. And then we've got our quantity and cost per item information as well. So let's dive in with some. It's on start there, so we just want the formula. So, first of all, you need to start off by selecting the some range. So we're interested in the quantity will high light these cells here, just down all the way to the bottom there, and then let's say we're interested in chairs that are blue. So first of all, we need to specify our criteria Range. One on that will be the item column where the different furniture Reitinger listed and then we comma. And then we have to specify the item of furniture. So here, interested in chairs. So we're going to add chairing. And then this is really right difference to some if because now we move on to our next criteria criteria range, too. We're interested in the blue ones. So we're gonna ride in criteria range, too. Eso that's gonna be the color column here, going, going all the way down to the bottom on. Then we're gonna ride in blue and then hit. Enter and we get 14 on. What we can see here is that it's gone along through this data. It's found this road here. We can see there's seven there, and then if we keep on going down, there's also this road here. So that's seven plus seven. So we get our 14 because it's based on those two criteria. Now we've got to quite over in here. We could also add 1/3 criterion here. A z you know, there's there's the extra column of them for the material here, so we can be even more specific. So let's say we add in, Ah, criteria range three. So we're gonna add in material. Highlight that row there column story and then we're going to select what we want. So let's say we're interested in plastic. None of you hit enter. So now we're summing up the total off chairs that blue and the plastic. Now we go to the list. You'll see there's only actually one row here where that's all three of those criteria. True on its right. Eight chair blue plastic on this seven. So it's actually just summed up from the total of one cell. So that's our answer. Their illustrates how you can be very specific with some s former. Let's move on to you. Count ifs now so we don't elect some range because we're counting rather than something. So what, you're gonna dive in and do the criteria range one on Let's say in this example, we're interested in lamps that are metal, so we want to count the number of rows where there's lamps that are metal, where their present, so we'll start off with our item list from all type lamp in. Then we move on to criteria range, too. So select materialised on the more I didn't metal hit. Enter on, we get three. So what's that? What is that doing? Well, here we go here, we can see that there's one right here where it's lamp on metal. And if we keep on going down, we can see another road here where it's Lamping metal on def. We keep going. Got third row here, lamp and met. Also, it isn't counting, for example, this road here, which is lamp in plastic because it doesn't meet the criteria. So it's counting up those three data rose and we've got That's where that three comfortable . Lastly, let's move on to average ifs. Okay, so let's say we want to find out the average number, the average quantity off desks that are plastic across the different colors, so we're not interested in particular color. But we are interested in what the average level of stock is for desks that are plastic. So our average range again it's gonna be the quantity list, and then we're going to select the criteria range. One will go to the item list. And then we specify our first criteria, which is desk. Can we move on to quite area right to We're gonna cheat material. And then we just typing. Plastic did enter on. We get three. So what is that doing? Well, it's looking here. It's finding the guess. Plastic grows on and is looking at an average off this quantity so we can see there's one right here that's plastic proven at two in it. And then we go down a bit further, for we can see there's this one here that's got a desk plastic on before. So four plus two equal six divided by two equals to the and that's the average it's 8. VLOOKUP: So in this video, we're going to look at the V look up formula. Which dancer? Vertical? Look up now. This formula can be a bit confusing at first, but it's actually really simple, and it's incredibly useful, So it's a really good one tonight. What it does is is it returns the value from a cell in a table based on a value that you specified. That's in the left, most column of a table. So it's really easier if we just dive into an example to explain that because it concerned a bit confusing at first, I'll say. But it is. It is quite simple. So we've got this table here that's got some dummy data in them. We've got a list of ingredients with their quantity on their weights in kilograms. So let's say we were interested in the weight off biscuits. So using a V look up formula show you how you can achieve that. So we want to look up the value of biscuits. That's a look up value Andi were using The table array is simply just the table of data where the information comes from, so you can Hi, lytle the table like that now it looks in the first column, so it's gonna be looking for biscuits in that first column on. Then it's gonna be retrieving a value, which is in a column to the right of that first column based on this column index number. So because this this table has three columns in and you have to include the first column toe 123 This column here is what it's called. Three. So we enter that so you don't you don't consider this to be a kind of blank column way. There's you can't from. So it's not a case of one, too. We have to count all three columns that went to three there on then. Lastly, the range Look up for here. We just want to select false. This will be what you use usually on that would just return the value exactly and then close that off. Enter on. We get 0.5. So what's That's what that has done here is it's looked down. This list looked for biscuits because that's the one we specified, and it's looked in this table. So a three all the way down to see nine. It's gone Teoh column, 3123 And then it's looked for biscuits and it's returned the corresponding value of 0.5, and we can see that that's the number we've got. So we could easily change this. We could change biscuits to something else. That's change that to Rice. And then we get eight because that's the value here, ate and ate on an equally If we wanted to return something in the quantity column. What we could do is just change the three here. The column index number 22 and then because we've left it is rice. It's returning rice quantity one. So it's giving us the answer there, and you'll notice that the former is slightly different. We can just change it there, so we got the same number of decimal points. So that's the V look up formula now, in this particular example of a really small table just to illustrate the formula, it may not seem as useful is it can be. But when you've got an enormously long list of data going all the way down on, do you want to return values based on that and you can quickly see them? That's when it really comes into its own. So it's a very simple formula, but it's it's really, really useful 9. HLOOKUP: So in this video, we're gonna look at the hatred Cup formula. Now the hate look up formula is really similar to the V Look up formula. Except rather than being the vertical it's hates for horizontal s. So what that means is, is that rather than looking across the different columns to retrieve a value, it looked across different rose to retrieve value. So let's go in and have a look. Our example? Data. So here we've got a list of officers officers, ABC three to wear. And then we've got there, 2018 sales numbers. And then we've also got the 2019 set of numbers. So if we start off with an example, Sigh I don't hate you. Look up on, like to be. Look up. We specify are look up value. So let's they were interested in 2018 sales. Andi. Then when it's best off, specify our table away, and that's the same processes V look up to just highlight table for your data in now we're interested in 2018 sales on Let's say we want to find the 2018 sales for office. See? So this is where we do that same process of counting, counting down the counting of those which is different to the V. Look up because we're recounting the columns, so we're gonna count those roads. So 2018 cells, that's all. Very one. Whether look at value comes from we go. One Teoh, be four. We've got row Index off four and then folks that much. So that's the value that we get. 188 because it's looked across here. It's found 2018 sales and then it's looked down four rows on retrieved office sees value of one A. On if we just run through. A couple of examples were changing. It's formula. Let's say we wanted to retrieve the office, see sales, but for 2019 we could just change that look up value on. Then we're getting this value here such four rows down to 076 and then let's say we were interested in the office F sales, so that would be a case of changing the row index number. So want to be 4567 So we changed that 4 to 7 and then we get that a 3 to 5 figure. So it's pulled off that figure there from the table. That's an example of hate to look up, really useful formula to know on worth having in your talk it as well, just like the V look up. 10. LOWER, UPPER, TRIM, PROPER: Okay, So in this tutorial, gonna look at a few functions that you can use for converting tax strings so that they look different. They take on different format. We're gonna go through four functions. They're all really simple and really easy to use on their also quite self explanatory. Let's dive in. So here we've got our example. Data on. We've just got four words here that'll sort of formatted in different ways so we can see this one. Here is a lot the case. This is a lower case. This has got a couple of spaces at the beginning on this one Here is fairly standard, but it's all in lower case as well. So these the four functions that we're gonna go through, we'll just go through these one by one. So what the lower function does? Is it convertible letters in a text? Oring to lower Case says there. So let's just show that so we select flower, they go to all the letters have been converted to lower case upper. Very simple. It just does the complete opposite. So it takes little to up case and then trim. This is really useful one. It removes any black spaces unless they are blank spaces between the words. So if you've got a blank spaces at the very end of the very beginning, that's what we're informed us for. And then, lastly, proper. What this does is it converts this ex string to proper cases, which means the first letter in each word is converted to uppercase and all the other letters to locate. So it's kind of like title case, and they have it. That's four functions that really useful for converting text. 11. CONCAT: So in this video we're going to look at the con cat function. Now. This is a really useful function to know it's a little bit different. Some of the other functions informers we've been looking at. But yeah, that's a really useful to know. What it does is it joins up text strings so that could be sequences of letters or numbers that you want joined up. Or it could be different words that you want to join up to form new phrases or new sentences. So we've got our example. Data over here, we've got the items. So things chairs, desk table lamp, different furniture and then we've got their color. So over that pink, red, blue green, etcetera. So let's just dive in and look at an example. So at this point here, it's worth pointing out that you got Can Katyn eight. Andi Konka. So Konka will allow you to you combine a list or range of texting so you don't have to specify individual cells. You could just highlight a range, and I'll show you what that means. In a minute. Rose come fascinating. It joined several tech strings into one texting. You have to specify the individual cells, So can Katyn. It's actually a legacy function. It's still available in Excel on. You may find workbooks that use this formula, but can cat is the new ones, which you really want Teoh using So we just carry on without. So let's say we want to combine our text so that we've got pink on chair or when once out of bed and desk called and wants out. For example, so we can do is you can just select the cell that you want joined up on. Then you can select the other cell you want joined up, and it's worth pointing out that you can also write words in so you don't have to select ourselves. You could also put a chair there and then that we join up onto the phrase to. So if we've got those too in and hit enter and what you can see there is, it combines them. So we've got a picture now. Obviously, you're you know, you probably want to space between those two words on. That's also possible with the compact function. So what you can do is you can just come in here. I've got a little quotation mark a space, another one and then a comma and then that space in that. So that's really useful on what I meant by being able to slept Rangers off text is this? So if we just arrived in clicking on each end of itself individual cell, you can just highlight the whole range on, Then he enter. So you see, there's like the colon there rather than common between the two Selves on Hit Enter and it will join up all of those words like so so again not useful in this particular case but could be useful elsewhere. So if we just revert back Teoh the formula implementation that we had con cats, pink space Sure. Now, in this particular case, another point to mention is that you could office also just drag this down on. Then you could get all of these combined. So we've got purple lamp, pink chair, red desk on. Yeah, that's the cat function. It's a really useful want to know a little bit different to some of the performers looking at, but you're great having you talk it to