How to limit the value entered by Data Validation Excel

When working with numbers on Excel sometimes you will not avoid initiating the case of mistakenly entering data. This will affect the results and accuracy of the entire content. To limit the confusion that may occur when entering data on Excel, users can use Data Validation feature on Excel.

This feature will help users set the limit of data entry on Excel, according to certain rules. And when the user enters the limit, they will receive a notification in Excel. The following article will guide you how to use Data Validation in Excel.

1. Calculate the total value of the filtered list in Excel
2. Calculate the subtotal of the list on Excel
3. How to combine 2 columns Full name in Excel does not lose content

How to set data entry limits on Excel

Step 1:

In the Excel table that you want to set limits, black out the data area you want to set limits, then click on Data tab select Data Validation and choose Data Validation .

How to limit the value entered by Data Validation Excel Picture 1

Step 2:

Appears a new table to enter the value limit setting for the Exccel data content. Under the Settings tab, users will proceed to set up content. Clicking Allow will have items selected in the Whole number list only enter integers, List input values ​​in the given list, Date limited date data.

Next, the Data section will be the range choices for the data. If you click Whole number on Allow, then the options are as follows:

1. Between (not between): the limit is between a certain range, the example is between 1 and 100. In that Minimum is the minimum value limit, the Maximum is the maximum value limit.
2. Equal to (not equal to): equal to 1 specified value.
3. Greater coal, less coal: larger (smaller) a defined value
4. Greater than hoặc bằng, less than hoặc bằng với: Greater (smaller) or equal to a specified value.

How to limit the value entered by Data Validation Excel Picture 2

For example, here I will choose Between (not between) setting the data limit within a specified range. The minimum amount I entered is 500,000 and Maximum is 4,000,000. Click OK to continue.

How to limit the value entered by Data Validation Excel Picture 3

Step 3:

Switch to Error Alert tab , Title section enter the name of the message and Error message enter the content of the error message to the known user. If you want, you can change the icon type of the error message in Style. Click OK to finish.

How to limit the value entered by Data Validation Excel Picture 4

Step 4:

Returning to the content when you enter the data exceeding the limit set above will receive the message as shown below. If you want to continue entering numbers, press Yes or No to re-enter the data.

How to limit the value entered by Data Validation Excel Picture 5

Step 5:

To delete all the set of data entry limits in Excel we also open Data Validation according to the above instructions and then click the Clear All button to delete the entire content. Thus, you will not see any error messages when entering data in Excel.

How to limit the value entered by Data Validation Excel Picture 6

So with Data Validation on Excel, we can better control the content of input data in Excel. Users should note that Data Validation will be limited when the value is entered directly on the keyboard, not applicable when indirectly entered by the formula.

See more:

1. Instructions on how to separate column content in Excel
2. How to use Vlookup function in Excel
3. How to break VBA password in Excel

I wish you all success!