Excel Basics – Data Validation in Excel

Data validation is a feature in Excel that allows you to restrict the type of data that can be entered into a cell or range of cells. This can be used to ensure that the data in your worksheet is accurate and consistent.

Use data validation in Excel to make sure that users enter certain values into a cell.

Data Validation Example

In this example, we restrict users to enter a whole number between 0 and 10.

Create Data Validation Rule

To create the data validation rule, execute the following steps.

1. Select cell C2.

2. On the Data tab, in the Data Tools group, click Data Validation.

On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

Input Message

Input messages appear when the user selects the cell and tell the user what to enter.

On the Input Message tab:

1. Check ‘Show input message when cell is selected’.

2. Enter a title.

3. Enter an input message.

Error Alert

If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check ‘Show error alert after invalid data is entered’.

2. Enter a title.

3. Enter an error message.

4. Click OK.

Result

1. Select cell C2.

2. Try to enter a number higher than 10.

Result:

Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All. You can use Excel’s Go To Special feature to quickly select all cells with this feature.

Data validation is a powerful tool that can help you to ensure that the data in your worksheets is accurate and consistent. It can also help to prevent users from entering invalid data, which can save you time and frustration.

Here are some additional tips for using data validation:

• You can use this feature to create drop-down lists. This can be helpful for tasks such as selecting a product from a list or entering a state abbreviation.
• You can use this feature to create custom input messages. This can be helpful for providing instructions to users about the type of data that they can enter.
• You can use this feature to create error messages. This can be helpful for informing users when they have entered invalid data.

Data validation is a valuable tool that can help you to improve the accuracy and consistency of your data. By using this feature, you can save time and frustration, and you can ensure that your data is always up-to-date.