Data validation

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!).

Image showing how to apply data validation in Excel modelling

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.

Image showing how to use list validation in Excel

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).

Image showing how to run scenarios in a financial model using Excel list data validation

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.

Image comparing Excel dropdowns and list data validation for scenarios

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.



...join the conversation...

Your email address will not be published. Required fields are marked *