Why is Data Validation so important when entering data in Excel?

Nov 26, 2018 | Excel Hints and Tips

We have all seen those documents with so many errors in them that if you try to get any decent information out of them then it’s virtually impossible.  This is often caused by mistyping when entering the data or different abbreviations being used by different people which makes the analysis of data very difficult.  So what can you do to minimise the issue?  Ideally, you want to get the data accurate at point of entry.  Data validation can help with this.  It will not get the data 100% right but helps eliminate some of the inconsistencies that otherwise occur.

Data validation can restrict data to a particular type.  It enables the number of characters to be specified, the size of number data or the range of date data.  It even allows text to be chosen from a drop-down list.  Where it is less useful is in freeflow text fields where there may be many possible options that could be entered which makes it tricky to set up any form of restriction.


Restricting text length/number size/date range

data validation settingsAll of these are performed in a similar way.  From the Data tab validation is selected.  The Settings tab allows the data values to be set.  This can be the number of characters.  Similarly for numbers and dates.  For numbers this can be restricted to whole numbers only or allowing decimal places too.

Restricting options from a preset list

For this the list the options will be chosen from must already have been drop downcreated.  Then from the data validation option from the data tab choose the setting option of list.  From here the range of cells already produced is chosen.  When confirmed the options are viewed by clicking on the drop-down arrow in the cell.

Input message

data validation input messageThis is accessed from the data validation icon and the input message tab.  A title and message can be displayed if required that shows up when a cell is clicked on giving information to help the user fill the cell in correctly.

Error alert

data validation error alertAlso accessed from the data validation tab but this time from the error alert tab.  By default, this is set to stop.   This means that if any invalid data is entered it will not be allowed under any circumstances.  There is also a style for warning which gives the opportunity to change the data or to accept what has already been entered. The final style is information.  This gives information that the data entered is invalid but lets you proceed anyway.  A title and error alert message can be added in addition to help explain what the error is to help the person entering the data to correct it.

Circle invalid data

data validation settings

If data has already been input then the validation setting mentioned earlier can be applied to existing data.  Data that is invalid can then be highlighted by choosing Circle invalid data from the data validation drop down on the data tab.  To remove the validation select Clear Validation Circles from the same place.

If you found this useful see what Excel courses are available or email lara@laramellortraining.co.uk to discuss any training requirements.