Excel Tip of the Week #114 - Revisiting data validation & creating dropdown menus

Hello and welcome back to the Excel Tip of the Week.  In this week's Tip, we have a Creator post in which are looking back at data validation and the creation of dropdown menus.  These topics were originally covered in TOTWs #16 and #9 respectively.

What data validation is all about

Data validation allows you to set one or more rules about the contents of a cell.  Once set, these rules will prevent users from entering invalid data.  You can even set what happens on the entry of invalid data, including setting a custom error message.

Dropdown menus are associated with fixing a cell to a list of acceptable values.  When there's an exhaustive list of values, the list will be displayed on a dropdown menu when the cell is entered in to.

Data validation is commonly used in shared spreadsheets, templates, and forms, where consistency of approach is useful.  For example, they can help prevent typos such as entering invalid dates, or entering non-integer PO numbers.  They can also help consolidate together different versions of e.g. a client name, by not letting Company Ltd co-exist with Company Limited.

Data validation is not tamper-proof.  Copy-pasting new data over the cells, for example, will invalidate the data validation.  But it is a useful way to avoid accidental error.

How to set up data validation

Select the range of cells that you want, then navigate to Data => Data Validation.  This menu should pop up:

The 'Allow' menu lists the following options:
   Any value - The default setting (no data validation)
   Whole number - Integers only (i.e. not 4.5 and not FQY).  Also allows a maximum / minimum / etc. to be set.
   Decimal - As above, except non-integers are permitted.
   List - A finite list of acceptable options, with a dropdown menu.  See below for more details.
   Date - Only validly formatted dates will be accepted.  Can also set min / max dates.
   Time - Only validly formatted dates
   Text length - Controls the length (in characters) of text strings that can be entered into the cell.
   Custom - Write a formula to create a rule of your own.

When you select any of the appropriate options, the menus below the Allow menu will change to adjust.  Generally speaking, the options are pretty self-explanatory.  For each, you can generally either enter a value directly, or enter a cell reference to pull one from that cell.

For the List option, you can drag-select a range of cells to populate the dropdown.  From Excel 2010, this list does not need to be in the same sheet as the cell with the dropdown menu.

The Custom menu allows you to write a logical function (one that has a TRUE/FALSE value) that will assess cell entries for validity.

Input messages and error alerts

You can use the other two tabs of the data validation menu to set up a message to be shown to users when the cell with validation is selected; and an error message to be shown if they attempt to enter some data that doesn't pass the validation rule.  It's a good idea to use at least one of these, so that users don't get frustrated with a cell that won't let them enter data, and won't tell them why not.

You can also customise the error alert such that it will permit invalid data entry, but will show a warning message first.

Reviewing data validation

You can see the data validation rules set on a cell by highlighting that cell and then opening the data validation menu.  You can also clear rules using the Data Validation option under the Data heading of the Ribbon.

If you select a range of cells, some of which contain data validation, and then open the data validation menu, Excel will prompt you, and ask if you wish to extend the data validation to all the selected cells.

Have a look at the attached file for a practice task, with its own solution.

Previous post - Revisiting Goal Seek 
TOTW index
Next post - Style Manager

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 Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

  • but then not everyone has the p&l to be able to employ VBA capable administrators so I reckon this works just fine and dandy for simple tasks and I haven't found the copy and paste a big issue myself

    thanks for the refresher!

  • ...or use a database for what a database is good at and Excel is poor at.

  • Hi David

    Nice article. However, more often than not, people haven't got time to fill out a huge data validation exercise (certainly if they have a whole wealth of data to put in).

    They'll resort to the age old favourite of copy and paste, which invalidates any data validation in Excel (a lovely little flaw that Excel hasn't been able to rectify).

    An alternative to this is to use a locked down workbook and VBA forms to allow the VBA to perform the data validation, before inserting the data to a workbook.

    Takes a lot more time, but is much more rigorous and prudent against the 'control c, control v' method.

  • Thanks for the comment!  In actual fact there will be an extended post on various dropdown menu approaches in a few weeks - plenty more to dip into on this topic!

  • You can use a range of cells in a different sheet in Excel 2007 to populate the List option so long as you give the range a name. You can then use that name in the source box e.g. Sheet2 contains a range named "NoYesOptions" holding two entries - "No and Yes" while Sheet1 has a data validated cell using a list of which the source is "=NoYesOptions". This will allow a dropdown box containing the values "No" and "Yes".

    This allows you to build a data sheet containing common list parameters for use in further workbooks.