Data Validation in Excel

Data Validation in Excel is a technique that restricts the type of entry allowed within a cell.  The restriction is based on specific, user-defined criteria and is an excellent way to ensure greater quality control and functionality within spreadsheets.  As an example, creating a Data Validation Drop-Down-List is an excellent way to give you complete quality control when applying filters in Excel.  By default, Excel turns every cell entry into a filter criteria, so if someone types Mechanic, someone else types mechanic and someone else types mechanics, you have three different filter criteria that all mean the same thing.  It is much harder to filter the data without having to change the entries to a consistent format.  If however, you create a Data Validation Drop-Down-List, the end user can only pick from the list and all the entries in the spreadsheet will be consistent, giving you greater control over the filters, once applied.

It is also very useful for giving the end-user specific information and instruction on how a spreadsheet should be filled out as you may include input messages and error alerts.  An input message and an error alert can both be used to give direction and correction when filling out a spreadsheet.  Data Validation is found (across Office 2007 – 2013) on the Data Tab of the Ribbon within the Data Tools Group.  To access the Data Validation feature in Excel:

  • Select the Data Tab
  • Select the Data Tools Group
  • Click the Data Validation Button
  • The Data Validation dialog box will launch
  • Click the Settings Tab to select the type of validation
  • Click the Input Message Tab to enter an input message
  • Click the Error Alert Tab to enter an error message
  • Click the OK button when done

Below is a list of the types of Data Validation that may be applied in Excel:

Any Value Restricts data entry to any value within limits
Whole Number Restricts data entry to a whole number within limits
Decimal Restricts data entry to a decimal number within limits
List Restricts data entry to values in a drop-down List
Date: Restricts data entry to a date within a time frame
Time Restricts data entry to a time within a time frame
Text Length Restricts data entry to text of a specified length
Custom Use a formula to calculate what is allowed

 So next time you are creating a spreadsheet, consider using this timesaving tool to prevent inconsistent entries and save yourself time and re-work in the long-run.

This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."