Viewers: in countries Watching now:
In Excel 2010: Data Validation in Depth, author Dennis Taylor shows how to use the data validation tools in Excel to control how users can input data into workbooks and ensure data is entered consistently and accurately. The course covers creating dropdown lists, preventing duplicate entries, and controlling the format of numeric data, dates and times, and text entered into worksheets. Exercise files are included with the course.
Unlike Excel's many database management tools which are designed to handle information that's already existing, data validation controls data at the point of entry. In column B, we want to put in some whole numbers; in column C, perhaps some dollar amounts. In its basic form, data validation will allow us to control a range of cells and what goes into them. And certainly, in some situations, you will specifically highlight cells because the data is not going to go beyond that range. In this example, let's say we don't know how many entries are likely to be here eventually.
Let's just select column B. And the feature we are looking for is on the Data tab in the Ribbon, Data Validation. Just click the icon. Of course, you might read the tip as we see it here. We are going to control data before it ever gets into this particular worksheet. And many times when you are you dealing with just pure values, you might give some thought as to whether you want whole numbers or decimals. It doesn't really make any difference. Let's say in the example here we are talking about maybe items that are being sold, and based on the items in question, they must be whole numbers, so we will choose that.
If this were a salary column, you might want to choose whole numbers--if we are talking about a yearly salary. On the other hand, for certain other kinds of data, maybe you want to allow decimals. Note the word allow here, in a certain sense, does mean allow, but it also means require. If we put in a whole number here, it will not accept decimals. And we are immediately prompted with a 'between' here, and we can put in values. The last time this was used, maybe these numbers were used. We don't necessarily have to use those of course; we can use other constructions as well.
Maybe we simply want to put an upper limit on these values, or a lower limit, and you can see all the variations that are here, if we want them between a certain range. Maybe the range we are trying to use here is between 1,000--and that is inclusive, by the way. It will allow 1,000 and a maximum of 5000. So, we want to make sure all entries fall within that range inclusively. Click OK. We are also saying that it must be a whole number. So, of course, values like 1,200, that's just going to work just fine. 1300, 2300, these are all fine as long as they are within that range.
Let's put in a decimal and see what happens. 2400.5, and we see a prompt. We can click Retry, click in here and make a change, probably get rid of that. That's certainly is the one option. If you need to look up the value and it's not here--suppose, you put in the value here with a decimal and we just know it's wrong, we can't look it up right now--cancel, of course would be the appropriate response sometimes. In column C, if these are dollar values, and if we do want to allow decimals here--once again, selecting the entire column--which is the most likely way to approach the values for column C, Data Validation, this time we will allow decimals and between a certain amount, maybe these are dollar amounts.
In the example here, they might range from $50. We don't necessarily need the decimal in right here if you want. These are all going to be between $50 and $300--or they must fall within that range, 50 and 300. So we could type whole numbers. There's nothing wrong with that. Let's say a value like 78. That's going to be just fine. We didn't really format yet. We could have done that ahead of time or later. That's all right. It does allow decimals, but why did that fail? It's outside the range, so we will do a retry.
That should have been 65. So I think you quickly get the idea how this works in its basic form. Just by controlling data at the point of input, you will be up to prevent long-range problems that occur when data is bad. Quick and easy to use in its basic form, data validation.
There are currently no FAQs about Excel 2010: Data Validation in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.