Google Sheets - Advanced

1. Introduction: Hi, I'm Ed McRae. And it's my pleasure to welcome you to the Google Sheets. Advanced class. We have a lot of excellent things that we're gonna discuss here. Very exciting stuff. Let's take a look at our outline and see what we're gonna be covering during this advanced course. We're gonna take a look at how the link and protect data will learn how the work with range names or named ranges. We'll take a look at how we could use logical functions. We will explore, look up functions. We'll learn about how the work with text functions, date functions. We'll learn how the work with regular data in our sheet, we will understand the pivot tables. I'm gonna introduce you to macros and scripts, and we're gonna learn how the work with forms. So, like I said, lots of interesting things that we're gonna talk about. Let's go ahead and get into our first lesson 2. Importing Data: All right, let's learn how to import data into a sheet now in the import data into a sheet, the data that were importing is going to need to be in the excel format. So you want to make sure that you have dated in that format and then what we're able to do is we're able to connect to that sheet and actually import an entire worksheet from the Excel format in as a Google sheet. Let's take a look at how to do this. I'm gonna go to file. I'm going to go to import. That brings me into this area here where could choose where I'm importing from so I can import from my drive, of course, files their shared with me re cents. Or I could upload something right here in the moment and imported. I want to do my drive. I'm gonna go ahead and navigate and I have a whole folder just for my excel files. And so here we go. The five of that one is list beverage. Someone to go ahead and choose that when they hit select. All right, now, when I import this file, I can choose to insert new sheets, replace the current spreadsheet, right? Or create a new spreadsheet, someone that in certain new sheets, I can also choose to import the theme, right? So if I want to keep the bring the theme from that other workbook in and replace the one I have now for colors and fonts and stuff, I could do that. I'm not interested in doing that. I'm gonna go ahead and click import data. All right, there we go. It imports all of those sheets that were in that workbook in here. Right? So now I have t list, which we did not have before. Let's move that onto the back. I have a beginner inventory here. We don't really need this, so I'm gonna go ahead and just remove uh, this worksheet. That's fine. Go. Bye. Bye. And there we go. We have our beverage, Lis. Now we have our tea list, and we were able to import that. So remember, it has to be in an excel format first, and then we can do file import. We can pull it right in to our Google sheet. 3. Linking Sheets: inside of Google sheets. We can link values from other sheets into a particular sheet that we're working on. Show you what I'm talking about. So if we take a look here we have all these different sheets are an item Lis Fruitless beverages, right. Tease anybody getting hungry out there? All right, so all that stuff is right there, and we have totals and just values here. And then we have a summary worksheet, and I want to be able to take a look at the total inventory for each of these things. Item list. Fruitless beverage list. Anti list. So let's take a look at a couple different ways that we could do a linking. First of all, I'll do for item list. I just want to grab this total. That's right here. Gonna grab the total. That's right here. And I want that total value displayed right inside of here. All right, so all I'm going to do is to a simple formula where I'm linking the value of that cell to this cell I'm gonna say equals go over to item list. I'm going to click on the cell that I want a link in and look at my formula up here it is putting the name of the sheet followed by the cell reference when they hit Enter. And there we go. It puts that value there now for fruitless. Just to show. You want to do this a little differently, do an actual some function here. But I'm gonna have the arguments come from another sheet someone say equals some. We know we open up parentheses, right? Of course you do. When you go over the fruit list and I'm going to highlight these values, I don't have a total here, so I could just do that one cell. But I want you to see that we don't have to necessarily do that when the close, my parentheses and look at my formula. Same thing. It's doing a range of cells, but it's establishing the name of this sheet first and enter. There we go. 3758. Take a look at that 37 58. Good stuff. Someone do the same thing for beverage and t list. I'm just gonna link the individual cells so equals Let's go over the beverage list. Let's grab our total here. Enter and then T List. Let's do the same thing. Gordon T List. Grab this value and enter. All right. And there we go. That's how we link now. These are active links, right? So if I go over here the item list, let's just come over here and let's change what's happening here. Let's change the quantity of garlic press. Let's say we have 50 of those now, so that updates will do another one of these two. Let's change this to 25. So our total now is updated 41 43. The summary. Oh, look at that item list told us. Updated as well. So we simply create a formula where we're linking to either an individual cell or using a function or pulling in a range. And it's gonna automatically update as items change in the original place will see the updated values and are linked sheet 4. Linking to Another Spreadsheet: All right. I want to talk about linking to a different workbook using Google sheets. It's a little bit different because our sheets are cloud based. I can't do a link like I would do to a different sheet within the workbook, right? Like we know usually in the workbook, I would be able to just go and do an equal sign. And then, you know, we know we could go to another sheet and then click on the value I want and hit enter. Okay. If I tried a link to a different workbook like, let's say I want to be able to to put in the value for the beginning. Inventory would be nice if I spelled that correctly, right? Don't judge me. All right, so I want to put that in here. Um, I'm gonna go ahead, and I have this list open right here at the beginning. Inventory total right there. So I want to link that so I'm gonna go equals and I'm gonna go over to this other one, and I'm gonna try to click on this and hit Enter, and I see that it's not It's just not working, okay, because it's in the cloud. It's on another. Another browser tab can't do it that way. So if I want to import of value from a whole other workbook, I need to use the the function of import range Import range will allow me to establish or put the link to that workbook and then the specific range I want to import. Now, we do have a little bit of work that we would need to do in order to do this. But once we get it done, it would work. The first thing that I want to do is I'm gonna go over here, and you probably want to open up the other sheet in edit mode. All right? You also want to have it in the sheets format. Really? For this to work the way you want it to. So I'm gonna go over here, and I'm gonna just grab the U R l up here from my address bar. Copy, copy, Copy. And there we go. Going to go over here to where I want to put my value. Let's just ah, format, paint this just cause I want to. And we might as well format paint this guy as Well, all right, s o. I want to go ahead and put in import range. So equals import range. Here it is, right here. Imports data from another spreadsheet. Come. So the first thing I need is Do you are well, and I noticed that it needs to go in quotes. Look at the example here. We need to put the u R l in quotes, and then we're gonna need to put the range in quotes. We're gonna go back and talk about that in the moment. So I'm gonna go ahead and paste in the URL. At least I think I am. I think I have another value in here for some reason. Let's go back and grab that you are. L again Maybe didn't take the first time. Copy. Let's try that again. Equals import range. And let's go ahead and open up a quote. There we go. Paste in. Are you are well, close quote. Now the green here lets me know that that's correct, right? If I don't have these quotes around, it is gonna be black. That means that it's not gonna work. So there we go. Common my next argument. Now the next thing I need to do is put the name of the sheet and the cell reference. So if you look at the syntax here, we know that we put the name of the sheet an exclamation point and then the cell. I'm gonna come over here and just verify these things. The name of my sheet is begin end with no space and then the cell. I want slb six. So over here, let's go ahead and put that in. Quotes begin. I envy exclamation point B six. Close my quote. Close my parentheses. E with a hit. Enter and there we go loads right up. Now, the reason why mine loaded right up here is because I've connected to that particular sheet before. When you do this for the first time, it's going to tell you that you need to allow access. It will give you like a red area here when you click on it. It would say it will give you a little blue button to click to allow access. You click that and once you allow that access to the science permissions and so then going forward, you don't have to do that anymore. But here we go. It's pulling that value from that other spreadsheet. All right, so very easy. A little bit more work than we would do for just an individual sheet. But we can use import range to pull values from whatever the workbook we want. 5. Protecting Sheets and Ranges: all right. I want to show you how we can set up protection settings for your sheet. We can protect and say that certain sheets are read on Lee, or we can make it so that individual ranges are edible by specific users. All right, so let's take a look at how we can set these things up. In order to do this, your sheet has to be in a sheets format, not in excel format. So here, I mean, the sheets form, and I can see that up here. And if I go to the data menu, I see an option for protected sheets and ranges. Right now, I've got the same file open here in an XLs format. See XLs X up here in excel format. And when I go to data, that option is not available. Okay, So very important to remember. If you are in the next less X format, you need to use the protection settings and excel to set this up. Okay, let's go ahead and close that over here in our sheets format. We're gonna go to data. I'm gonna choose protected sheets and ranges. I'm gonna go ahead over here and add a sheet or range someone to click on that, give us a description and say projected projections on Lee. All right now I could choose to set permissions were just lock a specific range. Or I could go to the sheet and say that I want to deal with the entirety of the sheet. So on a lot the whole sheet, except for certain cells and those certain cells are gonna be this projections area. Want people to be able to enter those in click. OK, and now I could go ahead and set permissions for the whole sheet and the default permissions here. Show warning. When editing this range, I can restrict who can edit the range. I could come here and say, Do a restriction. Why put specific individuals or just me? Or I can say show a warning when editing this range. So I'm just going to show a warning here, or I like I said, I couldn't restricted for everyone had done and I know says says can edit with warnings. If I go back in here, I can change permissions if I want it. It's edible by everyone. I'm gonna change this to Onley. I can edit Onley. I'm able to edit the sheet except for those cells. That's my exception area. Well hit. Done. And there we go. I see this lock showing up down here to show me that the sheet is now locked. All right, Now, because I am in the sheet, I'm still able to edit everything, so I can't really show you this. But if this was shared with someone else, they would only be able to make changes within this range. Right here. So this is how we're able to provide permissions again? Remember, we need to be in a sheets format in order to use this feature. 6. Working with Versions: version ING is something that's turned on by the fault in your Google sheets. And what that's going to do is when the Ch'tis say there s changes air made periodically instead of overriding the original file is going to save a new version. Now what we're able to do a see all those versions and go back through the version history we could rename, delete or restore previous versions to view our version history. We're gonna go to file and I see a version history option here. I can name the current version. What I want to do is see the version history someone say C version history shows me a listing of all of my versions. Come and I could go to each of my sheets here and I could see what's going on inside of that sheet. All right, Now, if I look over on the right, I can expand and I have inside. This is the overall version underneath each. I have all the miniature cause it's saving every minute or so. I have all of the minor versions created for this major within this major version. Okay, so I could expand and see that if I click back on this one. I can see everything that's there. I'm gonna click on the three dots to the right. I'm gonna name this version original. I'm gonna name this version first draft. All right. Or better yet, let's rename that first edit. All right, so now we go as I can't more versions created, they'll pop up. I can go over there, I have it set up. We're showing me changes that were made. So it's highlighting things and blue that were changed. I can turn that on and off. And this is a very good way for me to have control. So if someone makes a change that did not want I'm not stuck with it, I can always go back and restore a previous version. 7. Reviewing Accessibility Settings: There are a few accessibility settings that we can enable, and we want to do that, especially if we're dealing with a sheet that is going to be shared with, AH, large number of people. We want to make sure that if anyone has any disabilities and the issues as far a sight hearing and things like that, they have a screen reader who want to make sure that they're able to use our sheet and view or access our information to see these accessibility settings. We're gonna go to tools and yes, you guessed it. We're gonna click on accessibility settings. All right, we can see here that everything has already turned on, but a couple things here, first of all, turn on screen reader support that is going to enable us to have Braille support and collaborator and out Smith's right. So that's working. And then we can also turn on the screen, magnify support, so you just want to turn all these things on. It's not gonna hurt anything to have them on, so we'll turn all that stuff on. We'll click, okay, And if anyone is using a screen reader or anything to help them out it is going to work the way that it's supposed to. All right now there is a project file that's associated with this lesson. I want you to go ahead and access that file. You can get some practice on all the things that we talked about here, and practice makes better, doesn't make perfect, but it does make better. So you want to go ahead and get as much practice as you can so you can really advance yourself in Google sheets. 8. What are Range Names?: Let's talk about range names or named ranges. So typically, when we're doing a formula right, we're using cell references. So we have a continuous range or we have individual cells that were referencing, and that's fine. But if I'm doing multiple formless using the same range, or if you know I want to be able to quickly navigate to a certain range, that could be kind of annoying and kind of cumbersome. And also, if I'm doing a long or complex formula, you know it's going to make my function on my form even more complex. Looking at all these cell references, I'm trying to figure out Well, you know what in the world is a six through B 7000 right? I don't know what range that is. So what we can do is we can take a continuous range of cells. We can assign a name to it, and what this is going to do is allow me to quickly reuse the same cells in multiple formulas. It's also gonna allow me to see in plain English what the name is or what the range is I'm dealing with. So what are range names? Formally, we'll arrange name is the names of ranges of cells that can be used in formulas as absolute references and as navigation devices. Right now, there are a couple of rules that we're going to need to adhere to when we are naming our ranges. Let's talk about them. One. The first character has to be a letter or in underscore, so we cannot start with. The number has to be a letter or text character or underscored to. We cannot have spaces or special symbols. No symbols are allowed other than underscores. All right, so it has to start with the text cat during underscore cannot have spaces or special symbols, and lastly, it cannot be the same as a cell reference. So I can't do like a one b 3000 right? It has to be something mawr than that because those cell references already exist, so we don't need to re use those won't let us rewrite them. So if we adhere to those rules is gonna make creating name range is all the more easy, and another video will take a look at how we can actually create name ranges and then how we can use named ranges 9. Creating Range Names: Now that we know what named Ranges are, let's take a look at how we can create and do some navigating using named ranges. So I have three sheets here 2017 2018 and 2019 all with sales numbers. Then we have a summary sheet over here, and what I want to be able to do is I want to do some named Ranges four all of our sales values. All right, so let's take a look, and we'll do several name ranges here. I'm going to start. But just highlighting all my sales values here like so we're gonna go to data. I'm gonna go to named Ranges. I'm gonna go ahead and I'm gonna call this sales 17. Done. Let's do the same here in 2018. Let's go ahead and highlight. This whole guy right here at a range will call this sales 18. Then we'll do the same thing here calling this sales 19. Now, check this out while this is open. I'm just going to click on each of these and it's gonna navigate me right to those rangers . Let me de select So you can really see sales 19. Boom brings me right there. Sales 18. Boom brings me there. Sales 17 brings me right there. All right, so I can easily navigate and select whatever I want. Now, I could use this in the formulas. Well, we'll learn how to do that in another video. If I want to edit a range, I can use a little pencil to the right here. Okay? And I could just come in here and change the cells and I'm using, or if I need to delete a range, I could do that as well, using the little trash can. Right there come so very easy to edit our ranges. If I want to be able to navigate using the name range, I could just pull up this named Ranges window, and I could just easily move back and forth between the different ranges. 10. Using Range Names in Formulas: we have our name ranges in place. I want to use these name ranges in a formula. So I have my three name ranges here. Sales 17 18 19 of a summary sheet here. I want to see the overall total An average for the whole company. If we wanted to, we could have done name ranges here for each region if we want it. We didn't do that. So we're just gonna do the overall. So here I am. I'm gonna do the total First SE equals some open parentheses, E. I'm gonna start typing. My first range s a and we can see it gives me this right here. I can see the symbol here for a named range. Then click on that. Let's add in 18 comma at in 19. We're putting commas to separate out our arguments. Of course, you guys know that we do that hit. Enter. There's my overall total. Alright. I'll be nice to see that in my bank account right now. I'm fantastic. All right, let's do the average equals average. Let's do the same thing. 17 18 and 19. Enter and there we go. All right. Fantastic. Okay. So once you have your name ranges in place. Very easy to use them. As you start typing in the name, it gives you the list where you can choose which ones you want. Obviously, we can have this name ranges. Pain opened on the right so that we know the ranges that are available to start tightening them. Okay. I want you guys to get some practice with this. So go do it. Now. Open up the project file that's associated with this lesson. I want you to practice with this. This is really gonna make your formula life a lot easier. It's gonna make your syntax easy to understand. It's gonna make it easier for you to reuse the same cells in multiple formulas. 11. Understanding Logical Functions: Let's talk about logical functions. What is a logical function, right? A logical function makes a comparison, then returns either a true or false value. So basically, it allows us to have it make a decision. We're gonna input some kind of condition, some kind of criteria that needs to be met if the if the criteria is met is going to result in the true, if it's not going to result in the Pfalz now, based on that, true or false, depending on the specific logical function you're using, it could then go on to do other things. All right. It could do something you tell it to do if it's your fault or could do a pre disposed thing . If it's true, it could sum up certain values or average certain values. Or what have you? We'll look at some different examples of this Logical functions require the use of logical operators. Here's some examples of some common logical operators that you would find all right equals greater than greater than or equal to less than or equal to less than not equal to All. Right now I want to show you and we'll go back and we'll learn how to use them. But I want to show you some. A couple of common logical functions that we're going to get into here is an if statement. Now all of your logical expressions are made to either work with or built on the foundation off if, and this basically allows me to put in a condition put in if it's true what to do and what to do if it's false. So we see these arguments here, we'll revisit this as we learn how to use these functions you also have, and and or and in logical. I need to explain to you what and and or mean so and is a combine er of conditions. So whenever we use and it says that all things on either side of the and have to be true in order for it to equal to a true or is a toggle er or a choice of condition so with, or if any of the things on the sides of the or are true, then it will result in a troop. We can see the arguments in the expressions here for those snakes. We'll learn how to use all these functions. In another video 12. Using Logical Statements: All right, let's take a look at how we can use the most foundational logical statement there is. And that is, if all of the logical statements are either made toe work with the if function or built on the if platform and the if function is cool because it allows us to set a condition and then also set what to do if the condition is met and what to do if the condition is not met . So if we take a look at what we have here, I have a list of sales here, and I want to figure out what commission people are getting. I have two different commission rates. If the sales are two million or greater, they get a 3% rate. If it's less than two million, they get a 2% rate. So let's check it out. And let's do an if statement here, where is gonna analyze the total sale and apply the appropriate commission rate based on the amount. So I'm gonna click right here. I'm gonna do equals if open parentheses. E. My first argument is my logical expression. This is my condition. So I'm going to say if this cell is greater than or equal to two million. Now, if I had that two million defined in the cell, I could point to that cell. But I don't, so I have to type it in. Okay, comma value. If true, now we can see right now is showing me false. This is not greater than equal to two million, which is correct. This is not greater than equal to two million. All right, so if it is greater than equal to two million, I wanted to go ahead and take that and let's multiply by this percentage. That's right here. Then when the need to make this absolute right because I'm gonna copy this down. I don't want to sell reference to change comma. If it's not, then I wanted to go ahead and give me this value. Times this cell instead make this absolute. Also, we can see it resolving to that result because it is less than two million. Let's close our parentheses. E Let's it enter and let's copy that all down and there we go. Now let's drastically change these percentages just to see if it's working correctly. I'm gonna change if is greater than equal to two million. I'm gonna change it to be 50% and check it out on Lee, the ones that the great unequal two million changed right? So we see it's working. If it's less than two million, you get zero. All right now we could really see that are logical. Statement is in fact, working. Let's change one of these values so it becomes greater than two million. I'm not quite there yet. Let's do there we go and it's working OK, so are logical. Statement is looking at it. It's resolving based on the conditions that we set in, and it's doing what we wanted to do if the condition is met and if the condition is not met . 13. Using AND and OR Functions: I want to show you how we can use the and in the or function within an if statement to combine together multiple conditions that either all of them have to be true or some of them can be true. So we'll start with. And so I want to set it up so that in order to get a c. U bonus, you have to have at least 1.5 million in sales and you need to have mawr than four. See you credit. So both of those things need to be true. So let's go ahead and do our if statement going to do equals if open parentheses. Now, what we're gonna do now is called nesting. When use a function inside of a function, it's called nesting. All right, so we're gonna nest the and function into this if function in the logical expression area to say that both of these things have to be true. So in the pudding and here, whenever you put a function name, you need to open the parentheses. I'm going to go ahead and do that, and I am going to say, First of all, my total sale has to be let's say greater than we'll do greater than or equal to 1.5 million. I can't use the common there. Sorry about that. And the other things. I'm putting a comma to move to my next argument. My The other thing that needs to be true is that this sell the see you has to be greater than four. We're not gonna do great in the equal to for that one. Common. So we're saying both of these things have to be true in order for it to be true. All right, All right, comin. Let's go on to our next part. We see it's resolving to false right, Because both those things are not true. If it is true, then we're going to go ahead and we are going to multiply this times. Let's just do a 10% bonus, right? So times 0.1, if not, we're gonna have it say no. Bonus. Close quote, close parentheses. E There we go. Was copy this down. And the people who are getting bonuses are the ones where this is greater than 1.5 or equal to and see you. Credits are greater as well. All right, I'm going to just copy this because I don't want to redo the whole thing. And I'm gonna pace this over here and I'm gonna just simply change this and to an or so same conditions here but instead of and let's do or and watch the difference here, the copy that down and check it out a much different result because we're now saying that any of those things, if any of those things are true, then they can go ahead and get a bonus. So if we take a look over here, Walter was not getting a bonus because both of these things aren't true. But when the or column, because at least one of them is true. Now Walter is getting a bonus. So that's how we nest. Using and and or and that's the difference and is more restrictive. All the conditions have to be true or is less restrictive. Any of them can be true. In order for it to result in a true 14. Working with Nested IF Statements: all right, I want to talk about doing nested if statements a nested if statement is when we have a if statement with multiple conditions and actions for those conditions. So it's a little bit deeper than just using and or we have multiple conditions. But the same actions here we have both show you two ways to do this. The 1st 1 is gonna be the old school way where we're just using if and then I'll show you a way that we can use it. It's a little bit cleaner using the ifs function. So here I have three levels of commission. If is great in equal to two million, they get 3% between one and two. They get 1.5% and anything under one million. They get, um, 0.5% commission. So let's go ahead and do it. We have to do it manually, Of course, so equals if when you're doing a logical statements and you doing greater than you always want to go in a descending order. So I'm going to start with my highest value or my highest breaking point. I should say, my highest condition. So if it's greater than their equal to two million. Then we want to go ahead and multiply that by this 3%. Of course, we're gonna make this absolute. All right, now in the false area is where I would put my next. If so, instead of putting a final faults, I'm gonna put another if open parentheses. All right, If this value then is greater than or equal to one million, then go ahead and multiply that out by this 1.5. Let's make this absolute. All right, comma. So I would continue to do this until I get to my final faults. Now, I don't have to do a less than one million condition, because if it's not greater than one million or equal to one million, it's less than one million. So I'm just gonna put my final faults so everybody else is going to get this times the 0.5 . All right Now, what we have not done yet is closed Any parentheses, so we'll do all that together. Now we've opened how many? 12 parentheses. So let's close one to parentheses. Well hit. Enter. We'll copy that down and there we go. It is doing it based on the correct percentages. Again, I could change this to zero and see those who are less than one million change this 10 It'll affect everyone who's in between. And we could see all those who are greater than two million obviously are getting 3%. Now we can do this. If I look at this formula can be a little hard to understand. Sometimes when you're getting used to this, especially we had a lot of conditions because right we have everything. Another if and ass weird. So there also is an ifs function we can use that allows us to establish a criteria followed by a action and then another criteria followed by that specific action. So let's look at that. Equals ifs. Let's open this up here. Condition one. Let's do the same thing. Condition one is gonna be Is this greater than equal to two million right? Value one. Go ahead and take this. Multiply it out by that percentage condition to right, so we're kind of able instead of having to do another. If we're just able to do multiple conditions here, right? Is this greater than their equal to ah, one million action for that is going to be. Take this. It's multiplied out by the second guy right here. Now for this one, I do need to do my my less than All right. So that gives me a final actual condition here. Right? So my last one here is going to be have condition one I have conditioned to condition three . If this value is less than one million, then go ahead and multiply this out by this guy right here. Your function, That four. Let's just make sure we have everything in the right place. Here's condition. One value here is conditioned to, and the value here is conditioned. Three and the value on Lee need to close one parentheses e into that. Let's copy that on through, and it's working exactly the same. Okay, so if I take a look at this, it's a little bit easier to understand because I don't have multiple ifs. If statements I should say I'm just using the ifs function and having my conditions and values matched up 15. Using SUMIF, AVERAGEIF, COUNTIF, and More: all right. I'd like to take a look at some of the functions that are built on the if platform specifically some if count if average. If Max if and men ifs all right. And these are all functions. They're gonna allow us to extract data on do some analysis. So as different from a regular if function, we're gonna provide the criteria, but we're not gonna tell it what to do. The function is going to tell it to some count or average or whatever. This just to be clear, I've made some name ranges here. I have my name, range area up. I made some name ranges for these different columns. And when they use because gonna make life a lot easier than going through it highlighting stuff, right, So we're gonna use the name ranges here. Let's start by doing account. I want to do account to figure out how many instances of each of these lines there are. So when you use count, If so, let's do it equals count. If Cassie have two arguments here, my first argument is going to be my range. The range of them analyzing is going to be my lines right. Someone just gonna start typing in line right again. It gives me my name range. I could just use that. What line am I looking for? This specific line listed right here, See, is giving me a result of 18 it enter. I can copy this down, and there we go. Now, if I want to check my work, and you can just make two of these to be the same and the value should be the same Or I could make one of them to be something that doesn't exist, and it should give me a zero. All right, let's do this on a cross for these other ones. Let's do a some If now we're gonna notice that a lot of these have the same arguments. Some if and these others are gonna have additional arguments. So the 1st 1 is again going to be my range of analyzing the lines. My criteria is I'm looking for this line. But now I can tell it a different some range. What values don't want to some? Well, I wanted to some of these last year production values. Copy that on through. There we go. Same thing for average if analyzing my lines, I want to look for this specific line. And I wanted to average the last year production data here. Very good. Now my max ifs and men, if they're gonna be a little bit different No said it's plural and all the other ones have plural counterparts. Average ifs, some ifs. We use that for if we have multiple sets of criteria, right, we only had one for these. So that's where we only had to use. The one just doesn't give us a singular version for Max or Min if right, So choose Max ifs. The first thing I need to establish is the range of cells that I'm analyzing for or calculating for the max value. So that's gonna be my last year production. Now I establish my criteria range. I'm analyzing the lines and lastly, I establish my criteria looking for this line, and it should show me the highest value Men. EFS is gonna be the same thing. Same process. Put our range last year production. Let's put our lines as our criteria range. And then let's go ahead and look at this cell for our criteria. Copy that on through and it's showing us our smallest values. OK, so this is great. We can use these functions to extract data where supplying the condition and then the function is controlling what the final action is. 16. Using the IFERROR Function: Sometimes an error is not actually an error. There. Certain functions, like averages, anything that involves an average right or a look up function, where if there's no value, will give you an ever to say in a error, it's not applicable or like a div. Zero error. But maybe it's not in error. Maybe there's just no values yet, like what's happening with our averages here? We're calculating the daily average down here, and we have an error here because we can't divide by zero there no values. If I put the value in here, the error goes away. All right, so I want to be able to change this so it doesn't show up as an error. Maybe it shows up with zero or it says no values. Okay, to do that, we're gonna use the if error function. If ara function is a logical function that allows us to tell it what to do if there is an error and what we're gonna do is we're gonna just wrapped the if era function around this average that we already have. So when I click here between my equal sign in my average and I'm going to start typing if error when I open my parentheses, e And now I'm inside of the if error argument. So that's a good lesson as well. For nesting. We know how to nest when we have a function. We're putting something inside of it, but you can also wrapper function around in existing formula. All right, so we see here that if error has two arguments, the value, which is the formula and then the second argument is what I wanted to do If there's an error. And I think if there's an error, I wanted to actually put a text value in here to say no data has to go in quotes because it's a text value. Close parentheses. E enter. So these first ones are gonna be the same. But if I copy this on down, let's go ahead and copy this down. It takes care of my error for me. So now, instead of it saying an error, it says, no data. Awesome, awesome, awesome, awesome. We could have changed this so instead of it, says not, saying no data could have just been a numerical value. Let's do zero Copy that on through. Now it's just doing a zero instead. Okay, So however you want to do that, you can. The advantage of this also is if there were any formulas looking at those formulas, it wouldn't affect those latter formless either. So the error wouldn't compound to other areas of our spreadsheet. So that is awesome. I want you to practice all this stuff, all these logical functions. There's a project file, want you to go ahead and open that. Try some of these out, create some scenarios on your own. And the more you practice this stuff, the better you're gonna get at it. 17. Understanding Lookup Functions: I want to introduce you to another category of functions called look up functions. The look of functions are a little bit different. Most other functions just perform a calculation right there in place. Look up functions. Job is a little bit different, is a calculation. But sometimes what it's doing is only a part of a larger calculation, or it's affecting other things somewhere else. In your sheet, A look up function simply uses a unique identify, like a product number on employee I D to retrieve information from a large array of data. So I'm working on some data here, and I want Oh, you some other information. Let's say, for example, I'm filling out a list of sales for, you know, my company. And one of things I'm putting is the employee who is making the sale, and I need to calculate how much commission each employee is getting. Or let's say I am ah national company or global company, and I'm entering sales, and I need to calculate the tax rate. But the tax rate is different based on the location, right? If I have that information that never list, let's have another list has each state and the proposed or the specific tax rate there. I can use a look up function to go and find the particular state that I'm using for that particular entry. Get the tax rate and bring it back for me. And I could even didn't use that as a larger formula. So that's what they look up function does. It takes a unique value. It finds it all, matches it up in another list and then brings back whatever information we want to bring back. Now there are two basic kinds of look up functions. There are other functions that fall within the look up category. But typically when we talk about look up, there are two of them, horizontal and vertical. You have H look up, which is horizontal. Look up and hear. The arguments for each look up will get deeper into the arguments and what they each are as we learn how to use these functions in other videos. And then we have the look up, which of course, would then stand for vertical. Look up. All right. There are other look up functions that we will discuss that kind of are a little bit even more powerful than the specifically will learn about index and match. And you know any combination of those four functions? Index match V N H. Look up. You can pretty much retrieve whatever information you want from wherever you're trying to look. All right, so we'll get deeper into those. But this is a base understanding of what look up functions are. 18. Using VLOOKUP: the look up is probably the most common of the look up functions. Right? Stands for vertical Look up we can use. We look up first of all when we have our headings at the top of our columns. So that's the base prerequisite for V. Look up. There are a couple of other prerequisites as well that I'll discuss as we walk through using one. But let's look at a simple example here. I haven't employees look up sheet here, and what I want to happen is when I choose a specific employee I d. From my drop down list here. I wanted to go ahead and populate all of this information. What for? Now, at least down to the health plan will deal with the dental in another video. But I wanted to populate all this information. What? Where is that information coming from? Well, I have a list over here of an employee roster, and here we see the same employee numbers. So what we're gonna use V look up to do is go find whatever employee number, whatever value us here is gonna come over here, is gonna find that value and then it's going to retrieve whatever information we want. So first name, last name and all the other stuff will have it retrieved that. All right, so let's walk through. And don't be scared of this, right? We look up, it sounds intimidating sometimes. What in the world is a V? Look up. It's not that difficult as we begin to learn how to do it. All right, so let's let's do this thing. I'm gonna go ahead. Oh, by the way, I've already created named Ranges. Just want to give you a heads up as why I have my named range area up over here. So I already have some name ranges created that I will be using as we go through some of these different exercises. So already have a named range, and this is something you're gonna want to do. I've already created the name range that represents this entire list that I'm gonna want to look into. You're gonna want to do that because it's going to make life a lot easier for you when you're doing your V. Look up. Okay. Okay. So here we go. I'm clicking in cell B five equals the look up open parentheses. E right again, just for to be thorough. We know that we could also go to insert. We could go to function if we look at our look up area Here we see the look up a sitting there as well. All right, so we can do that also. All right, first argument is the search key. The search key is the thing that you are looking for. If you're coming from an excel world, this is look up values what they will be called in in the excel world. Right? And this is the thing that the two lists have in common. It's what we're looking for in our larger array of data. We know that we're looking for the employee numbers. I'm gonna go ahead and click on that. It's sitting right there. Now, What I'm gonna do here is I'm gonna go ahead and make this absolute cause I'm gonna copy this down to my other ones. So we know that we need this to be an absolute reference. Doesn't always have to be. In this case, it's going to be OK. So this is the thing that the two lists have in common. This is what we are looking for in order to use V. Look up the look up value has to be the first column in the area that we look into, and we're going to specify what area we're looking in next. But the look of value has to be the first column in that area, and the value that we want to retrieve or bring back has to come to the right of it. All right, So in this case, if I want the first name, that means that the employee number has to be here. And then the first name has to be in a column to the right. If that is not the case, let's say for the name was first and the employee number was later, I could not use V. Look up for that. I have to use an index match situation. All right, so I just want you to keep that in mind. Let's put our common here. Next thing we have is the range right or what we call the table array as well. This is the list that I'm looking into again. This has to start with our look up values. So whatever range we've highlighter put here. Look up. Value has to be in the first column, and then, obviously we also need to include the values that were retrieving has to be a continuous range. We already have a named range for this called employee roster, so we're going to use that. This makes life a lot easier. The next thing that we have here is the index or what we call the column index number. And this is the number of the column within the array that we're looking into that has the value that we want to bring back. So I need to go over to my array. I'm going to switch over to the employees list here. I want the first name so I can count the columns. First name is in column 12 Now, it's just a coincidence that it's also the second column on this sheet as a whole. I'm not really concerned with that. I'm only counting within the cells I've highlighted. All right, so I'm counting the number of columns within the erect. If this data was all shifted over here somewhere, it would still be column two that we're putting here. Okay. All right. This is good now. It's working already, but we have one more thing that we want to do. We haven't is sorted option or a range look up area. And this is where we can decide whether or not we wanted to do in the exact match or in approximate match. Nine times out of 10. You wanted to do an exact match, meaning if I'm looking for employees s C 5805 I wanted to find specifically that. And if it cannot find that I wanted to give me an error. If I leave this argument blank or put the number one or the word true in there, it would do an approximate matches going to find the next closest thing. You're gonna know this, that our if you know, beforehand, if I look at this, it's telling me Eva is the first name. Eva is not the first name that's here should be John. And the reason for that is because we have not specified to do an exact match. I want to put a common here. It's going to default toe faults here with zero there to indicate faults. Enter and there we go. It's given me, John, as the first name. This is awesome. I'm gonna go ahead and switch to a different employee number, and I can see it putting the name for that specific employees. I'm gonna go ahead and copy this on down to health plan. And in order for this to work, I'm gonna need to adjust the column index numbers that they're looking at now, later on, I'll show you a way where we could have it automatically do that. But we'll save that for another video. Eso I want the last name, which is in column three than the division is in four departments and five. All right, so let's go ahead and start changing some stuff here. So I'm just going to use my formula bar here. Just changed this column in next number 23 Division is in four, I believe, actually, five. Sorry. I forgot that fast. Well, I think I may have said the wrong number, and then the department is in six. Good. And our health plan we didn't look at that. One health plan is in eight. It looks like Let's go ahead and update this again. I'll show you a way. We could have this automatically happen for us later on. And there we go. We've got ourselves a V Look up. I can choose whatever employee number I want. That's populating all of this with the correct information. 19. Using HLOOKUP: All right, let's do an example of an H Look up, H. Look up. Stands for horizontal. Look up. We're gonna use that when we have headings at the beginning of each row instead of each column. So if we take a look here, I want to know if a dental plan is included in the specific health plan. But that information is down here. So we have our headings here, like so. All right. And then we can kind of see what's going on over here. So we're gonna use h look up to look for the health plan. I see health plan here is the heading is gonna look for the health plan, and then we're gonna tell it what row has the value that we want to bring back. So we want to bring back the dental information, which is in the second row. All right, s so here we go. Let's go ahead and do this equals h. Look up. Same exact arguments, guys. Search key. I'm looking for what? Looking for this specific health plan? Comma range one. This whole range. I've created a name range called Health Plans. So let's go ahead and grab that index. What row? This is a row in next number, not a column In next number. We know that our dental information is an road number two here. Glad. Put that comma. Zero for an exact match. Clothes are parenthesis The C Let's see what we have. Health plan PP one. Did that have a dental plan? Yes, it does. It is returning a. Yes. Let's switch this to someone else. BC one. Yes, Let's try somebody else here. See if we can find one of the nose. There we go. B C. Three. No. Right, So it's doing the same thing. Except our information is horizontal, so we have RO index numbers instead of column index numbers. 20. Using INDEX and MATCH: so we see that V and H look up are very powerful. They are limited, though, right? And they're limited in their names. V look up. Vertical look up. Things have to be vertical h Look up cars on to look up. Things have to be horizontal. And we are also see, the values that we are retrieving have to come after the values were looking for. Well, in this situation, that's not the case. I want to be able to return the item name for the product idea or based on the product idea over here. All right, Now, if I take a look, though, the name comes before the i d number. So normally I would be able to use if you look up to do this. I can't do that because I can't put the negative column index number here. All right, So what we need to do is use a function called Index, and we're actually gonna learn how to use two different functions. We're gonna learn how to use index. We're gonna learn how to use match, and then we're gonna learn how we could combine them together to make a very powerful function. All right. So Index basically allows me to analyze a range and return any value at a specified intersection of a column and row number. So basically, I can tell it Hey, look at this range right here and bring me back the value that's at column three, Row four, and they will go here and say, All right, $5. Okay, so in this case, we're gonna be very specific. A 987 is in Rhode number four, and the value we want to bring back is in column. Once we're gonna use index for this, a name ranges already created here for our product table. So we're gonna utilize that equals index open parentheses, E. All right. The first area that we put is the array, or the reference that we're looking for now is very important that we exclude row and column headings when we do this right? So we already have a range called product table. Let's use that comma. Specify our road number. We already said that this is gonna be in row for specify our column number. This is coming from column one. Enter and there were ghosts returning the value here of serving spoons. Okay. Now, that by itself, is okay. The problem is, when I copy this down now, I'd have to manually come in here and figure out all right. This guy here is roast six and have to change that to Row six. And then this guy over here is whatever role that is road nine and have to change that. The road nine. So, you know, I have to manually kind of come here, and I didn't even get that right. I'd have to come down here and do all of these manually, right? Which it's not good. I mean, I only have, like, six values, so 56 value. So it's not a big deal, but it's still annoying, right? So I don't wanna have to do all that so I can use a function called Match to kind of expedite this. Now, the match function by itself doesn't really do much. But if you combine it together with other functions, it could be very powerful. Basically, the match function tells me the order that something is in within a range. So, for example, I want to look at we already know this is row for cause. We count it, but I wanna have match. Come and look for a 987 within this product, I d range and tell me what position it's in. I wanted to tell me it's the fourth item in that range, so let's take a look equals match. Of course, this is under the look up category, guys. First thing is the search key or the look up value right. Looking up this value comma. Next thing is the range or the array. I wanted to look inside of my product. I ds com. 1/3 thing. Search type 01 An exact match. Close parentheses. E There we go. So it's telling me, and if I copy this down, it's letting me know what row each of these things are on, huh? So that's pretty cool. So by itself, again, not really doing much for me. I mean, I can come in here and I could tell it instead of looking for the number four. Specifically, I could tell it to look a inside of this particular cell, right, and that's kind of helping me out. But, you know, I don't wanna have to do that. So now that's That's kind of working out there? Um, I guess, But what I want to do is use match inside of use, match inside of index. I want to use match inside of index. Now, to do that, what I'm gonna do is come right over here and we're gonna use match to specify our role number. Now, what I could do is I could use it to specify the road and column number if the column names were exactly the same or if I had, like, other headings in a row not going to do that here. So what I'm gonna do is I'm gonna say equals Index. We already know our reference is the product table comma. I'm gonna use match to specify my roll number. Match open parentheses, E Let's look for a 987 inside of our product. I d range. And let's get an exact match for that. Come comma. Now, our column number. We're just gonna put a hard one here. Copy this down and there we go. It's working for me. Fantastic. Now, if I wanted to, I could use match inside of RV lookups that we did earlier. If I come back over here and look at my column in next number here. I could put a match in here so I could say, Alright, let's match up. And I didn't do a name range for this one. When the do it manually, let's close this up so I could see Let's match up on Let's look for this specific column. So let's look for this column name right here. Let's look for it inside of these headers right here. Make that an absolute reference. Let's get an exact match. Close parentheses. All right. Into that end. Now it's going to give me an N a right now, and it's because the names are not exactly the same. So if I just changed this to first name Tutta now we are good. Change this to last name and we will be good. Okay, so now watch what's gonna happen here. Come on, behaved. Watch what's gonna happen here? I can copy this on down and it works. It's automatically filling figuring out what the column in next number is for meat. Right? So we start using match inside of our look up functions Index H. Look up V. Look up. We can create a very powerful tool. And, of course, index match will allow me to look up anything from basically anywhere 21. Comparing Two Lists with VLOOKUP and IFERROR: often times When were you thinking about V? Look up or h, look up. We're thinking about looking into a large list of data and doing some analysis, but you can actually compare one column to another to see if a value exists. For example, I have a list of products sales here, and I want to be able to see what products have not yet sold. Somebody gov look up to do that. Here we go. Equals V. Look up. It would help if I clicked in the cell. Right There we go. Equals he look up open parentheses, E. I'm gonna look up the product. I d comma. Now, I'm not gonna do a name range for this. I'm just gonna highlight these. I could do one, but I just didn't school. Hadn't made this absolute right. I'm looking in there. Column in next number. There's only one column. So is one and my last one here. Let's do a zero. All right. So I could do it this way. But you know what? I think I'm kind of changing my mind with one that what I want to do here overall. So let's look inside of the whole list. Instead, let's look inside of the whole list, and what will do is we'll have it returned. The sales value for us, right? Eso Let's have it. Go back and look in column number two, we'll do a exact match. Close parentheses. E. And there we go and copy this down and is giving me my sales values now. Where? There It cannot find this product. I d is giving me an error rights giving me an error. So what I want to do is use the if error function, right, Go back and watch the video on our logical functions. If you are not familiar with that, I'm gonna wrap if air around this. I would say if error open parentheses. If there's an error in this formula and I wanted to say no sales close parentheses. E There we go. Let's take a look at that Result was copy this down and there we go where there are no sales. It is saying no sales. So we're able to use we look up and just look at one column. I know I added that second column there just to bring the sales value in, but we could have just looked at that one column if we wanted to, and it would have done the same thing we can use if error to deal with that hole in a situation where there are no values, I want you to try this out. I want you to experiment with this a lot of different ways. Go grab that project file that's associated with this lesson and want to take some time and get some practice with your look up functions. 22. Using CONCAT, CONCATENATE, and TEXTJOIN: text functions can be very useful. We can use them to clean up data. Sometimes we have data that separate that needs to be joined or join that needs to be separated. Or we have casing to clean up all kind of things that we could want to adjust with our text characters. We're gonna take a look at a few different text functions here that we can use for joining . Now all joining is kind of based on the premise of the contaminate function. But there are three different versions we can use. Let's start with the version of Can Cat. So have the first name and last name here. Can Cat allows me to specifically joined together to values? So I'm gonna say equals Couldn't cat concatenation off to value specifically value one value to. So I'm gonna put the first name comma in the last name Enter And there we go. There's their full name all the way down. All right, now the limitation here, I want to put a space in here, right? So I'm gonna put space and quotes hit Enter is going to give me an error. Right? Because can cat limits me to two arguments. So if I want to do any mawr joining where I have more than two, I need to use concoct innate equals. Can Captain E here? I can do strings, and I could do multiple strings. I could do it as a continuous range if I want it. Okay, But that's going to do the same thing is gonna mash it up. So I'm gonna go ahead and do the first name, a comma for my next argument into a space open quote space, Close quote, comma. And then I want to go ahead and get the last name here. Enter and there we go. Copy this on down. Beautiful. All right. What if I wanted to join a bunch of things together and they all have a common delimit? Er, for example, I want to create a employee label here that includes the number first name and last name, and I want to separate all three of them. Let's say with an underscore, I'm going to use a function called text Join equals text join. Now, the first thing texture is gonna ask me for is my delimit er someone to go ahead and put an underscore may need to put that in. Quotes underscore, Comma, Do I want to ignore empty cells? I'm gonna go ahead and put true to ignore empty cells, and now I can put my text range. I could do individual ranges or could do a continuous range. Close my parentheses. E hit. Enter and there we go, joining all three of them together. Let's do a dash instead of an underscore, so it's a little more easier to see joining all of them together with a dash. Copy that down and there we go. So can Cat is limiting me to joining two values? Could Katyn eight. Let's we join as many as I want. Ah, and text joint allows me to join things together using a common delimit er. 23. Splitting Text to Columns: Sometimes we have data that is joined that we need to separate. And one of the ways we can do that is using the split text to columns. Feature. This is great. When there's a common delimit er so here I have a part number and I want to split this up into category manufacturing, part number and product greed. Let's go ahead and highlight. And what I'm gonna want to do is I'm gonna copy and paste this over. You'll see why, in a moment from the Duplicated like So I'm gonna highlight all this data. I'm gonna go to data and I'm going to choose split text to columns. Now it's gonna ask me for the separator. This is the delimit er there are some common ones, right? Common ones or commas, Semicolons, peered spaces. In this case, my delimit er is a dash. So I'm gonna need to do a custom do custom gonna put my dash and I could see it working right away Hit, enter if I want to And there we go. So you can see why I copied at first. Because it whatever column the data is in is gonna become column one So if you want to keep the original and split it out, you need to duplicate it first. So now here we go. We have all this data. I can go ahead and align it However I want Fantastic. So split text to columns. Very easy feature to use. 24. Using LEFT, RIGHT, and MID Functions: we can easily separate information out using text functions. Let's take a look. I have an employee code here, and I want to separate out different pieces of it to tell me the department. Fix that column with their the division and the extension. So let's start with the department now. The department is at the beginning of my code. Here's the 1st 2 characters at the beginning, to be exact, so I can use the left function. The left function allows me to extract a certain number of characters from the left of a text during so for department, I'm going to say equals left. I have to tell it the string to look at. Let's look at Cell D to comma. Have to tell it the number of characters I want. Unlike two characters, simple close parentheses. E enter. Let's copy that down. Here we go. Easy stuff. Now if there's a left function that would stand to reason that there's a right function and there is the right function will pull from the right. So I want to grab the division and the division are is gonna be the last three characters from the right of this text a ring, Same strength I'm gonna say equals, right. Analyze this text ring. Give me the last three characters And there we go. We have the division, Easy stuff. Now the mid function does the same thing. The only difference is I would specify what my starting character is going to be. So my extension is five digits. 12345 and it starts on. The third character in the string equals Med. Analyze this cell. My starting at is going to be character number three, and the length is going to be five characters close parentheses e enter and there we go. Were able to separate out the extension. So depending on where the values air coming from, that's going to determine which function you're going to use to extract whatever you want to extract. 25. Using UPPER, LOWER, and PROPER Functions: we can use some text functions to adjust casing. Let's take a look here. I see that I've got some weird things happening here with some of my data here. So my employee numbers are all over the place. As far as some of my characters are lower case, some or not. If we take a look at our first and last names, we can see that there. Some are all caps, Some are not. So might be all lower case and then with my email. I don't really want capital letters in there, so we're gonna fix these now. In order to fix them, I'm going to insert some columns. And once we get everything fixed, we'll leave it the way this you can see the difference. But real world, we would replace the existing information with our new stuff, and then we'll just do the email right over here. All right, So first thing I want to do is I'm going to use the upper function. The upper function allows me to capitalize everything that is inside of the text drink. So when you say equals upper, I simply need to tell it what cell I'm looking at wanna look at these cells Enter. It's copy this down. Boom. All that works for me fixes those things. Go ahead and grab all of this. Let's copy it. And ah, Then we'll do a pace special over here and well paced the values on Lee. All right, so there we go. Beautiful. We can get rid of this column now. I can use a proper function if I want to have it fixed names or, like, sentences where capitalizes each beginning letter or each word for that I'm gonna use proper So equals proper. Tell it what text I want to deal with. Enter. Let's copy that down. Boom. Fixes all that for me. Same thing over here. Go ahead and fix these. Marvelous. All right, let's go ahead and paste special. This data at it pay special values only. We'll clean this up, get that out of the way. Same thing here. Just paste our values on Lee, right? Every gun that we can clean this up. All right. How about the email? I want all my email address is to be lower case. So if you have a upper would stand to reason, there'll be a lower right, so equals lower. Look at this cell reference. Enter. It's copy that down. Very good. Same thing here. Let's copy this, or we can cut it. Actually. Go ahead and do our paste special values on Lee. All right, we'll get rid of this. And there we go. We've been very easily able to clean up our data in terms of casing using some simple text functions. 26. Using the LEN Function: Sometimes you may need to know the number of characters in a text string. The lin function is gonna allow me to do that. Maybe I want to use that to make sure that when I've imported data, the data that came in does not exceed a character limit, For example, may be on to check these email addresses to make sure, but they fall within a specific limit. I'm gonna go ahead and put in equal side here when the click on or actually was insert our function. That might help Lin. Let's look at the length of this text during enter. It's copy this down. Beautiful. Now let's say that I wanted to use this is a part of a logical function. Let's call this email status and let's say that all my emails should be 20 characters. Email addresses should be 20 characters or less. All right, let's take a look and see if things meet that rule. So I'm gonna put the if statement here. If land here is less than or equal to 20 then we will have it say good status. If it's not, we'll have it say bad. All right. And there we go. We can copy this down, and it's gonna go ahead and work for me. All right. Fantastic. So, Len, confused by itself? It can be used or nested within other functions. But the general purpose of Lin is to show me the number of characters in a text string. 27. Removing Duplicates and Trimming White Space: Let's take a look at how we can remove duplicate values and trim extra blank space that may be in the cell. Because sometimes that blank space can really mess you up When you start doing certain formulas and things like that special, like a look up function or some if, where it's looking for a specific value, it includes that space as a part of the character value. So we might want to clean that up. So let's take a look at how we can deal with this. First thing we're gonna do is remove some duplicates. I'm gonna highlight this whole list. I wanna go to data and I'm gonna choose. Remove duplicates. All right. Gonna tell it that my row, my data has ahead a row. Now I can choose what things I want to look for. So I could look for and analyze everything like any duplicate rose completely. Let's check. All right. No duplicate Rose found there. How about any duplicate combinations of I D and shirt size on the duplicates found there either, huh? How about any duplicate combinations of just shirt size? How? We found some duplicates. All right, so it says it finds 17 removes them. Only five unique ones remain. That doesn't tell me what it removed. No, it just wants to remove stuff. So I'm gonna undo that. So we wouldn't want to analyze our data first. See what things air duplicating. So we making sure that we're removing things we actually want to remove. I'm gonna highlight my employee names. Now, if you look at some of these employees names closely, we have some extra white space at the beginning of some of these names. That's probably some space. In the end, I'm gonna go to data and I'm gonna choose trim white space. And you can see that the ghost or it tells us that trimmed white space from the 12 selected cells from 12 of the selected cells. And there we go, gets rid of all that space. Let me undo that. So you can see. Look closely. Look at that. See out moves some of that stuff back over to the right. Go ahead and trim are white space again. And there we go. It gets rid of all that white space for us. All right, now, I want you to practice this. We have a project file for this lesson. I want you to go and take a look at that. I want you to get some practice using these text functions. 28. Understanding Date Functions: Let's discuss date and time functions whenever we have dates and we want to, Let's say, do calculations involving multiple dates. Maybe we want to find out the number of days between two dates, or we want to extract a part of a date, maybe a year or a month. Maybe we even want to know what the current date or dating time is. Lots of different functions that we can use to help us out with that if we go take a look at our function categories. Insert function. Take a look at date. You'll see we have a whole bunch of different date functions here that are going to help us out. With that, we're going to take a look at a few of these in another video. I want you to know that those functions are dynamic, meaning that they are going to automatically change. Let's say we were using the today or the now function these air functions that allow us to see the current date or dating time. Those are dynamic meaning that tomorrow they're going to change to whatever that date is. But maybe you don't want that. Maybe you just want a time stamp or a date stamp. I'll show you some shortcuts we can use to do that. Now, these air not formulas. These are just shortcuts to paste in the current date or time for the current date. I'm gonna hold control. Or if you're on a Mac, you're gonna hold the command button and I'm gonna hit my semi colon and that pace in the current date that you can see here. This is not a formula. This is all text. If I want the current time, I would do control colon. So control shift colon, and that will paste in the time again. Take a look at that. And you can see this is not a formula that is just text. So those air time stamps control semi colon for the date control colon. For the time, we're gonna take a look at some of the dynamic date and time functions. In another video 29. Using Today Now and Days functions: Let's take a look at a couple of the dynamic date and time functions that we can use. So we saw how we could do time stamps and date stamps. Let's do some functions here for the current date. We would use the today function, so I'm gonna say equals today. Open parentheses, E. Now we can see that the today function, unlike other functions, does not take any arguments, right? It doesn't take any stuff from you. All right? Don't play any games. So no arguments. There were just gonna close operate. See, here they enter puts in the date. Noticed that this is a function, though, right? As opposed to us doing our control Semi colon, where it's just putting in that date. So that means tomorrow when I come in here. If I would open this up, the date would automatically change to tomorrow's date. How about the current date and time will. For that, we're gonna use the now function. This is another function. Doesn't take any your stuff. All right, equals. Now enter. And there we go. It puts in the time that we can go in and we can adjust formatting for all these different things, and it is beautiful. Now I want to do some calculations here. Based on these dates. I want to know the days since the last review the days since the last review. So I'm going to do a subtraction between these two dates. So if you subtract or do any kind of just regular calculation addition subtraction between dates, it's going to do it for days automatically. Okay, so I can say equals. Let's take the latter date right? Current date minus the last review date hit. Enter. That's going to give me a date here for the format. Let's go ahead and change our number format here. And let's format this as number. And let's go ahead and get rid of our decimal points here. All right. There we go. Tells us has been 258 days since the last review. I can copy this down now. If I want to copy it down, I'm gonna need to make the C one. Absolute. Of course. Right. We know that. And I could go ahead and copy this on down and there we go, showing me the days there. Now I want to know the remaining days in a year. Remaining days left in the year that I could do this manually. But because I don't have the last day of the year in a cell I'm going to use a function called Days Equals Days shows the number of days between two dates. Let's do it. So are in date. Let's put the last day of this year. Our start date is going to be the current date. Clothes are parentheses, E. And there we go, showing us the number of days remaining in the year. Now that doesn't look right to me. Eso Let's go ahead and let's put this in quotes. I think that's where we were missing. There put quotes around that date. There we go. That looks more like it. We don't have negative 4000 days left in the air, folks. All right, so there we go. So we need to make sure we wrap that in quotes. We could have told we should have known that because it didn't turn green right to remember . That's an important thing. So there we go. We see a couple of functions there that we can use to do calculations for the current date current time, and we see that we can do differences between dates as well, using simple subtraction or using a function. 30. Calculating Yearfrac and NetworkDays: Let's take a look at a few more date and time functions here. So one of things I want to do is I want to figure out how many years and individual has been with the company. All right, so I have their date of hire and we have the current date. Now we know that if I do a subtraction here, it will give me the number of days. And then I could divide that by 3 65 to get the number of years right. But there is a function called the year frack that I can use to automatically do that for me. Let's take a look equals year frack exact number of years between two dates. My start date is the first argument that's gonna be the higher date. Comma. My end day. It's gonna be the current date. Let's make that absolute close. Parentheses. E There we go. Let's change our number formatting here. We'll just do a whole number of years. Fill that down and there we go. We can see the number of years. I want to know the number of days they have worked this year, how many days where they work this year. Now, I haven't area over here for holidays, and it gives me a list of holiday dates. So I want to be able to know the number of work days minus the holidays, and we can use a function called net work Days. To do this so equals network days. And there's an international version here as well. I want to say network days, my start date. I'm gonna manually do this as January 1st 2019. My end date is going to be the current date, which is dynamic. So that's going to change to make that absolute. And then I have to highlight my holiday range. So let's go on over here. The holidays. We'll highlight our holidays here. Now, if I had a named range for this would make it a little easier. I don't have one, so I'm just gonna go ahead and make that an absolute reference when I copied it doesn't change close parentheses and hit Enter. We can copy this down and Aiken seethe number of days that everyone has worked this year and the number is gonna be the same for everyone since the you know, it's not based on the higher date is based on the current date. They're based on those holidays that are there. How about the remaining work days left in the year? Right. Well, we're going to do a network days for that as well. So equals network days time. My start dates going to be the current date. Don't need to make it. Absolutely. I'm not gonna copied anywhere in date is gonna be the last day of the year. Let's go ahead and grab our holidays here. We don't need to make this absolute. We're not copying it anywhere. And there we go. We can see that there are 25 work days left in the Aren't too happy about that. All right, so that is beautiful. Okay, So several very useful functions we can use here to calculate things involving date and time. We have a project file available for you would like for you to take a look at that. Use that get a little bit of practice with these date and time functions. 31. Using Data Validation: data validation is a powerful tool that we can use to make sure that the specific data that enters that entered into ah set of cells matches whatever rules we have for those cells. So, like, for example, we have this whole data entry area and let's say folk gonna be entering data all the way through here. All right, so this whole areas where date is gonna be entered matter fact, let's put a little border around here so we can know what our data entry area is going to be. So we want to put several rules in place for some of these different columns. The first thing that will take a look at using data validation to do is creating drop down lists. Now, when I create a drop down list, the source of my drop down list can be typed in manually into the data validation window. But it's better for it to be defined somewhere on your workbook. Now, I have a lists sheet over here that has a list of divisions, departments and health planes. Now, we could do named ranges for these, all right? If we wanted to not gonna do that, I'm gonna show you how we can create some of these lists. So let's take a look. Let's do our division first. So, division, department and health plan right in here. So I'm gonna highlight this whole area so we start by highlighting the area where you want your data validation to go. Then we're gonna go to data data validation. All right, so if we take a look here, I can first of all established a cell range that I'm affecting, which I've already done. Then I have my different criteria of data validation, and we have all these different things here. We're gonna take a look at most of these, right? So I could do list from a range, or I could do a list of items, list of items. I would enter them in manually. Right again. It's better to do a list from arranged. So that way, if the values change or things Air added, you can deal with that a little bit more easily than things you manually typed in someone to go ahead and establish my range. Gonna go. And these are my divisions. So my divisions are here in the highlight, like so now one of the things you can do is you could include some blank cells. And that way, if things were added in, they would become a part of the list. I don't think we're gonna add any divisions in, so we'll just highlight those for little. Right? Okay, that's going to show this in the drop down list in the cell. If the data is invalid, I can tell it to show a warning or reject the input. Let's just reject the input all together. And I can also choose to show some validation, help text, All right. And we'll do that with some of our other examples. Let's save that. And now if I take a look here, I see drop down arrows all throughout this column, and there we go. I can use those to fill in data. Let's go ahead and do that for our department and for our health plans as well. Data validation. Doing a list. We're gonna reject any input that is not valid. Let's go ahead and grab our departments, all right? And then let's go ahead and do the same thing for our health plans. So this is great for data entry this is also or can also be useful just in general, if we are doing like a look up function or something like that, if you take a look at one of our videos on the look up, you will see that this is in use where I have a drop down list and we're able. We have V look up. Pointing to that cell where the drop down list is so as I'm making changes in the drop down list is changing other things throughout the rest of the sheet. All right, beautiful, fantastic. Let's take a look at a few mawr here. Let's look at our employee number and our employee number must be six characters long when the highlight the area again. Data data validation. I have myself here, and we can see that we have a formula in place here. To say that as to be equal to a length of six. All right, so that's already there in place for us. Awesome. I could go ahead and hit. Save. There has to be six characters. Let's take a look at the higher date must be on our after today's date. Let's take a look at that data validation. So here I'm gonna manually set this up. So my criteria here I could do date if I want, and that's just going to say, is a valid date or is on or after a certain area. And I don't believe this is going to let me do a formula. So let's go down here and let's do a custom formula. So I want to say custom formula is, um, this date has to be greater than or equal to today. If it's not, we're going to reject the input. Let's put some Ah, little help. Criterion here must be on or after today's date. Let's save that. That's going to give me some errors here because these are not owner after today's date. That's fine, right? If I come down here, I can see that it's giving me my window here. Telling me has to be honor after today's date. Let's put in something that is not owner after today's date, and we can see that it gives me an error, is going to yell at me. Let's put in something after today's date. They're I think they're the wrong date there, but it's working there for me where the date has to be today or later. All right, let's do one or two more. I'm gonna highlight this column here. Has this been sent to I T. Let's put some checked boxes in here. Data validation. Check box, Save. There we go. Throws check boxes in there so we can see all different types of uses here. Four data validation were able to control the input into the cells and were able to set it up. Where we have warnings, we have input information boxes that are popping up, letting people know what should go there. And it's yelling at us if we don't meet the current criteria. 32. Adding Conditional Formatting: Let's talk conditional formatting. Conditional formatting allows me to go into some cells and basically put rules in place. Determine when a specific format should be applied. They're all different kinds of conditional formatting. And this could be very useful for me. The highlight specific values that I need to see or like, certain thresholds for cell or even data that's invalid, where I can use this all kinds of different ways. So it's looking a few different examples of conditional formatting here. All right, so here we go cases that are in stock, anything that has less than cases in stock, anything that has less than 10 cases in stock. I wanted to go ahead and highlight this a certain way and format it for me so that I can know that it's time to re order someone to start by highlighting these cells, right? We always highlight the cells that we want this to be based on. I'm gonna go the format, and I'm gonna choose conditional formatting. Now, when I do conditional formatting, I could do a single color or a color scale, will come back and talk about the color scale in a little bit. So I wanted to a single single color. I see the range I can choose to format. My cell by the father's going to say, is not empty. If I go here, I can use this. And I see all different kinds of formatting, text based rules, date rules, basic logical operator rules. And that's what we're gonna do. Anything that is less than 10. We could see it already working out here. I wanted to take on this format so I can now choose the foreman I want. Let's format it and orange. Let's make it bold. It done. And we see that rule at work here. I want to do a scaling for my totals here so I can see my highest and lowest values. Or maybe I want to see certain breaking points. So I'm gonna highlight this area, will add another rule. Gonna go to color scale now by the fault is going to color it from the darkest values, being the least all the way up to the lightest values being the greatest ones. So I could do that or I could do something specific. So let's actually, instead of using this range, let's actually based this rule on our prices, and we'll do some breaking points here for our prices. So let's say down below, Let's go ahead and change this instead of minimum to maximum. We could do numbers or percentages. I want to change offering these two numbers, and so anything that's $200 Amore is going to be high priced. Anything between 102 100 let's say, is going to be medium price and then everything else year we will allow to be. Let's just set that to zero. Everything else there will have be a low price. So let's go ahead and format these and different colors. Let's make this one green. Let's make this one yellow and let's make this one red. And there we go. We see those breaking points there and we can see the shadings in between those points. Awesome. Last one. I wanted to format the name of this item as red if it is out of stock. So we need to do a formula here. I'm gonna do it for this 1st 1 and then we're gonna copy it down to the others because I'm basing it off of this specific cells So it's at a rule in. We are going to do a custom formula. The former is gonna be equal to D two, which is this cell here equals in quotes, out of stock when that equals out of stock. I wanted to color this red. It done. Now, this 1st 1 is not out of stock. Let's test it out real quick. Out of stock. There we go. It's working its coloring that read. All right, so what I'm gonna do, I see my rule is working. What I'm gonna do is I'm gonna copy this and then I'm going to do Edit Paste Special and when the paste formats on Lee pasting formats includes conditional formatting. And so there we go. We see it working here. All these guys that are out of stock are automatically being colored red Come. So as you can see conditional formatting very easy to apply and very useful. It draws our attention to values that we need to see, and it can be used for data analysis as well 33. Grouping and Ungrouping Data: Sometimes you'll have multiple columns or rows that kind of are related to each other, and you may want to be able to collapse or expand that area in your sheet. We could easily do that by grouping and or ungroomed ping columns or rows. Let's say that I want a group together. These three columns here, the price, the stock status and the cases in stock. Right. I'm going to highlight those three columns. I'm highlighting the whole column, so I'm doing it in the column Headers. I'm gonna go to data the first look down towards the bottom. I see group columns see through E. And there we go groups them together. I can now collapse using this outline view using this. Plus on it can collapse and expand. Awesome. How about some of these rose? Maybe I wanted to group these tea rose here together. Want to grab all these teas and group them together? Data group rose his group these waters as well. So actually highlight the whole road, though. All right. Data group rose. All right, there we go. I think we included are other group in. That's Let's undo that. There we go. We could see everything collapse. Expand. There we go. Awesome. If I want a new group, this stuff, I can highlight data. UN group highlight data on group. There we go. It's very easy tool to use if I want to group or ungroomed things collapse and expand, especially if you have a lot of data. Sometimes you want to do this so that you can more easily get through that data. 34. What is A Pivot Table?: What is a pivot table? You've probably heard of them. What in the world is a pivot table in? Why would I want to use one? Well, I'm glad you asked me that. I'm gonna tell you right now. So a pivot table. Let's do a formal definition and I'll explain further our tools used to query, organize and summarize large quantities of data. And we have several rules for a pivot table preparation, but to expand a little bit further on that, and then we'll get into our rules. Pivot tables are very useful when we have repetitive data as well, so we often think of them as gesturing in use when we have a lot of data and that may be true, but it's really more so if you have repetitive data as well. So if I have a list like an itemized list, let's say a sales list every time there's a new sale were just entering in the details for that sale, everything's gonna be repeating, right? Let's say we're keeping track of the cuffs customer, the product, the person who made the sale, all these different things. They're all gonna be repetitive, and they'll make it difficult for us to analyze our data in any kind of concise, logical fashion because of the repetitive nature of that data, a pivot tables going to consolidate all that data together in each column and roll the consolidated data up into the heading, say that 10 times fast consolidated data. And then what will be able to do is grab those field headings and drop them into the different areas of the pivot table to be able to create a consolidated report will also be able to move the data around. However, we want to arrange the data and the way that we see fit. All right now, some rules. Here. We want to start a data table and sell a one or separate from non related data with an empty roar column. We need to have a continuous range of sales here. All columns must have a unique header field names and occupy only one cell. So no merged columns there no empty rows or columns in the data set. Empty cells are allowed, so again we have to have a continuous range. We want to get rid of any sub totals and again get rid of any merged cells. All that fancy formatting stuff. You don't want to do that. We just want to have a nice, clean, continuous range of cells, and then we can create a pivot table off of that range. I want to show you how to do that in another video. 35. Creating a Pivot Table: Let's take a look at how to create a pivot table. So if we look at our data here, we see that we're meeting our rules that we set for Howard. Data should be we have a solid, continuous range of cells here. All right, so I'm gonna click inside of it. I'm gonna go to data. I'm gonna choose Pivot Table. Uh, I can see my data range here. The whole range has to be a continuous range. I'm confused. To put it on a new sheet or within an existing worksheet, I'm gonna put it on a new sheet and I'm gonna click. Create. All right, so here we go. We have our pivot table area over on the left, and then we have our field list. On the right are a pivot table editor. Let's talk about the different sections of a pivot table. You have your rose area. Whatever we put in there, it'll display as headings of the beginning of each row. Now I have an ad button here, and if I click on that, I couldn't put whatever field I want. So let's just say we're gonna put customer there when we see it. lists each customer as a heading at the beginning of each row. Columns will be the same, but it'll do headings at the top of each column. Yes, I want to put the category of product there. Someone say category. No regard. Drops are categories right there. Values are going to calculate into a numerical calculation based on my headings in the rows and columns. Let's go to values. Unless I want to know the total amount sold to each customer. Within each category, it's to total. There we go. We can see that. All right, this is awesome. Now I can change my calculation here. Switch that to an average if I want it. Why? I can also begin to move things around right so I can grab stuff. And I could move it where I wanted to go. Let's I want to move customer from Rose. Two columns Wanna move category down into the rose? I couldn't do that. Let's say I want to remove customer and just look at categories. I can hit my ex to get rid of that. Let's say I want to add the products within each category and break that information down when you goto ad next to Rose and drop in the product. I d Now we have a grouping there. This is awesome. I can also choose to turn the totals on or off. I have a check box here. Show totals here. So my grand total. I can also see here my category totals. I could turn those on and off, which are my sub totals. So we can basically arrange this. However we want, we'll talk about the filter's area in another video, but we can move fields around however we want. We even have a suggested area up here of different types of things that we could drop in average of quantity sold for each category, some of price for each product, average of customer for each item. So if we wanted to start with one of those and then manipulated, we could do that as well. 36. Working with Pivot Tables: we can easily manipulate the data in our pivot table using a report filter. So I have a filter's area here, and I'm gonna go ahead and add a filter. And for category, I have my products listed here, but I want to be able to view them by specific category. So when you click, add next to my filter and I'm gonna choose category. Now, my category shows up over here is a filter by the fault is going to show all items. When I click my drop down here, I noticed that I am able to choose whatever I want. Let's clear everything out and I'm gonna filter this by or four. T click OK, that filters specifically just for tea. I can add another one if I want. Let's do tea and juice that's doing it 40 and juice. We, of course, can filter using different conditions based on dates and times and things on. And then here were filtering based on the values that are in place. All right, this is great. Now I have a total here for this product. I'm going to right click on this total, and I'm gonna click the show Details option, and what that's going to do is remember, that's a consolidated value. So there are multiple values that are making up that total. When I click show details, it's going to spit out those values on to a new sheet for me, like so. So I look here and I can see these values. This is completely separate from my pivot table so I could do a different set of analysis on this if I wanted to do so, and it is all good. So those are some ways we could manipulate the data in our pivot table using filters and how we can show the details from that consolidated data. 37. Creating A Chart From a Pivot Table: we can very easily create a chart based on our pivot table. Let me simplify my pivot table a little bit here. I'm gonna replace my I'm gonna fill, get rid of my filter. Here. Let's select all. And I'm going to change and move category over here into the rose area. We'll get rid of product. Right? So we're just looking at each category. Okay? That's one of the things you want to do. If you have a ton of data in your pivot table, your chart, it can be unreadable. Right? So it's better to you kind of clean the date up a little bit. Eso Here I go. I'm gonna click in my pivot table, go to insert when you choose chart And here we go. It inserts this information for me. Now it's including several things, including my grand total Will get rid of that and deal with a few other things here. Let's start by just changing our data range here to remove that grand total. Let's go to be six instead of B seven. There we go. All right, we're gonna move this out of the way a little bit. Let's go back in and edit our charts, Amore. Now, my pivot chart is based on my pivot table, my charts based on my table here. So I have all the same settings that I would normally have for a chart. Go back and watch video on on chart creation, and you'll get deep into how to set up all the things regarding your chart. What I want to do is I'm gonna change my pivot table a little bit, and we're going to see the chart reflected. So I'm gonna change this instead of the total here, I'm going to get rid of that. And instead of that, I'm going to add and the quantity sold. And look at that that changes to quantity. Sold their like, so. All right, I can also change the calculation type average. We can see that adjusting there also. All right, so whatever we do in the pivot table is gonna be reflected in side of the chart. All right? I'm gonna change this label here. All right? I could see that happen there. Very good. Of course I conform at my chart. However, I want whatever does I want to do, I am able to do can adjust my chart title. All these things are right here. Okay. So very easy to do. We have a project file that's going to help you get some practice with these things. I want you to open that up. I want you to create some pivot tables, truck rating different pivot tables and putting your fields in different ways. You can get used to it. Try making some charts and you could make a nice dashboard with multiple charts and pivot tables, even on the same sheet. 38. What are Macros?: what are Mac Rose? Well, let's take a look at the formal definition, and then we'll dive deeper into it. A macro is an individual or set of actions that could be used to automate tasks. When we're doing different things in sheets, you may have things that you want to be able to quickly repeat without having to manually do it. Let's say you have something that you want toe format a certain way, or you want to generate a chart that looks a certain way or anything that you would do over and over and over again. We can create a macro that does it for us. Now Mac Rose are going to record what you do, so we're going to see that in another video. When we start creating a macro, we're basically gonna hit record, and it's gonna record everything that we do will then be able to take that recording and replay it on another sheet or in the same sheet or wherever we want. Now what's really happening is when we're recording a macro, it's creating something called scripts for us, and this is code and we'll see what that looks like later on, So this is just a high level introduction to this. And using the recorder is going to allow you to do some things, but to really, truly automate stuff, you're gonna want to get down into the script environment and learn that language and kind of learned how that works. But we are gonna be able to do some basic automation gesture using the macro recorder so macros allows to record repetitive tasks will learn how to create a macro in another video . 39. Creating a Macro: Let's take a look at how to record a macro. So in this scenario, I have this workbook here and I have four sheets that have set up similar. I want to be able to format the header area here on one and have it apply to the others as well. So here's what. We're gonna do it with a recording macro where I'm formatting this hetero, and then we'll replay it on our other sheets. I'm gonna go to tools. Mac Rose. I'm gonna choose record macro. All right, now it's currently recording. I am going to use absolute references, and this means it's going to do exactly what I'm doing in the specific cells I'm using. We do have a relative action here that we can use where it will do it relative to where I start. It'll kind of offset how far removed from certain places. We're not gonna do that here in this course. We'll do an absolute reference. I'm gonna go ahead on the highlight, this header area. Let's just do a little bit of formatting here. Let's set the format here. Let's change the font size a little bit. Let's also go ahead and adjust our text color will go the blue color. Here, Let's go ahead and put a border and you can see it. Ah, listing the things that we're doing. We see the actions that are being set. That's cool. Let's go back and grab that again. Let's do one more thing. Let's go ahead and put a fill color in here. Um, and that will do it Aren't when you're done. So you want to do whatever it is you're going to do when you're done, you're gonna hit, save. All right. Now, when I save this no spaces of special characters someone call this format headers. I could choose to do a keyboard shortcut for this if I wanted to do so. I'm not going to do that here. I'm gonna go ahead and hit save and it's saving my macro. There we go. It saved. It gives me the option to edit the script. We're gonna look at that in another video. Let's go over here. The week 23 go to tools, Gonna go to Mac Rose. And here I see format headers right there when you click on it and there we go. This needs my permission to run someone to go ahead and continue this. Let's just go ahead and give this permission so that it can run allow. And there we go goes ahead that runs that script. Let's do this on 24 Mac Rose. All right, now that it has permission, right? It's just going to go ahead and run. Beautiful. So we've been able to create a macro. When we recorded the macro, it generated a script write code for what we did, and we've been able to easily replay it on our other sheets. Another video will take a deeper look at it. We'll see how we can manage the macro, view the script and just use the manage macro window in general. 40. Working with Scripts: Let's take a quick look at some of our macro management tools. All right, so I have this macro here format headers that we create it. We're gonna go the tools, we're going to the Mac Rose and I want to choose. Manage, Mac Rose. All right, so here I see my mackerel. First of all, if I want to adjust the name of it, I can If I want to incorporate a keyboard shortcut, I couldn't do that. Let's put the letter f there or we have to do a number. So go ahead and do that. If I click on my three dots next to this, I have an option to remove the macro or edit the script. Let's click. Edit script opens up my script here, and I can see exactly what's going on now. This whole script can be adjusted if I want to. So first of all, I can see, even if you've never looked at this before, very easy to decipher, I can see the cells that I am affecting. If I wanted to change something here, I could do so, um, I can see my father options. Let's say I wanted to change this. Let's change this to a 16 5 I could change the color any of these settings in here. I could adjust if I wanted to do so. Come, I can go ahead and save this. And so now the next time I run this macro, let's go back here. Licit update. Let's run this macro again. And this time it runs with that adjustment so we can go in and update anything pertaining to the macro. We can even go into the script area and do adjustments. And, like I said in our introduction, the script areas where you're really going to be able to get more advanced and kind of get out of the box of just the things you can click on here in sheets and be able to do more repetitive things, Lukes. More logical statements and things like that. We have a project file available that you can use to get some practice on this. I would recommend that you go take a look at that, get some practice on it and just kind of dive into the world of macros. Here in Google sheets 41. Creating a Form: forms are very useful for capturing information from other users or even from yourself. If you needed to put some information in, there is a really cool form creation platform that we could use. And that works hand in hand with Google sheets as sheets is going to be able to capture the responses from the form. Very good stuff. Have a blank sheet created here called Product Survey. Let's create a form and link it to the sheet. I'm gonna go to tools and shoes, create a form. It goes ahead that says the new form has been created. I'm gonna click the add questions here link, and it brings me here so I can see my form that sometimes that opens up automatically. If not, you can use that link to get in there. So let's add some questions in here. Let's do a multiple choice here. We're doing a product survey. Which product did you purchase? Put some options in here will be generic product a product, be products, see, and we'll throw in other in there. All right, now, if I want to add now, before I have another question down at the bottom here, I can choose to make this a required question. I have options for deleting or duplicating this question, and we'll look at some of the other question types in the moment. I'm gonna go ahead and click my plus sign to add a new question. I'm gonna change this, right? I can do short answer paragraph check boxes, upload a file. Let's do a linear scale, linear scale, endless label This poor excellent. We'll do it from 1 to 9. How would you rate your buying experience? Make this required, adding another question. We'll do another rating. Never lineal scale. Are you satisfied, man? If at will change it to a choice, are you satisfied with your product? Let's change this to check boxes. Yes, new make this required. We'll add in one more. We will do a paragraph and will name this comments slash suggestions, and we will not make this required. So So there we go. We have a nice survey here. Now we don't have to save this. It's automatically being saved. All changes are being saved. This has saved as a separate document inside of your Google drive, and it is linked to our sheet that we'll see in another video how to preview and send responses from our form, and we'll see how it links to sheets where we can view those responses in our sheet. 42. Working with Forms: our form has been created less preview our form and let's test out the different questions will send a couple of responses and then we'll see how to view those responses both in our form and in sheets. So I'm gonna go over here to the upper, right? I'm gonna click my preview button, this little eyeball over there, and it's gonna open up an actual functioning form in another tab for me. So I know this My different questions that are required, right? I see a little asterisk. Let's go ahead and mark some stuff here. I'm gonna go ahead and submit that I'm gonna choose to submit another response. That's 23 of them did not work when I got it home. Submit. Let's do one more. We do offer product. Be excellent. Yes, submit. Beautiful. All right, I'm gonna go ahead and close this tab here, and I could see the responses areas lit up here now to tell me that I have three responses , so I'm gonna go ahead and click on that responses area, and if I take a look here, I can see these responses here. My three responses I can see kind of a rating of what we've been looking at. So it's been product a product. Be I could see responses here for the ratings here. My yes is in my nose, and I could see my suggestions. Very nice. Now, in the upper right, I have the ability to instead of a summary up. By the way, I could look at individual responses. 12 and three can print them out. Right? I can stop accepting responses from my form. And they have the option here. The view responses in sheets. And if I click on that, it brings me right back over to my sheet that I was in. And I can see all the responses, like so All right, that is awesome. And here I could go back to my original form, go back and edit the questions if I need to do so. All right. So forms is accepting the responses, Aiken, view them in the form, and I convey you it in sheets as well 43. Sending a Form: We've created this wonderful form, and now we want people to be able to access it well. We can, of course, share it through Google Drive but also have a send button in the upper right hand corner of my form here, someone to click. Send now when I hit send. I have several options here I can send via email. This will put the form in the email. I could say include form an email so they could respond right there in the email. I have the option to send a link to the form I can even shorten the u. R L and I have the ability to embed this form in a website if I wanted to. Also, I also have options here for sharing the form via things like Facebook and Twitter. So several options so that I can use to get my form out to whoever I need to get it out to . Now there's a project file associated with this course that I want you to take a look at once you to get some practice with creating forms, viewing responses and sending those forms out 44. Course Recap: Wow. We learned some amazing things in that course. Don't you feel advanced? Don't you feel powerful? Of course you do. I want to remind you of a couple of things that we talked about that I think are useful. One thing is remember when you are using your look up functions, make sure that you are using named ranges and not even just would look up functions within general. When you using name ranges that makes your formula entry a lot easier specific to look up functions. Remember that you could embed the match function into not only index, but be look upon h, look up to really make things a lot more dynamic. I also want you to remember that you can capture information through forms and you can view the responses to that. Information not only informs but inside of your sheet as well. Wow, It's been my pleasure to share this information with you. I'm glad that you learn a lot of awesome stuff, and I'm looking forward to spending some more time with you. In another course.