Simple data validation or enum fields in Excel
Excel is still often used for simple time tracking or roadmap planning in small IT projects. It is good to know that with a simple steps you can create a basic data validation for certain fields in your sheets.
Kind of validation, as discussed here, focuses on forcing user to select or enter only values that are found in some reference list (dictionary). A classing enum / enumerator field well known in databases. If user provided an incorrect (out of scope) value then an error is displayed:
Unfortunately, due to nature of Microsoft Excel, this fails completely when data is pasted.
Dictionary
First, create a list of allowed values somewhere:
It can be a separate sheet (reference), holding many of such lists. Or it can be the same sheet where you’re going to use this data, because values can be “hidden” using white font color or can be in a hidden column.
Settings
Next, mark fields which will be using this list of allowed values and go to Data → Data Validation:
Then:
- Change Allow field value to List
- Click button in the Data Validation field and select your list of allowed values
Hit OK to confirm and you’re done with a basic approach.
Tooltip
Using Input Message tab you can create a tooltip that will be shown to user whenever they select given cell:
Error
Using Error Alert tab you can define a message that will popup after user enters a value out of range (i.e. not in the list of allowed values).
The Show error alert after invalid data is entered checkbox is checked by default, so you actually have to do nothing in order to activate this feature. But, if you wish, you can customize the icon, the title and the error message text itself of the displayed message.
If you do some customization, you’ll achieve something similar to the image given in introduction. If you keep it as it is instead (empty title and error message) then your user will see a default Excel’s message in that case:
Pasting
As mentioned in the beginning, this feature covers only situations when user hand-written some incorrect value. It is completely insecure on pasting values directly to the cells:
This is the general feature of Excel that has nothing to do with this particular topic. When pasting, you’re overwriting cell’s content along with format and any metadata, including settings for cell value’s validation.