Hello, my name is Bob Akbar, and in these lessons, we'll talk about the next steps in your Excel education and what is new for 2023. In this lesson, I will show you data validation. Data validation is a very useful tool. If somebody goes in here and types in, say, hello, data validation is your friend that's going to jump in and say, wait a second, you can't do that. This cell must contain between 1 ,000 and 10 ,000, and we can retry. So let's see what this data validation rule is. I'm going to first show you what it is in there. I will go to data, data validation, and here we go. So this is what has already been put into this to start off our lesson here, and you can see the settings. First, it says, what would you like to allow? You can allow any value. You can allow decimals. You can have a list, date, time, a certain amount of text length, or you can have a custom thing. For sales, whole numbers make sense, and we know that our sales are 1 ,000 to 10 ,000, and we can have a message here where it says, and of course, we can make it look pretty by putting commas in there. Whatever you would like. You can put dollar signs. This is a simple text message that pops up, and you can even put the warning on top and copy this and put it down here in the message, and there it is, and there's also an error alert where I can either block it, and we're having a little bit of humor here, and there it is. So anytime somebody clicks on this, it gives us the warning. Now, of course, we don't always want to do that, so let's go ahead and get rid of those here, and we can just separately hit clear all, and also notice there's an ignore blank. You can't have empty values. If you do not want blank values, please get rid of that ignore blank. Okay, so we're going to clear it, and now we can do anything we wish to do. Not allowed. So this cell must contain between 1 ,000 and 10 ,000, and that's all we had in there, and if you hit cancel, it goes back. So what we want to do is if we want to get rid of it, now we have to go back in here. We have to clear all and then apply. Once we hit the apply, and this is a two -step process. That's why I just showed you what happens if you do not hit apply. So now we can put anything we want in here. We can put in 50, and it's absolutely fine with us. So let's go here. Let's use a different example, and this one, you will notice we have Toledo, LA, and New York City as the three options where we have offices. One reason why we may want to limit what people can put in there is the Toledo office might have made a sale in Detroit, but we want it to say Toledo. That's the office. Same thing with Los Angeles. Our San Diego sale belongs to the Los Angeles office. There is no office in San Diego. So what we can do is we can make a list here, go to data validation, and say allow from a list, and the list, it asks us where we would like it to be. So we click in here, and please remember to click in there, and we can highlight the three cells. You will notice it's showing them being circled. There it is. We don't want to ignore the blank because somebody has to get credit for the sale, and it even says, would you like it to be a drop -down? Of course, we would, Excel. Thank you for offering, and we can click apply. So here it is. Now, anytime we get a sale, we can pick which office gets credit for it. Pretty neat. And then if we take a look at this here for the data validation, these are dates, and we want to make sure that stays as a date. Now we can pick a range, and we can say it has to be between, and since this is for the first half of the year, we'll put, and the end date. So this will make sure that we can only put in six months in the date range. If we try to put something else, it will jump up, and it will say, sorry, it has to be within that six months. So if we're keeping track of all these six months worth of sales in this sheet, this will ensure that we have done that the right way. Now here, the sums that come in, these I would not recommend that you try to regulate them here. These are results you will notice. This is the SUMIF function that's being used, and the SUMIF function is taking these numbers in purple, that we manually input it. The place to put the data validation is not to say, we are not going to allow anybody to have sales over 25 ,000. That is not what the data validation is for. What the data validation is for, and the proper use of it, is to make sure that we are inputting the detailed data into our detailed table, where all the details are being stored, has the right range of values. And you will see here the commission numbers. We can also, for our final example, allow any value that is between, all number that is between, and we want to make sure that the minimum our people make is $100, and the maximum we can leave, and we don't want any blank values, because that means they get zero. And look at what happens. If you pick between, it will make you give a maximum value. So that is where the greater that, or less that, or greater than, or equal to, comes in. So we'll simply change that. If you see any fields where you are not putting any values in, just like the ignore blank here, you will have to put something in. So if it's asking you for more than you are seeing, please select the correct data type. Stay tuned for the next lesson, where we'll talk about pivot tables. Thanks for watching.
