Creating a user-friendly Data Validation in Excel Displaying help out
of the way
Content
Introduction
I regularly design worksheets into which a user has to enter information.
Of course when I do so, I make extensive use of:
- Cell styles
- Proper labelling
- Data Validation
This post is mainly about leveraging the Data Validation Input Message.
See the animated gif below:
Download
I prepared a
sample workbook for you to download.
Cell styles
When you design a data entry form in Excel it is a good idea to use cell
styles to clarify the purpose of the cells on the entry form and to direct
the user to where his attention is needed.
A simple data entry form might look like this:
Looks nice doesn't it.
Note that I did not apply any manual formatting, I used the built-in
cell styles:
Now as happy I might be that Microsoft decided to provide some pre-defined
styles, they didn't quite finish the job properly. Look at the formatting
options of this "Input style":
See what's wrong? No? They didn't set protection! So if I decide to password-protect
my worksheet, all my input cells are locked. This is why I always add a
user-defined style called "InputUnprotected". And perhaps a couple more
if I need various different number formats to match what needs to be entered.
One for each input type:
- InputUnprotectedNumber
- InputUnprotectedDate
- InputUnprotectedText
OK, enough about styles. Want to learn more? Read
on about styles here.
Proper labelling
You might think this goes without saying, but I've seen plenty data entry
sheets where it is left to the fantasy of the user what information is expected
to go into which cells. My screenshot at the top lacks inspiration a bit,
only the first cell is labelled more or less clear. Though you might argue
that just asking for a name is ambiguous at best :-)
Data Validation
Now to the fun part of this article. Data Validation. Lets click the
cell next to Name and open the DV dialog. Forget about its first tab for
now, you already know what that one's for. In comes the "Input Message"
tab!
That looks pretty useful, does it not? You can set a Title and an Input
message, with which you can show elaborate help! But there is a problem.
It is elaborate and the message doesn't go out of the way:
Also, the popup covers the remaining input cells and is very distracting,
especially if the form contains many cells to fill. And it cannot be dismissed.
You can drag it out of the way, but that causes all subsequent popup messages
to appear in the same place. Luckily Excel does not save that position,
so next time you open the file the popup appears in its nice in-your-face
position again.
I resolved that issue in my sample file in an interesting way, which
I am sharing with you below. You did notice the tiny (i) "icons" next to
the data entry cells, didn't you? Here is how I created them.
- Make sure there is an empty column next to your data entry cells
- Create a copy of the Heading 4 style (right-click, choose Duplicate)
- Name the new style i (yes just i, I'll explain below)
- change its properties so the Font tab looks like this:
- Uncheck the Style's Locked box, we need to be able to click the
cell.
- Now look at the Styles drop-down again:
Notice the nice i icon? The boxes in the Styles drop-down give an impression
of what a cell looks like after applying the style. This includes the Font.
If I would have used a regular name for this i style, the drop-down would
have looked like this (I used 'SomeNameForTheStyle'):
Given that the Webdings i character looks like the information icon I
thought using i for its name might be good!
- So. Let's enter an i in the cells next to the entry cells. Then
apply the new i style to them.
- Now select the first i-cell and open the Data Validation dialog
and enter the pop-up information. Just like shown above.
- Also, to prevent people from changing the i to something else, setup
the first tab of the DV dialog to this:
The FALSE formula prevents you from entering anything into the cell.
That is how a custom formula in the Data Validation works: If the formula
evaluates to TRUE (or 1), the entry is acceptable, otherwise it is not.
I know, I know, you can still hit the del key and the cell will be empty.
Copying and pasting will also do its harm. But this is a good start.
Finally I want to add a nice touch to this new icon: I want to see this
when my mouse passes over it:
This is done like so:
- Select the cell
- Click Insert, Link:
- Set it to these settings:
Note the Text to display and that the cell reference equals the cell
I am adding the link to. We don't want Excel to jump to another cell,
it should stay put.
- Click the Screentip button and enter this:
- Click OK twice.
- Curse because Excel changed the cell-style to Hyperlink so you have
to re-apply the i-style again
You can avoid having to apply the cell style twice by reversing
the order: first define the hyperlink, then set up the data validation and
cell style.
- Cheer, because now your data entry form looks like this if your
user clicks an i:
