Hi everyone, Kevin here. Today, we are going to learn about formulas and functions in Microsoft Excel. Maybe you have a job interview coming up or a test where you have to demonstrate your knowledge of Excel. Don't worry, I've got your back. We're going to start with the fundamentals of how you can even enter a formula into Excel, and then we'll advance to the most commonly used functions. By the end of this video, you'll be a pro with formulas and functions. To follow along, I've included a workbook down below in the description. You can also jump around this video using the timestamps down below. All right, let's get started. Here I am now in Microsoft Excel, and I'm currently on the formula fundamentals worksheet, just in case you want to follow along. And I want to calculate how much revenue I earned from the three different types of cookies that we sell here at the Kevin Cookie Company. So I could use a formula to calculate this, but even easier, I want to add up these three different cells. When I highlight these three different cells, if we look down below here on the status bar, you actually see that we have the sum right here. It's 23, and that's how much revenue we made from these cookies. So you don't even have to enter in a formula to get some basic metrics back. And in fact, if I click on the sum here, I can go into the total cell and press control + V to paste, and here I've added up all of these different cells, just like that, just by using the status bar. Now down below, when I have these three values highlighted, I could also go to the status bar and right click. And here, if I scroll down this menu, you'll see that I also get the average, the count. Here I could get the min, the max, and here I have the sum. So here, if I check this on here, I could also see the max now down below on the status bar. So this is one way to get some quick answers without even needing to enter in a formula. Now, of course, this video is all about formulas and functions, and we should probably enter in our first formula. So here I'll come up and let me delete the 23 that we got from the status bar. And here, once again, I want to add up 10 plus 8 plus 5. So the first thing I need to do to enter a formula is to enter the equal sign. This tells Excel that I'm about to enter in a formula. And here I could take 10 and I could add 8 and I could add 5. Here you could see my very basic formula. Now, if we look up above here in the formula bar, you'll see the formula here as well. To calculate this formula now, all I need to do is to press the enter key. Congratulations, you just entered your first formula in Excel. To go back and edit this formula, here I can click into this cell and here we see the formula up above. So I can modify the values up here, or alternatively, I could also double click into this cell and this will once again show me the formula and then I can make modifications here. Now, one problem with just entering in the formula like this, let's say that now we made revenue of 11 on our chocolate chip cookie. Here, I'll press enter. You'll see that this did not update, and the reason why is we hard-coded in these values. So as these values over here change, my formula doesn't change. So instead of hard-coding these numbers or values in, instead I want to reference the different cells. So here, for example, instead of typing in 10, I want to use cell B2. So here, let me delete 10 and I could type in B and then I'll enter 2. And here you see that the cell has now highlighted. And here also for the 8, instead of hard-coding that, here I'm going to type in C2. And also here, I want to type in D2 for the sugar cookie revenue. And now I could press enter. And here you see I get the same result. But the nice thing now is let's say the revenue for the chocolate chip cookie changes. Let's say it's now 11. You'll notice that the total automatically updates. When I click back into the total cell, here you'll see that Excel uses different colors. So here you'll see blue on B2. And this is also highlighted by blue. Here C2 is red and here you see red. And here's purple and here you see purple. So the colors here in the formula correspond to the cells that they're referring to. Now, of course, typing in a cell reference works, but it also requires quite a bit of effort. And instead we could use our keyboard or our mouse to reference specific cells. Here, once again, in the total cell, I'll enter the equal sign, and instead of typing in B2, I can simply use the arrows on my keyboard to select that cell. Here, I'll type in the plus sign and I can arrow over, and once again, I can arrow over and then hit enter. So that's yet another way I can refer to specific cells. But even with the arrow keys, let's say I have a very large sheet and it could take a while to get to the cell that you care about. Here, once again, I'll click on delete. Here, let me enter the equal sign. And instead of using my keyboard, I can also use my mouse. Here, I could click on B2 and I could press the plus sign, and here, let me select C2, plus, and then D2. And here too, I could press enter, and once again, we had revenue of 23. Now that we know what the revenue is, let's say I want to know what the cost is. Now, once again, I could type in the equal sign and I could select this cell. I could add this and then I could add this. So just like we did up above, but that takes a bit of effort. Instead, I can click into this cell where we already have a formula, and I can click on this fill handle in the bottom right-hand corner. There, you see that my cursor changes to a plus icon. And I can simply click on that and then drag it down. And here, you'll see now that the formula has updated where it adds up all of my different costs. So the formula adjusted relatively. I moved the formula down by one row, so it also updated all of the references by one row. Over here, I can also use subtraction. So let's say I want to calculate the profit. To calculate the profit, that's the revenue minus the cost. Once again, I'll enter the equal sign. And here we had revenue of 10 for chocolate chip, and let me subtract 4. And once again, I could press enter and here the profit is 6. Now, just like we did before, if I want the profit for all of these other columns, once again, I can click on the fill handle and I can drag this over. And once again, it automatically adjusts the formula relatively. So I moved it over one, so the formula references moved over one. And here you can see how that worked for every single column. That's a quick way to reproduce a formula in many different columns or many different rows. Now, this is some pretty nice profit, but unfortunately, you have to give up some of that profit towards taxes, and to calculate the tax, we're going to use multiplication. Down here in cell B5, I want the profit of 6, and I'm going to multiply it by the tax rate of 10%. So once again, let's enter the equal sign. And here I'll reference this cell with a profit. And once again, I want to multiply it by 10%. Now, you might think the multiplication sign is an X, but in Excel, that's just a character. To enter the multiplication sign, we enter the asterisk. That's how you multiply something. And here I'll select 10% and then I could press enter, and it looks like the tax is 0.6 or 60 cents. Now here, once again, I want to calculate the tax for all of my different cookie types. So just like we did before, we could use the fill handle. Here, once again, I'll select that and let me drag it over one column. But here you see that the tax is zero. Now, from a business standpoint, that's good news, but I don't think it's calculated correctly. Here, if I click on the cell, I can go up to the formula bar, and when I click on this, I can see all of the different references. And here it was making relative references. So once again, as I pulled the formula over, here it adjusted it to look at this cell, but if we look up here at the tax rate, it also moved this reference over one. But I want the reference to point at this specific cell or G2. So I basically want to lock this reference. So let me go back to the original formula where we calculated the tax. And in here, once again, I want to lock this reference in. And we're going to use a shortcut key to do this. We can press the F4 key or the function 4 key on our keyboard. And you'll notice that it adds a dollar sign before the G and a dollar sign before the 2. The dollar sign tells Excel that we want to lock both the column and the row. Here, I'll press enter. And now if I pull this over, you'll notice the formula works as we expect it to. And when I click in, here you can see that it adjusts this first cell relatively, and then it locks in the tax rate. And here it did that for all of the different tax calculations. This is what's referred to as an absolute reference. As a quick note, here when I'm in this cell and we see the formula up above with the dollar signs, if I press F4 again, now you just see a dollar sign on the row. If I press F4 again, you just see the dollar sign on the column. So you could just lock the column or you could just lock the row. And here, if I press F4 again, it removes it from everything, and once again, it's purely just making a relative reference. But once again, I want to lock it, so I'll press the F4 key. With this formula, I'm currently referencing the tax rate that's on the exact same sheet. But you can also reference a cell on a different sheet. So here, once again, let's go back to this first cell. Here, I'll delete these different values. And instead of referencing the tax rate here, let me delete this. And right now, I need to enter in the tax rate. And it turns out there's another sheet called tax rate. I can click on that sheet, and here we see that tax rate of 10%. I could click on it here. And once again, I could press F4 to make this an absolute reference. Here, I'll press enter. And once again, this works. And here you see that it uses the worksheet name. So it's pointing to this tax rate sheet and then the cell on that sheet. You can even refer to a completely different workbook if you want. And you'll see the notation appear right up here in the formula bar. Now, once again, I can take the fill handle, and let's calculate the tax across all of these different columns. Now, this seems to work well where I'm referencing a different sheet. But when you look at the formula itself, it's a little bit messy. You have the sheet name and then you have the cell reference. I'll go back to the tax rate sheet and I can give this cell a name. So I don't have to refer to it as A2, but instead I could use the name reference. Right up here where you see A2, here I can simply type in tax rate and then press enter. I've now assigned a name to this cell. Let's go back to formula fundamentals. And here, instead of referring to the sheet and the cell, I'm going to delete that. And instead, I'm going to type in tax rate. And here you see a hint that this is a known name. Now I can press enter and that too works, and it's a lot cleaner now. Now I simply refer to tax rate and it's really easy to understand what that is. Now, let's say that I'm entering in a formula and I forget that I had a name called tax rate. I can press the F3 or function 3 key to see all the different names. When I press F3, here I get all the different defined names and here I see tax rate. I can simply double click on that and that inserts tax rate into my formula. And then I can press enter. So once again, that's another way to get back to your different named cells. We've looked at addition, subtraction, and multiplication, and you can also do division as well. Here, I'll go down to the profit split cell and here at the Kevin Cookie Company, we made $14 of total profit. For now, I'm going to ignore taxes. And let's say that we have two owners of this company. It's me and Patty. So here, once again, I'll type in the equal sign. Then I'll select the total profit that we earned, and I want to divide it by two to see what my share is. To do division, we're going to enter the forward slash, and then here I will simply type in 2 and press enter, and here we can see that my share of the profit is 7 and Patty's share of the profit is also 7. Next, let's move on to the order of operations worksheet. And here, once again, I see the revenue, the cost, the profit, and the tax for chocolate chip cookies. Now, let's say I want to calculate all of this in just one cell to see what the tax is. I'll enter the equal sign. And first off, I need to take the revenue. I want to subtract the cost and that should give me the profit. And next, I'm going to multiply this by the tax rate of 10%. And then I'll hit enter. Whoa, what's going on here? My tax is 9.6, but my profit was only 6. That's ridiculous. I mean, I know tax rates are going up, but it shouldn't be more than my overall profit. The problem here is the order of operations, and when I click into the cell, let's double click in, we can take a look at how Excel calculates this. Excel looks for multiplication and division first, and then it evaluates that part of the formula. So here, for example, it's taking 4 and then it multiplies it by 10% or 0.1, and that gives us 0.4 as the result. In fact, if you want to calculate a portion of your formula, here I have this portion highlighted. I can press the F9 key and here I can see that it evaluates to 0.4. I'll press control + Z to undo that. So it's taking 10 and then it's subtracting 0.4. And once again, that gives me 9.6. Now I want it to first calculate 10 minus 4 and then take the results of that and multiply it by 10%. To do that, I can use parentheses and that way I can define what I want the order to be. Here I'll put a parentheses around B2 minus B3. That way Excel will evaluate this. It'll take the result and then it'll multiply it by 10%. Now when I press enter, I get 0.6 and that's exactly the same as what we got on the previous sheet. So one thing to be aware of when you enter formulas, there is an order of operations. Now up to this point, we've purely been looking at formulas, and next we're going to look at what's called a function, but how is a function different from a formula? Well, with a function, you can pass in a parameter or an argument and then you get a result back. Now I know that probably doesn't mean that much, so why don't we make this real and look at some examples. Here on the basic functions worksheet, I want to answer the question, how many cookies did we sell? Here I see all of the different cookies we sold, so I want to add up this list. Now we could just use a simple formula to do this. Once again, I could enter the equal sign and I could take this and I could add it to this and I could add it to that and I could go through this whole list, but that takes a lot of time. Instead, we want to use a function and that'll make this entire job a lot easier. So how do we insert a function? Well, one thing we could do is up here, we could click on formulas or the formulas tab and over on the left-hand side, there's the option to insert a function. As I hover over, you'll also see that the shortcut key is shift + F3, so you could use that as well. Let's click on this. This opens up the insert function dialogue and right up on top, it says type a brief description of what you want to do and then click on go. Now, once again, I want to add up all these values or another way of saying that, I want to sum up all these values. So let's type in sum, then I'll click on go, and here it looks like there's a function called sum. There are also some other options and we'll get into these later, but for now, we just want to sum these up. Let's click on okay. Here now it asks me what numbers I want to sum up. I'll move this over just a little bit so we can see the numbers we want to add up. And here it says number one, number two. Now I'll delete this value and here I can click on this cell, number two, I can click here. But you'll notice that this isn't very much faster than just adding up all of the different cells using formulas. So here, let me delete those again. Instead, here I can highlight this entire column. So here I can highlight all of these different values and you'll notice here it says B2:B8. So I'm passing in all of these different values, basically this range, and here you can see the range of values that I pass in. These are referred to as the arguments or the parameters that I'm passing into the function. And right here at the bottom, you can see what they add up to. So when I sum all of these different numbers, that gives me 421. And this is a basic function. Next, I can click on OK, and here you see that I just summed up all of these different cells. One thing to call out. When I double click into the cell, you can see the notation of a function. So once again, you start with the equals sign that lets Excel know that you're entering either a formula or a function. Then you type in the function name and then you open the parentheses. Here you pass in the arguments or the parameters, and then you close the parentheses at the end. So instead of using the insert function helper, you could simply type this in. Now, interestingly, there's also a function that you could enter in that shows you a function. So here I could type in equals and let's type in formula text, and then here I'll open the parentheses and I have to reference a cell. So let me reference this cell and then close the parentheses. So I'm following the same format that we saw with sum. And when I press enter, here now we can see the function that's being used in this cell. So just to help you see what's going on to get this 421. Now, Excel has many, many, many different functions that you can use, and here, if we click on formulas up on top here, you can see some of the different categories. You have financial functions, you have logical functions, text functions, and there are many, many different options here. But once again, if you want to search for a function, once again, you can click on insert function and then you can use the search command. Now there's one category here called auto sum, and when I click on the dropdown, this contains some of the most commonly used functions. Here for example, we see sum again, but you have average, count numbers, max and min. And then once again, you can get to all of your functions. So let's say, for example, here at the bottom of the list, let's say I want to sum it up here. I can click on formulas and then simply click on auto sum and it'll default to sum, and here I could simply press enter and I get the sum. Now, auto sum is used so frequently that it's also on the home tab. Here I could click on home and I could go over to editing. And here, once again, we see auto sum. Now, when I hover over this, you'll see once again that it defaults to sum, but here too, you'll see the shortcut key. I could press alt together with the equals key. So here I'll click into this cell and I can press alt and equals, and once again, that will also give me the sum. So that's an even quicker way to get the sum. Now, once again, this is one of the most commonly used functions in Excel. Over on the right-hand side, I want to continue answering these different business questions and I want to know the most number of cookies sold. Here, once again, I could click on formulas and let me go back to this auto sum group because it has some of the most common functions. And here's the function for max. I could click on that and here it enters max, but it's looking at this cell. And instead I want the max number from this. So here, once again, I'll highlight this list. So this is the argument or the parameter that I'm feeding in and I could press enter. And here I could see that chocolate chip had the most number of cookies sold at 97. And here we can validate that by looking at this list. I'll delete the 421. Right over here, I also want to know the least number of cookies sold. And if we just look at this list, we see that it's fortune with 36. I'll click into this cell. And once again, I could go to formulas, auto sum, and here I could select min. But instead let's just type in the function. Once again, I'll type in the equal sign and let me type in the function name min, open the parentheses, and then we have to pass in the argument. And that's this list of values. And then I can close the parentheses, hit enter, and here we see that fortune cookie only had 36 cookies sold. Let's say instead of knowing the max or the min, I want to know, well, what was the second most sold or what was the second least sold? And for that, we could use the function large or small. Here I'll type in equals and I can type in large and then open parentheses. And here it asks for an array and a k. And if you're not quite sure what these mean to make it a little easier, we could click on this FX or insert function, and this once again opens up the function arguments. And here we see some hints on what those values need to be. So first off the array is the range of data. So here I'll click into this cell and let me select my data or what's referred to as the array. And then K, this refers to the number that we want back. So here I want the second largest. So let me type in a two, and here we can see that it's 77 for oatmeal raisin. I could click on OK. And here I get the result back. So as you enter different functions in, if you need help with them, remember you can click on this FX right up above, and that'll open up the function helper, and this will help you to compose your function. Down below, I want the second least sold. So let me type in the equal sign. And for this, I'll type in small. And once again, it wants the array. That's this list. And I want the second smallest. So once again, I will type in a two, hit enter. And here I can see that peanut butter is the second least sold cookie. With the large and the small functions, we entered two separate parameters. So here, once again, when I open the function helper, here you see that it's asking for an array and it's also asking for the number that we want. So with various functions, you may have many different arguments that you can pass in. This is just one example where you can pass in two different arguments or two different parameters. Next, let's say I want to know how many cookies had any sales at all. And there's a function for that called count. Here I could type in count. And we see there are also some other options. And in a moment, we'll get to what some of those are. But here I'll type in count. Once again, open parentheses. Here I can highlight this list, then close parentheses and then hit enter. So I can see that seven different cookies had at least some sales. Now let's take a look at the count of the cookie names. So here, once again, I'll type in count and let me highlight all the cookie names. So how many different cookie types do we have? And here I could press enter, but here it shows me zero. So why is that? Well, when we use the count function, this is counting numerical cells. And these cells over here have text-based values. So it's returning a zero. And once again, if I click into this, let's take a look at some of these other count functions. Once again, I'll type in equals count, and here I see that there's an option called COUNTA. And when we read this, this will return back all the cells in a range that aren't empty. So let me select that. And here, once again, I could highlight this list, close the parentheses, hit enter, and here now it tells me that there are seven different cookie types. And just like you could count cells that have a value in it, you could also count all of the blanks. So here I could type in count blank. And let's say I select all the cookie names plus one additional blank row. Then I'll close the parentheses, and here it tells me that there's one blank value. So yet another count function that you can use. If we move down just a little bit more, there are a few more questions that I want to answer, and this will show us even more functions. I want to know across all of my cookies, what was the average sold? To enter the average, you could probably guess it, but the function name is average. And once again, if we go up to formulas and auto sum, here we'll see average here. So you could also get to it here. I could select average. Once again, I could highlight the list and I could hit enter. So I don't always have to close the parentheses. I can just press enter and that too will calculate. So that saves you a little bit of time. And here I can see that on average, we sold 60.14 cookies. Along with your average, you can also calculate the median, or basically which number is in the middle of this list. If we just look at it, we see that it's 52. Here I could type in median, open the parentheses. Let me highlight this list and press enter. And there we see that it's 52. And we could also use something called the mode. And this tells us which number occurs most frequently in this list. Now, when we look at this list, 97 shows up once, 77 shows up once, but here we have 52 that shows up twice. Here I could enter equals mode. Once again, I'll select this list, hit enter. And here we see that 52 shows up the most often in this list. Moving on now to the conditional functions worksheet. We're now getting a little bit more advanced. And here we have a function called SUMIF. So here we can sum up values if it meets a certain criteria. So let's say for instance that I want to know how many cookies we sold in the United States. So if it's the United States, I want to sum up all of these values. And for that, we're going to use the SUMIF function. Now, once again, probably the easiest way to enter a function is to click on the insert function icon, and this opens up the dialogue that will help us write this function. Here I could type in SUMIF, click on go, and here I see it down below. I'll click on this one. Here it asks me for the range. And so what is the range? Well, here I could just look down below and it tells me exactly what that is. It's the range of cells that I want to evaluate. Now, remember, I want to evaluate whether it's in the United States. So here I could simply highlight this column right here. And for my criteria, I want it to be the United States. So I'll click in this cell. Now I could also type in United States here, but in this case, I'll simply click on this cell. Lastly, it asks me for the sum range. So if it finds United States in this column, then I want to add up these different cookies sold. So for the sum range, I'll select this column and here I could already see that it's 362. I'll click on okay. And just to confirm, I can highlight all of these different cells and down below, I can see that the sum is 362. So this is how you can use a conditional function. Down below, you'll see some additional functions like AVERAGEIF, and here's COUNTIF, and these work the exact same way as SUMIF. If some condition is met, then you'll average out all the values, or if some condition is met, we could count all of those items. So once again, they work exactly like SUMIF, except in this case, you average or you count. I want to show one neat thing that you can do. here within the function for SUMIF, Let me open up the function helper again. And for the criteria, here I'm going to type in United States. And once again, I'll click on OK. And we see that the function works the exact same way. But let's say that I want to sum up all the values for countries that end in an S. Now it's an arbitrary situation, but I want to show you how you can search based on a wild card. So here I'll remove most of the United States, but I'll leave the S. And here I can insert an asterisk or the wild card symbol. So it'll look for any characters before the last character, which needs to be an S. Here, if I press enter, I get a sum of 802. So this is the sum of all the cookies sold in the United States, which ends in an S. And also the Philippines, which also ends in an S, And here, if I highlight all of these cells, I can see that the sum here is 802, and that matches up with 802 right up here. So this is yet another way that you can sum different values based on a condition. Next, we have another formula called SUMIFS. So it looks like the same thing as SUMIF, but why do we have an S on the end? Well, you can think of this as the plural version. So you can look for multiple criteria. So let's actually test this out to see how it works. Here, I'll click on insert function. And here, let's type in SUMIF and S. I'll click on go and then select this function. And here first, it asks me for the sum range. So what do I want to add up? Here, I'll select the cookies sold column. And here it says the criteria range one. So I want to evaluate the country column first. So let's say I want to know how many cookies were sold in the United States. I'll select this column, and then my criteria is United States. But now I can add additional criteria. So maybe here I want to check, was it a chocolate chip cookie? So here I could select this range and for the criteria, I'll select chocolate chip. And then I can click on OK, And here I see the sum of chocolate chip cookies sold in the United States is 28. Building on this, let's say I want to know how many cookies were sold in the United States that were either chocolate chip or oatmeal raisin. And I can do that as well with SUMIF and S. Once again, I'll open up the function arguments. And right here, where I have my criteria for the cookie type, here I chose chocolate chip cookie. I'll remove that. And here, let me highlight chocolate chip and oatmeal raisin. So I could select two different values as my criteria, and then I'll click on OK. And here I get two values back. One is 28, which is the sum of chocolate chip cookies, and then I get 32, which is the sum of oatmeal raisin cookies sold in the United States. Now I get them back separately. So each criteria comes back as a separate entry. Now, if I just want to get this back as one, right up here within the formula, I can click here, type in sum, insert a parentheses around the SUMIF function, then hit enter, And here it'll add up those two values. And so the sum of both chocolate chip and oatmeal raisin in the United States, here, if I highlight these cells, we see it's 60 and here I get 60 back. Down below, we also see AVERAGEIF and S, COUNTIF and S, and then also MIN and MAX. So once again, for all of these different functions, you can have multiple criteria. Now you might be wondering, well, with SUMIF and S you could just have one criteria, at which point it's exactly the same as SUMIF. So you could just always use SUMIF and S and then ignore SUMIF, because this not only does one criteria, but it also allows you to add in multiple criteria. Moving on to the next worksheet, we're going to look at some functions that allow you to manipulate text, and these can be a big time saver. Here's the first one. We have the Kevin Cookie Company name, but I forgot to capitalize the first letter of each word. Now, of course I could go through and I could change that, or I could use the proper function. Here I'll type in equals, proper, open parentheses, and here I can place this text in, close the parentheses, hit enter, and here it applies proper casing to every single word. The next function is the trim function. And here, once again, we see the Kevin Cookie Company, and here I have some leading spaces in front and I don't want those. Now, of course I could simply delete those, but once again, if you have a lot of rows of data, that might take a lot of time. Here, I can type in equals trim, select this text, close the parentheses, and here it's automatically removed those leading spaces. Next, we have a set of functions that allow you to concatenate or join or combine text together. So here I have Kevin Cookie Company, and each one of these references is in a different cell, but I want to bring them all together. I could enter in equals concat, and you'll see that there are two versions. There is concat and then also concatenate. Concat is replacing concatenate, so I'd recommend using this one. This is simply here for backwards compatibility. Concat has a few advantages over concatenate, and that's the preferred one to use. Here, I could simply copy all this text, close my parentheses, and hit enter, and here you see that it brings together all of that text. Now, of course, I don't have spaces between these different words. So up here, once again, let me go back to the function. I'll delete the text or the range that I entered there. And here I can click on the first one, place in a comma, then I could insert a quote, a space, a quote, another comma, and then I could click on the next text, and I could also go through and do the rest of it, but I'll just leave it at this. When I hit enter now, you'll see that it includes the space. So here I concat this word with a space, with the next word. Now, instead of typing in concat, you can also use an ampersand. So here, for example, I'll enter equals Kevin, insert an ampersand and then Cookie, and then an ampersand and Company. And there you see it combines all of them. Now, of course, once again, I have that same issue with the space missing. So here I could insert a quote, a space, a quote, another ampersand, and then hit enter. And here you see it's added a space and I could do the same between Cookie and Company. But by far the easiest way to join together text is to use the text join function. Here I have Kevin Cookie Company. I'll enter equals text join, open parentheses. And here I get to specify what the delimiter is or what's going to separate each one of these cells full of text. I'll enter a quote, a space, a quote, a comma. And then I could say, what should I do if I encounter an empty cell? Here, I'm just going to ignore them, put in a comma, and now I can select all the text that I want to join. I'll highlight these three and then hit enter. And here I get Kevin Cookie Company back with a space in between each word. So out of these three options of concatenating or bringing together text, text join is by far the most powerful one. Moving on, we have some additional functions, here right, left, and mid, and these allow us to take out text from the right-hand side, the left-hand side, or the middle. So what do I mean by that? Well, let's say that I want to pick out the zip code from this address. I can use the right function. I'll enter in right, and then I'll select this address as my text, a comma, and then I need to define how many characters do I want to take out from the right. And here we can see the zip code is five characters. I'll type in five and then hit enter. And this pulls out the zip code. Next, we have another function called len, and this is an abbreviation for length. So I could use len to figure out how many characters make up the Kevin Cookie Company. I could type in equals len, open parentheses, and then I could put this text in, close parentheses, and there we see that it's 20 characters long. Next, there's another function called find, and then there's one called search. And they essentially do the same thing, but there's one key difference. Find is case sensitive, and then search is not case sensitive. Here in this example, let's say I want to find out what is the position of the @ character within this email address. And I could use either find or search to find this since @ isn't case sensitive. Here, I can insert the equal sign and let's use find, type in the function name, and I want to find the @ symbol here. I'll insert it in quotes since I'm searching for text, put in a comma, and I want to find it in this email address. I also have an optional parameter at the end, but I'll leave that just blank. Here, I'll press enter, and I can see that the @ symbol is in the sixth position of this text. Now, you might be wondering what good is it knowing what position the @ symbol is in. Well, you could really get some nice power when you combine this with other functions. So let's say that I just want to pick out the name portion of this email address, and I don't care about the @ or the domain at the end. Here, I could start combining some of these text functions. For example, I could use left together with find. Let's enter in the left function. Now, remember with left, it'll keep the left most characters, and I have to define how many characters on the left I want to keep. Here, I'll select this text, then I'll enter a comma, and now I have to define how many characters I want to keep. Now, I just want to keep five, basically right up to the @ symbol, and I could use the find function to tell me where that @ symbol is. So the same function that we entered right up here that gave us six back. Here, I'll type in find, and once again, I want to find the @ symbol. Here, I'll enter the @ symbol, and I want to find that within this text. Now, I'll close the parentheses and close the parentheses again. And when I run this now, I see that it has Kevin and @, and that's because here it kept the left most six characters. So one thing I could do, if I go back into the formula or the function, I could subtract one from the results of the find function, and then I could hit enter, and it just keeps Kevin, because I don't want to include the @ symbol. The nice thing now is this is completely dynamic, so if I change the name to, say, Sue, here it automatically updates to Sue. So this shows you the power of when you start combining these different text functions together. Moving on to the logical function sheet, here we're going to see how you can add some logic to your spreadsheet. And first off, we could simply check is something true or false. So here I'll enter in the equal sign again, and is the Kevin Cookie Company equal to Mrs. Fields? Now, I hope all of you know the answer to this, but of course not. The Kevin Cookie Company is way better than Mrs. Fields. Now, this simply compares those two text values to see if they're equal, and of course they're not, so it says false. But you can also use this for numbers. So is 10 equal to 5? And no, that's false. Here I could also say is 10 greater than 5? And that's true. Here I could also say is 10 greater than or equal to 5? And that's also true. And here is 10, let's say, less than 5? And that's false. So just a quick look at how you could compare different values within your spreadsheet. Here, too, you could also check if multiple criteria is true or false. I'll enter in an equal sign and then type in and. Open parentheses, and here I could say, let's say, is 10 greater than 5? Then I can insert a comma and let's say is 10 greater than 11? And then I can close my parentheses. And that's false. 10 is not greater than 11, but it is greater than 5. But both conditions have to be true for this to come back as true. So let's say I entered a 9 in here, and that is now true. Now, instead of using and, I can also use or. I'll modify the function to or. And here it says, is 10 greater than 5? Or is 10 greater than 9? So here, if I put the 11 in, it's still true because 10 is still greater than 5, even though it's not greater than 11. Moving down now, we're going to write an if statement. This is kind of feeling like programming a little bit. We get some if statements on our spreadsheet. And here's the business question that I want to answer. Do we sell more chocolate chip or oatmeal raisin cookies? Here we just look at these numbers and we sell 97 chocolate chip and we sell 77 oatmeal raisin. So of course we sell more chocolate chip, but can we use an if function to answer that question? To make this easy, let's click on the function helper up here and let's type in if. And here I see the if function. Let's select this one. Now here I can run what's called a logical test. So let's say is 97 greater than 77? That's my test, and here it comes back true. And then I can define, well, what happens if it's true? So here I'll say we sold more chocolate chip, but if that's false and this is not greater, then I could say, let's say oatmeal raisin, and then I could hit enter. So do we sell more chocolate chip or oatmeal raisin? Well, we sell more chocolate chip, but let's say that oatmeal raisin, let's say we had a big day of sales and I sold 150. Well, now it automatically updates and we've actually sold more oatmeal raisin now. Moving down, we can also use and together with our if statement. So here, for example, does chocolate chip sell more than all other cookies? So let's once again, insert an if statement. Here I'll click on the function helper and I'll select if again. So here I can enter my logical test. I'll type in and remember, this is just like we did up above. And I want to run two separate tests. First off is 97 greater than 150. And for my second test, I want to check is 97 greater than 50? Then I'll close my parentheses. And if true, let's say yes. And if false, I'll enter no. And then I can click on okay. And here we see that does chocolate chip sell more than all other cookies? No, we don't. Oatmeal raisin sells more, but let's say I typed in 96 here. Now it's yes. Now, just like we looked at previously here, I can take this function and let's use it for this or statement. So does chocolate chip sell more than oatmeal raisin or sugar cookie? I'll paste in this function and instead of using and I'll type in or, then hit enter. So does chocolate chip sell more than oatmeal raisin or sugar? Now let's say oatmeal raisin sold, let's say 150. Well, it's still sold more than sugar cookies, so it's still yes. But let's say sugar cookie also sold 150. Now it shows no. So that's how you can use and and or within an if statement. Now you might be wondering, can you include an if statement within an if statement? And you absolutely can. And here in this example, let's say I want to know the price of a chocolate chip cookie. So here we see all the cookie types and the associated price. And I can use a nested if to figure out what the price is for this cookie type. Let's work through it here. I'll enter the if function. And first I want to check, is this cookie type this one right here? And if it is, well, I want to get this price back. But if it's not, well, we want to then check the next row. So here I'll type in if again. And here I want to check, is this name equal to this cookie type? And if it is, we get this price back. And if it's not, then we want to run one more if statement. So is this cookie type equal to this cookie type? And if it is, this is our price. And if it's not, then we'll say unknown. And then I can close the quotes, close the parentheses, close the parentheses again, and close the parentheses again. One thing that's really helpful, when you're closing the parentheses, you'll see color on the parentheses. So the first one's black, the next one's red, the next one's purple. And here you can tell that I've inserted all the right number of parentheses. Now I could hit enter, and I could see that the price of chocolate chip is 10. But here I could type in sugar cookie, and there I see the price is five. So that's how you can use nested ifs to do that. Here, I'll enter in formula text, just so we can see what all of the formula looks like. So you'll notice it's a fairly long formula, and it starts to get pretty complex. Now, there's something new called IFS that makes this a lot easier. Here, I'll type in IFS. And here, first, I could do the first logical test. So is this cookie type equal to this one? And then if true, I want this price. The next logical test is this cookie equal to this one. And if true, I want this price. And lastly, is this cookie type equal to this one? And if so, I want this price. Now I can close my parentheses and hit enter, and here I get five back. I'm going to move it over one. And once again, let's show the text for this function. And you see, it's a little bit shorter and easier to read than what you see up above. And in fact, with IFS, there's really no longer a need to use nested if functions anymore. Instead, you could simply use IFS, and I think you'll find that it's a lot easier. Moving on to the next worksheet, we're going to look at lookup functions, and this allows you to look things up. So here, for example, I have a table with cookie types, the associated price, and the associated cost. And let's say I have the question, well, what is the price of an oatmeal raisin cookie? So to look that up, I would say, well, here's oatmeal raisin and oh, here's the price. So I just looked that up. But we can have Excel do that for us using the function VLOOKUP. Here, once again, let's click into the function helper, and I'll type in VLOOKUP. And here it is, let's click into that one. Here, first it asks me, what is the lookup value? So what do I want to look up? Well, I want to find the price for oatmeal raisin. I'll select that. Then it asks me, well, what is the table where you want to find this? Well, this is my table, so let me select the table. And then it says, well, what is the column index number? What is a column index number? Well, that's what column do I want to get back when it finds a match? So here it looks in this table, it'll look at the first column, And when it finds a match here, I want to get the price back. And the price is the second column of this table. So I'll enter a 2 here. Now for range lookup, I could have an exact match or a partial match. Now I want just an exact match, so I'll type in false. But once again, this is optional, so you don't have to fill this in. It's true if it's omitted. I'll click on OK, and here you see the price of oatmeal raisin is 8. Now here I could type in sugar cookie, and here you'll see that the price is 5. So here it automatically does the lookup for me. Now, right down below, there's another function called XLOOKUP, and this replaces VLOOKUP. It's a newer function that's available in the newer versions of Excel, and it has some key advantages over VLOOKUP. But first, let's do the same example and see if we get the same results. Once again, let's open up the function helper, and here I'll type in XLOOKUP. Let's see if it shows up, and there it is. Here it asks me what is the lookup value, and that's what I want to search for, and I want to search for sugar cookie. And then it says, well, what is the lookup array? And so this is a little different from VLOOKUP. I want to look in column A for sugar cookie. Here, I'll select column A, but I could also select these values, but I want to select the column. And then it wants to know, well, what do you want to return? And I want to return the price. Here I have some optional parameters, like, well, what if it's not found? Do I want to give an error message back? I have match mode. Here, if I scroll down, you'll see a few other options. So we have a few more options compared to VLOOKUP. I'll click on OK. And here we see I get the exact same thing back, and it works the same way. Here, I could type in chocolate chip, and you notice that I get the price of 10 back. So why would I ever use XLOOKUP compared to VLOOKUP? Well, let's look at some of the advantages. What if I wanted to get the price back and I wanted to get the cost back? Once again, let's click into the function helper. And for the return array, right now I have it set to just give me the price back. But here I can highlight these two columns. And here I get two values back. I get both the price and the cost. So I can get an array of values back. Also, let's say that the cookie type column was the third column and price and cost came first. VLOOKUP wouldn't work anymore. When you use VLOOKUP, the column or the value that you're looking for has to be the leftmost column, and then the values that you return have to be to the right of it. But with XLOOKUP, you can return any column, even if it's over to the left. Next, I want to show you some date and time functions. And these are really simple. They're actually functions that you don't even pass any arguments or parameters into. So let's say you want to get today's date back. You simply type in equals today, open and close parentheses. And the reason why is you're not passing any values in. When you hit enter, you get today's date back. And down below, you could also type in now. It works the same way as today, except instead of just getting today's date back, you also get the current time back. Here we are on the very last worksheet. You've made it just about to the end, and so I wanted to give some bonus tips of how you can work with formulas and functions. On this sheet, I have a number of different numbers and some of them have formulas and some of them are just a number on its own. Here you'll see it's a mix of both. So how do you quickly identify all the formulas and all the numbers? Well, you can press the shortcut key control together with the tilde character and that'll automatically show all of the different formulas and functions on your sheet. Here you'll see that I have a few different formulas and here I reference a few different cells and I can press control tilde again and I can toggle back and forth between those views. So that's a nice way to just view all of your formulas. Now let's say you just want to see all of your formulas and maybe you want to highlight them. Let's click on the home tab up on top, and over here, let's click on editing. And then here we could click on find and select. Here you'll see the option for find formulas. I could click that and that'll highlight all the formulas on my sheet. And now here I could apply some color if I just want all of my formulas to stand out. And once again, if I press control tilde, once again, you can confirm that these are all of the formulas on the sheet. Now one more thing I want to show you. When I click into this cell, you'll notice that it references two other cells. And when I click into this cell, this also references other cells. So let's say that you're troubleshooting a function and you want to find all the precedents or you want to find all of the dependents. You could very easily do that. Here with this cell selected, I could click on formulas up above and here I could trace all of the precedents. So this cell relies on these two other cells. But here, if I go back to formula, here I could trace the precedents again. And it looks like this cell relies on these two other cells to get this value. So this is going to help you as you're auditing all of your different formulas. Here, if I click down into this cell, once again, I could click on formulas and here I could trace all of the dependents and this is the dependents. If I want to undo all of this, here I can click on remove arrows, but a nice little bonus trick to help you audit all of your formulas. All right, well, hopefully now you are a pro at formulas and functions in Excel. To watch more videos like this one, please consider subscribing, and I'll see you in the next video.
