Whoa Nelly!

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.

Find Data Validation on the Data menu
Find Data Validation on the Data menu

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.

I am restricting the data to a date in the 1970s
I am restricting the data to a date in the 1970s

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.

You have a lot of options for restriction
You have a lot of options for restriction

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.

Excel complaining, rather cryptically!
Excel complaining, rather cryptically!

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.

Be brief and helpful
Be brief and helpful

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 user will see this message when s/he selects that cell
The user will see this message when s/he selects that cell

The default setting of Data Validation does not allow invalid data.

The default is to prevent users from entering invalid data
The default is to prevent users from entering 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.

This user will see a warning, but can still input the data
This user will see a warning, but can still input the data
This is what the user sees
This is what the user sees

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.