Start learning with our library of video tutorials taught by experts. Get started
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.
In this worksheet, column B has data validation criteria set up. And as I click in cell B2, I see the pop-up reminder, so I am not going to type in a value below 30,000 or above 90,000. So, I will type in a number, for example, for the particular cell I'm looking at, 43,218. Not a problem. I've got another cell, and I am looking at my sheet of paper here. It's $67,509.50. Enter. And the error message here is pretty generic.
It says it's not valid, and I'm saying to myself, "Well, it falls between the range. I wonder what's going on here." I can't figure out what's happening here. What do we need to do in the example here? If we were to cancel and then jump to data validation, we will see the rules, and what we might have overlooked and it might take us a while is that this must be a whole number. It wouldn't have made sense possibly for the input message to say that, we certainly could alter this and that might be one approach. Another approach could be error alert.
Now, it's a little bit of redundancy perhaps--and I am going to simply take this text right here and press Ctrl+C--but we also have control over the error alert that pops up after an error has been made. So, I am more or less just going to put this right here, but at least it explains--although not fully--what's going on here. Why don't I precede this or include it, alter in such a way that the message reads, "Must be a whole number and," let's say what the way we want, "must be." Now, some people love this feature, because they like the idea that their words are here in what looks like an official Excel dialog box. And so sometimes they will get either cute or fancy sarcastic or have fun with the titles and the messages sometimes as well, too.
So maybe I will do that just a little bit here by putting in the word WRONG! That's not that funny or anything, but still, you get the idea. So, the next time someone happens to either use a number that's out of range-- and again, that would be unlikely with the input message already there--but with the error alert, we now have control over it. So, here's a value here, and suppose it happens to be a decimal. And there is the error message that we see here, in our own words, and it gets the point across. I often will use either an input message or the message here, the error alert, not both. But you have the choice of doing both of those.
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.