Prevent invalid data entering in specific cells

— Set criteria in your worksheet to accept specific data.

Use data validation in Excel to make sure that users enter only values that meet a set criteria into a cell.

Steps to navigate to Data Validation icon in Excel

Data Tab → Data Tools group → Data Validation

Data Validation Example
Create Data Validation Rule
Input Message
Error Alert
Data Validation Result

Data Validation Example

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

Worked Example: How to Break Worksheets into Pages in Excel

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.

Worked Example: Embed Excel worksheet in Word document

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’.

Worked Example: How to reference named range different sheet in Excel

2. Enter a title.

3. Enter an error message.

4. Click OK.

Data Validation 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 data validation.

Data Analysis

References

Data Validations