Whole number vs decimal limitations

- [Instructor] In the chapter one file, we're looking at the Number sheet. We've got three columns of data here, and this is being set up for another user. We want to make sure that the quantities that are entered here might fall between a certain range of numbers. We might also want to ensure whether they are whole numbers or decimals. We've also got a value column, indicating the price of the item, or the sale price, and there, too, we might want to consider an upper and lower range, and here, we might want to allow decimals. Data validation is found on the data tab in the ribbon, and unlike many other commands in this tab, like sorting and filtering, data validation is mostly about controlling data before it appears in a worksheet, rather than later. Let's imagine that, in column B, this is the company that handles large appliances, we can't have large decimals here, there's no such thing as half a refrigerator, anything like that, so we want to make sure that these entries are whole numbers. As soon as we click the data validation option on the data tab, we see three tabs, settings, input message, and error alert. Where we define the restrictions, that's where we see settings. The word allow here, at different times I want it to say require, initially we see any value, but here are some other choices. In the example here, we do want to use whole number. Now, by saying allow whole number, what we really meaning is require whole number. In other words, no decimals allowed in the entries here. Now, as soon as we make a choice here, and I'm making the choice whole number, immediately we see what pops up, and certainly, in some situations, we might have a minimum and a maximum here, but let's say for this data here, we only have a maximum, so it's got to be 500 or less, let's say. So, we could say less than, and so the maximum would be 500. By clicking okay, we've set these. Now, I've selected column B ahead of time. Most of the time, but certainly not always, before using data validation, you will want it to select an entire column. There could be other cases where you've highlighted cells across multiple columns and rows, that's certainly possible, it certainly much, much more common to set a the data validation for an entire column. In this list already, item numbers are already in place, but we might add others additionally, They might be five digits even, but by defining data validation in column B, it's open-ended, and therefore it works through the entire column, we don't have to come back later and add additional definitions here. So, we've got a restriction here, I'm going to put in a number that's acceptable, I'll put in another number that's not acceptable. Later, we can come back and customize this message to show why this entry is unacceptable. Right now, we wouldn't necessarily know. If you click cancel, the entry disappears, if you click retry, you're in edit mode, maybe that should have been 467. I'll change it to a four there, and that is acceptable. Now, let's try a decimal entry, five point four. Maybe that was a mistake, a typo, same idea here, that's incorrect, let's retry. Maybe that should have been a 54, somehow we typed a decimal there, didn't mean to, and so on, correct that. For the value column, which would be a cost entry, let's again select the entire column, back to data validation, and on the setting tabs, use allow, this time we do want to allow decimals. Now, that doesn't prevent us from putting in whole numbers, but it does allow decimals. So, this case, too, Maybe we've got a range here in this case, a minimum and maximum. Maybe the minimum value of any of the entries here is 50, and the maximum is 4000. Now, even though these don't show decimals, we can certainly work with them here. Let's click okay. Remember, between 50 and 4000. So, we'll put in an entry here, that's okay, 50 is going to work, 49 is not going to work. We wouldn't know why necessarily, if we saw only this. Same idea as before, let's do a retry here, if that should've been a 59. I put in whole numbers here, let's of course show how we might want to use decimals. These are appliances, they commonly, if there under $100, for example, will have a 95 in them, a 99, something like this. Maybe this price here is 89.95. That's certainly acceptable. Ahead of time, we might also have formatted this column, or later, we could come back and certainly format this. Easiest way for a lot of people, home tab, maybe click the coma button, that's certainly one way to do it, that exposes the decimals too. So, we've got entries here that do allow decimals, and it does prevent us from putting in numbers that are too small or too large. Here's a number that is too large. Regardless of whether we put in decimals or not, that's too large. Once again, we have not yet defined here in the error message why they are too large, but earlier, and certainly if you are working with the data, you would come back and visit, you could click column C, you could then go to data, data validation, it's on the data tab of course, and see the actual definition that's in place here, if you forgot it. We'll show you in later movies how you can put indicators that indicate what those limits are, both by way of an input message, and an error alert.