Whole number vs decimal limitations

Keyboard Shortcuts

Using data validation, you can define a validation rule that limits all future entries into a range of cells so that they must be whole numbers—no decimals entries allowed. With the same feature you can choose to allow decimals and with both settings you can have upper and/or lower value limits.

- [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 quantitiesthat are entered here might fall betweena certain range of numbers.We might also want to ensure whetherthey 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 consideran 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 databefore 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 tomake sure that these entries are whole numbers.As soon as we click the data validationoption 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 timesI 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'vehighlighted cells across multiple columns and rows,that's certainly possible, it certainly much, muchmore common to set a the data validationfor 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 worksthrough the entire column,we don't have to come back laterand 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 customizethis 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 ofthe 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 numbersthat 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 herein 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 canput indicators that indicate what those limits are,both by way of an input message, and an error alert.

Resume Transcript Auto-Scroll

Author

Released

9/19/2016

With Excel's data validation tools, you can control how users enter data into workbooks, ensuring that data is consistent and accurate. You can control dates, times, even the length of the text they enter, or simply provide a list of acceptable choices to eliminate any possible mistakes. Here, Dennis Taylor explores how the data validation tools in Excel 2016 can be used to set dropdown lists and control numeric, text, date, and time entries.