Have you ever created a spreadsheet people used to enter data?
A bit like a data entry form, but on a worksheet?
You may have wondered how to make the form more intuitive. That way your users knew what they should enter in each field.
If that’s the case, this post is for you.

Making Data Entry More Friendly in Excel

I recently created a spreadsheet template for a friend. He was going to use it as a lead magnet.
It was a personal finance template where you could enter your income and expenses and so on.
To make it more intuitive, he wanted prompts letting people know what they should enter in each field.
My friend wanted something like an online form. The kind where a prompt appears in a field when it’s blank and disappears when you start typing.
The prompt reappears if you clear the field.
You would have seen this before on landing pages or squeeze pages where you enter your name and email address.
Something like this:

Now, Excel doesn’t have this kind of functionality natively, so you need to apply a little smoke-and-mirrors.
Here’s two sneaky methods to achieve this affect.
The first uses hidden formulas (sort of). The second uses some good ‘ol VBA.

A Data Entry Prompt using a Formula

Before we look at the ‘trick’ to this technique, let’s take a sneak peak as to how the effect looks.

Here we have a couple of simple fields for data entry.
When you enter some values in the fields, they replace the prompt.
If you clear the entered values, the prompts reappear.
Simples!
Now let’s see how it’s done.
The eagle-eyed among you may have notice from the video above that 'Column C' isn’t showing.

Note that it’s not hidden.
Its width is set to less than 1 (0.08 actually).
It’s collapsed and not hidden because it has our "smoke & mirrors" formula.
If the column was hidden, the formula wouldn’t work the way we want.

Here’s the formula

This basically says, IF the cell to my right (D3) is blank then show the prompt “Enter your first name”.
Otherwise, don’t show anything.
Because 'Column C' is so narrow, when the cell to the right is blank, the prompt text “spills” over to the next cell.
When you enter a value in cell D3, it prevents the prompt text from spilling over, effectively making it disappear.
Like I said; smoke and mirrors.
This technique is fast, simple and easy to implement.
It does have one drawback though.
Because the column with the prompt formula must be so narrow, it really does mess with your page layout.
If you have a lot of fields, it can really restrict the look and feel of your worksheet.
Let’s look at another technique which achieves the same effect but requires a bit more work and requires us to cut some code.

A Data Entry Prompt using VBA

For this technique we need to do 3 things.
First, we need to create a table of prompt values in a separate worksheet.

This lists each worksheet you want to have a prompt in. Which cell the prompt should apply to and the prompt itself.
Next, we’ll need to VBA code to make use of this table.
And here it is:

Essentially the code loads the list of sheets, cells and prompts from the Variables worksheet.
It then loops through the array and checks to see if any of the sheet names and cell addresses match the Target cell which we’re checking against.
If there is a match, it enters the prompt text in the cell and exits.
Finally, we need some way to call this routine.
In this example, we’re going to use one of the Worksheet Events:

Worksheets objects have a wide range of events we can use to trigger, or run, some VBA code when something happens on or to a worksheet.
We’ll use the Worksheet Change event like this:

When our worksheet “changes” – that is, one of the cell values has changed – this event fires up and passed across the cell (called Target) which was changed.
We can now refer to Target like any other range variable.
We’ll just pass it to our DisplayPrompt routine to do all the hard work of ensuring the prompts are kept up to date.
The VBA approach may take longer to implement than the formula approach, but it has several advantages:

  • We’re not restrained in the layout of our worksheet by having almost hidden columns to hold the formulas.
  • The prompts are centralized in one sheet making them easier to view and maintain.
  • If we want to make whole changes to our prompts it much easier to do.

Marcus

Marcus has been an Excel & VBA developer for over 20 years working in the trenches in Investment Banks and Assets Managers, previously in Melbourne and now in London.

>