Another take on Data Validation

To validate data entry and show error messages to your users in Excel, you can do a couple of things.

  1. Use traditional Data Validation (go to Data » Validation).
  2. Use event handlers in VBA.

Both of these methods may be used:

  • To show error messages if the input is outside the expected range
  • To tell the user what range of values may be entered
  • To block data entry outside expected values

Here I'll outline another method I like to use. It involves conditional formatting, with data validation used in a cell. You might use this method with more experienced users who have already been trained on how to use your spreadsheet, and only need a little social engineering. It's a passive form of data validation, since it won't stop bad values from being entered.

Two-Step Process

Setting up the conditional formatting is a two step process — First, set up your error message in a cell and change the font to white, so the cell text is invisible. Put the data validation criteria into a cell and set up the conditional formatting to point to that cell.

Let's set up a sample worksheet with this technique. Cell E3 will hold the error message. Cell B3 will be the data entry cell. D3 is the data validation cell; it contains the formula "=B3>5" which will display true if B3 is greater than 5.

Cell E3's font is white; we'll change that with conditional formatting. Here's the conditional formatting setup for E3:

When B3's value goes above 5, the conditional formatting rules will change E3's background color to red, indicating an error. That's because the conditional formatting is waiting for cell D3 to become true. Once it becomes true, the formatting is applied.

You might ask why I've created a separation between the conditional formatting and the rule. Can't you just put the formula in the conditional formatting box directly and skip the intermediate step?

There are two reasons I do this.

  1. It's easier to change the conditional formatting rule by going to the worksheet than by going to Format > Conditional Formatting and opening the dialog.
  2. By separating the formatting from the rule, we're adhering to the programming principle of encapsulation.

Obviously, it's easier to change formatting rules when they're on the worksheet instead of going to the dialog box every time we need to change it. The second reason needs some explanation.

By abstracting out the formatting rule, we avoid hardcoding the conditions in the conditional formatting. That means the formatting will apply when the condition is met, regardless of what that condition is. We've set it up so that we can easily change the formatting rule, and whatever rule we choose will be dutifully applied by the conditional formatting. It doesn't need to know what rule we're using.

In that way, the conditional formatting becomes a kind of "black box" that takes an input, doesn't care what form it is, and only acts when it's passed a value of true. It will simply apply the formatting whenever the condition we set up in cell D3 becomes true, without regard for what exactly the formula is. This makes our formatting much more flexible.

I've attached a sample workbook with the formatting. Download it here.

About JP


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 6 Comment(s) on Another take on Data Validation:

  1. JP,

    When I teach courses on spreadsheet design I go a fairly large section on data validation, and have always broken it down to two parts. Pro-Active data validation, and Re-Active.

    Pro-Active, using the traditional tools and VBA should be used wherever possible, imo, to prevent bad data from every hitting the sheet in the first place. But we can't always do that of course, which is where the Re-Active methods come in.

    Personally, I like to change my input cells to red if the user enters an invalid entry, but I've also made use of the technique that you mention to feed out reactive messages too. Can work like a charm if it's done well. I'd just throw out to anyone new to this though, that you need to design it correctly.

    I made a huge mistake in one of my older workbooks where I wanted users to enter numbers to make something balance. I started with a red message in the top that said it didn't balance, which went away when it did. Bad move, as people eventually started ignoring it because it was always there and lost impact. It would have been better to start white, like you've done, and show a message when it is okay to print.

    Good article!

  2. Data validation has a long way to go before it becomes effective. During the pre-release time of 2007 the Excel team collected a lot a feedback on their blog on what needs to be improved in data validation and implemented none of it in 2010 (oops..forgot just one improvement …the fact that you can now refer to another sheet in the list validation directly)

    However the following will improve the effectiveness of data validation

    a) Prevent users to paste information on validated cells
    b) Remove validation from the shapes collection
    c) Allow relative names/formulas in custom option of validation
    d) Remove the 255 length limit for formulas in the custom option of validation
    e) Allow for Sort option in the list option of validation
    f) Allow auto complete in the list option of validation

    Data validation through event handlers are a bit clunky as there could be several occasions when the events don't fire…

    • The big one in my mind is (a). If you can just paste over data validation, it ruins your hard work. Otherwise, you need to add VBA to the workbook, which creates its own set of issues.

  3. Defintely don't disagree with that. :)

  4. Richard writes:

    I am utilizing Excel's built-in data validation to restrict certain user inputs.

    I have got a button on my sheet that simply clears the contents of the blocks into which the user enters data.

    My problem is I cant seem to find VBA code to control the data validation used in certain cells (with the clearing of the other cells, I would also like to reset the data validation selection to the first line).

    Can you please assist JP?

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel