What level of input Data Validation is needed in Excel?

The built in functionality for Data Validation in Excel is quite useful in my view and a lot of people would benefit from improving their knowledge in this area. It gives developers the option to ensure that the inputs are of a certain category or type (like a date, integer, or from a data list). In financial models of the types we work with at Corality (project finance, M&A, corporate finance, valutations, etc) the most powerful Validation Criteria is List, in particular when linked to a Named Range (as it gives the ability to create off-sheet referenced data lists).

Setting up Data Validation in Excel

Using Data Validation of a List is very useful when you want to give a user the ability to choose between a number of cases (say for forward curves for oil prices) and you want to make it easily selected in a drop-down menu.

Data Validation in Excel dialog window

It is almost impossible to build a financial model in such a way that it is impossible for a user to input an incorrectly structured assumption. Even though the technical foundations for this has been included in Excel it means in practice that so much testing would have to be allocated to the strictness of the user interface that the mechanical areas of the model would suffer from lack of attention.

How much Data Validation should you use?

In my view on should focus the Data Validation to certain areas/structures of a model

• Selection of named cases (scenarios, prices, options, assets, etc)
• Whole number data validation in inputs used for LOOKUP (as there is otherwise a risk of the LOOKUP failing to find data)
• Time resolution data. For example if you are building a quarterly financial model and have an input for the sizing of the Major Maintenance Reserve Account as an input (coded with a SUM(OFFSET(…)) then you should restrict the user to only use 0,3,6,9,12 etc.

Data Validation - List Option

Applications of Data Validation to avoid

It is easy to go overboard with Data Validation and even though there are not strict rules of right/wrong these are some applications that I would recommend avoiding. The key thing to keep in mind is to test working with your own model. If you find your own Data Validation annoying then it is probably also annoying for other people- even if it adds integrity to your financial model. If you would like to take this one step further then ask a friend/colleague to answer a simple question with assistance of you new shiny financial model (like ‘what is the NPV of the investment if you were to update the assumptions A,B,C,D with X,Y,Z,W).

Want to learn more about Data Validation - more advanced?

• Check out Chandoo’s blog - Pointy Haired Dilbert - article ‘Advanced Data Validation’
• We have a full tutorial on the Corality corporate site ‘Data Validation in Excel’

Print this page Email to a friend