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.
- Calculate the total value of the filtered list in Excel
- Calculate the subtotal of the list on Excel
- 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:
- 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.
- Equal to (not equal to): equal to 1 specified value.
- Greater coal, less coal: larger (smaller) a defined value
- 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:
- Instructions on how to separate column content in Excel
- How to use Vlookup function in Excel
- How to break VBA password in Excel
I wish you all success!
You should read it
- Limit input values using Data Validation in Excel
- How to create duplicate data entry notifications on Excel
- Check data entry in Excel
- How to create drop down lists in Excel
- Excel data entry faster
- How to limit data with Data Validation in Google Sheets
- How to import data from photos into Excel
- Manage goods with Data Validation in Excel very well
- How to display 0 in front of a number in Excel
- How to enter formula data in Excel
- What is ### error in Excel? how to fix ### error in Excel
- Instructions to stamp negative numbers in Excel
May be interested
Make the most of the power of Microsoft Word with these 10 hidden features
How to use the function to delete spaces in Excel
Guide to transfer ebook into the Amazon Kindle
How to fix the error of accent marks when writing capital on Word
How to create duplicate data entry notifications on Excel
How to convert Cad to Word, PDF, Excel