Restrict what users can input (e.g. to values from a drop down list)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

When you’re preparing workbooks for others to fill in (for example an Excel template to collect data from people) it is a good idea to try and ensure data quality at source-rather than you having to filter through the data received for any erroneous/invalid values, you can set up the worksheet so that it checks the validity of the data as the user populates cells, preventing invalid data from being entered in the first place. For example, you can provide users with an in-cell drop-down box to select a value from.

This is all achieved using the data validation tool which can be accessed via the Data Ribbon. It is a good idea to combine data validation with protection which then prevents people from modifying your validation settings or overwriting any validation formulas you might have.

Firstly, highlight the range of cells which you want to apply a validation rule to (e.g. a column or selection of data entry cells) and then select ‘Data Validation’ from the Data tab of the Ribbon.

The dialog box shown below will appear and the first tab will be ‘settings’ where you can specify the rule you want to apply to the cells selected.

If you want to have an in-cell drop down box then select type ‘list’ and then as the source either:

• Type in the values separated by commas such as: a,b,c
• Use the range selector to select the cells containing the values you want for the drop-down box (In Excel 2007 you can only do this provided the values are on the same sheet)
• Use a named range which contains the cells (on any worksheet) which you want as drop-down values. Precede the named range with an equals sign. So for example if your named range is called ‘dates’ then the source will be =dates (You can type this in or press F3 to see a list of named ranges in the workbook)

Other data validation types include restricting cell inputs to a certain number of characters (select type ‘text’ and specify a length), or to restricting to input to being within a date or numerical range, subject to a minimum/maximum threshold which you specify. The threshold can be fixed (by entering a number into the minimum/maximum fields) or dynamic (by referring to a cell reference instead). By selecting type custom you can also apply more complex validation rules.

The ‘input message’ tab allows you to type in some text that comes up as a small Yellow pop-up when users are in the cell(s) that this has been applied to. This text is intended to give users instructions on what they should enter (and perhaps outline the validation rule that you’ve applied).

Whilst input messages have a particular application with data validation, I also find this quite useful for providing general comments (e.g. outlining what a particular cell value represents or what a formula is doing). In many ways I think they’re more visible (but at the same time less intrusive!) than Excel’s comments functionality (available from the review tab). Whilst you can’t cycle through input messages and review them in the same way as you can comments, they’re useful for providing information about cells which tend to be static from the point of design anyway.

The ‘Error message’ tab allows you to specify whether you want to actually prevent users from entering invalid values (this is the default option – style ‘stop’), or just warn or inform them which (will still allow them to enter invalid entries).

Here you can also provide a custom error message to appear when a user tries to input a value that doesn’t conform to the validation rule you’ve specified. If you leave this blank the default message of ‘The value you entered is not valid’ will come up though you can change this to be more specific in terms of the validation rule you’ve applied (e.g. to something like ‘Please enter a valid date between 1/1/12 and 31/1/12’).

In the Topic Example workbook, apply data validation to the yellow cells as per the description.

The first section requires data validation in the form of an in-cell drop-down box (with and without input/error messages), and the bottom section requires restrictions on inputs e.g. to numbers, dates or text length.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this (Note that to see the Data Validation, the file must be opened in Excel – click on the Excel icon at the bottom):

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Create dependent drop down lists in Excel