You are at their mercy!

When you create a spreadsheet for your colleagues to use, they could type in absolutely anything.  Incorrect entries will make the calculations, which you slaved long and hard over, generate the wrong answers.  As the old saying goes – “Garbage In, Garbage Out”.

Whilst you can’t be looking over their shoulders all the time, Microsoft Excel has a nifty feature to help ensure that the data typed into a spreadsheet is at least in the correct “ball-park”.

You can force them to enter a Whole Number or a Decimal, insist that the entry is a Date or a Time along with a variety of other settings. It is even possible to provide a fixed List of choices for them to pick from.

Data Validation – Settings

In the first part of the process, you define the Settings that you would like to enforce.  Choose [Data / Data Tools] – Data Validation to reach this dialog box:
DataValidation A

In the above illustration, we are restricting the users to entering a Whole number between 20 and 99.  Other possible choices are:

Allow: Data:
  • Whole number
  • Decimal
  • List
  • Date
  • Time
  • Text length
  • Custom
  • between
  • Not between
  • equal to
  • not equal to
  • greater than
  • less than
  • greater than or equal to
  • less than or equal to

[I’ll explain the List choice in more detail later in this blog]

Data Validation – Input Message

There is a facility which lets you add an (optional) Input Message to tell the users of the restrictions you have set:

DataValidation B

The text that you enter is shown when the user selects one of the relevant cells:

DataValidation C

Data Validation – Error Alert

You are also able to supply your own customised Error Alert which is displayed when the user breaks your rules.  There are three levels of Alert – Information, Warning and Stop.  Each of these causes the program to react to the error in a slightly different way.  [Incidentally, you may wish to pick a slightly more professional message than the one I used here!]

Information Warning Stop
DataValidation D1 DataValidation E1 DataValidation F1
DataValidation G DataValidation H DataValidation I
  • The Information option simply tells the user that their entry is not what you were expecting.  They are quite at liberty to click OK and ignore your rule.  If they click Cancel their new entry is discarded.
  • If you choose Warning, the user has three ways to proceed.  Yes will allow them to enter the “illegal” value; No gives them a chance to correct their entry; Cancel discards it.
  • Stop is the most restrictive choice.  The user cannot ignore your rule and put in a different value.  The can only Retry (i.e. have another go) or Cancel (i.e. give up)

Data Validation – Using Lists

In this example, only a few entries are valid. By setting up a List (and using the Stop Error Alert) you can prevent users from making any other entries.

DataValidation J

I’ve made the Source for the list clearly visible in these illustrations.  In the real world, I would either hide Column D or put the list on a different worksheet.

DataValidation K

Data Validation – Custom

The Custom facility can be used to restrict entries in many ways, limited only by your own ingenuity.  To use it, you are required to create a formula which will either work out to be TRUE or FALSE.

Here is an example based on a monthly budget.  The current entries add up to 1,450.  We need to make sure that any alterations don’t take the amount to 1,500 or beyond.

DataValidation L

The same Data Validation settings have been made to all seven highlighted cells –  Allow: Custom and Formula: =SUM($B$2:$B$8)<1500:

DataValidation M

When an entry is changed, but the total remains below 1,500, our formula evaluates to TRUE and the new entry is allowed.  Should an alteration take the value above 1,499.99, the formula becomes FALSE, preventing that entry.

This is just one simple use of the Custom facility, but it gives an insight into how powerful the feature can be.

Checks and Balances

Data Validation doesn’t guarantee that the entries your colleagues make are correct. However it will make sure that they aren’t wildly wrong.  It is still prudent to have other mechanisms in place to ensure that the results from your worksheets can be trusted.