Limit input values ​​using Data Validation in Excel

The numbers in Excel spreadsheets are extremely important, even the confusion of 1 digit in the spreadsheet has huge consequences. To avoid data confusion, you should set a limit for the column of data cells to minimize the consequences of confusion. The following article will guide you in detail how to Limit the value entered by Data Validation in Excel.

Step 1: Select the data range you want to limit the value (for example, here limit the value entered in the position allowance column) -> go to the Data tab -> Data Validation:

Limit input values ​​using Data Validation in Excel Picture 1

Step 2: A dialog box appears and click the Setting tab as follows:

- The Allow section includes the following options:

+ Whole number: Only allow integer input

+ Decimal

+ List: Limiting the value entered in the list available.

+ Date: Limiting data type to date.

+ Also type Text Length, Custom.

- Data section includes options for the range of data, for example when selecting Whole number has the following ranges:

+ Between (not between) : the limit between a certain range of values, for example, in the range from 1 to 100. Where minimum: Limit the minimum value, maximum: Limit the maximum value.

+ Equal to (not equal to): Equal to a specified value.

+ greater than, less than: Greater than (less than) a specified value

+ Greater than or equal to, less than or equal to: Greater than (less than) or equal to a specified value.

Limit input values ​​using Data Validation in Excel Picture 2

The example here limits values ​​from 250,000 to 900,000:

Limit input values ​​using Data Validation in Excel Picture 3

Step 3: After limiting the value, you need to adjust the content displaying the error message so that users can understand and re-enter the value by clicking on the Error Alert tab :

Limit input values ​​using Data Validation in Excel Picture 4

Step 4: Return to the worksheet, enter the value for the Position Allowance column, when entering the value beyond the limited range, an error message appears specifying the error content for you to edit:

Limit input values ​​using Data Validation in Excel Picture 5

However, with Data Validation only limiting values ​​when that value is entered directly from the keyboard, the case of entering indirect values ​​for cells using the Data Validation formula has no effect.

The above is a detailed guide on how to limit the value entered by Data Validation hope to help you. Good luck!