Data validation can be helpful in financial modelling if you want to ‘force’ or direct users to make particular entries in a model, perhaps because you have set your model up so that it only copes with certain inputs.
Data validation: an example
Here you can download an example of data validation. If you click on cell B4 and go to the “data” tab, you can see the validation settings by selecting “Data Tools”, “Data Validation”. You can see that data validation has been set up so that a user can only enter 3, 4, or 5 (in this example we are imagining that our model has only been set up to cope with a 3, 4 or 5 year term). If the user enters any other value they receive an error message. Try entering a value in cell B4 that doesn’t meet the validation criteria and you will notice that you are prohibited from doing so (and abused at the same time!).
Data validation with lists
Here is another example where data validation is being used to force a user to class expenses into one of five categories. You can download the example here: list validation. If you select column B and then select data validation (“data” tab, “data tools”, “data validation”) you should be able to see the key inputs needed to get data validation working. If click on a cell in column B e.g. B11 you will see that you can only enter an entry from one of the five expense categories.
Using list data validation to run scenarios in a financial model
Sometimes you will see modellers using data validation to run scenarios. Here you can download an example of that happening, combining data validation at cell B5 with a “match” function at cell B6: scenarios. We prefer drop-down boxes for this purpose (see the notes at the bottom of the page).
Combining list data validation with vlookup
Sometimes you might want to use list data validation to for example provide you with some of the key inputs for a Vlookup function. But, if you think about it, you’ll find that dropdown boxes could also solve this problem for you. There’s always multiple ways of solving a financial modelling problem in Excel! Here you can download an Excel spreadsheet containing examples of both approaches: dropdowns vs. list validation.
Financial modelling tips: dropdowns vs. data validation
In general we prefer drop-down boxes:
- You can only see that you’ve got data validation in place when you ‘hover’ over the Excel cell where validation is applied. Dropdown boxes are more visible, giving the user some big clues about what they should vary in the model.
- You can copy and paste the boxes across different tabs in your model, so that you can change your scenario from any tab and you always know what scenario you are working in.
Next in the Excel financial modelling course extract
In the next lesson we look at how to password protect spreadsheets or areas of spreadsheets.
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel formulas.