Transcripts
1. Intro to The Excel Formulas and Functions Course: the way I look into the Excel 2010 functions and formulas. Course I'm so glad you're here, and I'm so glad to teach you all about functions and formulas. If you are at all intimidated by excel, I just want to tell you that you are not alone. Excel is an extremely powerful but involved and complicated software, and even most experts with Excel don't know how to use every formula function and feature that there is. The important thing is that you just start where you are and learn as much as you can so that you can start to harness the amazing, calculating and sorting power of Excel. Now let me share a couple of ways that you can get the most out of this course. First off, if you are a brand new Excel user, I highly recommend taking my course Excel 2010 Basics for beginners so you'll understand how to navigate through cells, how to format, sells out of select cells and all of the basics to just clicking and moving and using an Excel worksheet. If you've already taken that course or you already have some experience with Excel, then you can dive right into this course, you'll find a lot of value in the lectures that you're watching. We're going to start out with a blank spreadsheet like this, and we're just going to go in and add some simple formulas like the some formula in this example. I'm gonna show you how to do that. And I'm gonna show you the complete breakdown of what all the pieces and parts of the formula are what that anatomy looks like and what the parts are used for. And then we're gonna build that up into a more complex spreadsheet that does some powerful calculations for a group of sales Reps. Joe Carroll, James, Bob and Marcy and allows us to very quickly and easily create calculations for their monthly commissions, whether or not they've met there. Monthly targets adding in bonuses if they have met there, monthly targets using if statements doing calculations on how much money is coming into each region, east or West, and calculations on how many employees there are per region. We're also going to transform the state into a table and use a V look up function so that we can plug in any sales rep number and retrieve their name and their total monthly pay. And finally, we're gonna learn how to use the left mid and write functions to sort a representatives I D code to make the data easier to see and easier to use. A couple of things I want to show you to make your viewing and learning experience a little easier is that when you're in the U to me dashboard watching these videos, you'll notice a couple of things if you hover your cursor over the video. One is that you can quickly rewind 15 seconds or fast forward 15 seconds in the video if you missed something or if you're in a boring part of the lecture. I don't include a lot of boring parts, but everyone so well there will be something that you already know and you'll want to skip past it. Another one that's very important is the speed control. You'll notice here that you can slow down the video and go half speed. You can go at regular speed, or you can increase the speed by 1.25 times 1.5 times or two times the speed. So if you find that I'm speaking slower than you need, then you can easily speed that up, and you can use your time better to get through the course and learn all the material without having to wait around for me. And then you can also expand the screen out to full screen if you need to. If you're not able to see what I'm showing, I do zoom in a lot when there's details, so you shouldn't be having to do that. But if you need Teoh, that option is there for you. And finally the course is set up so that you can go directly to the function that you want to learn within the course. I do recommend going through the 1st 2 sections, particularly the lecture on Absolute versus relative cell references, because that will help you down the road. But once you get to the seven most important Excel functions, you can pick and choose your favorites or go by the ones that are most important to you and most valuable to you at the time. You don't have to go through those chronologically, so I'm really excited that you're here. Thank you so much for being a part of this course, and let's get right into learning some itself
2. What is a Formula?: okay, And this lecture, we're going to be talking about what is a formula and what can it do for you? And this is an important question, because the formula is the basis for almost all of the calculations that you'll do using spreadsheets and excel. So what is a formula? There are a lot of ways to define a formula, but basically it is an expression and that can be mathematical or using language to calculate the value of a cell. And I'll give you the most basic example. If I have five in the cell and five in this cell in the third cell, I can create an equation or a formula that will calculate a new value for the third cell. And we always start a formula with the equal sign, and in this case we're going to add the 1st 2 cells. So we're going to take the value of a one plus the value of a to and hit enter and you can see the formula is here equals a one plus a two, and it's taking the five plus the five, adding them together and giving us 10 and that's a formula. So you can see it. In this case, it's just a simple math equation, and we could easily change this formula by turning this to a division sign and you'll see that now. The answer is 15 divided by five equals warm, or we can change it to a minus sign. And the answer is now zero. So five minus 50 And finally, we can change it to a multiplication sign, which happens to be an Asterix five times five equals 25. So therefore really simple but powerful mathematical calculations that you can do simply by entering that formula into your Excel spreadsheet. Now this entire course is going to be devoted toe how to use formulas like this and then take them to the next step using functions which will talk about Maurin in just a minute and using those formulas and functions to create powerful calculations using the data in your spreadsheets
3. What is a Function?: Okay, so you've learned what a formula isn't excel. Now let's talk about what a function is. A function is basically just a formula that Excel has pre defined and stored in its library for you, for ease of use. And you can see I'm actually clicked on the formula tab up here. And this shows you the entire function library and Excel has a ton of functions. Most Excel users don't even know what all the functions are or how to use them, including some very experienced Excel users. So don't feel bad or overwhelmed by this. The important thing is that you just start to dig into this and I'm going to introduce you to the most important ones so that you can just start Teoh, use them in your spreadsheets to start making calculations and basically saving yourself a lot of time and a lot of effort. But if you look here, you can click on financial and you can see the dozens that are listed under financial and again, don't worry, if these don't make any sense to you, that's okay. Logical will talk about if statements which are very important and very useful text you can compare and sort texts. Which is nice because Excel works with text, not Onley with numbers. Same applies to dates and times, hours, minutes, days You can sort data by those values. Look up just allows you to search for various things in various ways, and then the most obvious is math and trigonometry. And you can see. For instance, if we scroll down here, you'll see the most obvious one is some which is just adding, like we've done in our formula before or for instance, product means multiply just like we did in our formula before. But this would be doing it with a function instead of a formula. And then there's one more tab here which actually gets you in a whole bunch of additional ones. But let's go back to the beginning here and see exactly what a function is, what it looks like and what it does. So in our previous example, we had taken originally five plus five equals 10 and then we changed it to minus and divide , and finally we changed two times using the Asterix. So now we have five times five equals 25. Let's see what how we conduce these same calculations using a function. So what we would do? I'm gonna click on this cell, and I'm just gonna hit the delete button or the backspace button, and that's gonna delete our formula. There are a couple of ways that you can access functions you can go to insert function. You can click here to go to all. Or in this case, we're going to use a math once we can click on the math and then type in S um and that's gonna bring us to some. Or we could just type a description of what we want in here. For instance, if we type addition and that go, it's giving us recommended functions and you'll see one of them. Here is some, which is what we're looking for. So then you would click on that, and it'll automatically insert that function into your cell. And in this case, it's pre populating what it thinks you want to do because it's smart enough to know that you probably want to add up the two numbers above the cell that you're working with. So it pre populates it with a one a two, and in this case, it's correct, so we could just click. OK? And so you can see as opposed to our old formula, which which looked like this equals a one times a two. Now we just have equals The sum and how this is right is the sum of sells a one through a two, and I'll explain to you what the parentheses and the colon are for as we dio. So don't worry too much about that, because right now I'm just showing you examples of what a function is. So let's get rid of that one and let's do a product and I'll show you another way to find it. I could go to the same place here and go to product, or I know that multiplication is probably under math and trigonometry, so I go to math and trigonometry, scroll down to the peas, quick product. Then you click OK, and there it ISS. So instead of saying a one times a two now, it says equals product of a one through a to So the main purpose of functions is just because they are pre formatted there, much quicker to implement, because you don't have to type out the entire formula. And as you've seen, it even does some fancy things, like anticipating what sells you're gonna want to use in your function or in your formula. As you start creating more complex formulas. Using the pre formatted functions allows you to do more and more quickly, and it also allows you to get help from Excel as you're going. So, for instance, again, when we go into this product, it gives you a lot of information. Here you can click right into the help if you needed to, but it tells you what the function does, and it even tells you the results of your function or your formula before you even put it in. And there are essentially three ways to access a function again. We can go to this and just go to the Function library and click on any of these functions. Or we can go to the insert function, which is gonna allow you to just search for one or scroll for one. Or you could just type the equal sign and begin typing the name of the function if you already know it. So if I'm doing the sum of something, you start typing s you and it brings up everything that starts with s you and then you can just click on the some and it pre populates your first bracket so they could start typing in the cells that you want to reference. So in this case, we're doing a one through a two, then close it with the brackets, and there it is. So that is what a function is and what it does next. We're gonna look at all the little pieces and parts of the function, including the equal sign, the name of the function, what those parentheses air for, what the Conus for and other little pieces and parts that tell you exactly what the function or formula is going to do and allows you to organize your information so that the function will do what you want. Todo
4. Anatomy of a Formula: okay, This lecture, we're going to look at the anatomy of a formula. It's really important to understand this anatomy because number one if you know what all the pieces and parts of a formula are, then you can not only write them, but you can also modify them to make them do what you want them to, dio. And you can check them for errors and understand why a particular formula might not be working. So they're six parts to a formula, and we're going to go through all of those in this lecture, the first part we've already discussed. That's the equal sign. The equal sign is how you start any formula, and it basically just tells excel that you are going to be creating a formula. And you're not just entering raw data into a cell. And I'll enter these into a spreadsheet as we dio so that you can see what it's gonna look like. So the first thing is the equal sign. The second thing is, functions functions we've also covered, and an example would be s, um or some The third part of a formula is parentheses, and we've looked at parentheses a little bit before, but I didn't explain what they actually do. Parentheses help you to define your order of operations. And if you remember, way back to like seventh grade math, where you learned about the mathematical order of operations, that order of operations is exactly what Excel uses. So, for example, if you have two plus two times five, the first thing that you have to do mathematically is do the multiplication, then you add the two. However, if we put some parentheses around the addition portion of the equation than the parentheses , tell us that we have to do that part first and then everything else. The parentheses also help you when using functions to define the cells that you're referring to. And this brings me to our next part of a formula, which is your cell reference. And we've looked at cell reference before as well. Sell reference would be, for instance, be won through be five. Then we'll close that off with parentheses, and that completes that function. But there's still two other things that can go into a formula, and one is an operator, and we also looked at operators very early on when we first learned what a formula is, and operators are things like plus minus divide and multiply. And you probably remember learning about operators in math as well. A couple of other operators is you can use greater than less than or used them both together, which means does not equal or equals. So those are all operators in this case we're going to use plus, just to keep it simple. And the final one is Constance. A constant is a number or text within a formula that just does not change, as opposed to, for instance, a cell reference. If I'm referring to a number in Selby one, I could change that number at any time, and it will change the outcome of the formula. A constant, on the other hand, would be like the number two, and I can change any of the other cells. But that number is still going to stay the same, and that right there is the anatomy of a formula in excel. So again, just to summarize, we have six parts. We have the equal sign which tells Excel that we're starting a formula. We have a function which in this case, is the sum function. We have parentheses which define the order of operations or help to define the range that we're looking for. We have the cell range, which in this case is B one through Be five. And we could change that, by the way and put a comma there. And then we would just be referring to Cells B one and B five. But we'll go back to the B one through be five. Then we have an operator, which is the plus symbol in this case. But that could be plus minus divide, multiply greater than less than not equal to or equals. And then finally, we have a constant, which is just in this case, the simple number two. So let me show you quickly how this would work once we create it. So I'm gonna hit, enter and let's go toe these cells and add in some values. Okay, Now we can go back and see their according to our formula, which is the sum of B one through be five plus two. So five plus five plus five plus five plus five is 25 plus two equals 27 so you can see how the formula worked. And that's the anatomy of a formula
5. How to Select Cells Easily When Inserting a Formula: Okay, this lecture, We're going to be talking about how to select cells when you're inserting a formula. No, I already know that to select cells in general, you can just click on a cell and drag your cursor. The highlight that, or you can click shift and the arrow key down, or the arrow key to the right or the Iraqi up, or the arrow key to the left. But there are a few different ways to select cells when you're entering a formula. So let's take a look back to our some formula. And obviously you can see here We just have, ah, spreadsheet with some sales reps, names and their monthly commissions, and we're just trying to create a total here. So we're going to go back to our some function, gonna click equals and start typing s um, and then a bracket, and that gets us to our range. One way to do this is to just highlight and drag and highlight all of the cells that were trying to add close the brackets and hit enter. But let's try a different way. So hit equals again. S U M parentheses. You can also type it in manually. So you'd say be to through B six, highlighting that range of cells B two through B six with the blue outline closure brackets and it it entered, and you'll get the same total. Or remember, you can use the function library up here or the insert function button. So let's use the insert function button, and it brings up our most recently used functions so we can double click on some. And the nice thing about using the insert function button is it's going to make assumptions about what you want to highlight. And in this case, it's assuming that you want to add up all of the numbers above the cell that you're working with, which is a logical assumption. So it's already populated B two through B six. So if you're happy with that, you can just click. OK, but let's just pretend for a minute that has selected cells that we don't want. Let's say, for instance, that we only want to add up the sales from the ladies in the group. We could just go over here and click Carol and use control Jane and holding down the control button Marcy and you can see. It's just going to highlight those three cells and list them with a common in between them . So be three comma before common. Be six or again. If we wanted to go back to having the entire list selected, we can actually use the mouse left, click and drugs, and it will again go back to our B two through B six. And you can really do any combination of cells using this method so you can click just the first cell, hold the control button and click the last, so or we could click just the top three. Or you could even click this entire column, plus this entire column. If you had data over in this cold and you'll notice that includes two ranges sources be two through B six comma, and what that common does separates The range is essentially saying this range and this range. But again, we just wanted to be two through B six, so we're just gonna get rid of that click OK, and there's our total. So those are just a couple of ways to select cells when entering your range in your formula
6. Absolute vs Relative Cell References: okay, This lecture, we're gonna be talking about the importance of absolute versus relative cell references, and you might be wondering what this even means or how it's important to you. And that's a relevant question. And I'm going to show you an example of why it's important and how it can work really well to your advantage to help you to enter formulas way quicker and without heirs. So first, I'm just going to show you an example on a blank spreadsheet of how relative versus absolute cell references works. So I have a formula entered into this cell right here, which is some C one through C three and do you want. If I double click on it, you can see the cells that I'm referencing highlighted in blue and green boxes. Now you may or may not know this, but if you want to copy this cell, you can click off the cell, click back on the cell, hold your cursor over this little black box down in the right hand corner and your cursor turns to a skinny plus sign, and you can drag this plus side down as many Rosa's you want. Release it and it will copy that formula down All of those rose and the cool thing that it does is it makes those formulas relative to the row that the formula is on. So, for instance, if I click on the top one and double click it to show which sells its talking about, it's talking about C 12 and three and D one. Now, if I go to the next row and double click on the formula, you can see that it's dropped down one row to make it relevant to the cell arm. F two. So we have C two, C three, C four and D two, and so on and so forth. If we go down, you see that it's copying the formula, and it's modifying the formula to be relevant to the formula on this row. And this is a really smart thing that Excel does, and it works really well for most formulas. The problem comes in when, let's say, for instance, that I want to have the relative set of numbers down here. But let's say that I still always wanted to go back to D one in my cell reference. So no matter what row I'm on. I always wanted to be referring to D one rather than Defour or D five. How do I do that? And that's where absolute cell referencing comes in in this case, the way I've done this now, these air called relative cell references, so the cells that this formula is referring to are relative to the cell itself. So as I moved down, they change. Now if I were to change this and I'm gonna go ahead and delete all of these and go back to our original formula if I were to highlight the reference to sell D one and click the F four Key, you'll notice that a couple of little dollar sides appeared next to the D and next to the one now. What are those mean? It basically means the C one and C three section of this formula basically is not necessarily referring exactly two C one and C three, but it's referring to a range of cells 123 rows over and 123 cells down. And that's relative. So if I go copy this down to here, then it will be referring to 123 rows over and 123 rows down. However, the D part of this formula is referring exactly to this cell. It's referring to column de Rohan, and that's the only sell that is referring to and let me show you how this works. So I'll click off of this click back on it, and I'm going to copy that formula down again. And now, when I go to the 1st 1 click on you'll see it's referring to our original cells. When I click on the next one double click to highlight it. Notice, see how these shifted down? But this one stayed as D one, and if I continue down, you'll see the same pattern repeating. So the blue cells are also all relative to this cell. But the green cells, because they have these absolute anchors. Thes dollar signs are anchors its anchoring the text up there, and it's staying as D one. And again you might be asking, why does this matter? How is this gonna help me? And I'm glad you asked, because I have an example for you. So let's click over here to sheet one where I have a little spreadsheet set up and you probably recognize this part from the last lecture where we're just adding up these monthly commissions, and we have a simple some formula built in there. But now we're going to create a formula for each of these, similar to the formula that you just saw on the previous page, where we're adding in their holiday bonus of $500 to their monthly commissions. And I'm only going to create this formula once using a relative reference to this column and an absolute reference to this specific cell, and then I'll copy it down and you'll see that it will work in all the cells. So, of course, we start with E equals and S, um, parentheses. And then we'll just highlight that sell comma and that cell and then remember to hit F four that puts the dollar signs in their toe anchor the column D and row to exactly to this cell . Then we close the parentheses and hit enter. That gives us our total, and you can see that it's correct. 2000 plus $500 bonus makes $2500 and then we're gonna copy this down. All these let go and then Let's double check our math at the bottom column. We have $2500 plus the $500 equals 3000 and we can double click on this to see that it kept this be be calm relative. So in other words, it's It's not referring to back up here to be, too, but it's referring to the cell that's relative to this cell, which is one to the left. And then this one, using our dollar signs, is anchoring this reference to exactly that cell D to, and it works. So that's how you use absolute and relative cell references to easily create and copy formulas into multiple cells.
7. Average and Averagea: I thought, Okay, this lecture, We're going to be talking about the average and average A functions, and there's really only a slight difference between the two, and I'm gonna show you how that works in this lecture. So let's just go back to our old spreadsheet here with our sales reps, and you'll notice that I've added one more row below the totals, and that's the average. And, as you probably know for math, average is just the mean of all the numbers. So if we have five sales reps, we add up all plus divide them by five, and it gives us the average, so to do the average, it's very similar to doing the some. So we simply start with our equal sign and typing average parentheses, select ourselves and hit him, and that gives us the average dollar amount, which is $2180. Pretty simple. I'll show you one other way to do it, and you can also do the some formula. This way. You go to the auto, some up here, click on the drop down and just click average and you'll notice its auto filling. The cell references for us It's highlighting all the numbered cells above the cell that has our formula, But you'll also notice that it's highlighting the total line, which we do not want. So we'll have to click shift and up arrow to change it to highlighting Onley the cells that we want for the numbers of the sales reps. And then we can hit enter again and we'll get the same total. So that's the average for all of the monthly commissions for the sales reps using the average function. So what is the average? A function for the average? A function accounts for a situation where you may not have a numerical value in one of the cells, but you still want to count that sell as part of the equation. So let's say, for instance, that Bob was not working in the sales department for this month. He was on loan to the accounting department, and so in this cell, it says accounting. So now our average cell has changed a little bit because it is taking into account 123 four sales reps and averaging their monthly commissions. But if we want to take into account all five sales, reps and average their monthly commissions, which, of course, would put Bob's at zero. Then we need to use a different formula, and that's the average egg. So we're just gonna put average a here. But you could also title this something like average, including all sales reps or something to represent what we're actually trying to accomplish . Then, of course, we go back to our foregoes. We click equal and average, and you'll notice it pops up with average a there select average a highlight the cells that we want to include and then hit enter. You'll see that it's a lower number, and the reason for that again is because it is including Bob's numbers in the formula, and it's counting his monthly commissions at zero. And the reason that someone might want to do this is because they're still paying Bob from the sales department. So they want to know how many dollars air coming in for how much money they're paying out, using the average A equation, as opposed to using the average equation to find out the average monthly commissions of all active sales reps who are earning commissions that month. So that's how you use the average and the average A functions. To find the average of a list of members
8. Count and Counta: I thought, Okay, this lecture, we're gonna be talking about the count and count a functions and these air gonna work a lot like the average and average A functions. Except instead of taking an average of ah list of numbers, we're going to count how many are in the list. So going back to our handy dandy spreadsheet that we've been working with have taken out the averages to clean it up a little bit And let's put in here active reps and then down here, we're going to put total reps. So the active right for the active reps we want to count. 1234 In other words, the reps that are actually in the sales department this month and selling and for the total reps we want to count. 12345 In other words, all of the reps including Bob, who doesn't happen to be working being sales this month. And of course, it's simple in a small spreadsheet like this. But if you had hundreds of sales reps, you would definitely want to use the count formula to figure this out. So we go to our active reps sell quick, are equal sign taping count, double click slumped over cells and click. Enter, and it returns an answer of four. So we have 123 four active reps. The count function, the regular count function. It's going to ignore any cells that do not have a numerical value. And now we're going to go to the total reps where we're going to use our count. A function double click It highlight all the cells for all the sales reps hit. Enter, and it's gonna return a number of five because again, it's going to count all of the cells in the range, including the cells that don't have a numeric value. So it wouldn't matter if this was blank or if it had an accident. It's going to include all of the cells in the range. So that's how you use the count and the count. A formulas to quickly and easily add up a number of values in the list
9. IF Function: okay, in this lecture, we're going to talk about the if function. The if function is a very simple yet powerful function. It basically says, If this condition is true, return this value. If this condition is false, return this other value. So let's dive back over to our familiar spreadsheet, and we'll take a look at how to use the if function. And as you look at this, you'll notice I've cleaned it up again. I've taken away our averages and account totals, and I've added in a new column, and that is our monthly target. So that would be essentially the sales goal for our sales representatives. And the goal would be $2100 you'll notice a few people fall below that. Few people hit that. And if you're above it, so that's gonna be perfect for doing our if calculation. We're also going to go over here to this holiday bonus, and we're going to change this to a regular bonus. So now it's no longer a holiday bonus. It's just a regular bonus, but they only get the bonus if there's that magic word. If they meet the monthly target here of $2100. So our job with the if function is to determine if they have met the monthly target, and we're gonna put a simple value of yes or no in this column and then, based on that, will be able to determine what their total monthly pay is. So first we need to go into this and create our if function. So we're going to go to our formulas and the if function is located under logical. So we got a logical click on the If, and you'll see that it brings up our argument pop up screen. So we start out with the logical test. So we want to determine if this cell is greater than or equal to 2100 because that's our monthly target goal. If it is, then our test is true, and we want it to return the value of yes, if it isn't and it's less than $2100. Then we wanted to return the value of no and we'll click OK, and in this case it is less than $2100. So says no copier formula all the way down the road, and you'll see that the two sales reps who made $2000. It says no under their monthly target and the three reps that made equal to or greater than $2100. It says yes. So that's a simple answer to our question of whether they've met there. Monthly target. Now we can go determine their monthly pay based on whether they met the target and get the bonus or not. So we're gonna go over to here, gonna go back to our if function again. Of course, if you want to, you can go to the insert function and under logical it's right there. Or, of course, if we go to most recently used, it'll be right there as well. So in this test, we're trying to determine whether they met there monthly target and if so, they get their bonus. So our test is if this cell equals no, then we just want to enter their normal monthly commissions, which is that sell. If it doesn't equal no, then we want to add the monthly commission, plus the bonus so you can see our answers. If it's true that the sell says no, then it's only going to enter the normal commission of $2000. If they have met there, monthly commission, then it will come back with the additional $500 bonus. And remember to hit the F four button to put our anchors for this bonus cell so that it's always going to that as an absolute reference rather than moving down as a relative reference to these blank cells. So we click OK that returns are $2000 commission because they did not meet their monthly target. We'll copy this formula down to the other cells and they will look at our last cell here and you can see that they made their monthly commissions of $2500. They met there monthly target and we added in their bonus. And so they get $3000. And of course, you can look at the formula appear to make sure it's all correct, and we see that it is so. That's how you can use if functions to make calculations based on a simple, true or false argument in any given cell
10. SUMIF and SUMIFS: Okay, this lecture, we're going to be talking about some if and some ifs formulas, and you already know how to use the regular some formula. And the only difference between the some and the summit is that some if adds a range of cells. If a certain condition is met in another selected range of cells and the some ifs formula does the same thing, but you can use multiple conditions in multiple ranges of cells. So let's jump over to our sales reps spreadsheet. Take a look at what we've done to change this one so you can see I've added a region column here defining whether the sales rep is working in the East region or the West region, and then down in this little section below here, we're going to calculate the total monthly pay and the total monthly pay only for sales reps who have hit their monthly target based on the two different regions. So, for the total monthly pay here, we're going to use of some if formula and the if part is going to be whether they are in the East region or the West region, and then for the total monthly pay With the target being hit, we're going to use the some ifs formula with an ass. And the first criteria is going to be whether they are in the east or the West region. And the second criteria is going to be whether they hit their monthly target. So let's get started creating a sum if formula under the total monthly pay. So I'm gonna go to the formulas tab, go to insert function, and I was using some if recently so I can double click some if and it brings up our argument pop up. So our range is determining if they're in the east region or in the West region. So our range is going to be the region cells. I like those. Our criteria is going to be, in this case, the east region, and then are some range is going to be all the cells with the total monthly pay. Then we can click okay, and you'll see that this found everyone in the east region which is only two people and took 2000 plus 2800 and got 4800. It will copy that down, and then you can see that it took the same formula for the West region using the relative reference, and it went to these 123 people and came back with a total of $7600. Now let's use two conditions to create the total monthly pay for the people who hit their target. So again we'll go to insert function. Some ifs with a mess double quick, and you'll notice that some if is in a little different order than the sum. If so, first we're going to select the some range. Then we're going to select the first criteria range, which is east or west, and the criteria is east. And then we're selecting the second criteria range, which is whether they hit their monthly target or not. And then we're looking for the second criteria, and we're looking for people who have hit their monthly target. So, yes, we can click OK, and it will drop in our formula and so you can see that first, it refers to the east regions which there are two people in the east region. A minute goes to the second criteria, which is whether they hit their monthly target and you can see that Bob is the only person who it is monthly target, so it comes back with the total of 2800. Now we'll copy this formula down, and you could see that it does the same with the West. So it goes to the three people in the West region, and then it goes to the second great Iria, which is who hit their monthly target. And you can see that Jane hit her monthly target and Marcy hit her monthly target, so it comes back with a total of $5600. So that's how you use the some and some ifs formulas to add up a list of cells based on one or more criteria.
11. COUNTIF and COUNTIFS: okay, this lecture, we're going to be talking about the count if and count ifs functions and these functions air very similar to the way the sum if and some EFS functions work where you're essentially creating account formula and then adding an additional criteria using the If so, let's jump over to our worksheet. And you can see now that I've added some information here about employees. So we're still working with our regions. In the first column here, we're going to determine how many employees there are per region and again in such a small spreadsheet. This would be very easy to just figure out by looking. But if you had hundreds of sales reps, this would be a really valuable calculation, and we're going to use the count if formula for that. And then in the second column here we have employees per region who have hit their target. So we're going to use the count ifs function to calculate first by region and second by whether they hit their target. So let's dive right in, and we'll start creating our formula for the count. If I'm going to go to my insert function, going to go to statistical and go to count. If double click and to find out how many employees are in each region, we're gonna first identify our range, which of course, is what region there in, So highlight all of those. And then we will define our criteria. And in this case, we're looking for people in the east region. We'll click OK and you can see that this brings back a correct value of two because we do indeed have two people in the east region. So we'll copy that formula down. And it's also correct shows three people in the west region Okay, let's look at our count ifs and use multiple criteria to determine how many employees we have per region who have hit their target. So we go to insert function. We're already of statistical so I can go to count ifs and it's going to ask us for our first criteria range, which is gonna be our region, and the criteria is going to be east and then we'll go to our second criteria range, which is whether they have hit their monthly target and our criteria is yes, and we don't need 1/3 criteria range so we'll just click. OK? And you can see, based on a formula that there's one person in the east region who has hit their target and we'll copy our formula down. And it tells us that two people in the West region have also hit their target. And that is how you use the count if and count ifs formulas to count the number of entries in a given range that meet one or more criteria.
12. VLOOKUP: okay. And this lecture, we're going to be talking about V look up and the V look up function basically looks up a given value in a list, and specifically it looks for data or a value in a vertical list as opposed to a horizontal list, in which case you would use H Look up. But because of the way that we generally create lists using columns, the V look up function is generally a lot more valuable than the H. Look up. In fact, a lot of people tell me that V look up is their number one favorite function in Excel. It's useful for looking up part numbers, prices or inventory codes in any large list of numbers or coated info. And a great example would be if you have ah, 100 page catalog of some obscure part numbers auto parts, for example, and each one has an inventory code. You can put that in a vertical list, and you can use V look up to find the descriptions that prices or any other information that you have in your chart simply by finding that inventory coat. So before we dive right into learning how to use the B look up function. Let's talk about a couple of the basic rules that you need to keep in mind while you're setting up this function. Number one. The look of value must be in the furthest left hand column of the table that you're working with or it won't work, so to keep it simple, the easiest way to do this is to put all of your inventory codes or product members for this example in the A column. Then you won't have to worry about it. Number two Formatting and spacing in your look up cells must be exact and must be correct. For example, there can't be extra spaces blank spaces in front of the number or behind the number third in your table array reference. You want to use absolute cell values or even better, create a table with your entire look up array and name it. Then use the name as theory reference. And the reason for this is again that you can refer to the table as your search range. But a table is dynamic, so if you want to add or delete rose from the table later, you'll still be able to find those rows using V. Look up, and if this doesn't make sense to you right now, that's fine. I will go into this as we go. So now that we have some general rules it online, let's go take a look at our sheet. And I have, of course, made a few modifications to it again. First, you'll probably notice that I've removed all of the formatting from the cells that are going to become our table down the road. And the reason for this is if you create a table in an area that has a lot of formatting, it can get kind of messed up. So this is just to keep your table nice and clean. Secondly, you'll notice that I have added a column to the left here, which is the sales rep number. As you know, before we were working with Joe and Caroline, Jane and Bob. Well, these sales reps all have numbers, and that's how we're going to use the V. Look up to retrieve information about these different reps. Thirdly, you'll notice that I've added a number of reps in there, and the reason for that is just to give you a better idea of the effectiveness of the look up. But the assumption would be that you're in a giant organization and you have hundreds or maybe thousands of people that you're searching through and then finally have added this section down on Rose 12 and 13 where we're actually going to use RV, Look up function to pull up information using the sales reps I d. And we're gonna pull up their name and we're gonna pull up their total monthly pay using Be looking. Okay, The first thing we need to do before we even start entering in our function or formula is we need to create a table. So the way to create a table is you first, click somewhere in the middle of the data that you want to use, and we're going to be using all of this data from a two down to F 10. So you click in the table, click, insert and click table, and it's going to identify the range of your table. And you can see in this case it's including this bonus side, which I don't want to do. So I'm going to click shift and left Arrow key to reduce it down to exactly where I wanted to be, and then you'll notice. Also, this box says my table has headers and it has this checked while my table does have headers and you don't want those to be included in the list, you're gonna click OK, and there's your table, and this table by default has a very blue theme, which is fine by me. But you can change it something more neutral if you like. And there you go. Now we're not going to get into tables too much because that's for an entirely different course, because there's a ton that you could do with tables. But you will notice the The only thing you see differently is that these little arrows have popped up up here. And the cool thing about tables is that we can go to these and we can very quickly and simply sort these. Now. If you go back and look at this list, you can see these numbers are all random. But if I quickly click smallest to largest here, you'll see that suddenly the entire table is sorted. According to those numbers, all of the sales rep names have stayed with these numbers and you can see that the people have hit their monthly targets are more clear now, and the total monthly pay is more clear now. But that's just a example of how you can use tables. But now I'm going to go back to sorting this from smallest to largest, So we have our sales rep numbers in line. There's one more thing that you need to do, and that is to click inside your table once again and you'll notice a new tab comes up here table tools and it says, Design. If you click on that, go away over here, you have an option to name your table and we're gonna name this. Sales are for sales reps. So this is our sales are table and then click enter and that's the name of your table. And as I was mentioning earlier in the rules, we're gonna use the name of our table when we're entering RV. Look up function. So let's go ahead and do it. So this left column here is where we're going to actually enter an add value. For instance, sales rep number one, and when we had enter, it's going to populate these two excels with the relevant information about that rep. So let's go ahead and create our formulas so that it will do that. So first we click on this cell, which is where we want V Look up to place our final information once it pulls it out of our table. And, of course, we're going to go to our formulas tab, go to insert function and if it's not right here, which mine was recently used, So it's right here at the top. You'll go to look up and reference, or you could just type in V look up up here and search for it. But anyway, minds right at the top here, so we'll just click. OK, and they'll notice we have four important arguments that we need to get correct. So the 1st 1 is the look up value. So the look of value is where it's going to be looking for your input, which is this cell right here. The table array is the table that we're using, and you'll see if I tape in. Sales are that it's now referring to our table, and the next one is the column index number and this could be a little bit confusing to some people. But what this basically is is you're counting the columns from left to right within your table and you're entering the number of the column where the information is that you're looking for. So if we're looking at our table, this would be calm. One, 2345 and six. So we want to get the sales rep name back in this cell. So we're going to be looking not in column one, but in column two So enter column, too, in the range. Look up. You have the choice between true or false if you click, True than what it will do is basically try and find the next closest thing to a match. And there are very few cases where you're gonna want to do that. Generally speaking, you're gonna wanna put false because you don't want it to guess or get a close match. You want an exact match. So by typing fall soon here, you're asking it to give you an exact match on the data that it's looking for. So then we'll click OK, and then you'll see that it says an A in the answer sell here, which is okay, because we don't have any data input here. But if we go in here and we enter the number one sales rep the number one, it's gonna come back with Joe. And if we change that to sales rep number eight, it's gonna come back with Robert, so we know that we've done it correctly. Now we're going to create our look up for the next column, and again we go to insert function the look up, look up value from the same spot here. Our table array is sales are because that's our table column index. Now we're looking for total monthly pay. So we're actually looking for column E over here, which is going to be 12345 calling five. So entering five and there are a range look up again is false so that we just get an exact match. If you leave it blank, it will assume true. And you can get some weird answers back. So definitely click false. We click OK, and you can see sales rep number eight, his Robert $2600. And we'll try a couple more just to make sure we have the formula. Correct. There's Marcy sales rep. Number 5 $3000 Cells up. Number seven is Casey. Total monthly pay $1900 And it's not super impressive when you look at it on a small scale like this. But imagine you have a catalog with hundreds of pages and literally thousands of product members or employees, or any huge compilation of data where you need to bring back names, birthdays, commission dollars, sales regions, part numbers, part descriptions or other data based on one i D. Number or inventory code. This V look up function is going to be amazing. So that's how you use one of the most popular functions in excel that you look up from.
13. LEFT, RIGHT MID: e Okay, this is just going to be a short lecture on how to use the left right and mid functions to sort text from fixed with strings. So let's just dive right into our worksheet. And again, you could see I've changed it a bunch here, and it's just so I can clearly demonstrate what's going on with the sorting using left, right and mid. So as you can see, what we have here is a representative I d. Code in the first column with the sales reps name. And then we have three blank columns that need to be filled out with the reps. I d. Number their region and their sales group. And we're gonna pull that information out of the rep I decode in the first column, and you can see we have four characters in each rep I d code. And here's the definition of what those mean. So the first number is the reps I D. Number, which we're going to put into column See, the second number is the reps initial, which we're going to just ignore because we already have the sales reps name in there. The third number is their sales region Easter West So e for East W for West and the fourth number is their sales group. So we have a B or C. So to sort these, we're going to create left, right and mid functions to pull the numbers out and place them in our other columns. So first, we're gonna find our rep i d numbers go to insert function under text, go to left and then it's gonna ask what texts were looking for. We'll click on the rep i d code. And then the number of characters that we're looking for and the rep I d number is the very first number. So we're just gonna click one number and click, OK, and then we'll put our skinny cross on the corner, double click it and it will copy the formula down. And we have all of our rep i d numbers. Then we'll go to the region, go to insert function under text, gonna go to mid, and our text is the same rap I decode. And you'll know sis has one additional argument. So we go with our start number and our region is the third character over. So we go with number three. And then again, the number of characters. We just need one character click, OK, and then copy that down. You'll see it populates east, west, west, east, west and then finally will go to our sales group. We're gonna insert the right function so we go to text again, scrolled out to right, double click and same text, and the number of characters is one, and this is gonna pull the far right character from each of those codes. Click. OK, double click it down and there it's highlighted our sales group members. So that's how you sort of fixed with string of data, much like with the V look up function. Using the left right and mid functions to sort items is best with a huge catalogue full of part members. For example, let's say you have a partner that looks like this, and it's for a piece of exercise equipment. The A B stands for AB Master. That's the name of the exercise equipment. The 0119 is the actual part number, and the HC is the city and state where that particular item is manufactured. So if you needed to sort all that data out into separate columns. You can use the left right and mid functions to extract the information and sort it to make it more accessible, more useful or easier to read. So that's I use left right, admit functions to sort out.
14. UPPER, LOWER, and PROPER: OK, in this video, we're gonna learn about how to use the upper lower and proper functions and excel. And the purpose of these is to change how you're capitalizing text. So let me show you how to use them in a really simple example. And then I'll kind of give you an idea of how you would use them in real life and why they're valuable. So if we go to our Excel spreadsheet here, you can see I just typed in the sentence. You are learning excel, and you can see that the first letter is capitalized and the first letter of the word Excel is capitalized. Now let's say that we want to make this all uppercase while we can use the upper function by simply typing on our equals and start typing upper. You'll see that it's highlighted here, so I could just hit Tab to select that. And then I just go select the cell that I want to convert toe uppercase. Select there, and you can either manually close out your parentheses by adding parentheses, or you can just hit enter, and you'll notice that this is now entirely in capital letters. Okay, let's try lower. So we had equals and we start typing lower. There it is with Tab. We're gonna select the same one and again we hit Enter and you could see now they're all in lower case. And then finally, if we want to do proper case we had equals and start typing per upper when it selected Hit tab. Grab that hit Enter. And now it is capitalize the first letter of each word. Okay, so that's how you do that. You could play without a little bit. Get used to changing your case in your letters that the question is, what is this good for? And probably the main thing that this is valuable for is when you're importing data from somewhere else. Maybe it's from a C S V file or another spreadsheet if it comes in in a certain way. But you want to look a different way, you can use these to kind of quickly convert it. Okay, so I'm just gonna pastes, um, data it right in here that I have copied and we'll just basically pretend that I've imported this from, like, a CSP file. Said control V and there it iss so These are just a list of names and, you know, again, imagine this the huge long list. If you wanted Teoh convert these all toe upper case or proper case, it could take you forever to do it manually. So you just go over here and let's say we just want all these in caps thing you just do. Your formula equals upper. Select the cell. You want a reference it enter and it's converted it to upper case. And then we just highlight that. And we use our little black arrow on the corner here and we just grab it and copy it down. And those air all converted. And so let's look real quick if we did this as proper. Equals P r O P. Tab. Select your cell that you're referencing it enter. And now it's capitalizing the first letter of each name, and we select that and copy it down. I'm just gonna copyright over these and you see that it converted it. The only thing you have to be careful of there is that it's on Lee capitalizing the first letter of each word you'll notice. Like with my name's Steve MacDonald. The D is supposed to be capitalized, so you would need to go in and manually fix that. But that's pretty much the exception to the rule. And this is still going to save you a Thanh of time when you need to convert data that you've pulled in from another source. Okay, so there's your quick tip on how to use the upper lower and proper functions and excel.
15. DATE and DATEVALUE functions FINAL: in this video, we're gonna look at the date function, and the way you put in the date function is equals on D A t E. And there's two options here. The date returns a number that represents the date in Microsoft's date time code, and we'll talk more about that in a little bit, and the other one is date value. And this is where you take a text string and you turn it into the number that represents the date in Microsoft State time code. So we'll take a look at both of those and talk about the date time code. But first, let's look at date. So let me hit the tab here, and the date function asks for a year, month and day, and if I put a couple comments in here, you can see it highlight each of those things so had escaped to get out of that. Now, if you just want to type in the date that you just type in the date and you don't need to use the date function for that. But if you have multiple options that a date could possibly be, then the date function becomes useful or If you've imported data from someone else and it's all represented as just a bunch of numbers, then you could use the date function to sort that or make that more presentable. So let me go ahead and bring up the worksheet that is available to download with this lecture, and you can do that and follow along. And what we have in this worksheet is the years, the month with the month name and the month number and the days of the month. Okay, so let's start putting in our date function. We start with equals D E a T E hit tab and will just select a year from our list will choose a month number, and then we'll select a day, do close parentheses and had enter. Now if, instead of using the number for the month, were to click and drag it over to the month name and enter that formula, you'll see that Excel doesn't like it. You'll see it returns a value error. If you click it and hover over that button there, it'll tell you the value entered is the wrong data type. So again we can go back and pick another number and put that in our formula and you'll see that it works. I need to see if we copy this formula and paste it up a couple of cells. It will change because we have relative cell references. So if we head off to, you'll see it's referring to different cells now. So if we wanted to get the date formats for all of these, we could just copy this formula down the column, and it would represent all those numbers as dates. Now, once you have the date displayed, you can change the format on it. You just hit control one. To bring up the format box. You got a date. You have all kinds of options for how to make your date appear, depending on what you're using it for, so we can select a different one of these and hit OK, and there we've changed the formatting. One thing you want to be careful of is, if you copy the formula down, paste it down here, you'll notice the years off because our formula isn't referencing a year. We could copy and paste this down, and then we have a year in there and I could double click here to widen up the calm so that we can see our values. Okay, so that's the date function. Let's look at the date value function, so we start with equals and date value and select it on a tab. And let's select this particular date close parentheses and hit Enter. You can see that our function came back with an air. It's saying that the value have used in the formula is the wrong data type. So if we re enter our formula to take a look at that again, notice that it says it's looking for the date text. Well, this is a number, but it wants the text. So in order to do that, let me just finish out this formula and we'll get that value air again. And let's just turn this into a text string. So I'm going to copy it control, see, and then we'll do paste as values and see now it's not a formula anymore. It's actually just the numbers of the date. If you hit the home button to go to the beginning of the text string and hit on apostrophe , that's another way to format things as a text string. And if we hit, enter now, our date value function works. This number here is the Microsoft Excel daytime coat that we were talking about earlier. So the number of 43,419 represents the date november 15th 2018 in Microsoft's time value code. If we change the year to 2017 you'll see the number changes. It gets smaller. So to demonstrate how this works, we could take a date and just add a one to it. So we say equals select this state Timecode, Adwan and hit Enter and it adds a number to it, and we'll copy that down a few times. You'll see. It's just added one day to each of these numbers because each one represents a day. And now, if we go back and we select these date time values and reformat them as a date, you'll see that they're all one day apart. So first we choose date for the category, and we'll just pick this format and click OK, so now you can see each date is one day later than the next, exactly like it was with our date time formula. So let's undo that for a second when you have a bunch of members like this, these daytime formulas work for the math behind the dates, and the reason that's useful is that you can use it in your Excel formulas to calculate future dates, historical dates, etcetera and one just interesting side note is that if you convert January 1st 1900 to the date value, you'll find that it's number one. So the serial numbers that Excel keeps track of for the use of the date value starts on January 1st 1900. Then, if you look back at our example, November 15th 2017 is number 43,054 because it's 43,054 days after January 1st 1900. So that's just to help you understand what those numbers mean exactly. They just represent a date in time. So that's the date function and the date value function and how to use them in Excel
16. Hyperlink Function FINAL: okay. And this lecture, I want to show you how to use the hyperlink function. First, I'm gonna show you how to insert a hyperlink into a cell, and then I'm gonna show you a cool trick for using the hyperlink function to create a table of contents for all of the sheets in your workbook. So to enter a hyperlink in a cell, you just click into the cell and start typing your link. So if I'm doing google dot com, I type that in, and when I had enter, you can see it recognizes that as Google, because it's recognizing this has a Web address. The other way to do it is to go to the insert tab, go all the way over here. Toe links. Click the drop down click Insert link. You'll see it brings up this dialog box. The two important things are the text to display. So we want to just say google all right, a little cleaner looking. And then we put in the web address www dot already popped it up. Auto populated it. We click. OK, and now you can see we have the link, but it has some friendly texts in it. So those are two ways to put a hyperlink into cells in your spreadsheet. Now, let me show you a really cool way to create a table of contents that will allow you to navigate from sheet to sheet within a workbook. The hyperlink function in Excel is just not as intuitive as the other functions. And let me show you what I mean by that. So we start typing it in like any other function equals hyper it tab to go to the function . And it asked for link location and then friendly name. So a great use of the hyperlink function is to create a list of all the sheets names in a workbook, and you can create a table of contents so you can click the link, and it will take you to the sheet that you're looking for instead of having to navigate by scrolling through all say, 20 or 30 sheets to find the sheet that you want. So to show you how that works, let me escape out of this and insert a new sheet. Ault H I s is the keyboard shortcut for inserting a new worksheet. You see, we now have sheet one and sheet to, and we'll go back to sheet one here in column C will hit equals hyper link tab to open the function it asks for link location say sheet to H nine is where I want it to go than comma . Friendly name quote she to, and the friendly name doesn't have to match. Exactly. You could make it a little more reader friendly, so we put a space in there, close that hit, enter and now and she want. I have a link. See, it's formatted as a hyper link and clicking. It should take me to sell H nine on sheet, too, except it doesn't work. So the way to make this work is to do some extra code inside the formula. And once you add this extra code, this table of contents is very useful. So let me show you how to do it. So first, let's create a listing of the sheet names, so we say sheet, too, She three. We don't currently have a sheet three or sheet for, but we'll make those later so that's OK. So let's start typing out. The high polling function again equals hyperlink and there it is. In the list will hit tab. And instead of just going straight to the link location, I'm going to type a quote sign than a pound sign than an apostrophe on end quote. And I'm gonna get the ampersand sign that I'm gonna click on this cell here before she to then another ampersand, another quote, an apostrophe and an exclamation point. I'm gonna say I want to go to a cell h nine. That's what I want a reference. And then close the quote. And for a friendly name, let's just say sheet to because that's what I want to name it and then close parentheses and had enter. So now I've got this same formula, and it looks just like it used to. But now when I click it and that takes me to sell H nine inch eat, too, and it works exactly how we wanted to. So going back to sheet one. Let's copy this formula using control, See then shifting down arrow. A couple of times use control V to paste it had escape. And if I have to, here you can see it's exactly the same formula that we were using before but now were rough seeing sheet. Three. So let's escape again. Now we don't have a sheet. Three. So what happens if I click here? We got an air references invalid because I don't have a sheet. Three. So let's insert a sheet. Three. Instead of using the keyboard shortcuts, let's do it this way. Go insert and sheet and now I have a sheet. Three. You'll notice they're out of order, but that doesn't matter. We go back to sheet one and click our link again for sheet three, and now we're on sheet three. Cell H nine Correct sheet. Juan we could click sheet to. It works, so if you have 30 or 40 sheets, it gets difficult trying to scroll all the way back to the first sheet. So one way you could do that is to organize this right click on sheet one and move or copy and have this before sheet, too. If you do that, no matter how many sheets you have afterwards, you could insert a bunch of them. Just hit this, plus a lot of times to get a lot of sheets. That way. Whatever sheet you're in, you can always just have your table of contents in the very first sheet that you work with . If you hit control down and tapped the arrow button, that brings you to where you can click sheet one instantly. So let me show you another modification that you can do to the hyperlink formula. First, let's navigate to sheet four. I'm just gonna click a cell here and start the function equals hyperlink. Hit Tab Link location. Again, It's quote the pound sign. Now I'm gonna do the apostrophe and write the name of the sheet where all my links are. She want the apostrophe? The exclamation point, Let's say Sell a one close quote and for the friendly name I wanna have quote, table of contents and quote parenthesis. E hit. Enter it says table of contents. Let's click on it. Thank you. See, now we have a link that takes you right back to sell a one on sheet one. Now let's go to sheet for and navigate up to this formula here. We're going to cop it with control, see, and then use control page down to move to the next sheet. I'll go Teoh, so each one click control shift and page down a bunch of times and notice below. I'm selecting all of the sheets right now, and I'm gonna paste this link onto all of those sheets in one shot. So we hit control the We'll go back to sheet one and check it sheet for sheet to she. Three. She five, she 67 And I could just click with the mouse to instead of using the shortcut, you'll see the hyperlink for table of contents is on all of them, and when we test it, we can see that it works. So that's just one really cool trick for using the hyperlink to create a table of contents of all the sheets that you have in a workbook. So that's I use the hyperlink function and excel.
17. IFERROR FINAL: OK in this video, we're gonna talk about the if error function. The fair is a very useful function, but not by itself. And the function works pretty much how it sounds. If there's an error in your formula, then it will return a different value. So let's get right into it will start by typing equals and if and you see it's the 2nd 1 down, so it returns a value if error, and that's where you select the value that you want to return. If the function or formula that you put in returns an error and if it's not an error than the function, will just go ahead as normal. So we'll hit tab and take a look at the parts. The value will be any sort of function or mathematical equation that you want to use in your formula that will return some sort of an answer. So it could be a huge, long formula with nested if statements and all kinds of things, and then if that function returns an error instead of saying n a or divide by zero or some error message, you can have it returned something that you would prefer so let me demonstrate by having a comma and then two pairs of quotes next to each other and that will enter a blanket. What you do with those is you have a set of formulas doing an analysis for your calculation . You have an error returned. For example, if you're bringing in data that has numbers and text values in the same column and you're doing a formula on the whole column than the formulas that airing out we'll just show up is blank instead of with an error message. So let me escape out of here and show you what I mean. So just put it a list of members here and then in the second cell here, I'm gonna put a text value. So just do a simple mathematical calculation on this. I will just say this cell plus five. So you see 23 plus 5 28 will copy this down to all four of these and paste are formula in here. You see, the cell where I had the text is coming back with a value air because this is not a number . So to fix that and make it look pretty, here's what we would do well, go appear hit F two to open the function up hit the home key to go to the left side of the equation That right era start writing if air down arrow and tab to select it at the end key and this value is going to still be our calculation than comma And then our value If air is quote, quote and in excel language that means a blank so it won't put anything in that cell. So this is gonna make things look so much better. In our finished spreadsheet, we hit the close parentheses and enter. They will copy our formula from the top using control. See will use shift control down Arrow to select all of ourselves and then control V to paste the formula in there. And now we have a blank. You can see if we go into this. The formula is still there doing the calculation, but we've basically overridden the error message. And where this is particularly useful is when you're turning in a analysis or a report to 1/3 party, it's not professional toe. Have your analysis filled with a bunch of errors. So this is a way to just clean up your report or your analysis to make it more presentable for 1/3 party. Okay, now something else to be aware of if you're using an if air function like this is that it can slow down your system because the formulas being calculated twice by excel. So first it does this formula, and then, if there's a comma than it's gonna calculated again to check if there's the air. So if you have a lot of big formulas with if air functions in them, it's possible that it could take some time to do the calculations when you refresh it or are working in it. So in our example, instead of doing four math problems, Excel will be doing eight math problems. But that's the affair and one example of how to use it to clean up data that you're presenting to someone else.
18. INDEX MATCH FINAL: OK, in this video, we're going to talk about the index and match functions. An index and match are used similarly to be Look up and h look up. But you don't have to worry about the data being right next to each other. So in that sense, it makes it superior, more effective and easier to use. Okay, so we're gonna be using this table, which is included in the downloads for the lecture. And these are just random numbers. And most times, when you want to analyze data from something like this, you'd go to a new sheet and then you type equals and at the control page up key to grab the data. And by the way, I know bananas is spelt wrong and I'll show you why in just a minute he and grab, say, the topic that you want to look at and the year that you want to look at just by selecting these here and then Okay, you grab that and there it is. And then it makes it easier to do whatever analysis you want to dio. However, occasionally you might end up moving data around. So let's say we want events in bananas to change places. So we're gonna insert a row above events and hit Ault H i R. And I used shift space bar to select the whole row. When we go up here and shift space bar to select the whole roll up here control V to Paste and Ault H d r deletes the empty row. So now you go back to your sheet. Were you doing calculations? And all of your references are messed up. So if you use index Match to do this, then you wouldn't run into this problem. So let's delete thes and start over with index match. So Index Match incorporates the labels in your cells into those values. So let's just clear the rest of this out and let's get right into the function equals index . So the way Index works is it asked for an array and then your phone number and then your column number. And that's how you find the number you're looking for, and an array is just a collection of data. So in this case, we're going to navigate back to sheet one using control page up, and we're going to select the whole row of data next to apples. So we click here and say, shift control right to select the entire row than shift control down Arrow to select the entire array. No, I have everything selected. I'm gonna hit F four to make these absolute. So let's call this area The pond and our data is the fish. So all of our fisher in the pond So any fish that we want to find us right here and we can editor function right here in the function bar without having to go back to sheet too. So we hit comma, and you could just put in a road number six or seven or eight. But matches gonna help make this a lot easier. The match function is gonna help us find our fishermen. And these labels here and here are fishermen that are gonna help us to find our fish or our data. So we type in match and then tab and our look up value. It's gonna be back on sheet, too. It's gonna be in column D right next to our information, and we'll enter in a label there later, and we'll have f 43 times to lock just the column. Not the row, because we're going to copy this down and over. And we want to still reference the labels and column D or are fishermen. So that's our look up value. Next, we need to look up a race with a comma. Our look up array is gonna be all of the labels in all of the rows where data is. So we're going to select the labels for the rose five through 12 and hit F four toe lock those references and then comma and then the match type. In 99% of the time, you'll use zero for exact match. There are rare instances where you'll use long or minus one, but we're not going to get into that for this purpose. Most of the time you'll do it exact match because you want the exact spelling of that label . So that means you have to get all the spelling exactly right in sheet, too, for this to work. So that's important. Okay, so that match function identifies the rose close parentheses to finish the match function. So now we're back to our index function, So we just did our row number and now we're gonna do our column number now call a member has these brackets, which means it's optional. Well, let's just hit a comma so we could just put one. And you just have one column selected in your ray, and that would be just fine. But in this case, we have five columns, so that wouldn't work. We have to identify which column we're going to use. So we're gonna use the match function again, hit Tab Goto our look up value. And we're going to create a spot up here. And this time will hit F 42 times to lock the row number. But not the column, because we're gonna copy this across the columns. Okay, So where is our array for the fishermen across the columns? Let's go back to sheet one click on 2012 and then control shift right arrow to select the whole array and hit four to lock the array. The arrays should always be locked, comma zero for exact match. Closed the parentheses for the match formula, and then we close the princes for the index formula. So now we've nested to match functions into one index function. Now, when I enter you'll see I haven't error because it doesn't have any results yet because it's referencing blank cells when I want this match to be referencing E six rather than F six. But that's simple. You just grab it and move it over. Okay, so now we can put in our references. Let's start with the year we have equals and then we'll come over to sheet one, because this that's exact spelling. So we just click on this and it brings in 2014. And to show you how important matching up your spelling is, even if I type bananas wrong like it is in the spreadsheet. If I add a space behind it and hit Enter, it still is going to give us an error because even that space is enough to throw it off. So that's why I want to use equals and then select that cell and grab it exactly how it's spelled. So even though it's spelled wrong, it's still gonna reference correctly and see. Now it's grabbing the number that we want 66207 here and 66 to 7 over here. So now to show you the magic, let's see what happens when this reference moves. So let's go down here and select two cells and hit Ault H i R to insert two rows, shift space parts select this whole row and control. See to copy it control V to paste it in here. All to h d r to delete throw all hdr to delete this other row. Oh, and I forgot to delete the equals to find the bananas. I didn't take the foreign lot, So let's go back here and grab bananas again. And this time we'll take that formula out of it and then Ault h v v to paste it just a za value. No formula. Now let's move everything around again to see if our reference still works. So go appeared they ault h i r to insert a row shift space far to select the whole row control See to copy it Troll V to paste it and ultra HD are to delete Throw. Okay, now let's take a look at our reference. So bananas a 66207 And when we look at sheet 26 16 07 Okay, there it worked. No errors. So even though we moved our data around in the array. We didn't lose our reference because Index match doesn't care where in that selected array . The data is it just cares what the fishermen are or those two reference points or labels along the side and the top are so as long as you have the references along the side in the top, correct it confined it anywhere in that array. The only time you will have a problem if is if you take bananas here and copy it and paste it outside of the array that we're referencing and then go cult H d r to delete this row, and now you'll notice that it's not finding it. But that's because it's outside of our array, because our references only D five through age 11 and our references all the way down here on Rose 17. But you can even fix that scenario. And the way you do that is when you write your array, just select the whole column. So instead of having this limited to de five, we just have column D. And rather than having it be age 11 we just have it be calling H. But if you do that with your index array, then you have to do it with your match a raise as well, because they do have to line up. So we'll take out the rose on our match as well for our labels. And then we can leave the columns as they are. So now we enter and you can see that they're references working again. Now it doesn't care how far down the spreadsheet bananas is because our array includes the entire column. So that's how you use index and match together as a nested function to essentially do the same thing that you would with be, look up except you're not limited to just the left column, and you confined your references even if they move.
19. INT Function FINAL: okay, In this lecture, we're gonna talk about the inter function into short for integer and an integers just a whole number. So when you use the integer function, it brings back on Lee the whole number portion of a divided number. So our example here is like something you might see in a payroll department. You have a list of hours worked in these air total hours, and then we're going to try and break it down into days and hours, and we're going to use the in function to break down the total days, and then we use the mod function leader to break down the hours. So to use the in function, we, of course, select our cell and we start with equals. I m t tap to select it, and all its asking for is a number. But what we wanted to do is bring back the number of days. So we're going to select the number of hours worked, which is 36 in this case divided by using a slash and then the number of hours in the day close the parentheses and hit enter and you see it's bringing back on Lee, the whole portion of the answer, which is the one now. One thing you'll note. I'll go over here and do this. If I say equals and 36 hours divided by 24 hit Enter, it's gonna bring us 1.5, right, because 36 hours is 1.5 days, 1.5 days. But by using the in function in here, all that it's returning is the whole number portion of this equation. Okay, so we'll copy will. First don't delete this, and we'll copy this down and you can see on our answers. This one is 55 hours. So that's two full days, plus a partial day. And then this one is 12 hours, which is not on entire full day. So it brings back to zero and one thing to notice that the aunt function will always round down to the nearest whole number. So, for instance, if we take 22.5 and we create a formal for that and we just select that number, it's going to bring us back 22 so it's rounded down to the nearest whole number. But if we were to change this to a minus 22.5, and we update the formula. It notice it now has rounded down again, which brings it to the hole number minus 23. So that's just something to keep in mind when using the inter function and then in the next lecture will look at the mod function and we will fill in the remainder of the number of hours in this equation.
20. LARGE and SMALL FINAL: OK, in this video, we're gonna talk about the functions large and small. These two functions do the same thing just on the opposite ends of the number line. So let's take a look at large. First, we type equals and start typing large, and you'll see it says it returns the ke ith largest value in a data set. And the K basically is just your variable. So, like in algebra, it would be the X in your equation. So whatever number you tell it to do, for example, if you put five in there, we'll bring you back the fifth largest number. Small does the exact same thing except at the other end of the number spectrum. If we type in equals and small see, it shows the fifth smallest number. So let me just load a list of random numbers in here, and we will use these functions to find the largest and smallest of the list. So here's just a random data set, and let's say that I want to find out what the fifth largest number in this set is. I equals and large tab to select it. So the array, I'm gonna go select over here. Control Shift down Arrow to select all that data. Hit that four. That Locke set a race when a copy this formula down, it's not going to move the cell references. It's going to stay right where I want it hit comma and then are variable. Let's do five. So we want the fifth largest close the parentheses and hit enter and our function tells us that the fifth largest number is 84. Okay, now let's do the small function equals small tab. Picked the same array. Use control shift down Arrow to select the whole thing f four to make it absolute comma five close parentheses and enter. And 21 is the fifth smallest number. Now, a strategy I find very useful is if I'm entering my data, I don't wanna have to type in which largest or smallest number I want every time. So let's delete thes, and I'll show you a cool way to do it. That makes it easier. So first we're gonna create a little number list here that we can use for our formula. So we just put a number one here and then we do a little formula that one plus one that's gonna go to then we just copy that and shift down arrow a bunch and match it up with our other list and control V to paste that formula in there. So here all my numbers and I can pull from these to go 1st 2nd 3rd etcetera. So we'll put some titles here large and small is a heading over here. And then we'll do. Our formula equals large tab, an arrow over to select G three shift control down arrow to select the entire array F four toe, lock it so it stays when I copy it down my number column, comma. And instead of typing a number like five, I'm gonna click the one over here. Now I'm gonna lock it by hitting F for once, twice, three times. So now the dollar sign is on Lee next to the column J So the column is locked, but the row is not so. That means I can copy this formula down and over, and it will stay locked on the J column rather than moving over one to the right and I'll show you why I did that in just a second close friend sees and hit enter and 93 is my largest number. Can I'm gonna control see to copy it Use shift and down arrow to go all the way to the bottom and then holding the shift in a right arrow. I'm gonna put this formula into all these cells and control V to paste. So now I have the large formula. Correct 93 87 85 etcetera. Now I'll just go back in and fix the small formula. It says large now, but I'm just gonna change it. It's referencing all the right data. I just need to change this to the small function. Change that hit. Enter It controls C for copy, shift control Down Arrow and control Vita paste. So now my small function is working as well. So now we have a ranking from largest to smallest and smallest to largest. You'll notice down here, 41 is repeated twice, and it's also repeated twice over here. But that makes sense because 41 is in our list twice. So it's the 12th and the 13th largest, and it's the 11th and the 12th. Smallest. You noticed these columns or just the inverse of each other. The biggest numbers 93 the smallest number. The last entry is 93 and you can use this for all kinds of different calculations. You can use it for fractions and decimals as well. Asshole numbers. So that's a use the large and small functions to pull out a specific number in a list by its rank like fifth or eighth or 20th.
21. LEFT MID Functions FINAL: OK in this video, we're going to use the left in mid functions to separate our names into different columns. So we've already cleaned up our data and taken out all the extra spaces so that this will work better. So if we really want to separate our data, let's start with their table here that says First name. And then we'll have middle initial. And then we'll put in our last name. And we'll also include the year because it's there and we want to include that control shift left Arrow starting from the last cell, select everything we have in the header. That's why I left column C blank so that we would have a space between our original data and the new data that we're working with. Then we hit control. Be as in boy too bold. The headers. Okay, so let's start with the first name. I hit the aerial keys to get into cell D to, and I only want the word Janet to show, and the way to do that is to use the left function. So we started function with the equal sign, then left, and you'll notice it says returns a specified number of characters from the start of a text string. So we're gonna combine left and find functions. So hit the tab key to get the parentheses in there. And this is the data they want. And I'm gonna hit the F four key three times. 123 and see how it added the dollar signs into the cell reference. The dollar sign next to the A means the column is locked, but not the row. So once I'm finished with this formula, I can copy it all the way down and it won't move. It'll stay in the same column. That's our absolute cell reference, and you'll see how that works in a minute that we enter the comma. Well, you could just count the word. Janet has 12345 characters. The problem is, Gary has four. The name George. It has a whole bunch. So if we enter the number five here, it's not gonna work for each name. So instead, we're going to use the find function you'd see. It says returns the starting position of one text ring with another texture. Find this case sensitive. In this case, we're not gonna worry about the case so much. Hit tab. What text do we want to find? We wanted to find the space after the name. So we'll hit Quote Space bar and then end quote. They'll hit the comma, and we want to find it within this text, and we hit the F four button three times. 123 in this section with the brackets means that it's optional, so you don't actually have to put anything in there to make the formula. Work will do close parentheses twice once for the find function and wants for the left function hit enter. And now Janet shows up. We hit control C to copy it, Shift arrow all the way down and paste so it grabs everything up to the first space and enters it in here. The next step is to copy and paste those values, because occasionally you might still have a space in there. So let's clear this copy from Janet Copy Don't H V V. And when we look at Janet yep, we have that extra space. We don't wanna have extra spaces in there. So how do we get rid of those extra spaces in the first formula. We could have done a minus one and the find, which would make it a little more complicated. But instead we can use the trim function. So let's do that. We'll go equals trim. Grab that close. Parentheses copy. Peace it down. Escape so I can copy the whole field. Copy it back to here and Ault H V V again and I can hit the shift control down Arrow to select it and then hit the delete key. And now my data here will have no extra spaces. So let's go ahead and do the middle initial, and then we'll get to the last name. So, like this text at 43 times toe lock the column comma for our start number. Well defined function. Quote space quote comma where we want to find the space in this cell. 43 times close parentheses, close parentheses. Hopes. Forgot one thing. The number of characters in mid is not an optional section. You actually have to tell the number of characters because it's in the middle of the text string. So let's try if a huge number to make sure that it accommodates all of the letters because we're going to get the middle initial and the last name in the same cell, and that will make it work. So we're going to copy this, paste it all the way down. So now I have everything after the middle initial, and then we can use these results to get our last name to get the last name out of the middle initial section. We need to get rid of the formula and trim it because we have an extra space in there. So first we have to get rid of the foreign was by selecting them all. Shift control down arrow control. See, to copy it all Holt H V V to paste the values without the formulas. And let's trim it will come over here to a new section equals trim Tab hit control. Left arrow takes me right to that sell because that's where all the data is. And Aiken, skip over the blank columns in the middle. Close parentheses hit. Enter. Copy that. All the way down. Control V to paste over it now, Control, See to copy that whole section and come back over here Ault h v v to paste the values. Now we have it cleaned up and have just the values We don't need this anymore. Control, Shift down Arrow and elite. Now let's do the mid function equals mid tab this text comma find left Arrow to select that text I want to find and we want Actually, we want to find the space So put a space comma We want to find it within so you to close the parentheses for the find function at 100 for the number of characters close prince sees and enter and it brings back Smith Now what we could do to simplify this just f to toe go back into this formula Hit the home key brings me to the left side of the equal sign right there. Oh, and we could put the trim formula right in there and hit tab and hit the end key to add the extra close parentheses for the trim function now hit enter and that gives you the same results with it trimmed up without those extra steps of copying and pasting to clean it up . So now we can copy it. Shift there all the way down, paced with control V. And there we have all the last names course. We have a couple that are too big for the column here, so we'll auto fit the width of the column. Halt H O I to make the column bigger. It escaped to clear everything else we've copied. And I would just want the middle initial here. So let's just select this and move it out of the way. Using shift control down arrow Control, see a copy. Go over here, paste it back over here to delete it. Shift control down Arrow. Oops. I made a mistake. I didn't get the formula off the other one. So it's undo and come over here. Couple this first, then Ault H V V to paste the values only it's now weaken. Delete this stuff and our data will stay as it is. So in this case, we can use the left function again because all the data that we want is the middle initial here. So we can just go ahead and enter. The formula equals left Tab over. Here is the data we want to pull from comma two for two characters. Close parentheses after copy that formula down. Using shift down Arrow Control V to paste it. There are all our middle initials. So let's copy the whole thing and get rid of the formulas beheading cult H V V and pacing in his values. We'll delete this using shift control down arrow, then delete. Okay, now, let's do the year. Well, we could just do this equals that Copy it all the way down using shifting down arrow Control V. So that's how you use the left and mid functions to separate your data. But now you've got a nice set of data, all separated out and ready to do analysis on.
22. MOD Function FINAL: okay. And this lecture, we're gonna look at the mod function. What the mod function does is it returns the remainder after division. So in our previous example, we used the interviewer function here to bring back the whole number or the Inter Jer to tell us the number of whole days in this number of hours worked. Now we're going to use the mod function to bring back the number of hours left over so very similar to our interview function. We're going to just do equals and then m o d for mod tab. First it asked for the number we want to divide, which is this and then just to the comma here. And we're dividing by 24 hours. We hit Enter, he could see the answer is 12. So when we divide 36 by 24 were left with 12 remaining hours. And so by using our inter function and our mod function together, we could break 36 hours into one day and 12 hours. So now let's copy our formula down and you'll see that it's working. So 55 hours Brexit into two days and seven hours, right? Because 48 hours is two days, and the remainder is seven hours. 12 hours breaks down into zero days and 12 hours. And to show the mod function on a much simpler scale, we'll just give you another number. Last time we used the example of 22.5, and if we do equals M o d tab to select it, this is the number we're using. If we divide it by one, then the remainder is 0.5. Change that to three. It becomes 1.5 because three goes into 22.57 times, right, three times seven is 21 and the remaining portion of that is 1.5. So that's how you use the mod function to bring back the remainder after dividing two numbers.
23. Nested IF AND and OR FINAL: OK in this video, I'm going to show you how to nest multiple functions within on if function, and we're going to use and and or in conjunction with the Net function to do that so they would just have some numerical data associated with different fruits. So I've just colored this upper section orange, and I've also created a drop down menu so you can select different terms oranges, apples, bananas and grapes. So to create a nested if function, we start by typing equals If and we hit tab to start it, and it's looking for a logical test. So if something is true, then do one action otherwise do another action. So let's say we want to find numbers in this list that are located between these two numbers. So we start by nesting and and four go in here and we hit Tab to select it. And let's just select this number and will hit F 43 times to lock the column, just in case I want a copy. This formula over to the right that little still reference the D column. So if that is greater than this cell, well hit F or wants to lock both columns and rows on that cell that we had a comma slick. This number again hit F 43 times again and less than that. We select this number and hit F four to lock it as well when we closed the parentheses for the and function, which brings us back to our if function. So we hit comma. Now it wants the value. If true and we can return anything we want. We could put a one, or if we want to have a text string, we could do quote in between end quote and it will bring back that text. And then we hit a comma and her value. If false, I will just say blank. So that's just two quotes. Then we close the if statement function and hit. Enter and you see that it's blank because 112 is greater than 82. So let's just copy this control, see, then shift down arrow to the bottom of the column and hit Control V to pace that formula down. They will hit all to H O I to widen the columns a little bit, and we have four items that are in between 50 and 82. And the cool thing is, we can change these variables and our results will change. So if we change this 1 to 100 you see, we have mawr that fall in that range now And if we increase this to 75 I think you see, we now have fewer within the range for increased this toe 1 50 then we have quite a few within that range. So they nesting the and statement inside the if statement, we're referring to two different variables, and we can change those variables and get different results. So let's increase the strange even mawr and go to 45. And then let's add another element into our nested statement. So let's select our oranges and we'll have f 43 times again toe lock. That column just in case we copy it over to the right and then we say, is equal to and we select this one up here, hit at four, wants to lock it and then we'll go clicking door function here and we'll say in between comma and the right fruit we had enter and then we copy this formula all the way down. And now on Lee, the ones that are labelled oranges are displaying our text and will do all to H O I again to auto fit the width of the column. And now we can use our drop down and change us from oranges to apples and you see it changes. We can change it to grapes you see. Now it's just giving us data back on the ones that have grapes. So that's how the nest and inside an if statement, let's use, or so we'll just go ahead and delete this. Start over, we start with equals if tap to select it, we type in or instead of and and or checks whether any of the arguments are true and returns true or false. So we hit Tab to start the function. We'll select the cell F 43 times to lock. The column is greater than it will select. This number hit a four once toe locket comma or this. Select that again at 43 times to lock. It is less than this top member of four once. Then we entered closing parentheses. We're back to our if statement, say comma, and then our value of true, we could just show the name of the fruit. So click there and F 43 times. In this particular example, you don't need to lock the column, but it's a habit that you tend to get into because if you were copying these across columns thing, you'd want it to be locked and then you wouldn't have to go back and change it so comma and then we'll do this blank again. So to quotation marks right next to each other, close the parentheses and hit Enter and they will copy the formula and drag it down. And let me just change a couple of these numbers to make this work, and you'll notice that we have an answer on every one of these. The reason is, if you look at this formal the way I wrote it, the answer is true for any of these numbers. So let's just change these numbers up here to make some of these false. Let's change this to 300 so now you can see this one where the number is 1 85 It's not greater than 300 or less than 140 so that's essentially doing the same calculation as our and function, but just using the logic of the or function you can use the and and the or functions within these F statements. And you could even use several of them within the same statement to just add in different criteria that you want excel to look for and then bring back whatever response you want. Okay, so that's how you nest. And in an or statement within an if statement.
24. Rand Randbetween functions FINAL: in this video, let's talk about using the rand and rand between functions to use the rand function you start protecting equals R A n. There it is. Rand hit the top button and then you just hit the closed parentheses and you don't have anything to input into the function at all. This is how it works. You hit enter, and it gives you a random number. And most of the time the random number is a decimal, because there are a lot more decimals than there are whole numbers. So there's your random number, and you copy this by just sitting control. See and hold, shift down and hit the arrow keys right and down a bunch of times, then hit enter, and now you have a lot of random numbers. Now, for most situations, a random, desperate like this isn't very useful. More often, you would want a random number within a particular range. For example, if you're drawing a number out of a hat, it would probably be like the numbers one through 10. So let's it delete and create a range equals rand between Down Arrow than tab, and now it's asking for a bottom number. So for our lowest number, let's go ahead and just use one, then comma. Now it's asking for the top number, and the highest number we want is 10. Shift zero will close your parentheses than hit. Enter, and it brings back and nine. Now it's a copy and then hold down the shift key and use the arrow keys and then let go of shift and hit Enter. And now we have a bunch of random numbers between one and 10. So that's how you use the rand and the ran between functions to generate one or more random numbers.
25. REPT function FINAL: OK in this video, we're going to talk about the repeat function. And if I go like I'm entering a formula, we enter the equal sign and then our e p t. That I had tab and then asks for the text that you want and how many times you want to repeat it. So let me hit the escape key to clear that, and I'll show you another way to get to this one. Go to the insert function button and it's under the text menu, and you can go through here and go down to repeat. And you can do it through this wizard just by filling out these fields. Normally, it's quicker and easier to just do it within the cell. That way, it makes it easier to just reference cells within the sheet. So hit equals R E P T. Whoops got out of myself. You have to hit the tab button to get the open parentheses, and then we can type in the text that we want to repeat. So let's say that we want to type in the text, quote the quick brown fox comma, and I see the bold ID section goes to a number of times and let's do it 25 times close parentheses had enter. And there we go. We now have the same text 25 times. Now that could be useful if you're looking to have the same thing typed many times in one direction. So that's what the repeat function does. But let me show you another strategy that I really like. That's related, but I could just move out 25 times if I wanted to repeat something 25 times. And I'm just gonna type of filler text in this column and hit a one, for example, and I had control left Arrow, and that takes me right back to the very first column, and now I can type the quick brown fox, and if it copy control, see, you can also hit the copy button there, but then we'll have it copied. I can also hold control shift and the right arrow down, and now everything to where I put that filler text is selected. Then I can hit the paste button up here on the ribbon, or it can hit Control V. Now I have the quick brown fox copied and pasted into every single column, so you might use this strategy if you needed to repeat a header 25 times. Of course, you can't see all of them because the columns are too narrow, so you are a fit. The width we use are all key H oh, I and that makes every call in the exact same with another way to do that, I'll hit Control Z to undo it. Weaken Goto format, auto fits and column with right there, and that does the same thing. So the repeat function can be useful if you want the same thing repeated many times where we can use this strategy to copy data across columns. And if you want the same thing, repeated down a column in different rows. One way you could do that is to copy this whole selection, and we already have a copy. But if you didn't, you hit the escape key, so everything's un selected. So you make sure sell a wanna selected and you hold down the shift and control keys and then tap the right arrow key. It will take you all the way to the far side of that data, and we'll copy it that's control, See? And then I'll hit control home, which will bring our cursor back to calm a and let's put the cursor on, say, sell a three and we want to do a paste special. You could go through here and go to this paste special and then hit transpose. See what that does. All that data is now pasted, and each item is in its own rope, so that's one way of doing it. Let's sit control Z to undo, and I'll show you another way to do it is press Ault each V. Yes, because it's the same menu you see the E down here has that underlined. You hit the E on your keyboard and it'll automatically check that box for you. You hit okay with your mouse or hit enter, and that's another way to do it. So it's a few different ways to repeat text over sells horizontally or vertically down the rows
26. Round function FINAL: OK, in this video, we're gonna talk about the round function, and the round function is not particularly useful by itself. It's used in connection with other mathematical operations where you've come up with some numbers and you have many decimal places and you wanna limit it to a specific number of decimals, or you can go in the other direction. And if you have a member in the millions or more and you want around that down to the nearest thousands or hundreds, for example, and you can also specifically round up or round down and their functions for those that will explore as well. So here we've started with the basic mathematical representation for pie. And, as you may know, if you add more numbers after the decimal place, pie just keeps going indefinitely. But for most purposes, you just use the basic 3.14 Now, if you want to use this number as it stands in your mathematical operation, if you do your math with all of the decimal places, you'll come up with a slightly different number than if you just use 3.14 So that's what round is for. We start by typing equals and see you have round, round down and round up round down will round your number down to zero round up well around you, never up. So let's look at round first. So for mathematical operations that use pi, usually you want 3.14 and that's pretty much it. So we'll select it by hitting Tab on the keyboard. The number I want to round is selected right here, and so a one that I had the comma. Now it's asking for the number of digits, shift zero to close the parentheses and enter, and we get 3.14 and the restaurant zeros. So if we want to get rid of all those zeros, we could just get rid of those. Since they're not useful and we rounded that down to two digits, let's try showing another number. I'll just type in a bunch of digits here, and we'll show all those put the comma and get rid of zeros. Let's say we want around this big number to the nearest 1,000,000 equals round. Oops, missed. Typed it back space. There we go. Hit tab. That's the number I want around. So if we wanted to deal like we did above and showed two places after the decimal would hit a positive, too. But in this case, we want to show places before the decimal. So we hit. Let's do a negative seven and we'll around it to the nearest 10 millions close that parentheses hit, enter, and there it is, rounded to the nearest 10 millions. Let's go back into that by hitting F two. We're gonna move the cursor to delete that and put six, and now it's rounded to the nearest 1,000,000. So let's create another number and I'll show you how to do round up and round down. Okay, so here's a number and let's start with round up and it asks how many digits we want around up. And let's go ahead and put in negative three and we'll do the thousands. And there it is. We could do the same thing with round down equals round down. We picked that number, hit a comma negative three, and it rounds it down to the nearest thousands. So that's how you use the round the round up and the round down functions
27. Text Function FINAL: in this video, we're gonna learn about the text function in Excel. Now. This is a very versatile function. It's used mainly for writing lines of text where you want some of the numbers or some of the other inputs. Two. Very. Based on what you've put into other cells, you can have the text function, format things in a couple of different ways, and I'll show a couple of them here. Now let me give you the basics of using the text function. You start out by typing your equal sign. You type in the word text and hit the tab key. Then that starts you with your open parentheses and you're ready to put in your formula. And I'll hit the escape key a couple times to get out of that. Another way you can go to it is to go to the insert function and scroll through all the options here to find it, and you find the text function. So that's another way of bringing up the text function. To us. It's another way I like to start with equals type the word text and the bold text here says . What is the value or the number that you want to show or whatever else you want in there. If I say 0.25 that moves the bold section from value to format text. So now the function is asking, How do you want this formatted? And I want to show it as a percentage. So to format this, you'll start by putting an open quote. And that's how you tell Excel that whatever you're putting here is not gonna be showing in the formula. It's gonna be explaining to excel how you want things to be formatted. So I'm gonna take a 0.0 that will give me one digit before the decimal place and one digit after that I put the parentheses in here, and that's exactly how everything is going to show up. So hit the close quote, close parentheses and enter now shows 25.0%. Let's delete that and try again in a different way. Type equals text 0.25 comma open quote. Now I could just use the pound sign and then the decimal place and the pound sign and the percentage close quote, close parentheses. And now, because there is no number after the decimal place. The pound science says Okay, there's no number there in your value, so we're not going to show anything. So you can use that pound sign as kind of a wild card. Now the reason this is useful is let's say that I wanna have a line of text talking about a percentage say 25%. Same way we do a formula because I want the text on the text to reflect a number that's gonna change. So the equals sign, open quotes and then we're gonna type. Here is the 25% of the total number. Doesn't really matter what the sentence says. We're just using this as an example. So that's just a sentence. But I don't have 25% showing, so I'll just put 0.25 there. I'll go back to this particular formula, and if I had F to open up the formula again, scroll back here to where the 25% shows up. I hit the close quote Emperor Sand Side. Now that tells Excel that were no longer playing with text. What? We're gonna put a formula in here, so I start with text open parentheses. What's the value that I want. I want whatever happens to be in this cell. So click on that and you see a one is selected. I'll hit the F four key, which locks both the column and the row. So it doesn't matter where I moved the sentence. It's gonna make an absolute cell reference to a one that hit Comma. And then I'll delete the 0.25% because that's not gonna be important anymore. Format text. I want to be a percentage open quote 0.0. I like that zero behind the decimal, but you don't have to have it. The percentage sign is very important. You want a percentage to show close quote, close parentheses, ampersand and the open quote sign. So now you've got two phrases of text. The formula in the middle that another phrase of text hit Enter and the sentence says exactly the same thing is, it used to say, but say this now needs to be 20.35% notice the sentence changes, so I could say 0.7895 rounds it to the first digit because I have only one desperate place showing, So it's going around it to that last one instead of saying 78.95% it says 7 86 If I wanted more decimal places to show that I could go in here and put more of those in their or I could just put a pound sign there, only one of them. Let's do another one. Every time I get into the cell, I head off to, and that opens up the formula without having to click up in here or anywhere else. So that's how that text function works there. You can do this not just for percentages. You can also do this for other kinds of numbers. For example, let's say I have the number 1000 here and I want a sentence with the number of 1000 in it equals open quote There are, so we'll just make this one sentence. There are 1000 close quote ampersand sign. Get that one hit a four. So I lock it. So it's an absolute cell reference, and I'm going to hit the ampersand sign. Open quote, period. Close quote notice. I did not put the text formula in there because I want to show you something doing it this way. It shows you exactly how you have it formatted in the first cell. If you change the format of this number, we click up here. It's not gonna change it in the sentence. You have to actually use the text formula for that formatting to take. So you come in here. Text. There's my reference using the arrow keys on the keyboard comma formats open quote. We'll do the pound sign, then comma three spaces because I want three spaces after the comma close quote and the close parentheses hit, Enter and now I've got There are 1000 of these with the comma, but I don't have anything showing after the decimal place because in this format, I don't have anything in there saying that I want something after the decimal place to show . So if this number were, say, 1000.25 it's not going to show up in my sentence. If you were to say 1000.95 a round up as you see toe one, but it's not going to show the decimal place unless you tell it to. We're going back into the formatting section and decimal place and put something after it. And if you use the zeros, it's much more specific on two zeros. I wanna show, too. And that shows 1000 point, maybe five. Seeing comeback this number and say, now this could be a 1,000,000 of them. And however the number changes the text function, update your sentence for you. So those are just a couple of ways that you can use the text function.
28. TIME Function FINAL: okay. And this lecture, we're gonna talk about the time function. Excel reads time differently than what you might be used to seeing. It has what's called a date time code, which is just a number that represents hours, minutes and seconds. And the reason for that is that you can then use that number to do mathematical equations with times in excel. So to do the time function, we start with equals and then type in time and the time function converts hours, minutes and seconds, giving us numbers to an Excel serial number formatted with a time format. So in the background, you have the serial number that represents the time so you can see it asks with our will, hit the comma in the minutes, another comma and then the second. So if you just have a time that you want to enter, you can just type it hours, minutes, seconds and then we don't. But the function becomes beneficial when you want to pull hours, minutes and seconds from different places and have multiple options for your time entry. So let me bring up the download that I've included for you with this lecture, and we'll have some numbers that we can play with. Okay, so now we have calmed with hours, minutes and seconds. So now let's use the time function equals time tab Key. Pick an hour. Will do 16. Pick a minute will take 10 and for seconds will pick 50 close parentheses and had enter, and it displays a time of 4 10 PM And, of course, this function works like other functions where you can copy it control, see and then hit shifting down arrow and paste it. And it will copy your formula down, pulling from relative cell references. So if we select the last 1.5 2 and scroll down, we can see all of our cell references have moved down with the formula. So if we want one that represents an AM time weaken, just select the cell F two to open the formula, and we can pick on earlier time. We'll just grab the our drag it up to the AM section hit, enter and now showing am now when you're formatting time, let's just click control shift and down Arrow to select it all, and we can use control one to bring up the format cells option, and you could see right now it's formatted under custom. But you can also go to the time category and use these options as well, so you can see you can get military time. You could show the time and date which we don't have the date, but I'll show you what it looks like when you do that click OK, you'll notice it shows the date format in here, but we don't have an existing date. So shows up his ones and zeros. So that's how you use the time function in Excel.
29. TRIM function FINAL: okay, in this lecture, I'm gonna talk to you about the trim function in Excel. This one's especially useful, especially when you're importing data that's not quite as cleans that needs to be. Or if you have cell references that have extra spaces added, which happens when it comes from outside. Excel in your importing data from other sources. So the way the trim function works, you start writing the function by hitting equals and you notice, it says it removes all spaces except that spaces between words. So, for example, if you have some data that comes in and it has multiple spaces in front and then say it says just something simple, like, this is data, period, and then you have more space is at the end, and just so you can see it better let me out ofit. This and once again, auto fit is Ault h o I. You can see this gets kind of messy, especially if you have more data that comes in without the extra spaces. Might be somebody's name, for example, and it's a line to the left. The problem is when you use other functions that look at the data in the cells. You need to sell data to be consistent. So in order to get the data so it's cleaned up a little bit, use the trim function. So we hit equals. Start typing. Trim out, hit the tab button for the open parentheses, then select this data. It closed parentheses. Auto. Fit it with Ault H O I. Now the extra spaces air gone. No spaces before or after. Let me show you a way to test it. Now, if you go back to the original want, you could see if we have F to where the cursor is. There's all this space all over the place, and I just hit the home button to get to the front of it really fast. And I'm typing the arrow keys so you can see the movement of the cursor in this space for him. The N key. You can see these extra spaces. We hit the escape key and go down to Fred where there's no space is at all for Head F two and move the Aero Key. You can see there are no extra spaces at all if I go back to this one and I copy it hit control C and then I do a paste special. So if you paste it has a value, you will be able to do the same thing and see where the extra spaces have been removed. So you go to paste special values, OK, that I had have to You can see there no spaces. So this trim function is very, very handy for helping you trim the data that you have for analysis and getting your data ready for further usage. Okay, Now, let me give you a specific example of how to put this trim function into practice. So I have some data that I've put together that is not clean. I set it up so that it will have some issues so that I can show you how to use the trim function. So I'm just gonna pace it in here using control V. So you can see we have a list of names and year. So we're gonna use the trim function to remove the spaces and clean all the state up. You can see this one has a couple of extra spaces in the end, some of them have extra spaces in the middle and those spaces will get in the way if you're trying to do certain analysis on your data. So let's say we want to clean all these extra spaces out of these names. What will dio is it equals and type the function trim Hit the tab key. So we're ready to select our text. Gonna arrow over left on the keyboard to Janet Smith. Close parentheses, using the shortcut shift zero and then hit. Enter. So the formula basically just says trim, Sell A to see your says it right here. So I'm going to copy that formula. Then hold the shift button down and down arrow. So I match with the last name in my spreadsheet this and control of you to pay set formula down that all the names have all the extra space is removed. The only thing we have to do is to delete out that trim formula so I can analyst the data, so I'm gonna have escaped to clear out everything we have previously copied. I'm gonna copy everything that we have selected and now well paced, just the values over the top of the same data, and that will remove the trim function from the data. So we used the shortcut Ault H V V. So now the trim for Mills gone and all we have left are the names we had escaped to clear up what we've copied and lets it control plus space bar, just like the whole column. So I can auto fit the wit to match the data that we have there. And if you remember, the shortcut is Ault h o. I see the eye under the format here. That's for auto Fit column with. So now we have this cleaned out of here and I can copy it and it paste over the old stuff. And when I paste over the old stuff, it gets rid of all the formulas that we put it in there. See how the lines went away? See, this has the lines that are darker around each cell, and this one no longer does. But we don't want to ruin all the formatting in there. That's it. The undo button controls the and let's instead based on Lee values again. Holt each vv. And that leaves the formatting alone. So we don't overwrite any of the formatting that we used to have their. So that's how to use the trim function to clean up your data, to make it easier to work with and do more analysis on.
30. WEEKDAY WEEKNUM FINAL: Okay, This lecture, we're gonna look at two functions the week day function and the week numb function. And when we start typing these, you can see them right here in the week. Day returns a number from 1 to 7, identifying the day of the week of a date so it doesn't work by itself. You have to have a date to work with. And then week Numb is similar, but it gives you a number from 1 to 52 for the week number in the year. So let's hit escape. A couple of times, we'll put in a date to work with it. We'll just put in January 1st 2070. Now we put in our formula for a week. Day equals weekday tab. Select that date close parentheses hit, enter. And that was the first day of the week. And for excels purposes, the first day of the week is Sunday. And then if we create a little formula, say this plus one, add a day to it and then we copy this down and we'll copy are weekday formula down here a swell see 1234567 and then back to one. So it's reflecting our seven days of the week and again, one being Sunday to being Monday, three Wednesday on through to seven, which is Saturday. And then it starts over again at one for Sunday. Now let's check out week numb, and we'll grab our date over here again. Close parentheses. Enter will copy it and paste all the way down. So you see, these 1st 7 days in January were in the first week of the year, and this eighth day of January was in the second week of the year. So let's go ahead and put in today's date and see what results that comes back with. We'll use the Today function. We're gonna copy these, and we can do that by doing shift control and right arrow to grab both of them. Control, See to copy Arrow Down and Control V to Paste. It's now we can see that July 10th 2017 is the second day of the week, which is a Monday, and it falls in the 28th week. So that's our weak, numb or week number, so you can use these functions to do calculations and pull information out of calendar events so you can figure out how much time has passed between two dates or how many weeks have passed etcetera. These functions would be commonly used in payroll situations, for instance, calculating what day is payday? We're calculating things like pay periods in the year, so those are the week, day and week non functions.
31. YEAR MONTH DAY HOUR MINUTE SECOND Functions FINAL: OK, in this video, we're going to talk about the year, month, day, hour minute and second functions these air valuable for taking an existing date entry and separating it out into its individual parts. So we're gonna use are now function to enter a date and time in here and then we'll break it up with the year, month, day, hour, minute and second functions. So we'll do equals. And now and that will give us today's date along with the time and it doesn't show the seconds, but the seconds are there in the data. Okay, so we're just gonna put in interest for the year, the month, the day, the hour, the minutes and the seconds. So that will use each of these functions to bring back this data. So for a year, with you equals and y e a r it tab, go grab this date, close the parentheses and hit enter and it brings back our year 2017 from that text ring above. Now, if this reference were to change that our function will update, I'll show you an example of this will copy this, paste it as a value using Ault H v V and then I'll just go into the text and change the date to 2012. And when I hit the enter key and it updates, the year function also updates it in our cell right here. Let's do the month equals month tab. Select our reference, close parentheses and enter and see. It returns the number of the month the seventh month for day with equals Day tab. Select our data, close parentheses and enter, and it shows the 10th day. He also knows that are other versions of the day function. There's days and days 3 60 days returns the number of days between two dates and days. 3 60 returns the number of days between two dates based on a 360 day year. So that's 12. 30 day months. Oops. Gotta go delete that next one is our equals. Our returns the hour from the referred cell and the hour is nine for a minute. With you equals minute. Select our reference and it shows 39 finally we'll do second equal second, same thing. Pick a reference. So and to show you again how things update. If we put the now function back in here, you can see the second shows. Four. The year has gone back to 2017. How many time I update any of these functions in here? Everything else will update. So, for example, a hit off to to bring up the formula again and hit Enter again. Watch what happens to the seconds. Say it changed to 24. And what that means is every time you save a workbook with a live formula in here in your polling year, month, day, etcetera. It will update when you reopen that workbook or when you make any updates to the formulas. And if you want to lock any of these cells to show these values permanently thing enough to copy it and replace it just a za value and get rid of the formula. So that's how you use the year, month, day, hour, minute and second functions to pull different parts out of, ah, date and time
32. Conclusion: thank you so much for joining me in the Excel 2010 Formulas and Functions. Course, I hope that you learned a lot, and I hope that you'll continue learning in this lecture. I just want to talk about what we've learned. First thing we learned was the some function, and we broke down the formula into all of its pieces in parts. Then we created entire spreadsheet for a bunch of sales Reps. Joe Carroll, Jane, Bob and Marcy. Then we learned how to use the some function and if statements, we used the summit function to figure out regions, and we use the count if function to count employees and then created an entire table with all of our sales reps and learned how to use the V look up function to find A that we were looking for. And finally we used the left mid and write functions to separate a code into more easily viewable information. The next step in your learning journey is to go out and practice using these Excel formulas every chance you get. Thank you so much for joining me, and I'll see you in the discussions