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.
Featured Course
Excel Essentials for the Real World
Note that adding a new row below this range will not be automatically captured unless it is inserted within the existing range.
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.
Input message
- Select Data > Data Validation
- Go to the Input Message tab
- Tick the box to show an input message when the cell is selected
- Specify the message to be displayed
Error Alert
- Select Data > Data Validation
- Go to the Error Alert tab
- Tick the box to show an error message after invalid data is entered
4. The default message shown if invalid data is entered looks like this
5. To setup a custom alert, select a style and specify the message to be displayed
Applying data validation to other rows
In order to apply the data validation criteria to other rows:
- Highlight the area with existing data validation set up
2. Press CTRL + C
3. Highlight the rows where you want to paste the data validation criteria
4. Right click > Paste Special
5. Select Validation.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
