Excel Tip of the Week #296 - Data validation redux

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a Creator level post in which we're taking a definitive revisit to the important cell-protecting feature, data validation.  This was most recently covered back in TOTW #114.

How data validation works and what it does

Data validation is a form of cell formatting which restricts what kinds of values can be typed into the cell.  You can create a "rule" for a cell, and users will be unable to enter data that breaks that rule.

Rules can take many forms - at their simplest, a rule might be something like "only text" or "must be a number greater than fifty".  But at the more complex end, rules like "data must be chosen from this list" or even "data must satisfy this formula" are possible.

However, it is important to note that data validation is not foolproof.  It only applies to when a value is entered directly by a user.  If instead a formula is present in the cell, or the user copy-pastes over the cell with data validation, then the validation will not prevent that change.

That being said, data validation is still a useful tool for forms, data entry, and other situations where reducing the possibility for error is vital.  This might mean protecting against common misunderstanding and typos, or something more sophisticated like checking if a customer's name is on an approved list.

How to apply data validation

The menu we need lies under Data => Data Validation:

This lets us create our rule.  The "Allow" box has several options; these are:

Any value

The default option; any value is permitted i.e. no data validation is applied.

Whole number

Restricts the entry to numeric data with no decimal part.  You can also use the Data menu to set numeric constraints on the values:

The menus below are where you can enter your thresholds - note the "arrow pointing at a cell" button on the right - this means that you can set the limits either with a hard-typed value, or by clicking on a cell in the spreadsheet to set a linked threshold.

Beware that if you set a threshold via a cell link, then changing the threshold value later on will not affect any cells that are then outside of the rules.  Data validation is not retrospective.

Decimal

Like Whole number, but also allows decimal numbers.

List

Allows the creation of a list of allowed items.  This is usually done by reference to a range of cells in the spreadsheet with the list options.  This also will create a dropdown menu to help the user pick from the allowed list:

Date

Allows only date entries.  The Data menu will let you set thresholds such as "before" or "between"; just be careful - date values can't be entered directly into the menus, so either use cell references or the DATE function.

Time

Allows only date-time values; again you can use the Data menu to set thresholds.

Text length

Sets a limit on the text length.  Note that the default for all data validation rules is to allow blank cells; this can be switched off with a tickbox however if you want to disallow leaving a cell blank - but even then, the user can still delete/clear the contents of the cell - they just can't enter the cell, type nothing, and then leave it again.

Custom

This final option allows you to create a logical formula that will evaluate the entry in the cell - the entry will be permitted only if the formula returns TRUE.  Here's an example rule for "the cell must be an odd whole number":

If you're creating a rule for multiple cells, think carefully about if your cell references should be fixed with $s or not.  Without the help of the usual formula tooltip and markup, these formulas can be fiddly to write - if you're struggling, try writing the formula in a normal cell first, test it until it's working the way you want, and then copy the text of the formula over once it's ready.

Other data validation options

There are two other tabs on the Data validation menu - Input Message, and Error Alert.

Input Message

This lets you set the text for a small text prompt window that will pop up when the user selects the cell with the rule applied to it.  This is one way of making data validation more user-friendly - otherwise, an inexperienced user can easily get stuck unable to satisfy a rule and not know why.

Error Alert

There's a custom error alert message for data validation that appears when the user enters invalid data, but it's very generic.  This tab lets you customise that message:

As well as the title and error message, you can also set the 'style' - the options being:

  1. Stop - The user will be prompted to try entering something else and be unable to enter the invalid data
  2. Warning - The user will be told that the data is valid and the default option will be to enter something else, but they can override
  3. Information - The user will be told that the data is outside the validation rule but by default will be allowed to enter the value and continue on

Here's an example of a 'Stop' type error message:

Again, a good error alert can help the user to comply with the form.

Miscellaneous extras

Data validation rules are part of a cell's formatting, so if you want to duplicate a rule you've made, you can just copy and paste formats.  You'll also see on the menus above that there's a tickbox on the Data Validation menu for "Apply these changes to all other cells with the same formatting", which makes updating rules en masse much simpler.

It was mentioned above that data validation rules that change only affect future entries, and don't affect any cells which become invalid after they were originally entered.  While this is true, there is a tool to help find any such cells - under the same menu on the Data tab, there's an option called 'Circle Invalid Data' that will highlight all the invalid cells on the current sheet.

And that's everything there is to say about data validation!

Previous post - Binomial distribution functions
TOTW index 
Next post - Revisiting using #N/A! in charts

Anonymous