This is about a trick of using named range that includes a blank cell; which allows any input even the cell is with Data Validation that allows list.
When you attempt to input anything other than what is restricted by Data Validation, you will probably see the screen shot above which leaves you basically no choice but “Retry”. Actually there are two ways to get rid of this annoying message and input anything to a cell with data validation. Let’s talk about the conventional way first.
A) By setting appropriate Error Alert
The screenshot below shows how to set the data validation from a predefined list. To to that, go to
- Data Tab–> Data Validation –> On Setting Tab
- Allow: List ; Sources: “= $D$1:$D$5” where holds the predefined list that we allow:
Now go to Error Alert tab. By default, the Style is set as “Stop” which does not allow user to input anything else. Nevertheless we have two more options: “Warning” and “Information“.
Below is what you see when invalid data is input under different Error Styles selected:
- Stop – You have no choice but “Retry”. “Cancel” returns original value of the cell.
- Warning – You will be prompted for inputting invalid data, with a chance to proceed with the “invalid” input by pressing “Yes”. “No” –> Input again.
- Information – You will still be prompted; and “invalid” input will be accepted by pressing “OK”. Pressing “Cancel” returns original value of the cell.
So to allow “invalid” input, select either “Warning“or “Information” in the Error Alert tab. (Tips: you may customized the Alert Message in “Error message” box.)
To get rid of the error message without prompt, uncheck the check box as shown below:
You can input anything with this setting.
B) By defining Name to the predefined list; and then apply Name to Data Validation
The trick is to include an empty cell in the range when defining the Name. See below:
(Shortcut: CTRL + F3 to open the Name Manger)
Now we can apply the Data Validation using the name “List” just defined. Make sure the “Ignore blank” is checked.
Anything can be input even we did not change the default Error Alert setting.
Note: Interestingly, applying the range $D$1:$D$6 directly to the “Source:” does not give you the freedom; defining a Name to $D$1:$D$6 and then applying the Name does. Put it in other words, it seems that the “Ignore blank” option only works for named range.
It is actually paradoxical to allow anything be input in a cell with data validation.
The idea of using Data Validation is to prevent user from inputting whatever they want. This is quite important for data analysis because no one wants to work with a messy data base that contains a wide variety of input for the same thing, e.g. Jan, January or J.
However in some situations, a “form” is created using Excel for data collection, e.g. a questionnaire. Data validation is used to create a drop down list to facilitate input. Nevertheless we still want to collect information other than the list provided. Thus the trick just discussed maybe helpful.
Think carefully about what you really want to do! 🙂
Glad it helps.
WOW!! I had looked and saw the various options but did not realize/test that they functioned differently. I thought all I was changing was the icon and therefore the severity of my message. I was ready to start writing som4e heavy duty VBA code. LOL
Completely agree. It’s strange how often this is the case. And yet all that it takes is a few minutes to play about with some of these other options/parameters, if not just to see what they actually do!
Seems like the majority of us are unfortunately hard-wired to ignore what we consider “extraneous” information, even if, paradoxically, that information may actually be of use to us.
Keep up the good work.
LikeLiked by 1 person
And the worst part is some people keep complaining the software is “stupid” because they do not care do learn how to use it properly… ~_~
You too keep up the good work!
Nicely explained. I’d imagine there’d be quite a few people who weren’t aware of these options, so perhaps your post will help prevent some validation-related headaches!
Thanks XOR LX for your kind words! 🙂
Yes I agree with you. In my observation, many users do not care to look at different options offered in various built-in tools in Excel; and most users have an “instant” response to CLOSE error message without reading the message at all. Actually, they are not rocket science. They are just being ignored.