I’m a big fan! To set this up, first select the relevant input cell(s) and mark these clearly with a consistent colour – perhaps using a predefined cell style - so the user can immediately see that the cell is for an input or a drop-down list (restricted list of inputs).
Click on the Data Validation icon in the Data ribbon under Data Tools. You get a dialog box with three tabs: Settings, Input Message and Error Alert. You must complete the Settings tab, the other two tabs are optional but also useful.
Settings
Firstly, you must decide what to allow under “Allow”. As a default, this is ‘Any value’. Select an option and complete the additional input fields, which then appear, to restrict the inputs which are allowed and click OK.
Here are the main options that I use with some example use screenshots, after the relevant validation rule has been set up.
- Whole number
Only integers from 1 to 5, e.g., for fixed asset useful lives in years or 1 to 12 for month.
- Decimal (values that may have digits after the decimal point)
For example, only decimals with positive values (greater than or equal to zero) for sales prices or only negative values for planned cash outflows for rent or tax payments.
- List
Probably my favourite! Allows you to allow only entries from a list. Either enter the list in the dialog box itself e.g., yes,no (with separators – either commas or semi-colons depending on your regional settings – but no spaces between list items) or link to a list in the model e.g., scenario names or cost centre numbers. Now when a user clicks in one of the restricted cells, they see a drop-down arrow which enables them to select a predefined item from the list.
- Text length
Good for product codes or postcodes which have a fixed length.
- Custom
Another of my favourites because it is extremely flexible. A formula is required: this must start with an equals sign and be followed by a test which gives a true or false result, i.e. what you would typically type when using an IF formula.
For example, to ensure the data entered is a Monday enter this formula =WEEKDAY(C2,1)=2 where C2 is the cell with the data validation. Use $ fixing here, if appropriate. The formula can be tricky to get right so I recommend entering it as a normal formula in an Excel cell to test it. Once you are happy, you can copy the formula as text into the Data Validation, Custom, Formula field.
Input Message and Error Alert
You can also complete one or both of these optional tabs to advise users what type or range of data they must enter or why their input was not accepted - see screenshots above. This is useful because otherwise, if they input invalid data, they just get a standard error message with no clue as to what is wrong and what is acceptable.
On the ‘Error alert’ tab you can also decide what happens if input data does not meet the validation criteria. The default option is ‘stop’. Alternatively you can change this to a ‘warning’ or ‘information’.
- Stop: the default option - the data is not accepted.
- Warning: the user gets warned but can still go ahead – I use this for entries where I normally expect a negative value e.g., for a payment of taxes but which could, under certain circumstances be a positive value e.g., a tax refund.
- Information: invalid entries are always allowed - this makes little sense in my opinion.
Learn more
