Are you looking for a way to improve the data quality in your Excel spreadsheets? Data validation is a great tool to use! In this article, you will learn how to use data validation in Excel, and how to create a drop-down list using data validation.
Data validation is a feature in Microsoft Excel that allows you to control what data is entered into a cell. You can use data validation to allow only certain values to be entered into a cell, to limit the number of characters that can be entered, or to create a drop-down list of options. Data validation is a useful tool for preventing errors in data entry, and for making sure that only valid data is entered into a cell.
Data validation to accept only the numbers between 1 and 10
For example, you can specify that a cell can only contain a number between 1 and 10. Once you have specified the criteria for valid data, you can optionally specify an error message that will be displayed if someone enters invalid data.
Follow the below steps to add data validation to cell A1 to accept only the numbers between 1 and 10.
- Select cell A1
- Go to Data Menu
- Select Data Validation – to open the Data Validation dialog box
- Select the Whole number in Allow drop-down item. There are multiple options in the Allow dropdown in Data validation Any value, Whole number, Decimal, List, Date, Time, Text length and Custom.
- Choose between in the Data input box
- Type 1 in the Minimum input box, which is your minimum value
- Type 10 in the Maximum input box, which is your maximum value
- Press OK
Cell A1 accepts only the numbers between 1 and 10. If you enter any other value, the excel popup the error message.
This can be useful for ensuring that data is entered in the correct format, such as a date or a number.
How to create a drop-down list using Data validation
Data validation can also be used to create drop-down lists in a cell, which can make data entry easier and less error-prone.
Suppose you are designing a tool in which you have to add a dropdown in cell E2 to list the country from the range G2:G6.
Follow the below steps to add a drop down using Data Validation
- Select cell E2
- Go to Data Menu
- Select Data Validation – to open the Data Validation dialog box
- Select the List in Allow drop-down item. There are multiple options in the Allow dropdown in Data validation Any value, Whole number, Decimal, List, Date, Time, Text length and Custom.
- Type the range in the Source input box =$G$2:$G$6
- Press OK
Now Cell E2 has a Data Validation dropdown, user can choose only the country from the list.
Conclusion
Data validation is a feature in Microsoft Excel that allows you to control what data is entered into a cell. This can be useful for ensuring that data is entered in the correct format, such as a date or a number.
If you have any questions, please leave a comment below and I will get back to you as soon as possible.
