This article shows you how to use Data Validation in conjunction with a custom Input Message in order to tell a user what they can enter into a cell. This is a helpful feature in Data Validation because it allows the user to know what to enter into a cell before they get an error message from Excel. This is a good feature to use because error messages often freak people out.
So, let’s use the simple example of wanting to limit the number that a user can enter into a cell. Select the desired cell, hit Alt + D + L to go to the Data Validation window. Go to the settings tab, select “Whole Number” from the Allow drop down menu, enter 1 into the Minimum section and 10 into the Maximum section, and hit OK. Go to that cell and try to enter 12. You will get an ugly error message that does not even tell you what you can actually enter into the cell.
Now, let’s create an Input Message. Go to that same cell and hit Alt + D + L. This time, go to the Input Message tab. Make sure to check “Show input message when cell is selected.” Then, just enter a title, say “Number Restriction”, and a message, say “You must enter a number between 1 and 10.” and hit OK. When you select the cell with Data Validation, you will now see a nice message that tells you what you can input into the cell. This will save a lot of time and confusion.
The other option is to use a custom error message. Go to the Data Validation window and the Error Alert tab. Then, just enter a title and a message, similar to the Input Message section. Though, here you can choose between three types of warning icons so that you don’t have to use the ominous red circle with an X through it. After you do this, if the user enters an incorrect value into the cell, they will see your custom error message instead of the generic one, which doesn’t help anybody.
So, now you know two different ways to tell the user what they can input into a cell; you can use an Input Message or an Error Alert. The input message is best, but either way is better than leaving the user to guess what they can enter into the cell.
