Should you include these in your files?
Data Validation in Excel
Excel’s Data Validation feature ensures that the data collected is clean and to minimize the errors and hence the time it takes to correct them.
This is especially helpful when multiple people input data into the spreadsheet.

Types of Data Validation
By default, data validation allows any value to be placed in the cells.
There are, however, various validation criteria options:
- Whole number
- Decimal
- Date
- Time
- Text length
- Values from a list
- Custom (formula-based)
For the first five options enables you to specify ranges or specific values.
The last option allows you to add custom criteria for more flexibility.
Adding Data Validation
These are the general steps in creating a data validation criteria:
- Select the cell where the data validation is needed
- Go to Data > Data Validation
3. Select the type of validation needed
4. Define the validation or specify a source
To expound further, let’s take a report that requires users to specify a division, project start date, project number, company code and project description.
Below are the ways to setup data validation this scenario:
- Cell A5: Select a division from a list (Game, Productivity, Utility)
- Validation criteria type: List
- Source: For static options, use a comma to separate the choices, “Game, Productivity, Utility”
- Cell B5: Specify a project start date in 2018 that is after the current date
- Validation criteria type: Date
- Data: greater than
- Start date: =TODAY()
- Cell C5: Project number should be four digits within a range (1000 to 2000)
- Validation criteria type: Whole number
- Data: between
- Minimum: 1000
- Maximum: 2000
- Cell D5: Specify a company code that is at least 5 characters
- Validation criteria type: Text length
- Data: equal to
- Length: 5
- Cell E5: Project description that is sufficiently long (more than 20 characters)
- Validation criteria type: Text length
- Data: greater than
- Length: 20
Using the custom type
If there are additional requirements for a cell that are not in the scope of the other data validation criteria types, the Custom type can be used.
An example is when the Company Code field (column D) requires a text for the first character and the remaining characters to be numbers.
This can be programmed using a formula.
Dynamic Data Validation
In cases where the options change often, using a dynamic data validation is ideal.
To do this, place the options in a separate sheet or table and use cell references to point to it as the source.
Any changes made to this data set will automatically be reflected in the data validation criteria.
Setting up the data validation source
Adding new options to the dropdown
There are various ways to do this:
a) Expand the data validation reference to include the new row
This means that if the original range is MASTER$A$4:$A$6, this should be manually changed to MASTER$A$4:$A$7 to include the newly added row.
b) Insert a row within the existing specified range
Since the range originally specified was MASTER$A$4:$A$6, insert a row between 4 and 6.
c) Drag the new row into the range using the SHIFT key
- Add a new entry at the bottom of the range. If the range is MASTER$A$4:$A$7, add the entry in cell A8.
Click on the new row and hold the SHIFT key as you drag cell A8 into the original range.
d) Using an expandable cell reference
- Convert the range into an official Excel table by clicking anywhere inside the data set and press CTRL + T.
Change formatting based on preference under Design > Table Styles.
Rename the table under Design > Table Name. By default, it is called Table1. In this example, it is renamed to TableDiv.
- To refer to this range, the syntax is =Table_name[header_name]. In this example, is it TableDiv[Division]. This allows the entire range to expand when new rows are added.
- Go back to the cell where the data validation is needed and change the source. Since data validation is unable to process table references, there are two workaround for this:
- Use the INDIRECT() function to convert this text into an address.
This now becomes =INDIRECT(“TableDiv[Division]”
2. Use the Formulas > Name Manager to give the table reference a name to be used as a data validation criteria
This method allows you to add a new entry at the bottom of the range without the need to manually expand the data validation through methods 1 or 3.
Adding input and error messages
This will serve as a guide for users by displaying a message as they select the cell or if the value specified is invalid.
Thanks a ton, very informative.
Very good information and understanding your explanation
I have an interesting dilemma regarding Data Validation Lists in Excel 2013. Is it possible to have the validation list show a full name, but when an item is selected, only a code is entered into the cell?
This is the table is want to use for Data Validation:
Code Supplier Name
MP Micropoint
PX Phoenix Technologies
TA Takealot
Is it possible to create a data validation list that shows “Micropoint”, “Phoenix Technologies” and “Takealot” in the list, but the applied cell values must be “MP”, “PX” or “TA”?
It will certainly make my worksheets easier to use…
Hi Peter, best would be to post your specific question with screenshots on an Excel forum – for example Microsoft’s Tech Community right here. With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able. I’m certain someone there can inform you of the best way to reach your solution.
You are the best
Nice one, Thanks