Excel - Beginner to Advanced in Under 4 Hours

1. Promo Video: Hey, I'm major from ready excel dot com and welcome to my Microsoft Excel to hold. I have over 13 years of experience, both using Microsoft Tech so and teaching it to others. Basically, I'm the go to guy when it comes to anything Microsoft takes so related. And now I'm ready to share my tricks of the trade with you. In this course, I'll teach you the Microsoft Excel fundamentals, along with some other advanced tools and formulas that would set your skills apart from others. This includes but is not limited to, concatenation v. Lookups Index matches a raise, pivot tables charged, and some myths functions. My course is clear and concise, and I pack a whole lot of knowledge into a short amount of time. So what are you waiting for? Something for my course now and you'll become the go to excel guy or girl in no time. 2. Understanding the Excel Interface: All right, so in this video, I'm gonna go over the layout basics. I'm not gonna get too much into the menu or the toolbar or how to customize the toll bar. I'm going to stick with the basics of the layout. Okay, so let's get right into it. So every time you create a new file and Microsoft Excel, you create what's called a workbook. Now each workbook is comprised of different worksheets, as indicated here on the bottom left hand corner. OK, now you could add a new work sheet by clicking on this, plus this plus sign. OK, you could rename a sheet by double clicking on this sheet and typing into it. So let's call this sheet ready and this sheet excel. Now. Every time you had a new sheet, the default name is to call it sheet and then whatever. Whatever number sheet you're on now, let me go ahead and delete this sheet that I just added by right clicking on it and then selecting delete. Okay, so each work, she is comprised of columns as indicated by letters and rose as indicated by numbers. Now I know what you're thinking. What happens once you get to columns? E. Well, then it goes to column a column, a B and so forth. You could have up to 16,000 columns and around one million rose. Now the intersection off a column and a row is referred to as a cell. So, for instance, the intersection of Column A in Row one is referred to as Cell A one. The intersection of column B in Row one is referred to as Be one. The intersection of Column A and Row to is sell a two in the intersection of column B and row to its cell. Beat, too, and so forth. A lot of the time you'll hear people refer to a cell range, and it's nothing more than that. A cell range is a range of cells. So when I select this cell range, I'm selecting cell range A one colon, a nine. So that's referred to as a one colon a nine. If I select a one through B nine that's referred to as a one colon B nine. Now, data could be type directly into a cell or into this formula bar right up here. Okay, so I'm gonna type Ready? Okay, by typing into that formula bar. But now I'm gonna type directly into the cell, are sorry directly into the cell without using the formula bar. So let me type Excel Right there you could resize a column or a row by hovering your mouse between column A and column B. And once you do that, you should see this little icon pop up. You could make a column more narrow, or you could make it more wide. There's also a feature called Auto Size. So let's say the column is more narrow, thin the data in the column while all I have to do is come over here and double click, and it automatically resize, is it? Now let's make our row larger than should be and then let me double click, and it automatically resize is it? So let me add some more data into these cells, Okay? Now, if you select multiple columns or rows for that matter at the same time and you re size it , all of them are going to become the same width. So right now I'm re sizing it to 30 pixels, so as you could see columns a through D all became 30 pixels. Okay, Now I could also auto size all of them at the same time. Just all you have to do is make sure they're selected and then double click toe auto size. There's another way of doing it without having to select the columns. Okay, so what you could do is click on this top left corner and basically, what that is is a select all and once everything is all selected. All you have to do is a double click to resize, and it re size is everything. And the auto resize is something that I use all the time, and it's gonna be something that you end up using all the time. 3. Cell and Formula Fundamentals: All right, so in this video, we're gonna go over the cell and formula fundamentals. Okay? Now, what exactly is a formula? Well, formula is an expression that calculates the value of a cell. Okay, so let's get right into an example. Okay, So how do we initiate a formula? Well, we initiate a formula by typing the equal symbol. Okay, Now, on a side note, you can actually initiate a formula by using either the plus or the minus sign. But I tend to use the equal symbol, as do most other people. Okay, so you can actually reference data within another cell within your formula. So let's say we wanted to reference data from cell A one in cell A three. What? We could do that by typing up a one in our formula. Okay. Now, if we change the data and sell a one sell a three will change to reflect that. Okay, so let's undo what we just did. Now, as you just saw, I actually manually typed in a one, but you don't necessarily have to do that. You can actually click on the cell that you want a reference once you're in your formula like I just did right here. Okay. Now, not only can you reference cells within your worksheet, but you can also reference cells within other worksheets or within whole different workbooks, for that matter. OK, so let's take a look at how we reference cells in another worksheet. Okay, so let's say I wanted to reference cell a one within sheet number two, which we have right over here. Well, I do that by typing up she two. Okay, then I use an explanation point. Okay. Then I type cell a one. Okay, so that's how it's supposed to look. You have the sheet name followed by the explanation point followed by the cell that you want a reference. Okay. Now, you can also reference data within cells within whole different workbooks. Okay. And how do we do that? Well, I happen to have another workbook open already. Okay, so let's delete what we have type are equal symbol. Okay. Come over here to our new workbook and click on the cell that we want a reference. Okay. And never have it some data and another workbook. And what does this formula look like? Well, you have a single quote followed by a an open bracket. Okay, then within that open within those brackets, you have the file name. Okay, closed brackets. Then you have the sheep name. Okay, which the sheet name in this case is Sheet one. Okay, close your parentheses, and then you have your cell reference. Okay? Try not to worry about these dollar symbols right now. We'll get a little bit more into that later. But that's how it's supposed to look. Now, that's actually how it looks when we have it open. Okay, We actually have this file open, but let's see what it looks like when we close this file. Okay, so now we close the file, and let's see our formula. See, the formula actually is referencing a path to that file. Okay, Once you have the file closed, it's going to reference the path toe where the that file is actually actually located on your computer. Okay, Okay. So let's delete our formula and let's go with our original formula, which was a one. Okay, Now, let's try copying this formula to Selby three. Now, as you can see, it actually copied the data from cell be one rather than the data from. So a one. Now, Why did it do that? Well, I want to get into the specifics of that, along with simple arithmetic formulas in our next video. 4. Basic Arithmetic Formulas & Relative vs Absolute References: All right, so in this video, we're gonna be going over basic arithmetic formulas on. We're also gonna be getting into the difference between relatives of references and absolute sound references. OK, so let's go ahead and dive right into Okay, So in this example and column B, we have our quantity. And in column C, we have our price. Okay, So what do we want to do? Well, in column D we want to calculate our total, which is gonna be our quantity times our price. Okay, So how do we do this? Well, first we initiate our formula by typing in the equal symbol que And now let's type 11 times 90. Okay? And that gives us our result, which is 990. Okay, now, as we just learned in our previous video, we don't actually have toe type in 11 in 90. We could actually just reference these cells. Okay, so we say B two times C two okay. And then also gives us our desired calculation of So before I continue on with this example , I wanted to go over the different math symbols that that Microsoft excel uses. Okay, so the one that we just went over was multiplication. And for that, we use an asterisk. Okay, We use a backslash for division. We use the dash or or the minus button for subtraction. And obviously we use the plus sign for addition. Okay, We can also use a cared symbol for exponents. Okay. For instance, the formula equals to care. It three is the same as saying to to the third power, which will return the number eight. So let's get back to the example. Okay, so we have the total for cell D to which is Selby to time. See, too. Okay. And now we want the same formula being copied down to cells D three through d seven. Okay. What's a quick way to do that? Well, we could click on Cell D to and then we re type on our keyboards, control C, which is a copy. Now let's highlight cells D three through d seven and then type control V on our keyboard. Okay? And that's a quick way to do this calculation. So we double click on d seven. The formula pops up, okay, and this formula is referencing Selby seven and sell C seven. Okay, Well, How does Microsoft excel? No, to do that. It knows to do that because it's using a relative cell reference. Okay, which we're gonna get a little bit more into later. But I also want you to notice that the text B seven within this formula is this Ah, little blue collar. And if you actually look at B seven, the cell is highlighted with that same blue color, and the same applies to see seven And this red color. Okay. And that's a neat little feature that Excel has so that you could remember which sells you're referencing in your formulas. Okay, Now, let's suppose we wanted to calculate our grand total, which is the total off all the values in column D. First of all, a quick waited to calculate your total is to highlight all of the cells that you want to calculate the total for, and then looking here at the bottom right hand corner and seeing where it says some. So, as you can see, the sum of all these cells that we have highlighted is 6254. It also provides us with account and with an average okay, so That's a neat little future, but let's suppose we actually wanted it on our works. Heat. Well, let's come over here to see a let's type grand total. And as you could see, a kind of, uh, bleeds over into column D. So let's try using what we just learned in our last lesson, which is a resized and double click in between C and D to do auto resize. So how do we calculate the sum of all these values? Well, one way to do it is toe add up each value individually, and that gives us 6254 OK, but there has to be an easier way to do it because what if you had 1000 rows and you had add up each cell individually? Well, luckily for us, Microsoft Excel has a built in function called the some function. Okay, so let's talk a little bit about functions first. So Microsoft Excel has a huge library of built in functions. Okay, now each function consists of two parts. You have the function name and you have the function arguments function. Arguments are enclosed by parentheses and are separated by commas. Okay, optional arguments are enclosed by brackets. Now, all of this might seem a little bit confusing right now, but it will make a lot more sense once we start looking at examples. So let's take a closer look at sea, some function. So the first thing we need to do is delete what we already have in D eight. Okay, Now let's initiate are some function by typing the equal sign and starting to type the function name. Okay. Now, as you could see as we typed in our function name, Microsoft Excel automatically populated. Ah, list off all of its built in functions that contain the characters s um OK, so we just need this 1st 1 some. So let's go ahead and double click on it. OK? Now Microsoft Excel is asking for its arguments. Okay, The first argument is number one, and the second argument is number two, but the second argument is optional. Okay, so let's begin with the first argument. Okay, which is sell D to the second argument is cell D three and then let's go down the list. Okay. Now let's end our function by closing out our parentheses, okay? And clicking on our hitting enter on the keyboard. Okay, Now, I wanted to show you something. I wanted to mess something up on purpose. Just like a show you. Let's suppose I forgot to close out my function with the close parentheses. Well, Microsoft Excel automatically closes it for us. Okay. It's ah, useful little feature that it has. Okay, but there we have it. We have our total 6000 254. OK, but what's an easier way to do this? Instead of having separate arguments for the separate cells, we could actually provide Excel with a cell range. Okay, So for the first argument, we're going to select the cell range D two through D seven. Close out our parentheses. Click enter. Okay. And that's our total. So another way we could do this is by using the auto some feature. Okay, so we could just go over here to this top right corner and click the auto some button, which is located on the home menu. OK? And then click enter. And that's the same as the function that we just typed in. Okay, now, the keyboard shortcut for auto some is all equal. Okay, so Let's go ahead and try that all equal. And it's the same as an auto some. All right. Now, how does it auto some work? Well on auto. Some calculates that some of everything above the cell that you're in up until it finds text or a blank cell. In this case, it found some text. Now, before I continue on, I wanted to go over something called the Order of Operations. So remember, in grade school, the old acronym, Pam Das Or please excuse, my dear And Sally? Well, Microsoft Excel actually uses that same exact order of operations. So the way it works is everything in parentheses gets calculated first. Okay. In Microsoft Excel, all the exponents get calculated. Second multiplication, third division, fourth addition, fifth subtraction. Six. So let's take a look at an example that uses the order of operations. Okay, so in column E, we want to calculate our total with tax, so the total with tax is gonna be your value and column D multiplied by one plus the sales tax, which in this case is 6% or 60.6 as represented by decimal form. Okay, so let's go ahead and try that now, As you can see, we completely screwed up this formula. Okay? All we did was we take. We took 990 multiplied that by one, which is still 990 added 9900.6 So what's the correct way to do this? While the correct way to do this is to take the one and the H one and wrap that in parentheses? Okay, that way Excel performs one plus h one first and then multiplies that by d two. Okay. And there we have it. That gives us our total with the tax. So let's try copying our formula from Cell E two and to sell eat three. Okay. And right off the bat, I know that this formula is wrong, OK? Because our total is 820. So how can the total with tax still be 820? Okay, so when I double click on this formula, what I see is that we're referencing cell H two when we're supposed to be referencing cell H one. OK, so why is it doing that? It's doing that because we used a relative cell reference. Okay, so let's delete this and go back into our original formula. Okay, so when we tell Excel one plus h one, what we're really telling Excel is we want to take one and add whatever value is three columns to the right and one row above the so that we're in. Okay, so when we copy this formula down, it's applying that same concept. OK, Cell H two is three columns to our right and one row above this cell that were in. Okay, So what we want to use instead of a relative cell reference is an absolute cell reference. Okay, so how do we use an absolute cell reference? We use the dollar symbol. Okay, so let's go back into our original formula. Okay, Well, we could do toe lock in cell. Each one has put a dollar sign before the H and a dollar sign before the one. Okay. Putting the dollar sign before the column, locks in that specific column and putting the dollar sign before the row locks in that specific row. Okay, so, technically, we don't even need to put the dollar sign before Theo h. We just needed for the row. But we'll do it anyways. Okay, So now when we copy our formula down, Okay, everything should be correct. Okay. And we'll be going to e seven. We see that everything is correct, and we're still referencing cell H one. Okay, Okay. So let's do another auto. Some here to get our grand total with tax. Okay, Now, let's go ahead and change one of these numbers. So let's come over here and change this. 68 to 68.1 Okay. Now, the reason I did that is because I want this toe have more than two decimal places, so I could show you a neat little function called the round function. Okay, so suppose I wanted to round this value to two decimal places. Why do that? By using the round function. Okay, so let's go ahead and initiate the round function. Now, the first argument is the number. Okay. What number do we want around what we want around e eight. Okay, now let's put a comma. Now it's asking how many digits? How many decimal places do we want around it to? Okay, so let's put two here because we want around it to two decimal places as we do with currency. Clothes are parentheses, and there you have it. That's our round function. There's also a built in function called the Random Function. And the way the random function works is Microsoft Excel randomly selects of value between zero and one. Okay, now there's also another random function called the reigned between function. Okay, and the way the rand between function works is it will automatically return a random integer between whichever into Cher's you enter. Okay, so let's say we want to return a random value between one and 10. Okay? First we type one followed by a comma. Then we type 10 okay? And it's automatically gonna pick a value between one and 10 okay? And it chose 10. But let's go ahead and try again. And you see, it changed the value to two. Now, there's also formula called the mod formula, which is the remainder formula. Okay, now, if you remember from early elementary school days, what the remainder is let's say you had 10 divided by three. Well, the alway we used to do it in elementary school was the answer to 10. Divided by three is three remainder one. Okay, so let's say we wanted to calculate the remainder. Okay, the first thing we would do is initiate our formula by typing m o D. Open our parentheses. Okay, then are our number is gonna be 10 and our divisor is gonna be three. And that should return one. Because the remainder for 10 divided by three is one. There's also the pie function which automatically returns pie. Okay, accurate to 15 digits. Again, there's no arguments for pie. It has a require any. There's also another function called the absolute value function. So suppose that we had a negative value for cell E eight. Okay, so over here, we have negative 6000 629. Okay, well, what's the absolute value of that? The absolute value is the same thing, except there's no negative symbol. Okay, so the formula for that is abs OK equals a B s. And then you had to provide the number that you want to get the absolute value for okay, which in this case is E 14. Okay, so there's also a function called the men function. And the way the men function works is it provides us with the minimum value of a specific range of cells. Okay, so if we want to find the minimum price in this range, that's all we have to do. And the minimum value within that range that we selected is 48. Okay, Obviously the max function is gonna be the exact opposite is going to provide us with the highest value within that range. There is also a square root function equals sq R t. OK, and let's suppose we wanted to find the square root of nine. Okay, Wilders type nine and it should return three. Now, these air just basic math functions that excel has. I want to get into more complex functions in later videos, okay? 5. Copy, Pasting, and Using Autofill: So congratulations, everyone. You made it to the final video off Section one of our Microsoft Excel tutorial. Now, this entire section is dedicated to copying, pasting and using auto fill in Microsoft Excel. These are three very important things that everyone needs to have a pretty good understanding of. So let's go ahead and dive right into it. Okay, So in this example and column A, we have our part number and column B. We have the quantity and column. See, we have the price and in column D we have our total Okay, in the total is just the quantity times the price. We learned how to copy this formula and the last example. Okay, by hitting control, see, selecting the cells that we want to paste the formula to and then hidden Control V. We also learned how to do an auto some, okay, And the keyboard shortcut for the auto. Some was all equal, and that gives us the grand total. Okay, now, let's suppose we wanted to copy and paste this grand total from these cells into a different cell. Well, the way we do that is first we highlight the cells that we want to copy. We hit control C on the keyboard. Okay, We come to the so that we want to copy it into. Okay. And then we hit control V on the keyboard. But wait, What happened? It's showing that the grand total is zero. OK, Now, the reason why the grand total is zero is because we copied the formula from cell D eight into cell H eight. Okay? And this formula is using a relative cell reference which we learned about in the previous video. So how do we copy and paste just the values without having to copy and paste the formulas? So, first of all, let's undo what we just did. By the way, the keyboard shortcut for undo is control and Z Okay, so let's hit control Z. Okay, now, this little dashed box that's rotating around the cells that we just copied that indicates that we just copied these cells. Okay, so to get out of that, you just hit the skate button on your keyboard. Okay, but let's go back into these cells, okay? And let's hit control C again. Okay, Now let's come back into cell G eight. And instead of hitting control V on the keyboard. Let's right click. And now we have a bunch of different paste options, while the option that we want to choose is paste values. Which is this little clipboard with the numbers 12 and three on the bottom, right hand corner. Okay, that's how we paste just the values. Now, if we go into this grand total, we see that it has the value of 15,435 rather than the formula that was in sell the eight. Okay, and that's exactly what we want. But you might have also noticed that it didn't copy and paste the format. Okay, you see the text and sell C eight is bold and the background and sell D eight is yellow. OK, so let's suppose we wanted to copy and paste the values and the formatting without cough without copying and pasting the formula's. Well, the way we do that is first we highlight the cells that we want to copy. We hit control C on the keyboard, same as before. We come to the cell that we want to paste it into we right click OK, and now there's actually a right arrow here that has mawr paste options in the option that we want to choose is this paste values and source formatting. Okay, so now we copied and pasted the values and the formatting without copying and pasting the formulas, which is exactly what we wanted to do. You also might have noticed that when I copied and pasted, there was already some some data in these cells. So when you when you paste into the cells that already have data, all that does is it paste over your existing data? Okay, so there's also an option to copy and paste your data, but to transpose your data whenever you paste it. Okay, so let's suppose we wanted to copy and paste these part numbers, okay, but we wanted it to display horizontally rather than vertically. Well, the way we do that is we highlight the cells that we want to copy. We hit control C same as before. Okay, now let's come to sell G five. Let's right click. And then there's an option right here that says transpose for your paste options. That's exactly what we want to do in this case, and that's how you transpose data Okay, So now that you're an expert in copying and pasting, let's talk a little bit about auto fill. So what is auto fill? Auto fill is a tool in Microsoft Excel that can be used to automatically fill in a series of data in your worksheet. This includes dates, numbers, text and formulas. So how do we use auto fill? Well, it's actually pretty easy. Okay, so first of all, let's clear out all of this data that we just copied and pasted. Okay? And I like to use this Claire all button in the top right hand corner, which clears out all of your text formulas and your formatting. Let's also get rid of this grand total. And let's get rid of the formulas. Andi Cells D three through d seven. Okay, Now, instead of copying and pasting, we could simply use auto fill. So if you hover over the bottom right hand corner of this cell, you'll see this little cross here pop up and you could use that cross here and click and drag it down to the cells that you want a pace to. Okay? And now excel pretty much did the copy and paste for us now. You can also do it without Dragon. The data. Instead, you could just hover over that bottom right hand corner and double click. So Excel automatically knew to go all the way down the list of parts up until we got to a blank row. Okay, Now you might notice that our rose go from row eight to Row 10 and Road nine is hidden. That's because I hit it on purpose because I wanted to show you guys an example of hiding and unhygienic Rose. So the way Ewan hide it is, you highlight the two rows that are before and after your hidden rose. Okay, you right. Click on that row and then you click on unhygienic. And that's how we get our hidden row. Now, the reason I added this road because I wanted to show you an example of what happens when you try to do auto fill. But there's a blank row in between. Okay, so when I do auto fill, it automatically stops at row eight. Because that's a blank. Rome. Okay, so if you wanted to do auto, fill all the way through all of your data, But you have some blank rows. You'll need to get rid of those rose. Now, auto fill can also be used to predict values. Okay, so what do I mean? Well, let's take a look at this sheet called auto fill types. And I have a whole bunch of different examples of how you can use auto fill. So in column A. Okay. When we dragged down this bottom right hand corner, it cycles through ever every single day of the week up until it starts back over at Monday . Okay, So Excel was smart enough to know that you were going through all the different days of the week. Okay, Now, when we use auto fill for the date right now, the date is January 1st, 2000 and 19. So as we drag it down, it adds a day for each row that we go down. Now we're using American format for dates where the month comes first, and then the day of the month comes second. So it's backwards if you're in Europe. Okay? Now it also recognizes that we're looking at the month of January. Okay, so when I use auto fill, it automatically cycles through all of the months of the year up until it starts back over at January. Okay? And it's even smart enough to know that you're using the abbreviation for January. Okay, so when I used auto fill, it's gonna use the abbreviations instead. Now, let's suppose we had a date, Okay? January 1st, 2000 and 19. And then the next date was February 1st, 2019. And for each row that we went down, we wanted to go down a month. Okay, well, look what happens when I try to drag down the bottom right hand corner. It's adding a day. Okay, which is not what we won't do. We want to add a month for every road that we go down. So the way that we do this is we highlight both cells. Okay? And now excel recognizes that we're going down one month for every road that we go down. So now that both cells are highlighted, we dragged down this bottom right hand corner. Okay? And excel is smart enough. Toe add a month. Okay. So for every road that we went down So we have a part number BRT dash one. Look what happens when we used auto fill for that. It adds one number after the dash for every road that we go down. Okay, now let's suppose we wanted to add 10. The same thing that we did for the month and column E. We need to highlight both of these cells, okay? And then used auto fill. And then, as you can see, it adds 10 after the dash for each row that we go down. Okay, so auto fill is extremely powerful and it could save you a lot of time. 6. Data 101: welcome to Section two of our Microsoft Excel tutorial. This section is basically a crash course on data. Okay, Now, this is a relatively shorter section when compared to the others. But I still feel like it's extremely important for every Marcus optic sell user to have a good understanding off relational data and to also know off the terminology related to data . So what is data? Well, that's easy. Data is essentially information. A database is essentially in organized collection of data or an organized collection of information. A database consists off tables and tables consist off records now record can be divided into different fields. Okay. And to get a better understanding of this, let's think off a database in the context of a Microsoft Excel workbook. So in this example, this workbook, which is named Data 101 represents a database, and each worksheet within this workbook represents different database tables. Now we go back to our first table, will notice that in this table we have nine different records. So think of a record as a row in our table. So, as I also mentioned earlier, each record is divided into different fields. Think of fields, as are different columns and Microsoft Excel. So in this example, we have six different fields. We have the employee I D. First name, last name, department location and hourly rate. Now, for our employee i d. Each employee I d is going to be unique because it's what's referred to as a unique identify, rare or primary key, which we're gonna get into in the next video. 7. Relational Data & Primary/Foreign Keys: Okay, so now that you know a little bit about data and the difference between tables, records and fields, let's talk a little bit about relations data. So what is Relational data? Well, it's exactly what it sounds like, data that somehow relates to each other. So in our last video, we finished off by mentioning that the employee I D Field for this table contained the primary key for this table. So what is a primary key? A primary key is a unique identify her for a database record. Most of the time, the primary key is a number, but it can also be text or a combination of numbers and text. So what do we mean by a unique identify? Well, let's look at employee number one. OK, employee number one is Kenneth Martin. Okay, his employees Ideas number one, which means that no other employees on this table can have an idea of number one. Sure, you could have another employee with the name Kenneth Martin, but you can't have another employee with an idea of one. Now, let's take a look at column E, which is the location field. Okay, so this first employee, his location is location number one. The second employees location is location number two. So what is this? This field represents a foreign key. So what is a foreign key? A foreign key is a reference to a primary key in another table. So can you take a guess at which table this foreign key is referencing? You? Got it. It's referencing the locations table. So the number one for this employees location represents the Omaha Finance and Accounting Office. Okay. In the locations table. This next employee Sandy, works at location number two. OK, location number two represents the Miami marketing office. Okay. And that's how primary and foreign keys work. 8. Formatting Cells: throw in this video, I'm going to talk about four mining cells now. Four men themselves come in two different things. On one hand, it has to do with the actual style of a cell. You know, things like fought font color, font size, background color. Whether or not you want borders around yourselves, all these things have to do with the actual style off a cell. Now, I'm not gonna cover any of these things because there are pretty easy to do in there. Pretty self explanatory. Now, on the other hand, form and excels also has to do with how Excel displays data from that cell. For instance, how does excel know what format to display a date as or whether or not you wanted to display a number as currency? All these things were gonna cover in this section, so a cell can be one of three different things. It could be a number. It could be text or it could be a bully in Don't worry. Too much about bullying is now. We'll get into that later on. Now, if a cell contains a number, that number could be formatted many different ways. It could be formatted as a date, it could be formatted as time as currency as a percentage as a fraction in scientific format. Those are just some examples of how a number could be formatted. Now you may be asking yourself, How can a number before matted as a date? For instance, let's say we had the number five. How do we convert the number five to a date? Will by default? The number one represents the date January 1st 1900 Microsoft Excel and the number two represents January 2nd 1900. So obviously the number five would represent January 5th 1900. So as of this recording, today's date is may 9th, 2000 and 19. Okay, and the number that represents today's date is 43,594. So that means that it's been 43,593 days since January 1st 1900. So now that we have dates covered, let's talk about time. Since the number one represents one day in the time of the day is represented by a fraction of one. For instance, the time 12 noon represents 12 out of the 24 hours in a day. So noone is expressed by the number 0.5 6 a.m. Represents six out of 24 hours of the day. So that's expressed as 240.25 and six PM OK, that represents 18 out of the 24 hours of the day. So that's expressed by the number 240.75 OK, now, what if we had the time 6:30 p.m. And we wanted to convert that to a number. Well, that would be expressed as 18.5 out of 24. Okay, which is 240.7708333 Okay, but enough of me explaining all this to you. Let me go ahead and show you an example. Okay, So in the following workbook, which is called formatting data, we have a worksheet called Dates and times. Okay. And in cell A two, we have the number 20.5 and we want to convert this number to a time. What? We already learned that 0.5 represents 12 PM so we already know this should give us 12 PM But how do we convert this to a time? Well, in the home menu bar, there's this section up here. OK, which is the number format section. Okay. And on this drop down list, we have a bunch of different number formats that we could choose for this cell. Okay, now we want to select the time. Okay, so it returned 12 PM OK, which is exactly what we expected. Now, if we apply the same formatting 2.75 it should give us 6 p.m. Which it does. Okay. Now, let's suppose we wanted to convert this number one to a date. Well, the way that we do that is we come over here and then we choose the short date, and then we see that it's January 1st 1900 then the number two should be January 2nd, 1900. Okay, Now you can also convert something to a date and time. So 1.5, what should that be? Well, that should. That should represent January 1st, 1900 12 p.m. Okay, So if we come to this drop down menu, how do we choose a date and time? Well, it's not one of the options available on this draft on this drop down list. So let's come over here. Toe mawr number formats. Okay. And now we have under the date category. We have a bunch of different formats that weaken convert the date to, and let's choose this one right here, which gives us the date and the time. Okay, Now, let's suppose that we wanted to take this number and sell F two and converted to a date and time, but we wanted to convert it to a very specific date. Time format. Okay, We want to convert it to the following format. Okay, We want leading zeros. Okay? For our month. We want leading zeros for a day, and then we want the full year. Okay? And then we want standard time, okay? With seconds being shown. Okay, so the way we do that is we come into this cell K and then from the drop down list, we select more number formats, and then we come over here to custom, okay? And we go ahead and we type the format that we want. And as you're typing it, you could see a little preview up here. And that's how you converted to a very specific date. Time format. OK, now, I provided everyone with a cheat sheet in this section that gives us all of the different abbreviations for all the different types of date time formats. So feel free to look at that so you could get a better understanding. But anyways, let's continue on with our example. And so g two, we have today's date and we want to convert this to a number. Well, we already determined that the number for today's date was 43,594. So if we come up here and we select general, we see exactly that. Now let's convert this date time as well. By using this general option, we see that it converts it to the same number as G two. But it also has the time, as expressed by a fraction now. Wanted to show you guys something called the Format Painter, which a lot of you may already be familiar with. The way it works is let's suppose that we really liked the format in So F two on DWI wanted to convert e to to that same date time format will. First we select F two. Then we come up here and we click on the format painter and then we select the cells that we want to convert to that format. OK, and you could select multiple cells. But I'm just going to select eat, too. And now you could see E two is the same exact date time format as F, too. But not only does the format painter copy the number format or the date format, but it also copies the style. OK, so let's say that sell E two was bold. And then we used the format painter and we applied it toe to Well, now F two is bold as well. Now I have another worksheet in here, but it's hidden because I wanted to show you how unhygienic worksheet. So if I select the worksheet, date and times and then I click on unhygienic, you see all the hidden worksheets pop up okay and let's select this sheet called other number formats. And now this worksheet is no longer hidden. Okay, so now I wanted to show you a bunch of different ways of how you conform at numbers. So in cell a two, we have the number 20.5 and we want to express this as a fraction. So let's come up here okay, and let's choose for action. And now we see it being expressed as a fraction. And Selby too, we have the number 0.25 and we want to express this as a percent. So 0.25 is the same as 25%. And there's actually a short cut right up here, 4% style, OK, and now we see it being expressed as 25% and see two. We have the number 3.45 and we want to express this as currency. Well, there's also a short cut up here for currency and there we have it now in so d two, we have a number with a bunch of decimal places and what we want to do is we want to Onley show two decimal places. So the way we do that is we come up here and there's a shortcut for decreased decimal, and every time you select that, okay, it decreases one decimal place. And to the left of that, we have this option for increased decimal. And every time we select this, it increases it one decimal place. Okay, so let's go ahead and only show two decimal places and that's a quick way to do that. Now there are other ways. Toe only show two decimal places and I'll show you that later on. Okay, Now in so e two, we have the number negative 38.3. And what we want to do is we want negative numbers, toe have parentheses around them, which is very common in accounting. Well, one way we could do this is we can come up here and choose this common style option. And the common style automatically puts parentheses around negative numbers. Now, there's also another way to do this, which I'll show you next. OK, now, in cell F two, we have this very large number, and we want to express it with Commons. So a quick way to do that is to choose that same common style option. Okay, but if you notice, it automatically adds two decimal places to the end of this number. Well, what if we didn't want those decimal places? Well, what we could do is we can come up here okay to this drop down menu and choose more number formats. And let's come over here to number. And now we see okay, decimal places to Let's change that to zero. OK, and now we see that it's using a 1000 separator, which is the comma, which is exactly what we wanted. So let's keep that selected. And now, in this part, it's asking us, how do we want to display our negative numbers? Okay, the first option is to actually have a negative sign and for it to be black. The second option is no negative sign and read. The third option is parentheses around the number, and the fourth option is parentheses around the number and read. Okay, so let's stick with the default. It doesn't even matter for this one, because this number is not even negative. Now, let's copy this format into G two, okay? And now this numbers being expressed with commas without the decimal places as well. And that's exactly what we want now. I wanted to cover one more thing. Let's suppose that an e two we wanted the negative numbers to be in parentheses, which it already is, but we also wanted it to be read whenever there's a negative number. So let's come back up here to more number formats. Let's choose number Okay, And now let's choose this last option here and now it's showing as red as well as within parentheses. So let's suppose we had more data and the rest of these cells, as you can see the format and sells E three and E Ford is totally different than the format and eat too well, why is that? This is because we never applied the format and e to to the rest of these cells. Okay, so quick way to apply it to the rest of the cells In column e is toe select e two. Okay, choose the format painter and then click on the column. And now the rest of the column is the former and that we wanted to be. So if we come into E five and we type up another number, another negative number will see that it's formatted the way we want it to be formatted. Okay, and these are just a few examples of how you conform at numbers 9. Conditional Formatting: Okay, now, this video is all about conditional formatting and conditional formatting is mainly a visual tool. Okay, let's say you had a group of students and you had all their grades in one column. And you wanted those grades to be color coded, For instance, every grade, that was from a 90 to 100. You wanted that to be green. And then every grade from 80 to 89 wanted blue. And, you know, so on down the list, all the all of the efs, you know, 59 below. You want those to be read? Well, the way you would do that would be by using conditional formatting. So let's go ahead and take a look. Okay, so here we have a list of a bunch of different students. Okay? And for this, for this particular class, these students took three tests, and there is a possible 300 points for for all three of these tests. Okay, So their grade is there total points divided by the total points possible. Okay, so that's their grades and column G. So, first of all, let's put this student lists in alphabetical order. Okay? So let's come over here to the data tab. And let's click on the sort A through Z button. Okay, Now, what we want to do is, um Let's express column G as a percent. Okay, so let's come to the home, Ted. And let's click on this little percent sign right here. Okay? Now being expressed as a percent, but let's go ahead and add a couple of dozen decimal places to the percent. Okay, so let's click on this button right here, increased decimal, and let's show two different decimal places. Okay? Now, I don't like how wide column F is because of cell F one, and I forgot to show you earlier, but I'm going to show you now that this wrap text feature. So if I come up here, okay. And click on this wrap text button. Now, if I make this column more narrow, you don't see it right now, but the text points is now below the word possible. Okay, So if I want to show that, let me come over here to between rose one and two and double click. OK, now you see possible points. It's being wrapped. Okay. And that's the wrap text feature. So let me double click on this to resize it. Okay, and now that that looks a lot better, okay? And now we want to start doing some conditional formatting. So the first kind of conditional formatting that I want to show you is color scales, and it's pretty easy to do so Let's highlight Column G. Okay, let's come over here to conditional formatting, which is under the home tab. Okay, And let's go to collar scales and let's choose this 1st 1 So the way this color scale works is the higher the grade on this list, the more green the background is going to be and the lower the grade. Like for this 50% here, the more read the backgrounds gonna be. And you know, like the 86 that's a green. But it's a lighter green, and kind of the ones in the middle are like a yellow or like an orangish. So this is a really quick and easy way to do a color scale, and it's something that I use all the time. Now there's a bunch of other default color scales that you can use. So if you highlight column G and you come back to conditional formatting and collar scales . As you hover over each of these different color scales, you get a preview, and you can also customize your color scale. So if you come over here to more rules, OK, Now you can put your settings as to how you want your color scale to be applied, and this is all pretty self explanatory. So I'm not gonna walk you through it, but feel free to mess around with it. But I like to stick with the main green, yellow red color scale. So let's click. Cancel. Okay, Now let's suppose that we have test one Test two and test three. And we wanted to highlight every single test that was below a 60. So 59 below. We want those to be highlighted Red, just so we could kind of visually see off the bad test scores. Well, way we could do that is if we highlight all of these columns here. Okay? Which I already did. And we come here to conditional formatting, right? And now we come to highlight cell rules, okay? And we want to highlight all of the cells that are less than so Let's click here on less than okay and we want we want to form ourselves that are less than and right now it's set at 72. But let's set it for 60. So anything less than the 60 is gonna be highlighted, OK? And what do we want to highlight it with? We want to highlight it with light red fill with dark red techs. OK, we could also come here to custom format if we don't want any of these default ones. But let's stick with the light red fill with dark red text. Okay? And let's click. OK, and now we see all the bad test scores being highlighted. But there's something else that I wanted to point now when we when we told Microsoft IQ so that we want to highlight all of those cells with a value of less than 60 it ended up highlighting off the blank cells. Okay, so let's try to work around this. Okay, so let's highlight columns B through D again. And let's create a new rule. Okay, so we click on conditional formatting care and let's choose this new role option and let's for the rule type, select this rule type right here. Format Onley cells that contain. And now, where it's asking for Matt on Lee cells with Let's Choose blanks, OK, and now let's click on OK, but it's still formatting these cells with that same red color. There's something else that we need to do. So let's come back to conditional for Matic and let's click on manage rules. OK, and now we see this this rule that we just created. But what we need to do is we need to click on this check box that says, Stop if true. And now let's click on Okay and now are conditional Formatting is being applied correctly. Now let's also suppose that we want in all of the A grades, So a 90 above we wanted that to be green, so let's go ahead and do some conditional formatting. So we have columns beat through D highlighted. Now let's come up here to conditional formatting. Let's go to highlight so rules Okay, and then let's choose greater than and right now it's set at 72 but let's change that to an 89. So we want anything greater than 89 okay, and what do we want to format it with. We want to format it with green filled with dark green text. Okay, okay. And now all of the A grades are being highlighted green. Now you might notice that the test scores, for some crazy reason, are also being highlighted with that same green color. So let's fix that. So first of all, let's highlight columns B through D. Okay, let's come back to conditional formatting and let's come here to new rule. OK, and now let's say format Onley cells that contain. But let's put specific text. And for that specific text, let's put test, Okay, because all these headers have the text test in it. OK, and now for the for the format, let's leave it as blank and let's click. OK, but it's still green, so there's one more thing that we have to do. Let's come back to conditional formatting. Let's go to manage rules and let's check this box right here, where it says stop if true, same way as we did before. Now let's click, apply and okay, and now everything is how we want it to be. Now let's suppose there's one more rule that we want to apply. We want all of the bees. So from an 82 89 we want all of those cells toe have a blue background. So let's re highlight columns beat through D. Let's come to conditional formatting. Let's choose highlights so rules. And now we want to choose between. And we want our lower value to be 80 and our upper value to being 89. And what do we want to format it with? Well, we don't want any of these default options, So let's come over here to custom format. And now let's choose the fill option. And now let's select this blue color right here. Click OK and let's click. OK, and now you see that formatting being applied to all of the B grades? But let's suppose that we don't like that formatting. We think that the background is too dark and the red text is no good. So let's let's come back to conditional formatting and let's click on manage rules and now we see that role that we just created. Let's double click on that and let's come back to this option right here where it says format and let's choose a lighter blue color and under fought Let's choose black instead of that that red color. Now let's click. OK, OK, and OK, one more time and now it's being formatted how we wanted to. 10. Data Validation: Now, let's talk a little bit about data validation. Now, I'm gonna try to keep this section shortened Sweet, because, honestly, I feel like data validation is a little bit overrated. It's not really something that I use that often, but that being said, you should still understand what it is and how it works and how to use it. And who knows, you might end up using it more than I do. Okay, so here we have a list of employees. Okay, this is under the data validation file on the employees tab. Okay, But anyways, let's suppose that Sandy is our secretary and one of Sandy's duties is toe update the spreadsheet every time that we hire a new employee. But we want to make sure that Sandy is entering all of this information incorrectly. For instance, we want to make sure that when she puts in an employee, I d. That the employee I d. Is a positive whole number. We want to make sure that the first and last names, you know, don't exceed a certain amount of characters. Also, we want to make sure that Sandy Onley enters valid department names. That way she doesn't misspell it or enter department that doesn't even exist. So we could use data validation for that. And, you know, let's suppose that minimum wage was 8 25 We could use data validation to make sure that she doesn't enter an hourly rate below 8 25 And you could even use data validation for dates. You know, let's suppose that the business opened on january 6th, 2016. Well, you can use data validation to make sure that Sandy can't enter a date before that. Okay, so let's start off with the higher date. Now, the way that it works is whatever sells you have selected. That's what the data validation is gonna be applied to. Now, I usually like to select the entire column. Okay, this insurers that, you know, no matter how many records we add that the data validation is still gonna be intact. So if you go to the data tab, there's this little icon. Okay? If you hover over it, you'll see data validation. Okay, so let's click on data validation Now. It's asking us the validation criteria. Okay, so we want the criteria to be a date, and we want to make sure that date is between January 6 2016 which we said was the start date. And then for the end date. We could just put some arbitrary time in the future. And then let's click. OK, so now if I try to enter some text here, I get an error message. If I try to enter an earlier date, I also get an error message. Okay, but if I enter a valid date, it works for me. Now, let's say we don't like this default error message. Okay, Well, we can change that. If you re highlight the column, come back into data validation under the tab Error alert. There's an option to change your error message. Okay. And now we see that error message. You can also add an input message and I'll show you how that works. Okay, so let's highlight column f Let's come over here to data validation and let's go toe input message and let's put a little tip here. Okay, So now every time that you select a cell, you see that little tip pop up. Okay, Now, let's do some data. Validation for column E. Okay, so we want to choose a decimal, not a whole number of decimal. And we want to make sure that it's greater than 8 25 which we said with the minimum wage. So now if we want to be cheap and pay the employees $5 an hour, we see this error message pop up. Okay, but if we select a valid hourly rate, it goes through for us. Okay, now column D. We're gonna get to later on. But let's look at columns B and C. OK, now, we could actually highlight both of these columns because we want the same data validation rules to apply to both. Okay, So if we come to data validation, let's select text length and we want to make sure that it's between one and 50 characters. Okay, Now, if we try to enter a name that's over 50 characters, we get an error message. Okay, But let's try a valid name and it works for us, Okay? Okay. And what did we say about column A. We said that we wanted it to be a positive whole number, so let's come appear to data validation. OK, And now let's choose whole number. And we wanted to be between one and and one million. Let's say so. If I try to answer a zero or let's say two million, I get an error message. Okay, but if I enter 10 that it works for me. Okay, Now for column D we want to make sure there were only entering valid department names. So the first thing we need to do is make a list off all the different department names. OK, so first, let's copy this onto sheet, too. Okay? And now we have our list, but we have duplicate records, as you can see. So I want to show you something called the Remove Duplicates tool. So first we select the column, we come up here and it's actually right next to the data validation button. It might be a little bit different on your version of Excel, but here it is, the remove duplicates button. And if we select it and we hit okay, we get rid of all the duplicate records. Okay, so now that we have the list of different departments, let's go back into our employees worksheet. Okay, now let's highlight column D and click on data validation now for the validation criteria. We want to select list. OK, and now it's asking us for the source data. Well, if we if we select this little icon right here, it allows us to select the source data. OK, so let's select sells a two through a seven if we click, enter and click, Enter one more time. Okay. Now the data validation is being applied to this column. So if we come into cell D 12 here, Okay, Now we could choose from a list of all the different departments. However, if you wanted to add another department. Okay, so let's add a, um, logistics department. Now, if we come back here into this drop down list, we don't see that new department, the logistics Department, because we only select it sells a two through a seven under our data validation criteria. Okay, so we'll have to go in and change that. Let's change that. A eight. So now when we come back here, we see the logistics department. Now there is kind of a a work around for this. There's actually many different workarounds, but I'm going to show you one of them. Okay, Instead of adding the department to the bottom of this list added to the middle. So come up to Row five and click insert, and now it inserts a row. So if we put a new department name, let's call this shipping. That should do the trick. And there you see it shipping. So another thing you might have noticed is that this list is not in alphabetical order. Okay, so in order to put this in alphabetical order will have to change the actual source list. OK, so let's come back to the list and let's click on Cell A To. And then let's click this little sort button right here, sort A to Z. And this is also on the data validation tab, and that puts everything in alphabetical order. So now when we come back to the employees tab, okay and we click on this drop down list, it's It's also in alphabetical order 11. Intro to Cleaning Data: Okay, so this section is all about cleaning data now. A lot of the time, we work with very, very large amounts of data that come from various different sources. It could come from another spread sheet that comes from a co worker. It could come from a bank statement from QuickBooks from different point of sale systems, or maybe even straight from the Web. And on many instances, this data needs to be cleaned up. So what do I mean by cleaned up? Well, it could mean a number of different things. Maybe the dates are in the wrong format. Maybe all of the data is in one column, and it needs to be separated into different columns. Perhaps the numbers are in a text format, but they need to be converted to numbers. Maybe there's certain characters that need to be removed from text. Maybe certain text needs to be combined with other texts. Like if you have someone's first name and last name, maybe you want to combine them. So Microsoft Excel provides us with a number of different tools and functions that allow us to easily cleanup data. OK, so you could get the data into the exact format that you needed to be in by using these tools. So first, let's go over the text to columns tool. 12. Text to Columns Feature: So here we have a work she called NFL Weekly Data, and this is in the text to Columns Workbook. OK, and in this worksheet we have a bunch of fantasy football stats and just to give you a little bit of background, okay, I do a lot of work with Sports Analytics and on every Monday during the NFL football season , I pulled this information from a Web page. But when I pull it from the Web page, it's in this weird, funky format where everything is in one column. But if you look closer, you'll realize that there's actually multiple fields in this data set, and each of these fields is separated by semi Coghlan's. So what we want to do is we want to separate this into different columns, and in order to do that, we're gonna use the text to columns feature. Okay, so first thing we need to do is we need to highlight column A. Okay, then we need to come up here to the data tab, and we need to click on text to columns, and now it's asking us to choose the file type that best describes your data. We want to keep it on the limited. So let's click on next. OK? And now here under de limiters. Let's select semi Colon and let's uncheck tab. Okay. And now you see a little data preview here on the bottom, and you're seeing now that it's separating it into separate columns, Okay? And that's all we need to do now. So we could click on finish, and now everything is separated into separate columns. Okay, which is exactly what we wanted to do. 13. Filtering Data: So now that everything is in separate columns, I want to show you the filter feature. So, first of all, what is date of filtering data filtering is the process of choosing a smaller part of your data set and using that subset for viewing or analysis. So in this example, we have a bunch of fancy football scores for every player an NFL for a particular week. So in column A, we have the player name and column B. We have that players position. So when this first example, his position is Q B, which means quarterback in column. See, we have the team that this player plays for. So in this first example, Patrick Mahomes plays for K A N, which is the Kansas City Chiefs and column D. We have the opponent. Okay, so this is who this player went up against. In this particular example. He went up against the Oakland Raiders and column E. We have home slash away. So this means Was his player at home or was he away in this example? He was at home. That's what the H stands for and column F. We have the salary. OK, that's a fantasy football thing. Don't worry too much about that right now. And column G. We have the points. Okay, this is how Maney fantasy points this player scored. Okay, in this particular game and in column age, we have the date. Now you might notice that this date is in some weird format where the year's first and then there's a space. And then there's the month and a dash, and then the day. So we'll fix that later on with some of the things that we learned in this section. But anyways, back to data filtering. So let's suppose that we wanted to filter this data so that we're on Lee looking at quarterbacks. Well, we're gonna do this by using a date of filters. So first, let's make these headers bold. Okay, Control B is the short cut. OK, and now let's apply of filter. So in order to apply of filter, click on any cell in your data set, okay, and you come up here to data and then you click the filter button, and that turns your filter on, and then you could you could select it again to turn off your filter. By the way, the keyboard shortcut for this is control shift out. So let's type control shipped l That turns the filter on, and then we could type control shipped l again, and that turns it off. But I want him to point something out if you have any blank rows in your data set while your filter is going to stop right there. Okay. So if I try to apply a filter to this data, what you see is that it stops right where there's a blank row. So in order to work around this first, let's turn off our filter. And now we need to highlight our entire data set. Okay, so let's come all the way to the bottom of this data set and make sure everything selected . And now let's choose Filter. And now, as you can see, our entire data set is ready to be filtered. So what do we say we wanted to do? We said that we only wanted to see quarterbacks. Okay, so we need to apply a filter to column B. Let me expand that. Okay, so let's select this little option right here, and let's de select all okay. And now let's chooses Q B option. That's put a check mark right there and now, As you can see, our entire data set is being filtered. And the only records weaken, see are the records that have Q B as their position. Now let's suppose we only wanted to view quarterbacks who scored over 20 points in column G . Okay, so let's keep the existent filter on and let's come over here to column G. Okay, let's click on this option. And now let's go. This part that says number filters and let's choose greater than or equal to. And let's let's type in here 20. So now it's on Lee showing all the quarterbacks who scored more than 20 points now if we wanted to unfiltered this data so that we can see the entire data set again First we could come up here okay, and we could click on select all again, and that turns off our filter for column G. But now we still have the filter on for column Be OK, so we need to come over here and we need to select all for this one as well. And now there's no longer of filter on all of our data. OK, now let's suppose we only wanted to see running backs who scored more than 20 points. Okay, so let's come over here to position. Let's de select all okay and let's choose our be. And now let's come over here to column G number filters and then let's put greater than or equal to 20. And now you can see there's, ah, much smaller subset of data for running backs who scored more than 20 points. Now there's another way we can unfiltered this data, and that's to come up here okay by the filter button and click this Claire button, and that clears out all of the existing filters. Now you can basically filter for any criteria you can think of. So let's say we only wanted to, for some reason, see guys named Mike. Well, let's come over here to text filters and then let's come down here to contains okay, and then let's put Mike. And as you can see, it's being filtered for guys with the name Mike, and that's how you filter data and Microsoft IQ. So 14. Concatenation 101: Okay, now, this video is all about contamination. Now, I know it sounds like a big fancy word, but concatenation is actually a pretty simple concept. So what is concatenation? Concatenation is the process of joining two or more text strings together into one text string. Okay, so here we have a bunch of names and addresses. This is in the concatenation file and in column G. What we want to do is we want to combine the first name and the last name. What? We're gonna do this by using the concatenation function. So let's begin typing the function. And now you see a pop upper here. So let's choose this 1st 1 can cat. And now it's asking us for the first text string. Will. The first text string is gonna be the first name. OK, And now let's type a comma. And now it's asking us for the second text string, so that's gonna be the last name. Now let's close our parentheses and let's see what happens. So as you can see a combined the first name in the last name. But there's no space between the two text strings. So let's come back into our formula. And right after a two, we're gonna want to put a space so that space needs to be in quotations. Okay, and then let's put another comma. And now let's hit Enter. And now we see that it's putting the space between the first name and the last name. Now there's also another way to do concatenation without using the can cat formula. So let's come back into G two. Let's clear out the formula. Let's type up a new formula. And instead of using the King Cat formula, we're gonna use ampersand and I'll show you how it works. So let's select a two, followed by an ampersand followed by the space, which is in quotations followed by another ampersand. And then let's choose the last name Let's hit Enter. And that's pretty much the same exact thing as using the concatenation formula. Okay, so let's do an auto fill to copy these formulas down and let's auto size this column. Okay, Now let's come into Cell H two over here and what we want to do for H two. What we want to concoct innate the address City state And is it that way we get the full address. Okay, so we're gonna use the concatenation function for this Now, the first text string is gonna be the dress. The second text string is gonna be a comma followed by a space because we want a comma and a space after the street address. Okay, now, the third text string is going to be the city followed by another comma and space followed by the state followed by just a space. OK, we don't want a comma after the state We just want to space okay? And then we're gonna We're gonna add the zip Let's close our parentheses. And if I did this correctly, this should work out and it did. Okay. As you can see, we have a comma in a space after the street address, and then we have another comma in a space after the city name. So let's copy this formula down and expand this column. And now we could see we have the full address in column H But there's one more thing that I want to show you. Let's suppose that we didn't want all this information on one single line. Let's suppose that we wanted a line break right after the street address. Okay, well, we're gonna use HR 10 in order to do this. So let's come back into this formula and let's get rid of this comma followed by a space. And let's replace this with a Chart 10 and a chart. 10 is basically a line break. Now let's click. Enter now. There's still not a line break because we need to use a wrap text on this in order to see the line break. So let's come up here to wrap text, and now you can see that the street address in the in the city state and zip are on separate lines. Okay, so let's copy this formula down, and now you can see that it's being applied correctly for all of these other addresses. So you see, concatenation sounds like a fancy word, but it's pretty easy 15. Left(), Right(), Mid(), Find(), and Len() Functions: in this video, I'm gonna go over five different functions in Excel. The left right, mid find and lend functions. These air five very important functions that I use all the time. They allow us to extract a smaller sub string of text from a larger string of text. So let's take a look. So here we have a bunch of phone numbers. This is in the phone numbers worksheet in the file named Left right mid find Len and I apologize for my unoriginal file names. So let's come over here and to Selby to what we want to do and be too. We want to extract the area code from eight to okay, in the area. Code is the 1st 3 digits of the phone number. So in order to do that, we're gonna use the left function. So let's begin by typing up the left function. And now the first argument is the text. What texts are we referring to? What we're referring to? Sell a two and now how many characters from the left do we want to extract? So that's gonna be three. So let's come in. Copy this formula down. And now we have all of our area codes and column B. Now I'm gonna show you the right function. So let's come over here to the city state Zip worksheet. So in cell C two, we want to extract zip code from B to. Well, the ZIP code is always gonna be the last five characters of this text Oring. So we're gonna use a right function. So let's come over here and let's begin by typing are right function. And now the text that we're referring to is Selby to, and the number of characters is going to be five because there's always five digits in an area code. So let's close our parentheses and let's hit enter. And now we have our zip code. So with the left function, you're extracting characters from the left side of the string with the right function. You're extracting characters from the right side of this string. So obviously, now, with the mid function, we're gonna be extracting characters from the middle of the string. So let's go back into the worksheet called phone numbers. Okay, Now, let's suppose for some reason that we wanted to extract the middle three numbers within this phone number while in order to do that, we're gonna use the mid function. So let's come over here to sell C one and let's create a header. And let's use the format painter to copy the format from B one. Now let's come into C two and let's initiate our mid function. Now the first thing that it's asking us for is that text. So that's gonna be a to now it's asking us for the start number. So in this particular case, the start number is gonna be five. Okay, why is it five? Well, the number that we want to start from its five characters into this drink because the 1st 4 characters of the string are the area code, followed by the dash. So the start number is gonna be five. And now the number of characters is going to be three because it's asking us how many characters from this point do we want to extract. Okay, now let's close our parentheses, and now you can see that is extracting the middle three numbers, which is 861 So let's copy this formula down. And also let's use a format painter, and that's how you use the mid function. Now let's look at the find function. So let's come back into the city state. Zip worksheet. Now let's take a look at Selby to let's suppose we wanted to figure out how many characters into this text ring the comma appears. What we can do that by using the find function and I'll show you what What the whole point of even doing that is later on in this video. But let's come over here into D one and let's type up our header. Let's use a format painter as well. Okay, now let's come over here into D two and let's initiate our find function. Okay, now it's asking us what text do we want to find? Okay, what? We want to find the comma. So let's put the commas in quotes now. It's asking us within text within what text do we want to find the comma? Well, that's gonna be Selby, too. And now the start number is optional. It's saying, at what point of this text do you want to start this function? But we could leave that blank. And now, as you can see, the calm appears 13 characters into this text ring. So let's come over here and copy the formula down. And also let's use a format painter to give it that blue background. Okay, now what the hell was the whole point of doing that? Well, let's suppose that we wanted to extract the city name from this entire text. Oring. What? We know that the city name is everything that's left off the comma so we can combine a left function with the find function in order to extract the city name. So let's come over here. And two e one, Let's type up a header. Let's use the format painter and now let's come into E two and initiate Are Left Function. Now it's asking us for the text. Okay, so the text is gonna be Selby, too. Now, from how many characters to the left of this text Oring do we want to extract? Well, we want to extract everything that's left of the comma, and we know that the comma in this case is 13 characters into this text. So let's choose D two for the number of characters. Now let's close out our parentheses now. Something is going to be wrong. Watch as you can see it extracted not only the city name, but also the comma. So let's go back into our formula. And now where it says the number of characters. Okay, let's say de tu minus one, okay? And let's copy this formula down. And now we have all of the city names and column E. But now we have this column D in here, which looks kind of funny. So let's get rid of this. But wait. Look, what happens when we got rid of this? Now, this formula is trying to reference a cell that doesn't even exist anymore. Okay, so let's get rid of these formulas. And now what we're gonna do is we're gonna do a nested function. We're going to do a fine function within a left function. So So let me show you how this works. So now it's asking us for the text that's still gonna be be to, but for the number of characters for that, we're gonna use the find function. Okay, So what text do we want to find? We want to find the comma. Okay. And now it's asking us within text Within what text do we want to find the comma. Well, that's gonna be be to still. And now let's close out our parentheses. But that's going to give us the number of characters where the common starts. So we need to subtract one from that. Okay, now let's close out our left function. So let's copy this formula down to get all of the different city names. And that takes me to our last function of this video, which is the land function so you can use the land function to tell you how many characters are in a specific string. So let's come over here into our work. She caught part number now on Cell A two. We have a part number. Let's suppose that and Selby to we wanted to figure out how many characters were. We're in this part number. Okay, so let's type up the land function, Okay? And now it's asking us for the text, so that's gonna be sell a two. Let's close our function, and that's pretty much it. So now this is telling us that there's nine characters and sell a to. Now, if you notice all of these part numbers, have the characters dash F T R at the end of them. So let's suppose that we wanted to strip these characters out. Well, the way we would do that is we would use ah, left function in combination with the land functions. So let me show you how this works. So let's come back into B two and let's clear out the formula and let's initiate our left function. Now the text is gonna be sell a to now it's asking us for the number of characters. Well, the number of characters is gonna be the entire length off. Sell A to minus four. So let's type up our land function. Okay? And now the text that we're referring to sell a two week lows out of our land function. And now we're going to say, minus four. Let's close out our parentheses. And now we have the part number without the dash f tr. So let's copy this formula down. And now we have successfully implemented a nested left and lend function 16. Substitute() and Replace() Functions: Okay, Now I want to go over the substitute and replace functions. Okay, so let's take a look at cell a two. Let's suppose that in this cube, okay, sq is another word for, like, a part number. And let's suppose for some reason, we wanted to replace the dashes with an asterisk. Okay. What? We can use the substitute formula in order to do this. So let's go over here and to sell beat two and let's begin by typing the substitute function. And now you see it pops up. Okay, Now, the first argument is the text. What text are we referring to? That's gonna be a to now, what's the old text? The old text is gonna be the dash. OK, that's what we want to get rid of. And now the new text. Well, that's gonna be an asterisk, because we want to replace the dashes with Asterix. Okay. And now the instance number. That's an optional argument. I'll get I'll get into that next. But we can close out this function and it should work. And as you can see, it did work. It replaced the dashes with Asterix. Okay, so I mentioned the instance number. Let's suppose for some reason that we only wanted to replace the second dash. So let's come into Selby, too. And let's type up our substitute function, okay? And it's gonna be the same as before. Except now. For the instance Number. We only want to replace the second instance of the dash. We don't want to replace the first instance of the dash, so we're gonna type two here. Now let's close out of our parentheses. And as you can see it on, Lee changed the second instance of the dash into an Asterix, which is exactly what we wanted it to do. But let's get rid of that. And let's just keep the original function. Okay, let's do an auto fill to copy that down. And now let's come over here into into Cell C two and let's two of replace function. Now replace function works a little bit differently than a substitute function. Okay, so let's suppose that the 1st 3 characters okay these 1st 3 letters we didn't need those, and we wanted to replace that with the letters are Excel for Ready Excel? Of course. Well, let's let's do replace function for that so let's begin by typing up the replace function. Okay, and now it's asking us for the old text. That's gonna be a to now it's asking us for the start number. So at what point do we want to start replacing text? Well, we want to replace the very beginning. Okay, the 1st 3 letters. So we're going to say, for start number, we're going to say one. And now the number of characters, How many of those characters do we want to replace? What we're going to say? Three. Okay, because we want to replace the 1st 3 characters. And now the new text means What do we want to replace it with? So we're going to say our excel here, Let's close out of our function. And now, as you can see, it replaced the 1st 3 characters with our Excel. So let's do an auto fill. Now let's come over here into D two and let's suppose that we didn't want to replace the 1st 3 characters. We wanted to replace the Middle three numbers here. What we can use replace formula for that as well. So let's come and let's type up our replace function. Okay, now, the old text is gonna be a to now. This time the start number is going to be five. Because the middle three digits. Here they start at the fifth character. So let's type five now. How many of those characters from this point do we want to replace what we want to replace ? Three. And now the new text is gonna be our ex cell. Now let's close out of our function now There we have it replaced the middle three numbers with the characters are itself. So let's do an auto fill and that's how you do substitute and replace for. 17. Upper(), Lower(), and Proper() Functions: Okay, So in this video, I'm gonna go over three very simple functions. The upper lower and proper functions. So and so a two. We have some text here. And let's suppose that we want in all of this text to be upper case. What? We can do that by using the upper function. So let's come over here to be, too. And let's type up upper. And now the function pops up, Let's click on it. And now it's asking for the text. Well, that's gonna be so a to let's close out the text. And now everything is upper case. Now the lower function works. The same exact way is the upper function. Except it makes everything lower case. So let's go ahead and type up lower. Okay? And now the text, same thing a to. And now everything is lower case. Now, the proper function, the way this works is it makes the first letter of every ward upper case and the rest lower case. Okay, so here we have a name or here, Paul Johnson. The J in Johnson is not upper case, but then there's an upper case H right here. You know, this could be a typo or something like that. So let's come into e two and let's type up proper. And now let's refer to sell d to. And now the name Paul Johnson is being capitalized correctly. 18. Putting it all Together: So now we're gonna take everything that we just learned in this section and use it in order to clean up a large set of data. Let's take a look at the NFL Weekly data worksheet in the altogether file. So you may remember this data set from the filter video, but there's a couple of things that are wrong with this data. First of all, the players name is in an incorrect format. It has it with the players last name first, followed by comma, followed by his first name, and we need to change that into a normal format. Second of all, the team and opponent those those abbreviations for the different teams are all lower case , but we needed to be upper case. And lastly, the date is in this weird format that I talked about earlier, and it's actually not ah, recognized date by Microsoft Excel, so I'll show you how to get that into recognisable date format. So first of all, let's copy these headers directly to the right of this data set. Now, I already did that and I have it hidden. Here's let me unhygienic that so in So I two we want the players full name. However, if you look and sell a two okay, you'll see that it's currently in the format. Last name, comma, first name. But we wanted to be in the format First name space, last name followed by any suffixes like, for instance, this guy's the second so that suffix will also go on. And we'll, in order to do that, we're going to need to use some of the formulas that we just learned. So the first thing that we're gonna need to do is we're gonna need to extract just the first name. Well, in order to extract just the first name, we're gonna have to use a mid function. So let's begin by typing the function. Now it's asking for the text. So the text is gonna be a to now it's asking for the start number. Well, for the start number, we want to find the comma. Okay, so let's do a fine function. So now it's asking what text doesn't want us to find, what we want to find the comma. And we can't even put a space in there, too, because there's always a space after the comma. It doesn't make a difference. Okay, Now is asking within what text. So that's gonna be sell a to okay. And now we close out the function. That's the start number as to where the comma starts. But the actual first name isn't going to start until two characters after that. Because you have you have a comma and in a space. So we need to add to to this. Okay. And now it's finally asking us number of characters. Well, for this, we could just put, like, 100. Okay, that way we we make sure that it covers enough to cover the person's full name and let's close out of the mid function. And if I did this correctly, we should have successfully extracted the players first name. And we did. You could see Patrick, but we're not done. We still need to extract the players last name. So let's come back in tow. I two and let's do a concatenation by using the ampersand which we learned about earlier. So now we want to extract the last name. Well, how are we gonna do that? We're gonna extract all the text that's left of the Kama. So let's do a left function. Okay, now that it's hex is gonna be a to and the number of characters we're gonna use the find function for that. Now it's asking, what text do we want to find? What we want to find the comma and the space doesn't make a difference within what text will within a two. Okay, And then that last argument is optional so we could close out the find function. And now we want to subtract one from this because if we don't, then it's gonna include the comma and let's close out the function. And if I did this correctly, it should be showing the players first name and last name, and it did. But we're gonna also have to add a space okay between the first name and the last name. So let's come right over here after the first name. Let's add another and percent and let's put a space within quotations and let's hit enter. And now we have the first name, followed by the last name. Now for the position. We don't have to do anything to that. So let's just reference Selby to with a simple formula before the team and opponent we said that we wanted that to be upper case. So let's use the upper formula. Okay? And now the text. We're referring to his cell C two and let's close out the function. Now. I want to show you something. We can use the auto fill to drag this over to the right. And since we're using a relative cell reference which we learned about in a previous section, excel new to reference the cell. That's one column to the right Now for the home salary in points per game cells. We don't need to do anything for them. So we could just reference those cells directly. And we can do an auto fill for these as well now for the date we wanted to be in the following format. So if you come into Cell H two, you'll notice the year comes first, followed by space, followed by the month and then a dash and then the day of the month. So we're gonna have to get creative now the 12-30 that's actually already in the format that we need. So we need to extract just that part of the text. Oring. Well, how can we do that. We know that that part of the text string always comes after the space. So let's come over here and to sell p two and let's initiate a mid function. Now it's asking us for the text that's gonna be sell H two. Now it's asking us for the start number, while the start number is gonna be one character after this space. So let's use a find function to find where that space occurs. So let's type up the find function and now find what text? What? We want to find the space. Now it's asking us within what text was going to sell h two. Let's close out of the function, Okay, but we also want to add one to that because we want to begin one character after the space and for the number of characters we could just put 10 here and now we have the month followed by the day of the month, but we also need to add the year, so let's come back into this formula. Okay, let's come up here to the Formula Bar and let's add an ampersand in order to contaminate the year and now we want to extract the year while the years all always gonna be the 1st 4 characters. So let's use a left function. What text? I'll be referring to Sell H two and for the number of characters. It's gonna be four. Okay, and now we have the month followed by the day of the month, followed by the year now. We also need to add a dash after the day of the month. So let's come back here into this formula. Let's add the dash and let's add another amber sane. And now we have the date in the format that we needed to be. Now, I wanted to point something out, though. If we come up here and expand this column, we see that this date is aligned to the left of the column. That means that Excel still recognises this as text, so we want Excel to recognize this as an actual date. Well, a little trick that I've learned that allows excel to recognize this as an actual date. If we come up here into the Formula bar and we wrap this entire formula in parentheses and then we multiply that by one now you can see excels. Recognizing this as a number. And if you remember from our earlier videos, this number actually represents a date. So if we come up here, okay and we change the format to short date, you'll see the date that that number represents. Now, let's do an auto fill to copy down all of these formulas. Now, we have nice, clean data that we can work with, and the benefit of doing that is now, if I wanted to, I could copy this weekly data into the yearly data worksheet. Okay, so let's hit control C. Let's come into the yearly data worksheet. Let's come to the bottom of the data set by hit in control and in the down arrow. Okay, lets go down. One more cell and let's do a paste. Special values here. Okay? And now we added the weekly data to the yearly data and everything is consistent. And one of the benefits of fixing that date is now. If we wanted to, we could filter by date. So let's come up to the top. Okay, let's do a filter by hitting control shift L. And if you come over to the date column, you'll see that it recognizes the date, so if you wanted to, you could filter by year. You can filter by month, or you can even filter by the day, and that's the benefit of cleaning your data. 19. Intro to Advanced Functions: So in this section, I'm gonna teach you a bunch of different advanced functions, and I use the term advanced very reluctantly because these are all pretty simple functions once you get the hang of them. So by the end of this section, you'll know how to do the lookups index matches if statements and other conditional functions, and we'll even get into a raise. Also, I provided a link to Microsoft excels built in functions library, which explains what each function is and how to use each function. So if you ever have any questions about any other functions that I don't cover, use that length so you can learn more about it. But if you're in any sort of position or a job that requires you to use Excel a whole lot, you should definitely learn these ones that I'm gonna cover. They have the ability to make your job so much easier and save so much time. So let's start with the V. Look up 20. Vlookup() Functions: Okay, so right now we're looking at a bunch of different sales transactions. This is in the invoice worksheet in the file named the look up. Okay, so in column A, we have the transaction number. That's just basically a transaction. I d and column B. We have the skew. Sq is sort of like a product I d and columns. See, we have the quantity and in column D we have the sales price. Okay. And in column e, we have the total price. And that's just the quantity times the sales price. Okay, so what do we want to do here? We want to figure out what our total profit is gonna be for these nine different transactions. So what is profit? Will profit is the sales price minus the cost. So how do we know the cost? Well, in this work she called inventory here we have a list of all the different products, along with a quantity on hand and the average cost. So the cost is actually here in this table and columns, see, So if we come back into our invoice worksheet and then we look at B two here and we find this skew this L A C Dash 914 on on on our inventory list L A C 914 We see that the prices 7 86 so we can copy this into our invoice worksheet right here and column G under cost per unit. And that's one way of getting the cost into column G. But there's gotta be a smarter way. And there is the V look up function. The V look of function looks for value in the left, most column of a table and then returns of value in the same row from a column you specify . So first, let's get rid of the data in G two and then let's initiate the V Look up function. Okay, and now you see it pops up. So let's double click on it. And now the first argument is the look of value. Will the look of value is gonna be the skew. Okay, so, Selby to now it's asking us for the table rate. Well, the table Ray is gonna be the inventory table. So let's come over here and select sells a one through C 14 and now it's asking us for the column index number. Well, the column index number is the column that has the field that we're looking for. So in this case, we're looking for the cost. So that's in the third column of this data. So we're gonna tape with three here and now it's asking us if we want to use an approximate match or an exact match. What? We want to use an exact match because we want to match the skew Exactly. So let's type false for exact match. And I'll show you how approximate match works later on in this video. OK, so now let's close out our parentheses and let's hit Enter on the keyboard, and now you see that it's using a V look up to return the value of 7 86 Now let's go back into the formula because I want to point something out. The table of Ray is sells a one through C 14 in the inventory worksheet, but look what happens when we copy these this formula down. Now the table array is a two through C 15 and if I copy it down, one more you'll see that's a three through C 16 and that's because we're using relative cell references. So let's go back into the original formula, Okay? And where it says a one through C 14. Let's fix thes into place by using the dollar symbols so we could come into this formula and type dollar symbols before our cell ranges. Or we could just hit at four, but and and each cell and that fixes it into place for us. And now we could come back up here and we can copy these formulas down by using an auto fill. And if we come over here into G 10 we see that it's still fix into place. But what happens if we add a new product? And then we try to do the V Look up? Well, let's try it. Let's come here and to row 11 and let's right. Click it and let's hit Insert and that inserts a row above. Okay, now let's call this transaction 10. And for the skew. Let's call this new dash skew. Let's order one. Let's say the sales praises 100 six fan net and now let's copy the formula here down into G 11 and as you can see, it's showing up as hashtag and A. This means that it can't find the skew on the inventory list. Well, that's because it's not on there. So let's add it. So let's type new dash skew here for the quantity. Let's type 500. And for the cost, let's say 50 but something still gonna be wrong. Here, let me show you. If we come back here, we see that it still says hashtag and A And that's because the table array is sell a one through C 14 but we just added a new skew to Cell C 15. So we need to come back into this formula and change it to see 15. And now we see that it's working well. What if we constantly add new products to our inventory list? Well, then that means we have to constantly update the V look of formulas. So let me show you what I like to do in order to prevent myself from having to constantly update the V look of formula. So, first of all, let's delete all of these formulas and column G and let's come back and to sell g two and let's initiate the V. Look up the look of values still gonna be Selby, too, But now the table array instead of being cell a one through C 15 let's do column a through column. See? Okay, that's what I always like to do. What I'm doing. A V look up, because if I ever add any new products, this will make sure that it's always included in the table array. Okay, the column index numbers still gonna be three, and we're still gonna say false for approximate or exact match. Now let's do an auto fill here to copy these formulas down, and now we have the cost per unit and column G. Now there's a couple of more things that I wanted to point out. The look of value is not case sensitive, so if we type this in lower case, it's still gonna work. Also, if you have duplicate values in your table array, it's always going to return in the first instance of that value. So let's come over here into this inventory table and let's copy this new skew into a 16. And for the quantity this time, let's say 100. And for the the cost this time, let's say 10 now if we come back into the invoice worksheet, we see that the cost per unit for new skew is still 50 because it found the first instance off new skew. So if we delete this first instance and we come back into this invoice now, it's showing 10 as cost per unit because we got rid of the other instance where the cost was 50. So let's get rid of this new skew and let's get rid of it on the inventory list as well. And let's come back into the invoice. And now let's calculate the total cost. Well, that's gonna be the cost per unit, times the quantity and let's do an auto fill to copy these formulas down. Okay, now we have our cost. Now let's calculate our profit, so the profit is gonna be the total price minus the total cost. And for this, let's do an auto fill and copy. That's all the way down and now weaken due in auto some by hitting all equal, and that's going to give us the total profit. So let's expand this. And now we see the total profit of $146.38. So the V look up helped us and coming up with this calculation. Now, let's do one more V look up to come up with the quantity on hand. Okay, so let's do the V look up function. Okay. The look of value is gonna be be to still, and now the table array is gonna be a through B. Okay, We can actually say a through C if we wanted to. And then for the column. Index number put too. And it still will work for us. And you see, now it's working in a gate. Was the quantity available? So let's do an auto fill and copy this formula down. And now we have all the different quantities on hand for all these different skews. Now, I told you I was going to explain what an approximate match was, but I strongly suggest that you never, ever use it because they could get you into lots of problems. So if we come into cell F two, we see that the quantity available is 52. But if we come into this formula and we changed this last argument to true Okay, that way we allow an approximate match okay and we hit. Enter it still says 52. But look, what happens if we change this? Skew around slightly. Okay, so let's get rid of the one at the end. Now it's saying that the quantity available is 122. Well, that can't be right. That's why I suggest to never, ever use it because it gets you into all kinds of problems. But let me show you what it could theoretically be used for, even though I suggest never using it for this. So, first of all, let's fix this. Let's come back to the skew and put a one at the end, which was the original skew. And now for this original V look of formula, let's change the last argument back to false. Okay, now everything's back out. Waas. But let's add a new sheet, okay? And and sell a one. Let's type the value 21 okay and sell a to list like the Value 29 then a three. Let's type the value 31. OK, bear with me. Also, let's add a header here. Okay, so let's call this first column. Skew. Let's call this second column cost. Let's come into this inventory work, she and let's add some new products. Now the first queue for this new product. Let's call it 20. Okay, let's say that the quantity on hand is 100 and that the cost is $10. And now let's add another product with the skew of 30. Let's say that there's 500 on hand and that the cost for this is 50. Okay, now let's come back into Sheet one and let's do a V A V. Look up for this first skew, but let's do an approximate match. Okay? So let's do the look up. The look of value is a to the table. Ray is gonna be a through C. OK, And the column index numbers gonna be three. And for approximate match, we want to say true. Okay, now it's returning a cost of $10. Well, where did it get $10 from? Well, because we're using an approximate match. It found the closest value to this 21. Okay, so if we go the inventory sheet, we see that the closest value to that 21 is 20 okay? And that cost is $10 so what happens if we copy this formula down? Well, for the skew 29 it still returned the value of 10. Okay, if we come over here to the inventory sheet, we see the $10 corresponds to the skew 20 while 29 is closer to 30 than it is to 20. So how come it's still returned? The value of 10 well, kind of works like the price is right. It has to find the most approximate value without going over. So that's how approximate match works. So let's delete this worksheet. Okay, let's come into this inventory list and let's delete those last two skews that we added, and let's come back into the invoice and let's take time and appreciate what we just accomplished because we learned how to do a V look up, and now we're halfway to become an Excel masters. Now there's one last thing that I want to point out. You can use a V look up or any formula for that matter and reference data from entirely different workbooks. So let me show you what I'm talking about. I have a file here called inventory, and this is basically the same exact inventory list, except it's in its own workbook. So if I come back into my V look up formula here and sell F two, I can replace this table of Ray with a new table of Ray that's referencing the new workbook that I have open. So let me come into this new file that I have open and let's select columns A through C. And now, if I hit enter, you see that now it's referencing the inventory dot Excel as X workbook. OK, and if I copy this formula down by using an auto fill now, they're all referencing the new workbook. Now, if I close this workbook now you see that it's referencing the actual path that this workbook is located now. One last thing. Let's remember this Skew here L a c Dash 914 And let's remember that the quantity available is 52. Okay, let's save this file, okay, and you can save a file by hitting control as on a keyboard. That's the keyboard shortcut, and let's close this file out and let's open this inventory workbook back up. Now let's come to this skew that says L. A C Dash 91 form. And let's change the 52. Let's change that to 1000. Okay? And let's save this file and close it out. Now, let's reopen RV, Look up! Work, workbook. And now you get a security warning right here. Automatic update of links has been disabled. Okay? And you can still see that the quantity available is 52. OK, but if I enable the automatic update of links now you see that it changes toe 1000. So that's how the look ups and links work in Microsoft Excel. But the V look of formula only works when the look of value is on the left. What if the skew was on the right? Well, in that case, we would have to use different formulas. We would have to use a nested index and match function, which will talk about in the next video 21. Index() and Match() Functions: So let's talk about the index and match functions. So in the index match workbook, we have pretty much the same exact data as Thievy. Look up, workbook. But the only difference is, if you come over here into the image of inventory worksheet, you'll see that the skew is on the right. And I did it on purpose because I wanted to show you how you can use a nested function using the index and match functions in order to look up the quantity available and the average cost. Okay, so let's come back into the sales worksheet and let's come into Cell F two. So we want to find the quantity available for this skew right here. L a c Dash 914-351 OK, so we're going to do that by using an index formula. So let's begin by typing the index formula. Okay, let's double click on this, and now it's asking us for the array. The array is the range of cells that contains the value that you're looking for, So that's gonna be a one through a 14. And now let's type comma. And now it's asking us for the row number. Well, the road number is gonna be nine, because that's where this L a C dash 914 skews located. So let's type nine here. And then let's close out of this function. And now we see that the quantity available for this Q is 52 now. I wanted to point something out for the ray we chose sells a one through a 14. Okay, but let's suppose that instead of choosing sells a one through a 14 we selected sells a two through a 14 because we didn't feel like including the headers. Okay, well, now, if we leave the row number at nine, this is gonna be messed up. OK, now it's showing 582. Well, what is 582? Well, that's row 10. This is because when we chose nine as the row number nine is referring to the row in relative terms, not in absolute terms. So we would need to change that toe eight if we're not including Cell a one. So let's come back into the formula and let's change this nine to an eight, and now you see, it's referencing the correct row, but like I always try to suggest, Instead of selecting cell range a two through a 14 let's select the entire column. Okay, so let's come over here into inventory and let's choose column A. And now let's change this back to nine. And now we get the correct quantity available. Now I know what you're thinking. If we have to manually type Row nine in this formula, then what the hell is the whole point of even using this formula? Well, that's where the match formula comes into play. So let me show you the match formula on its own. So let's delete this existing index formula and let's begin by typing the match function. Okay, and now it pops up, and now it's asking us for the look up value. Well, the look of value is gonna be be to the skew, and now it's asking us for the look up array. So let's come over here into inventory and let's choose C, because this this is the array that contains the look of value that we're looking for. And again, I like to choose the entire column, and now it's asking us for the match type we learned about exact matches and approximate matches in the V look of video. But for this we want to use an exact match. So let's type zero OK, and now let's close the parentheses and now we see nine. So the match function provides us with the row that contains the skew that we're looking for. Okay, In this case, it's road nine so we can use this match function within our index function in order to come up with the quantity available. So let's go ahead and let's type up the index function. And now for the array. Let's come back to inventory and let's choose column A because we want to get the quantity available. And now for the road number. That's where we're gonna use the match function because that returned the road number for us so we can leave that as it is. And now let's close out of the index function, and now we get the quantity available 52. And if we do an auto fill and copy this formula down now, we have the quantity available for all these different skews. Now let's do one more index match to get the cost per unit So let's come over here and let's begin by type and index. And now it's asking us for the array. Well, this time the Ray's gonna be column B because we want to get the cost. Now for the row number, we can use the match function. Now it's asking us for the look of value. So let's come back into the sales worksheet and let's choose B two for the look of value and for the look up of Ray. That's still gonna be column C because that's where the skew is located. And for the match type that's gonna be zero. OK, and now we close out the match function and we do one more close parentheses to close out the index function and hit Enter. And now we got the cost per unit 7 86 And let's just confirm that this is correct. L. A C Dash 914 is the skew, so let's come into the inventory list L. A C Dash 914 that also has a cost of 7 86 so we know it's correct. So let's come back into the sales worksheet and let's do an auto fill, okay? and now we have the cost per unit for all the different skews and not allowed us to calculate the total cost and the total profit. And that's how you can use the index and match functions when you can't use a V look up. 22. If(), And(), & Or() Functions: So in this video, I'm gonna talk about the if function the if function checks, whether condition is met or not. And it returns one value if the condition has been met and it returns a different value if the condition has not been met. So here we have a worksheet called Sales. This is in the if functions workbook. And in this worksheet, we have a bunch of different sales transactions similar to the previous example. And what we want to do in this particular example is we want to make sure that we have enough quantity on hand for each of these skews okay, in order to fulfill the's purchases. So let me show you what I mean. For this first transaction, we have an order for 389 units. This is for E w Dash 86 to 4. But if you come over here into the inventory were worksheet. We see that for this skew E w 86 to 8. We only have 259 so we don't have enough to fulfill this order. And we want to use an if statement in order to figure out if we have enough quantity on hand in order to fulfill all of these purchases. So, first of all, let's do a V look up here in order to determine what our quantity available is for each of these different skews. Okay, so the the look of value is gonna be the skew. So be to the table of rape. Is gonna be columns A through C and then the column index numbers gonna be too, because we want to grab that second column there, the quantity available. And now we want to do an exact match. So let's type false. And now we have the quantity available for this first skew. So let's do some auto fill here and get the quantity for the rest of these skews. Now, In one of the earlier videos, I mentioned Bolivians and I explain that there's three different cell types. There's text numbers and bully ins. Well, I'm gonna show you what a bullion is right now. Okay, So let's come over here to column F and let's insert a column before that. Okay, let's call this column. Boolean OK, now would is a bully in a bullying is a binary variable with two possible values true and false. So we want to find out if the quantity available is greater than or equal to the purchase quantity. Well, let's come over here into Cell F two and let's type the following formula equals e to greater than or equal to C two. So we're saying, is the quantity available greater than or equal to the purchase quantity? So when we hit enter, we see that it's showing up as false. So now we have a bullying and sell F two in the value for this particular one is false. Now, if we do an auto fill and copy these formulas down, we see a couple of other false values, and that's basically saying that the quantity available is less then the purchase quantity . So if we go back into this formula will see this greater than or equal to symbol, and this represents a logical operator. So let me explain to you all of the different types of logical operators. The first and most obvious logical operator is the equal to symbol. Then you have the greater than symbol. Next you have the less than symbol. Then you have the greater than or equal to symbol, which is the one that we used in our formula, and then you have less than or equal to. And last but not least, you have the not equal to logical operator, which is represented by a less than symbol, followed by a greater than symbol. So going back to our example, it's checking to see if e two is greater than or equal to see two. So if e two is greater than or equal to see two, then it returns the value of true. If it's not, then it returns the value of false so we can use his bully in in our if function and let me show you how. So let's come over here into G two and let's type up our if function. And now you see it pops up, and now it's asking us for the logical test. While the logical test is gonna be F two. So it's seeing if F two is true or false analogy to comma. Now it's saying, if the value is true, then what do we want to do? Well, in this case, we want to calculate the total sales price, which is the quantity times the sales price. And now let's hit Comma. And now it's asking us what we want to do. If the value of is false wolf, the values falls. We want to display the text, not enough in stock. Let's close out our quote and let's close out the parentheses and let's hit Enter. And now you're seeing for this particular transaction. There's not enough in stock. But if we do an auto fill and expand its column, we see the total sales price for all the ones that have enough in stock and then all the ones that don't have enough in stock. We see the text that we provided in the if statement. Now, instead of doing our logical test in column F, we can actually do it within our if function. So, first of all, let's get rid of column F All right, and let's go back into our if function and now you c hashtag ref, because we got rid of the cell reference that was in this formula. So let's get rid of that. And now let's do the same logical tests that we did in our bullying column that we just got rid off so we want to say E to is greater than or equal to see to. And now let's do an auto fill and copy these formulas down, and now you see pretty much the same exact result as before. Except all of it is being done within the if statement now. But in this particular example, we're only checking to see if one condition is true. We're trying to see if the quantity available is greater than the purchase quantity. But what if there's multiple conditions that need to be met? An order to perform in action? Well, let's take a look at an example. So here we have a list of a bunch of different bachelors. Okay, this is in the if multiple conditions. Workbook and column A. We have the bachelors name and in columns, B through D. We have their looks, career and personality rated on a scale of 1 to 10 and Elizabeth is looking at this worksheet, and she's trying to figure out which of these guys is datable in which aren't datable. But she has a pretty simple set of standards. She wants to make sure that their looks, career and personality are all sixes and above. If one of these categories is a five or below, then in her eyes these bachelors are not datable. So we're gonna have to use an if statement with multiple conditions in order to figure this out. However, that if function on Lee checks to see if one condition is true. So how do we check to see if multiple conditions are true? Well, for that, we're gonna have to use in and function. So let's come over here and two e two, and let's begin by typing the and function. Okay. And now it's asking us for the first logical test, while the first logical test is going to be to see if B two is greater than or equal to six . Because, remember, we wanted to see if each of these different categories is a six or above. Okay, now let's type of common. Now it's asking for the second logical test. Well, that's gonna be if C two is greater than or equal to six, and let's hit one more comma. And now it's asking for the third logical test, which is gonna be a D To is greater than or equal to six okay, and you could do as many logical tests as you need. But for this particular example, we only need to do these three logical tests. Now, let's close out of this function and let's hit enter. And now we see that this first individual, Arnold, is datable. So if we copy this down to the second Bachelor Tom, we see that he is not datable. It's returning the value of false because he may have good looks in a good career, but his personality sucks, so he's not datable. Now, let's go on auto fill. And now we see whether or not each of these individuals is datable, but we don't want it to return the value of true or false. We wanted to return the value of yes or no. So we're gonna have to use this logical test that we have here within our if statement. Okay, so let's initiate the if statement, Okay. And now the first logical test is already in here. We want to use this same logical test, and now the value, if true, is yes. And the value if false, is gonna be no. And let's close out this if statement and Now let's do an auto fill and have these copy all the way down. And now we could see who's datable and who is not datable and it's showing up. It's either yes or no, which is exactly what we wanted. Now let's suppose that Elizabeth is a little bit shallow and she's willing to make an exception on the career and personality if their looks are 10. Okay, well, it's gonna look a little something like this. Each of these men will be datable if their looks, career and personality are all sixes and above, or Key Ward or their looks are a 10. So we're gonna have to use the or function for this. So let's come over here and to sell f two and let's begin typing the or function. Okay, and now it's asking us for the first logical test. Okay, so that's gonna be if the looks so be to equals 10. Okay, Because we said if their looks are 10 then nothing else matters, Okay? And then the second logical test, that's gonna be where we add the and function. So we want to say B two is greater than or equal to six Kama Si two is greater than equal to six comma. Indeed, to is greater than equal to six. And let's close out the and function. And now we need to close out the original or function. Let's hit enter. And now we see. True because one of those conditions was met. But if you look at Tom, originally, we determine that he was not datable. But with this new logic, he should be datable because his looks were 10. And now you see that it returns the value of true. But we don't want this showing up as true or false. In column F We want this in the original if formula in column E. So let's get rid of all the data and f okay, and let's come over here to eat two. And let's change this formula around. So right before the end, we're gonna put the or and the first logical test is gonna be if the looks are a 10. So let's say B two equals turn and let's type of Kama now, the second logical test we already have in there, which is checking the see if each categories at six or above. Okay, so Let's come to the end of the and statement, and now we need to end or statement that we just begin. And now let's hit, enter, okay and let's do an auto fill. And now you see which guys are datable with the new criteria. And as you can expect, Tom is now datable because his looks are a 10 even know his personality is a to, And that's how you do if statements with more than one condition. But in this example, there's only two possible values. Yes or no. But what if there was more than two possible values? What if there was three, or maybe even four? Well, in that case, you're gonna have to use a nested if function. So let's take a look at the file called Nested If and let's take a look At column A and column A. We have a bunch of different light colors and in column B, we want to determine the action, depending on the light color. What we know for red, we're going to stop for yellow. We're gonna have to slow down. And for Green that means go. So let's begin by opening if statement and now for the first logical test. Let's say a two equals red. And for if statements, it is not case sensitive. So what value do we want to return? If a two equals red? Well, we want to return the value off stop now. It's asking us, What value do we want to return? If a two is not read? Well, we don't know because we still need a check if it's yellow or green. So now we need to do another if function. And now for this logical test weaken, say a to equals green. So if a two equals green now, if that's true, we want to return the value off go. And if this is not true, then there's only one other action, and that's gonna be to slow down. So let's close out the second if function. And now we need to close out the original if function and let's hit enter. Okay, so we see for read. It says to stop. So let's do an auto fill, okay? And now we see for yellow. It says to slow down for green. It says to go so we know this is working right, and that's how you do a nested if function 23. Iferror() Functions: So in the previous video, we talked about the if function now in this video. I want to talk about that if error function. Now, let's take a look at the if error worksheet in the if error workbook and column A. We have a number and in column B, we have a divisor and in column See, we have a calculation. It's the number. So a two divided by the divisor. So 25 divided by five equals 5 52 divided by 13 equals four. Okay, so on down the list. But when we try to do 54 divided by zero, well, that gives us an error because you can't divide a number by zero, and it gives us this little divide by zero error. Okay, and in the next row were saying 34 divided by Z, and that's given us this hashtag value error. Okay, so these cells are error cells and in self C seven, we're trying to do a total, but it's not working because we have all these cells with errors, so that if error function will tell excel what action to do if there's an error in a value . So let's come up here and to sell d two and let's initiate are if error. Now it's asking us. What value do we want? What we want to do? A two divided by B two. Now let's hit comma. Now it's asking, What value do we want if there's an error? So let's put in quotations, not valid value. And let's close the quotations. And now let's close the if error formula. Okay, now let's do an auto fill and let's bring these formulas down and you can see for Rose five and six. It's showing not a valid value, and now we're able to properly calculate the sum because it ignores those text strings. So that's how if error function works, But I want to show you a more practical example. So let's come over here into the sales worksheet, and this is a similar worksheet to some of the other ones we worked on earlier and and column D. We have the total price, which is the sales price, times the quantity and in column E. We want to figure out the cost, which is in this inventory list, okay, and then in column F we want to calculate the profit, which is the price minus the cost. Well, in order to do the cost, we need to do a V look up. So let's do the V look up. Which we learned about earlier. The look of value is gonna be a to the table. Ray is gonna be column a through C and then the column index numbers gonna be three because we want that third column and then we're gonna type false for exact match and let's hit enter. And now we see that the cost is not available because it's not on this inventory list, but we actually have to inventory list. So this particular skew is on the second inventory list. So if it's not on the first inventory list, then we want to do a look up to the second inventory list in order to find it. Well, for this, we're gonna have to do and if error formula. So let's go back into the formula and let's type if error. And now, for the first value, we want the original re look up that we have on here. But if if that returns an error, then we want to do another V look up and the look of values still gonna be sell a to. But this time the table array is gonna be in the worksheet inventory list Number two and it's gonna be a through C and then the column index numbers still gonna be three, and then we're still going to say false. And now we close out the V, Look up. But now we also need to close out that if error function and hit enter. Okay, Now we see the cost 7 86 for this particular skew because it checked the first inventory list, it couldn't find it, so returned an error. But then it checked the second inventory list once they realized that there was an error and the first V look up. So let's do an auto film. Copy these formulas down. And now we have all of our costs and we were able to calculate the profit 24. Sumif(), Countif(), Averageif(), Maxif(), & Minif() Functions: now in this lecture, I'm gonna go over the sum if count if average if, Max if and men it functions now. This might sound a little bit overwhelming right now, but they're all pretty easy functions to learn, and they all basically work the same exact way. So let's take a look at the box scores Worksheet in the n B A. Summits workbook. So in columns a through F, we have a bunch of data related tow N B A box scores. So basically it has every single player's fantasy points for every game during the 2018 slash 2019 season. So in column A, we have the date and column B. We have the players name and columns. See, we have the players position in column d. We have the team that the player plays for and column e. We have the opponents. So the team that the player went up against on that evening and then in column F we have their fantasy points for that specific game. So let's put a filter on this data by hitting control shipped. L. Okay, and let's go. The players of column B and let's search for Stephon Curry and Let's Filter for Stephon Curry. And now we see all of his games during the N B a season. Now let's suppose that we want him to see what Stefan Curry's total fantasy points were. So everything in column F for the 18 4019 season. Well, let's come here to sell F three and let's go down to the bottom of this range. So let's hit control shift in the down arrow. And now we have all these cells selected. If you look here at the bottom right hand corner, you'll see that the total So the sum of all his fantasy points is 3026.4. You also see that there's a count and the Countess 68. That means there were 68 games that he played in during the season, and then the average is 44.5. OK, so let's go back up to the top of this range and let's turn off the filter. Now there's another way to calculate Stefan Curry's total fancy points without having to put a filter on. And that's the sum if formula. So let's come over here and to sell I two and let's initiate the summit formula. So we want to choose this 1st 1 some if now it's asking us for the range, while the range is gonna be the range of cells that contain the player's name, because we want to find Steph Curry. So let's choose column B as the range. And like I said, I like selecting columns rather than selecting cell ranges. And now it's asking us for the criteria. Well, the criteria is gonna be the name of the player. So in this case, the criteria is gonna be cell. I, too, because we have the players name here and now The some range is gonna be column F because that's where all of his fantasy points were. So let's close out this function, and if I did this correctly, it should return. That same total is before the 3026 I believe, and it did now remember, and our formula, the criteria is referring to Sell I two here, So if I wanted to, I can change the player's name. So let's type LeBron James here. And now you see that the total points changes to reflect this. So remember this number here? 2924.5. So let's go into column B and let's put a filter on for LeBron James just to check and make sure we got it right. And if we select all of these cells here, we see that the totals 2924.5, so we know that it's working correctly. Now. Remember this number right here that count 56. Let's go back to the top of this data. Let's turn off the filter and let's come into Cell I for here. So in this cell, we want to figure out how many games LeBron James played. What we can use account if formula in order to determine this. So let's initiate the count if formula. And now it's asking for the range while the range is gonna be the range that has the player's name. So it's gonna be column B, and now the criteria is gonna be the player's name, which is right here and sell I, too. Now let's close out the parentheses, and if I did this correctly, it should show 56 which we determined was the count, and it did show 56. So we know that this is working correctly now. We wanted to calculate LeBron James average. We can use a simple division formula here is gonna be Cell I three divided by Cell I for So is total points divided by his total games played, and now we see his average is 52.2. But there's also another formula that I want to show you called the average it formula. So let's use the average of formula instead just so you could see how to do it. So let's begin by typing up the formula. And now here you see it average. If so, let's click on that. And now it's asking for the range while the range is gonna be the range that has the player's name. So that's gonna be column B. And now the criteria is gonna be the player's name. So that's gonna be so I to and now it's asking for the average range. Well, that's the range of cells that has the fantasy points. So that's gonna be column F. And let's close out the parentheses and let's hit enter and now we see that same averaged 52.2, and I want to point something out. The average of formal pretty much works the same exact way as the sum, if formula. So if you look at the average if formula and the Rangers column B The criteria is Cell I two, and in the average range is column F. And if we come into Cell I three and we look at this, some if formula, we see the same exact parameters for this formula. The range is column B. The criteria is so I to in the some range is column f. OK, so when I said earlier in this video that they all pretty much worked the same exact way, that's what I meant. The only difference is the function name. Now let's not worry about the best game in worst game yet. We'll look at that later. But let's suppose that we had more than just one criteria because in this example we only had one criteria and that was the player's name. But what if we had more than one criteria? So let's come to sell I 10 here. And what if we wanted to figure out what Stefan Curry's total was against the Clippers. That's what the abbreviation L A. C stands for. Well, now we have two different sets of criteria. We have the players name and we have the opponent that he went up against. So now, instead of using the sum if function, we're gonna have to use the some ifs function. So let's begin by typing this some its function. OK, and now let's choose it from this list. We don't want to choose this some IT function. We want to choose this some ifs, and now it's pretty much the same as before. Except the parameters are in a different order. The first thing that it's asking for is the sum range. So that's gonna be the range of cells that have the fantasy points. So if you remember from before that was column F, now it's asking us for criteria Range one. Okay, so that's gonna be column B, because that's where the players name was. And now it's asking us for the first criteria. That's gonna be the player's name. Now it's asking us for criteria. Range number two. Well, that's gonna be the opponent. So column E and then the criteria Number two is gonna be Cell I 12 or I'm sorry I 11 here, and you could add as many different criterias need. But in this case, we only need to different criteria. So let's close out the function and let's hit enter. And now it's showing that Stefan Curry scored a total of 100.2 points against the L A Clippers this season. And let's do a filter and figure out that's correct. So let's come over here to player and let's type Stephan Curry. And now let's come over here to opponent and let's filter for L. A C, which is the Clippers. And now we see he has two games against the Clippers and his total is ah, 100.2. Okay, so let's turn off these filters and let's come into Cell I 13 here. Now we want to figure out the games played well. That's going to be the same as before. It said, we're going to use the count ifs formula. Okay, now it's asking us for criteria. Range number one. Well, that's gonna be the range of cells with the players name, and now it's asking us for criteria. One that's gonna be the player's name. And now it's asking us for criteria. Range number two. That's gonna be column E. Where has the opponents teams Analogic comma? And now it's asking us for criteria Number two. And that's gonna be so. I 11 over here. Now let's close out the parentheses. And if this work correctly, it should say to because he played two games against the L. A Clippers and it is showing two. So that's working fine. Now we want to figure out the average fancy points for Stephon Curry, and same is before we could through a calculation. And take this 100.2 and divided by two. Or we can use the average ifs formula. So let's use the average ifs formula. Now it's asking for the average range. Well, that's gonna be column f. Still, the criteria range number one is gonna be column B. The criteria number one is gonna be cell I 10 here and now criteria ranges. Number two is gonna be the opponent, and then quite the criteria number two is gonna be cell I 11 over here and let's close out this function hit, enter and Now we see the average of 50.1. Now let's come back and to sell I six over here. And we want to figure out what LeBron James is. Best game was for the season. Well, we're gonna have to use a max if formula except there is no Max it formula. For some reason, there's only a Max ifs formula. So the ones with multiple criteria. But we don't have to use more than one criteria, even if you're using a Max ifs formula. So let's type it up. And now you see it pops up, and now it's asking for the Max Range. Well, that's gonna be column F. Okay, and now it's asking for criteria. Range number one. So that's gonna be column B. And then Criteria number one is gonna be LeBron James. Now let's close out the parentheses, and that's the only criteria we have for this example. So let's hit enter and now we see LeBron James Best game. He scored 77.5 fantasy points. Now we can use a men ifs formula to figure out his worst game. And again, there's no men if formulas only men ifs okay, so Let's initiate this formula. And now let's choose column F for the men range for the criteria range less. Choose column B and then for the criteria. Let's choose LeBron James. Okay, so the same as the Max ifs formula. It's just a different function name. Now let's close out this function and hit Enter and now we see that LeBron James is worst game. He scored 23 points. Seven fantasy points. Now let's do a Max ifs formula with multiple criteria. So let's come down here and to sell I 15 and let's do a Max. It's formula to figure out what Steph Curry's best game was against the L A Clippers. Okay, so now the Max Range is still gonna be column F criteria range number one is gonna be column B and criteria one is gonna be the players names of Steph Curry and now criteria Range number two. That's gonna be the opponent. So column E and then the criteria Number two is gonna be Sell I 11 over here. Now let's close out this function to hit enter and now we see that his best game against the Clippers. He scored 55.2 fantasy points. So now let's do a men ifs to figure out what his worst game was against the Clippers. So the men range is gonna be column f the criteria range number one is gonna be column B Criteria number one is gonna be the player's name, So sell I 10 criteria Range number two is gonna be the opponents of column E, and then criteria to is gonna be so I 11 over here, the opponent. Now let's close out this function. And now we see that his worst game against the Clippers. He scored 45 points. Now let's suppose we want to figure out how many fancy points Steph Curry scored during a specific time period. Well, we're gonna have to get pretty creative with our some ifs formula. So first of all, let's freeze this top row Here, let me show you how to do that. Let's go to view. And then over here, where says freeze panes, click on freeze Top row. And now if you scroll down the top row is still gonna be fixed, okay? And let's come over here and to sell I 22. And now we want to figure out what Stefan Curry's total was for the month of March. So we have a begin date here. And so I 20 and we have an end date here and sell I 21. So in order to do that, we're gonna have to use the some ifs formula. So let's initiate this formula. And now the some range is still gonna be column F criteria range number one. It's still gonna be column B and Criteria number one is gonna be the player's name. So sell I 19 and now criteria range number two. That's gonna be the date column. So column A and now criteria to that's gonna be where we do something a little bit different. So in quotations, we want to put a greater than or equal to symbol. And now we want to add an ampersand. And then we're going to click on Cell I 20 here. So we wanted to be greater than or equal to March 1st 2019 and then criteria range number three. That's also going to be the date column. So column A and the criteria for this one it's gonna be similar is gonna be less than or equal to close out the listener equal to symbol and now do an ampersand. And let's click on Cell I 21 over here, which is the end date. Now let's close out the function and let's see what happens. Now it's showing that his total points for the month of March it's 622.1. So let's put a filter on Steph Curry to see if this this is working correctly. So let's come over here to player and let's search for Steph Curry. Okay, let's choose Stephan Curry. And now for the date we can filter by a month. So let's choose the month of March here and let's take the total for all these cells. And voila, 622.1. So that's working correctly. So let's take off these filters. And now we want to do account ifs to figure out how many games he played in the month of March. So let's initiate the count ifs formula and now criteria range Number one is gonna be column B criteria. One is gonna be the player's name, so sell I 19 criteria Range number two. That's gonna be the date column Criteria Number two. That's gonna be greater than or equal to in quotes, followed by the and percent. And then let's click on the begin date. So So I 20 and now Criteria Range number three. That's gonna be the date column. And then Criteria Number three. That's gonna be less than or equal to in quotations followed by the ampersand followed by Cell I 21 which is the end date. So March 31st 2019. Now let's close out this function, and now we see that the Games played for Steph Curry for the month of March was 14. But let's do another filter just to confirm that. That's correct. And when we highlight all of his scores, we see that the count is 14. Okay, so that it is working correctly and his average is 44.35 So we're gonna do an average ifs now to come up with that figure. So let's take off these filters and let's come over here and to sell I 24 let's initiate our average ifs formula. So now the average range is gonna be column F criteria Range number one is gonna be column B criteria Number one is never good players. Name seems before criteria range number two. That's gonna be the date column Criteria Number two is gonna be quote open quote greater than equal to close quote followed by the ampersand, followed by Cell I 20 comma and now Criteria Range number three. That's gonna be the date column. So column A as well, and then Criteria Number three. That's gonna be open quote less than or equal to the end date. So let's put in an ampersand and let's choose the end date, which is so I 21 now let's close out this function, and now we see that averages that we talked about 44.4. Okay, now let's see what Steph Curry's best game was during the month of March. So we're gonna use a max ifs formula. But instead of doing the formula all over again, let's go over here into this formula that we already typed with the average ifs formula. Let's copy this text here, and let's compare it to sell I 25 and let's paste the text. But let's change the formula. Name, toe Max. It's and Now we see that Stefan Curry's best game. During that time period, he scored 58.5 fantasy points. Now let's come back into this. Let's copy this formula and let's come over here to sell 26. And now we want to figure out his worst game during this time period. So let's pace that text that we just copied. And let's change this to say men ifs. And now we see that his worst game. During that time period, he scored 24.4 fantasy points and let's do one more filter just to make sure we did that correctly. And now the cool thing is, when you come into this fantasy points filter, it's sorted from from smallest to largest. So we see that his best game, or I'm sorry that his worst game was 24.4 fantasy points, and if we go to the bottom, his best game was 58.5 fantasy points. So it is working correctly. Okay, so let's take off these filters. And now, if we wanted to, we could change this to LeBron James, and now we could see what his average was during the month of March, he averaged 52.5 fantasy points. He played a total of 12 games. His total points was 630.7. His best game was 63.7 points, and his worst game was 38.1 points. Okay, now let's suppose that we wanted to figure out what the best overall game was for any player and what the second best game was and what the third best was. And then let's also suppose that we wanted to figure out what the worst game was, what the second worst was and what the third worst game was. Well, for this, we're gonna have to use these small and large functions, which I'm gonna talk about in the next video. 25. Small() and Large() Functions: So we finished off the last video by mentioning that we're gonna use thes small and the large functions to figure out what the best in the worst overall games were for the envy a season. So if we come up here to the filter for column F, which is the fantasy Points column, and we unsolicited all and become all the way to the bottom and select thes bottom three scores, which are the highest three scores on this list, we can see that the best game was a 95 point game by James Harden against the Knicks. The second best game was a 92.4 point game, also by James Harden. And then the third best game was a 90.1 point game by Karl Anthony Towns. Okay, so let's turn this filter off. So we're gonna use the large function in order to figure out what we just figured out. So let's initiate the large function over here. And so I 29 and now it's asking us for the ray. So this is gonna be the range of cells that have these fantasy points. So column F and now it's asking for K. So if we want to see the first best score, we're gonna put one for K if we want to see the second best score that we're gonna put to if we want to see the third best score, Will will put three for K and so on down the list. So for this one, we want to see the first best game. So let's put one Now, let's close out this parentheses. And now we see that 95 point game that we looked at earlier. Okay, Now, let's do that same formula to figure out the second best game. Okay, so the Rays still gonna be column F and K is gonna be, too. And we see that 92.4 point game. And if we do one more large function, we could figure out the third best overall game. And that's that 90.1 point game. Now for the for the worst game we're gonna use the small function, which works the same exact way is the large function just the other way around. So though raise still gonna be column F and K is gonna be one, and that shows us minus three and now the second worst game. Let's initiate the small function. The Rays still gonna be Column F and K is gonna be, too, because we want to see the second Morse came, and that's still negative. Three. That means that there's two different occasions when a player scored negative three points . And now let's do one more to figure out the third worst game. And now we see negative two. Which means the third worst game was a game where someone scored minus two points and you might be asking, How can you support negative points? Well, in basketball if you commit a turnover that counts as negative points towards your fantasy points. So that means these players had really, really bad games. But what we're looking at here are the best and worst overall games. What if we wanted to see LeBron James's first best score in his second best score in his third best score? Well, for that, we're gonna have to use an array, so I'm gonna talk about a raise in our next video 26. Array Formulas: so we already learned about the lookups and Index matches. But the problem with these functions is that they only allow us to match up single values. But what if we had to do lookups based on multiple criteria? For instance, in this example, which is in the index Match array file, we have three different fields, and each of these fields contain different information related to smartphones and column A . We have the model. OK, so it's either an iPhone or galaxy and column B. We have the version, so you have the version nine. The x X are the Galaxy nine or the Galaxy 10 and in column. See, we have the storage. Now suppose here. And so h A. We wanted to find the quantity available pain and so h nine. We wanted to find the cost for a particular type of phone based on the values and sells H three through h five. Well, in order to do that, we're gonna have to use what's called an array. If we try to do a V look up or an index match, it's not gonna work because we have duplicate values in all of these different cells here. So before I show you how to do it by using an array, I want to show you a different method that does not involve using an array. But it does involve one extra step. So let's come over here and insert a column to the left. Okay, and let's call this column. Can Cat Now in this column here and so f two. We're going to concoct innate the values in A to B two and C two. So now you see the contaminated value of thes three columns and sell F two. So let's do an auto fill and copy that down. And now we can do an index match and match up our look of value against column F in order to get the quantity and the cost. So let's go ahead and do that. Okay? Now the array is gonna be column D because we want to find the quantity available, the road number for the run number. That's where we're going to use the match. And now it's asking for the look of value. So for the look of value, we're gonna contaminate the values and sells I three through I five. Now it's asking us for the look up array. So that's gonna be this column f over here, which we just created. And now the match type is gonna be zero because we want to do in an exact match. And now let's close out the match function. And now let's also close out the index fund function. And now we can see for the iPhone X R 256 gigabytes, the quantity available is 149 units. And if we look on this data set, we see that that's correct. Now let's change this to the iPhone nine, okay? And let's say that it's the 16 gigabyte unit. Now we could see the quantity available for the iPhone 9 16 gigabyte, which is 358 units. Now, let's figure out the cost. So we're gonna use pretty much the same exact formula. Except this time the ray is gonna be column e instead of column D. Okay, so the array is column E. The row number is gonna be where we use the match formula, and now the look of value is gonna be the same as before. We're going to concoct in eight i three through I five, The Look up a Ray's gonna be column f and now the match types gonna be zero for exact match . Let's close out the match function and let's cause close out the index function. Let's hit Enter. And now we see the price for the iPhone 9 16 Giga Plates. And that's $600 which is correct now, Like I was saying before this method involved one extra step because we had to come here and create column F and contaminate all these values here. So if we wanted to avoid this extra step, well, we're gonna have to use in a ray function. So let me show you how to do that. First, let's get rid of column F here and now let's get rid of these formulas and go back into Cell H eight and create a new formula. And we're gonna first use the index formula. And now it's asking us for the array while though Ray is gonna be column D because we want to find the quantity available now for the row number, we're gonna have to type the match function so so far, pretty similar. And now the look of value is gonna be the same as before. We're going to concoct innate H three through H five. Let's take Comma and now it's asking for the look up a ray. This is where it gets a little bit different. So for this, we're going to select column A followed by an ampersand followed by column B, followed by another ampersand, followed by column C. Now let's hit a comma and for match type, let's type zero for exact match. Let's close out the match function and not let's also close out the index function and something something is going to be wrong. And now explain to you why. So let's hit tenor and now you c hashtag value. And the reason that says has hashtag value is because we didn't turn this formula into an array. Okay, The way to turn of formula into an array is to hit control shipped in en er instead of just hitting enter. Okay, so let's try that. Let's hit control shift enter and now we see 358 for the quantity available, which we know is correct. And if you look at the formula here in the formula bar here you see these curly brackets at the beginning and at the end of the formula that indicates that it is an array function. So let's come back and to sell each nine now and let's do the same formula. But instead, this time, let's look up the cost. Okay, so let's begin by typing the index formula. And now it's asking for the array. So this time is gonna be column E. Now it's asking for the road number, so we're gonna use the match function. The look of value is gonna be the same as before. The local ray is also going to be the same as before. So it's gonna be column A ampersand column B ampersand column C The match type is still gonna be zero for exact match. And now let's close out the match function and let's also close out the index function. And now let's hit control shift enter. And now we see the cost for this iPhone 9 16 gigabytes off $600. And if we look here in Row two, we see that that's correct. Now let's change this to Galaxy nine. And now, let's say 128 gigabytes. And now we see the quantity in the price for the Galaxy 9 128 gigabytes, which is 398 available, $650. So we know that this is working correctly, and that's how you look up values with multiple criteria. And you can also use a raise for other things. And let me show you an example of one which we touched upon at the end of the last video. So let's open up the next file. This is called the N B. A small, larger ray file, and you might remember this data set from before and in the last video I showed you how toe look up the first best second best and third best overall games for the season. And I also showed you how to look up the first worst and second worst in third worst overall games for the season. But I ended the last video mentioning that if we're gonna want to figure out the 1st 2nd and third best games for specific player in this case, Steph Curry what we're gonna have to use an array formula, so let me show you how to do that. So let's come over here to sell out three and let's initiate the large function. Now it's asking us for the array. Now here's where it gets a little bit different than the normal large function. So now for the array we're gonna have to initiate. And if Formula And now it's asking for the logical test. Well, that's gonna be if column B, which contains the player's name, equals Cell L two, which has the player's name, that we're looking up in the specific case and now it's asking for the value, if true, will the value. If true, we're gonna put column F because we want to grab the fantasy points from column F and we're not gonna have a value it fall. So we're just gonna end the it function now by closing out the parentheses E. And now let's type a comma. And now it's asking us for Kay and the K is gonna be the same as before. In this particular case, we want the first, best overall game, so we're gonna type a one here, okay? And now we're gonna close out the large function. But wait now what do we have to do here to ensure that this is an array formula? We have to hit control, shipped, enter on the keyboard. So when we do that, we see that Steph Curry's best overall game with 71.7 points. Now let's figure out his second best overall game. So let's go into this formula here and let's copy it. And now let's hit escape that way. We don't affect anything here and let's come over here and to sell l four. And let's pace that formula that we just copied. But let's change that 12 a two because we want to find the second best game by Steph Curry . And now let's hit control shift Enter on the keyboard. Because, remember, this is an array formula, and now we see his second best game is 64.3 points, and now let's do this one last time except let's find out his third best overall game. So let's change the one to a three this time because we want to find his third best overall game and let's hit control shift enter. And now we see his third best game, which is 62.4 points, but I want to do something a little bit different here and sell l six so in. So l six. We want to figure out the fourth best overall game, which is what this force for, and then and sell Al seven. We want to figure out the fifth best overall game and in so l eighth. We want to figure out the six best overall game. Well, I want to show you how to do this by using an auto fill. So let's type up the function again. The Rays gonna be the it function. The value, if true, is still gonna be column f close out, the if function And now for for K this time we want K to be so que six over here, Okay? Because we want to find out the fourth best overall game. Now let's close out the large function. And now let's take control shift enter on the keyboard. And now we see Steph Curry's fourth best came for the season Now for his fifth and six best overall games. I want to do an auto fill here, but there's one thing that we have. The change Let's go back into this formula. And now where we see that we're referencing Cell L two. We need to fix that into place by typing a dollar sign before the row number so we can type a dollar sign before the road number. Okay, like that. Or we could hit F four on the keyboard. And if we wanted to, we could hit F 4 to 2 times. That way it's on Lee locking in the row number. And now let's take control shift, enter on the keyboard. And if we drag this formula down here now we get the fifth and sixth best overall games by Steph Curry. And if we want to, we can change this to LeBron James, and now we see LeBron James's first through six best games for the season. Now, what if we wanted to see the same information, but for a specific time period? So let me show you how we would do this. So let's come over here and to sell l 14 and let's initiate the large function. And now we want to see his best overall game for the month of March. So for the array, we're gonna have to type the if function. And now for the logical test, same as before. We're going to say column B equals so 11 over here. But we're going to do this a little different now, So let's wrap up this first logical test and parentheses. And now let's use an ass trick. And now we're going to provide the second logical test also in parentheses. So the second logical the test is gonna be column A to see if the date is greater than or equal to the begin date, which is so l 12 over here, let's close out this parentheses. And now let's take one more ass trick and let's provide the third logical test in parentheses. And that's gonna be if the date so column a again is less than or equal to the end date over here. Okay, so cell L 13. Now let's close out the parentheses for this neurological test. And let's type a comma here and now for the value. If true, that's gonna be column F, which has the fantasy points. Okay, now let's close out the if function, and now let's type a comma. And for K, we want to find the best overall game, so we're gonna type a one. And now let's close out the large function. And now remember to hit control shift, enter on the keyboard because this is an array function and now we see Joe and Beads first . Best game For the month of March, he scored 78 points. Okay, so let's copied this formula here. Let's hit escape on the keyboard. Let's come over here and said So l 15 and let's paste it. But let's change this one to a two because we want to see his second best overall game and let's hit control shipped. Enter and let's do this one more time for his third best overall game. Let's change that one to a three and let's hit control shift. Enter. Now we see his third best overall game before his four through six. We're going to do an auto fill again, so let's go into so l 17 over here. Let's paste. Let's change this one. That's Gibbard of where it says one over here. And let's replace that with Cell K 17 which has the Ford. But also we need to change all these cell references. We need to lock them into place. So let's type F four. Let's come over here. Where says l 12. Let's hit F four and let's come over here where it says S O L 13 and let's hit at four. Okay, now we fix those into place and let's hit control Ships enter. And now we see his fourth best game for the month of March. And if we do an auto fill here will see his fifth and six best games for the month of March . Now try to remember these scores the best you can here so we could make sure that it's correct. And let's come over here to sell a one. Let's apply a filter by hitting control Shift L on the keyboard, and now we want a filter for Joel Embiid. So let's type Jowell and beat here, okay? And now we want a filter for the month of March. So let's hit March over here and let's sort the fantasy points over here in column F largest to smallest. And now we pretty much see those same exact numbers here. So let's turn off these filters now. If you wanted to, you could come over here in the cells l 20 through hell 25. And you could figure out his first through six worst games, which you already know how to do your pretty much gonna do the same thing that we just did . Except you're gonna use thes small function instead of the large function. 27. Pivots: so pivot tables allow us to slice and dice data in order to easily summarize this data. For example, let's look at the work she called box scores in the workbook called N Ba Pivots. Now you may remember this data set from our previous section, but if you forgot, it basically contains fantasy scores for every player in the N B A for every game in the 2018 slash 2019 season. By the way, if you were wondering, this data is actually really see if you come over here and to sell a one and you hit control shift L on the keyboard, that applies off filter to this data set. And now let's come over here where it says player and let's filter for LeBron James. These are all LeBron James is actual games last season. So let's turn off this filter for LeBron James. And now let's create a pivot table. So is actually pretty easy to create a pivot table. All you have to do is select a cell within our data set, which I already have a one selected and come over here and to insert and choose pivot table now it's saying, What range do we want to create a pivot table for? Well, it automatically knew which ranged to select, so we don't have to do anything with that now. It's asking us Where do we want the report to be placed? Well, we'll put it in a new worksheet, so we'll leave it like that. And now let's click. OK, and now we have a pivot table here, but now we need to start adding some information to this pivot table. So let's suppose we wanted to see which players scored the most total points during the season, while in that case we should add the player name to the row area by Dragon It like this. Now we have all of the player names in one column in the pivot table. Now we want to see what their total fantasy points were. So let's drag the fantasy points field into this values area, and now we see some off fantasy points. So these are these players total fantasy points. So we wanted to see who the top guys were. Well, we're gonna come up here to data, and we're gonna do a sort Zito A and now These are all the top players as Faras, who scored the most total fantasy points. So now you could kind of see how easy it is to summarize data by using a pivot table. But you can do so much more than just that. So, first of all, let's change these headers here by typing directly into them will call this first field player name, and then this second field over here will call it Total F p F P stands for fantasy points, and now we change. Those headers also Weaken Do an auto size here by double clicking between B and C. But let's suppose now we want to see how many games thes players played during the season because some guys play more games during the season than others because, you know, some guys have injuries and things like that. So in order to figure out how many games these guys played, we're gonna drag this field the same field here. Fantasy points down into the values column, but this time we're going to click on this and we're going to choose value field settings, and now we want to see the count. We don't want to see the some of the fancy points. We want to see the count because that's going to tell us how many games these guys participate in in. So let's hit OK, and now we can see. You know. James Harden played 76 games, Yanis played 72 and so on down the list. So let's change the name of this field to G P, which stands for games played. And let's also do a resize here and now I want to show you something very important. Now let's suppose that we want to see their fancy points per game, which is going to be the average Well, the average is gonna be the total fancy points divided by the games played. So let's come over here and to sell d four and let's staple formula. But I want to show you what happens when we try to reference a cell from a pivot table. It's referencing its location in the pivot table. So now let's say, divided by and now let's click on the games played over here and sell C four, and now you see that it's doing the same thing. It's referencing its location in the pivot table. Now when we hit enter, we still get the average. But look what happens when we try to do an auto fill. You see, it's referencing the same player in all these different cells. So if you're gonna try to reference cells in a pivot table, then you should type the cell name rather than clicking on this cell. So instead, this time let's type be four divided by C four. And now, if we do an auto fill with this new formula before divided by C, For now, we get the averages, but we don't even need to do that in order to get the average is, let me show you how to get the averages within the pivot table. So first of all, let's delete this information in column D. And now, in order to get back to those pivot table options, we need to click on any cell in the pivot table. Now let's drag fantasy points again to this values area underneath our GP field. And now let's go down to it and click on it and click on value field settings. In this time, let's choose average and now let's hit OK, and now we can see the average fancy points per game. So let's change this field name, toe average F p, which stands for fantasy points. And also let's change the format of this number here so that it only shows two decimal places. So let's go back to where says Value field settings. And now let's choose this option here that says number format and let's choose number. And now where says decimal places, we'll leave it at two and let's hit. Okay, let's hit, OK, one more time and now we see it being rounded to two decimal places. So now we have their total fancy points, their games played and their averages all by doing a pivot table, and we don't have to do any formulas like we did in the previous section. Now you probably noticed this filter area here in the pivot table options, so let me show you how that works. Now let's suppose that we wanted to get these same values, but we wanted to filter for a specific opponent. So let's say we wanted to see how everyone did against the Golden State Warriors. So what we can do is we can drag this field right here. This opponent field. That's what O. P. P. Stands for into the filter area. So now there's a filter up here being applied to the pivot table. But we haven't actually filtered anything yet. And now let's choose this select multiple items option. Let's uncheck this box here that says all. And now let's find GSW, which stands for Golden State Warriors and not let's hit. Okay, and these are all the players totals and averages against the Golden State Warriors now. It just so happens that the Toronto Raptors are going up against the Warriors in the finals . So let's add one more filter. Let's see how all of the different players on the Raptors have fared against the Warriors. So well, we're gonna want to do is we're gonna want to add another filter. But this time we're gonna choose the team field and drag that over to the filter's area. OK, and now let's come to the new filter that we just applied the team filter and let's choose the Toronto Raptors. And that's what Teal R stands for and let's hit OK, and now we have two filters applied. We have ah filter for the Toronto Raptors. So we see all the different players on the Toronto Raptors, and we have another filter for opponent where we chose Golden State Warriors. So now we can see how all of these different players did against the Warriors during the season. So Kyle Lowry play two games against the Golden State Warriors. During the season, he averaged 49.8 fantasy points per game against the Golden State Warriors. So you see how easy it was to find that information by using a pivot table. Now let's suppose that we wanted to see all of the different players from the Warriors and how they did against the Toronto Raptors. Well, let's change the opponent filter. Let's UN select GSW and let's choose Toronto. Let's hit OK, and now our pivot tables empty. That's because the team is still Toronto. We want to change that to the Golden State. Warriors is empty because a team can't go against themselves, obviously. Okay, so let's unsolicited Toronto endless. Choose GSW for Golden State Warriors, and now we can see how all of the different players on the Warriors did against the Raptors during the season, and I noticed right away. Steph Curry only played one game against the Raptors, but he only scored 17.1. Fantasy points it. So that's their star player, and he had a pretty bad game against the Raptors. So that's interesting to see now. Kevin Durant. He did pretty good against the Raptors, but Kevin Durant's actually injured right now so he might not play. So my point of all this is that you can use a pivot table so easily slice and dice our way through data summarised data, and it helps you analyze your data. Ah, lot more efficiently and effectively. Now let's get rid of these filters that we just added. And the way to do that is to come over here to the filters and dragged them out of the filter area back into the field area. And now let's suppose that we wanted to see all the different players totals, games played and averages, but for the month of March, while in order to do that, we're gonna add this date field here to the filter's area, and now we have a date filter applied, but I want to show you something. Let's come over here to the filter unless chooses drop down option. And now you can see that you can select and de select different individual days. Okay, but suppose that you wanted to filter just for the month of March? Well, you don't wanna have to come down here and individually select each day of the month for March, but luckily there's a little work around for that. So let's come over here and let's hit, Cancel. And now, instead of having the date as the filter, okay, let's drag that over to Rose, and we want to put that above the player we don't want to below the player we wanted above the player, and now we see the date and the players performance for that date. And if I scroll down now, we see you know, the second day and all of the different players performance for that day, but that's not what we wanted to do. But I want to show you something. If you come over here where it says the date and you right click, okay, and now you choose this group option. Now you can group thes days in two months, so let's choose months. But let's also choose years because we want a group it by the month and the year and let's hit. Okay, now it's all being grouped by the month and the year. So here we have October 2018 and if we scroll down, which we should see November 2018. Next, There we go, November, December and now we see 2019 January. But still, that's not a filter for the month of March. Like we were saying, Sure, we could scroll down the march, but instead we want this as a filter. But now that we have a group by the month and the year, well, now we can take these fields down here and drag them to the filter. And now we can come up here and we can choose for the year. We can choose 2019 and then in this next field, where says date, that has all of the months so we can choose March, and that's just a little work around. In order to group that dates how you want them to be grouped because when you put them into this filter's area, there's not a lot of flexibility as to how you congrats. These dates. But when you put them down here into this rose area, there's a lot more flexibility and it makes it a lot easier. So so what I usually like to do is added to the Rose area, group it in the Rose area and then dragged it back up to the filter's area. And then I could turn on my filter when I'm dealing with dates, because sometimes date filters are a little bit tricky. Now let's suppose that we wanted to see how each player did during each calendar month of the season. Well, a nice way to do that would be to put the months into columns, so it's pretty easy to do that with a pivot table. First of all, let's take these filters off. So let's select all for the month and let's come over here to the year and select all for the year. And now let's drag these filters to the columns area of the pivot table. And also let's drag this year's filter to the columns table, and we want that to be above. We want the year to be above the date, and we want the date to be above those values Microsoft Excel automatically added this values field when we were doing the totals and the average is down here. Okay, so now everything is organized into columns and his group by year and by month. So here we have October 2018. So James Harden, his average in October was 50 to his average in November was 54 his average in December was 56. And then we also had it summarized by year here, so his average for the year 2018 was 55 then the same applies to all of the different months in 2019. So that's a nice way to organize this. But if we want, If we didn't want these totals here, we could right click on it and we could We could select worth his salt total year and uncheck that. And now we don't have those sub totals for the year, and there's also gonna be totals for this entire data set over here at the end. And we can also get rid of those. So if we right click on it, we can say remove grand total right here and now we don't have those grand totals anymore. We just have all the totals for the different months. But there's also gonna be grand totals at the bottom of this data set for all the different players. So let's go to the bottom of this, Data said by hitting controlled Down Arrow. And now let's right click on the grand total here and let's hit remove grand total. Okay? And let's go back up to the top of this data set by hitting control up Arrow. Okay, and now we see all the grand totals are gone, and we have a nice, clean pivot table. Now let's suppose that we added some more data to our source data. So let's come over here into box scores and let's go to the bottom of this data set by hitting controlled down Arrow. And let's say that on 46 2019 I got signed to my favorite team, the Miami Heat. So let's put Adrian P over here in my position is point guard, because I'm too short to play any other position. My team is Miami, and the opponent is, let's just say Golden State Warriors and let's say I had a really, really good game. I scored 105 points. Well, now we need to refresh that pivot table in order to see my score here. So let's come over here to sheep, too. Let's right click on the pivot table. You could right click anywhere in the pivot table and let's hit for fresh. And now let's come over here to April and let's sword it from high to low for the average because I would be at the top of this list because I had a really good game where I scored over 100 points and I'm not on here. Well, why am I not on this list? Because we need to change the source data because we added a new row to that data. So in order to change that source data, let's come over here to pivot tables, tools and let's choose his option. That says change data source. And now you see that that last row is missing. So let's come back toe a one. Let's hit control ship, right, and then control ship down. And now we have the entire data set selected and let's hit. Okay. And now there you see me, Adrian P. I scored 100 and five points in April, so I have the highest average out of all the guys on this list. Now, the only reason kind of took me a long time to do these pivot tables was because I was explaining it to you. But I want to show you just how quick you can summarize data by using a pivot table. So let's suppose that we wanted to see how all of the point guards So guys with the position designation off PG, we want to see how all the point guards did in the month of April. So let's delete this sheet to here, which has the original pivot table that we created. Okay, let's go back to the top of this data set. And now I want to show you just how fast we can do this. So, first of all, let's come over here to insert Let's choose pivot table, OK and automatically selected the cell range that we wanted it to and will keep it on new worksheet. Let's hit. Okay, and now we want Ah, position filter. Because remember, we said we wanted to see just point guards. Let's come over here to this filter and let's choose just the point guards. Let's add the player field to the rose area, and now we have all the different point guards. Names here and now. Let's add the fancy points to the values area here, but we don't want to see the some. We want to see the averages. So let's come over here and let's choose average. Let's come to number format and let's make sure that it has two decimal places. Let's hit okay and let's hit. OK, and now we have all the averages for all of the different point guards. But I also said I wanted to put a date filter for the month of April. So let's come over here to date. And let's add that to remember Teoh the Rose area, because we're going to do that work around that we did before. And now let's come over here. Let's right click on the year and let's say Group and now we want a group by by months and by years. So we got rid of where it group did by quarters. Let's hit, Okay, and now let's drag this year's to the top of this filter and Let's drag this date here, which is the months toe underneath, years on the filter. And now we could come over here and we can choose the month off. Maher are April, I said, and let's choose the year 2019. And now we have all the averages for the point guards for the month of April and you see me here at the top and let's just do a sort here. So let's come to data and let's say sort Zita a and you can see I have the best average for the month of April and then Russell Westbrook right after me. So you see how easy it was to summarise data? That's why the pivot table was such an important tool, and it's so powerful. But I wanted to show you one more thing. What if we wanted to see all the games in the month of April that made up this 66.3 point average by Russell Westbrook? While all we have to do is double click on this and this will show us all the records that make up that number, So you could pretty much drill down into anything in your pivot tables. Let's delete this. And let's suppose that we wanted to come down to the bottom. Here's let's hit control down Arrow and we wanted to drill down into this total. So now when we double click on it, we see all of the games in the month of April by all of the point guards. So the drill down feature is a really cool feature within a pivot table, and it's very useful. 28. Graphs: so charting graphs help us to visualize data so that we can better analyze the data. Let me show you an example of what I'm talking about. So right now we're in the monthly sales worksheet. This is in the graphs and charts workbook and what we're looking, that is, a list of budgeted versus actual sales grouped by month. And right now it just kind of looks like a bunch of numbers, and it's kind of hard to see what's going on right away without digging a little bit deeper . However, let's come here to the monthly sales graph worksheet, and this bar chart is a visual representation of the data we were just looking at. And it's so much easier to see what's going on when you're looking at the graph. I mean, right away we can see that, you know, in March sales were really good and they far exceeded their budgeted sales. And then we can also see all of the week months, for instance, right here they had a really weak month in July. Their budgeted sales were really high, but their actual sales were really low. So it's pretty easy to see what's going on when you're able to visualize the data. So let's take a look at one more example. So if you come to this work she called sales by brand. Here we have a bunch of sales for cellphones grouped by Brand and right away. When you look at the pie chart, you see that Apple makes up pretty much half the pie, and Samsung is another big seller, and then you have these other smaller cellphone manufacturers that don't quite have as much in sales. So the pie chart is a pretty effective visual tool for something like this. Now I'm gonna show you just how easy it is to create a graph and Microsoft Excel. So let's come to this worksheet called basic Graph. OK, and here we have an column, a a bunch of different sales rep and in column B, we have their total sales, so we want to visualize this in some sort of a chart or graph so right away. An easy way to do this is to click in any cell in this data set, will come over here to insert and then will choose this option right here, recommended charts and now Microsoft Excel brings up a bunch of different suggestions for charts that we can use. So you get a little preview here on the right hand side as you go through all the different recommended charts. Or if you go into this next time over here called All Charts Now you can see you know the different types of charts. Right now we have a column chart, and if you hover over them, you could preview it. You can also do a line chart, a pie chart, which we just look that ah, bar chart an area chart. You can even do a scatter chart. So there's all kinds of charts that you can do a microscopic so. But let's go back to recommended charts and let's choose this first chart here and now. We created a chart that easily, so let's move this chart by dragging it up here and let's make it a little bit bigger by dragging the bottom right hand corner. Now there's all different types of ways that we can customize this graph. If we click on anywhere inside the graph now, we get thes options right here on the top right hand corner, and we click on this plus icon and come over here now it has all the different chart elements, and we can add different elements to the chart. Or we could get rid of different elements of the chart. So right now the total sales is the chart title, and let's suppose that we didn't want that as the chart title. Then we could just come over here and uncheck that. But we do want that. So let's recheck it. And let's change the name of that. And let's call this sales by rep. Now let's come back to the chart elements, check boxes and let's add an access title. Okay, and now we have an Axis title here on the Y access and another one here on the X axis. And we could change the names of these titles. So let's call this this one on the X axes. Let's call this rep name. Now we have all the different rep names on the X axis. And let's call this why Access title total sales. Now let's come back into the chart element options, and we can also add a legend. So if we check this box for legend now, we have a legend here on the right hand side and it's saying that the Blue represents total sales and if we wanted to, we could add data labels. So at the top of the bars, now it tells you the total sales. You can even add a trendline. So now we have a trend line here. So there's all kinds of options toe how to customize your graph. You can even change the values here in the UAE access. So let's right click on this and let's choose format axis and now we can set a minimum and a maximum. Right now the minimum is zero and the maximum is 350,000. But let's change this maximum toe 300,000. Now you see that the maximum is 300,000 and we could even change the units of measure. See, right now it's working in increments of 50,000. So if you look here on the left hand side, you see that it goes from 50,000 to 100,000 to 150,000. So let's change these units to 100,000. And now you can see that the Y axis works in increments of 100,000. So you have 100,000, 500,000. Or we could even come in and change this to 10,000. And that doesn't look very good, does it? So let's go with the original of 50,000. That looks a lot more clean now. You can even change the chart type after the charts been made. So let's say that we wanted this to be a pie chart instead. What we could just click on anywhere within the chart. Okay, and then we're going to select this option right here. That says, Change chart type, and that's come down over here. It's a pie. And let's choose this pie chart here and now we have a nice little pie chart instead of the bar chart. So it's so easy to create. And to customise charts and Microsoft Excel. Now let's undo what we just did by hitting Control Z. That way we could get back to the bar chart, okay, and I want to show you how graphs and charts can get a little bit more complex, but it's still pretty easy to deal with them. Now. Let's close out this chart options area on the right hand side and let's come over here and let's make this, um, graph a little bit more narrow. And let's add a field here in column C. Let's call this budgeted sales. Okay, let's do an auto size here and now for the actual budgeted sales. Let's just use a random function so we'll see. We'll say random number between 50,000 and let's say, 400,000. Let's do an auto fill here and let's copy and paste this as values. Otherwise, it will keep changing. Now we want to add these budgeted sales to this graph. That way we can see budgeted versus actual sales. So let me show you how to do that. So what you need to do is right. Click anywhere within the graph and then chooses option right here that says, Select data. And now we want to add something to the legend entries. Siri's. So let's click on the add button here and now it's asking for the Siri's name. Well, for the Siri's name, we could just click on Cell C one budgeted sales, and now for serious value, let's get rid of what's in there and let's choose cells C two through C 13 because we want these budget, its sales in there. And now let's hit. Okay, and now we have the budgeted sales in there. But if you look on this right hand side over here at the horizontal category Axis labels, we just have these numbered labels one through 12. But we want to change this. We want these labels to be the sales reps. Names here in column A So let's hit this edit button and let's choose these reps names over here. So sell a two through a 13 and let's hit. Okay, now you can see all the reps names here in the horizontal axis labels. Now let's hit. Okay, and now we have the budget versus actual sales by rep. Now I have these labels here, and I want to get rid of them. So one way we could do it is just toe select any of these labels and and Microsoft excel, automatically select all of them and then hit the delete button on your keyboard, and then we need to delete the other ones as well. So let's select them and let's hit delete on the keyboard. Or we could have done the other thing that we did earlier, where we went to the where we came here to the chart elements and we checked the data labels check box in order to add it and then unchecked it to get rid of it. Also, let's get rid of this trend line hair. So let's uncheck this box for the trendline. And now we have a nice graph for budgeted versus actual sales by sales rep. Now, with this particular set of data, we're really not dealing with that much data. If you come over here, you see that this is only 12 records. So what about when we deal with very large sets of data? So let's take a look at the worksheet called box scores. And as you can see, it has over 25,000 rows. So you can imagine that working with graphs in this particular data set is gonna be a lot more complex. So what I suggest to anyone who wants to create graphs and charts for very large data sets such as this one, is to create what's called a pivot chart, and I'm gonna show you how to do that, so it's pretty easy to create a pivot chart. All you have to do is click any cell within your data set and then come up here to the insert tab and let's choose pivot chart. And now it automatically selected the range that we want to select for a private chart. And now it's asking us where we want the pivot chart to be placed. So let's keep it as a new worksheet. And now let's hit the okay button. And now we're ready to start adding some stuffed our private chart. Now let's suppose that we wanted to see LeBron James's averages by month and a chart format , so we're going to approach this the same way we approached pivot tables, which we learned about in the last video. So first of all, we want a filter for LeBron James. So let's come over here and let's drag this player field down into the filter's area. Okay, now let's come up here to the actual filter and let's choose LeBron James and let's hit. Okay, so now we have a filter on for LeBron James. But we also said that we wanted to see his averages by month. So let's come over here and let's drag the date field down to the rose area. But now it's being grouped by year, so if we click on this button, we can expand it. And now we have the year and the quarter. And if we hit a woman time, we can expand it once more. And now we have the year, the quarter and the month. But let's get rid of the quarters because we don't need the quarters. So let's right click on it and let's choose this remove quarters option. And now we just have the year and the month and now we want to add the fantasy points field over into the values area. But now that's thes some. We want to change that to the average, so let's click on it and let's choose value field settings and let's come over here and choose this average option. And let's also format it to show two decimal places. So let's choose number and let's keep it at two decimal places. Let's hit okay and let's hit OK, one more time, and now we have his averages by month, so let's drag this bottom right hand corner to make this graph a little bit larger, and now we can start customizing this graph the same way that we did before. So let's right click on this axis on this y axis and let's choose this option for formatting axis. And now we want the minimum value not to be zero. Let's make the minimum value 30 and now it automatically changed the maximum value to 60. And that looks pretty good to me. So let's close out this format axis window, and let's also close out this pivot chart fields. And now we have a good look at our graph. And, as you can see, I mean, he's pretty consistent from month to month, but it's just a good little visual tool toe. Help us see. You know his averages by month. And with the pivot chart, it makes it so much easier to make graphs and charts for large data sets such as this one