On Wednesday, I demonstrated how to create a drop down list in Excel. In that post I mentioned that one of the benefits of a drop down list is instruction. In this post, I will show you how to create helpful messages that will explain to users why only certain content is permitted in a cell or group of cells.
Recall that restricting the type of data allowed in a cell is called Data Validation. You can find it on the Data tab.
In this example we will restrict the type of content to a date. (This is slightly different from the example of the drop down list, but I am using it so that you can see some other options with data validation.) Choose Date in the Allow field.
Notice that I have chosen to restrict the dates allowed to January 1, 1970 to December 31, 1979. There are other options available. For example, you can restrict input to dates before a specified date, or after a specified date.
Click OK to put the restriction in place. Now I will try to input a date that complies with the restrictions–“January 1, 1970”. Excel accepts that entry. Then I try to input “December 31, 1969”. Now Excel complains.
The message that Excel provides is singularly unhelpful. It doesn’t tell the person who has entered the date why it is rejecting the entry. This is your opportunity to be helpful and do some training!
To set up an informative data entry message, go to the Input Message tab. A title is optional. The text allowed in the message is limited, so be brief and to the point. Click OK.
Now, when someones click into the cell(s) with validation, they will see an input message that guides them as to the allowed data, and more importantly, why the data is restricted.
The default setting of Data Validation does not allow invalid data.
But what if you want to warn users, but not actually stop them from entering invalid data? To do this, use the Warning or Information option in the Error Message box. Be sure to include as much information as possible in the short space allowed. In the Error Message, a title is optional but I think it creates a nicer looking warning message.
Congratulations! You have now improved the quality of the data in your worksheets, streamlined the data entry process, and trained someone on your processes.
Have a great Memorial Day weekend all.