Like and see, form a habit; A fool is wise. There must be one thing.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
We often encounter a series of subsequent problems due to the failure of standard operation at the beginning of data entry, such as data can not be analyzed, and some errors are difficult to find, resulting in the unusable data due to some errors.
Today xiaobian shares how to set up in advance, so that the wrong data can not be entered, only the correct data.
These problems can be solved by using data verification. Data verification is mainly used to verify the validity of data, and you can set verification rules, display verification results, and next operations.
1. Only male and female entries are allowed
When we process the employee information, we will correspond to the employee gender column, and we hope that only male or female can be entered.
① Find a separate blank position, two consecutive lines respectively input male and female, such as A5 input “male”, A6 input “female”;
② Select only male and female cell area, select “Data” → “Data Tools” → “Data Verification” → “Settings” → “Verification Conditions” in “Allow” is “sequence”;
③ The source is set to input two cells for men and women at the beginning;
Now that the setup is complete, let’s look at the dynamic effect:
2. Repeat entry is not allowed
When you’re typing data, it’s impossible to expect your brain to remember what values it entered if it doesn’t allow the same data to appear in a particular column. Nonsense not to say, direct operation go.
① Select the cell region that does not allow repeated input; Select “Data” → “Data Tools” → “Data Verification” to bring up the verification dialog box;
② In the verification dialog box, “Allow” in the verification Condition under the “Settings” TAB is “Custom”, and the formula is “=COUNTIF(C 5:C5:C5:C9,C5)=1”.
③ In the verification dialog box “error warning” TAB, we select the style “stop”, the title and error message can be defined according to their own requirements, here the title: “error”, error message: “repeat input!!” .
With the setup done, let’s see what it looks like in action:
3. Only 11 digit mobile phone numbers are allowed
When we carry out a large number of mobile phone number, ID card, bank card number and other digit number, it is easy to miss input one or two numbers.
Operation is also very simple, first select the cell area that needs to be set, and then select “data” → “Data Tools” → “Data verification” → “Settings” → “Verification conditions” in “allow” select “text length”; Set Data to Equal. Fill in Length with 11.
Let’s see the effect:
4. Only digital input is allowed
Of course, we will also encounter a lot of data can only enter numbers, the operation and previous examples are similar, mainly is the formula is different, first select the cell area to set, and then select “data verification” “verification conditions” “allow” select “custom”; Formula =ISNUMBER(C5).
The effect is as follows:
5. Only the specified range of numbers are allowed to be entered
We also have situations where you can only be between 18 and 60 years old and have an income of 4,000 to 5,000 and things like that. What about a class that only allows a specified range of numbers?
Of course, the operation is also very simple, first select the cell area that needs to be set, and then select “Data verification” “verification conditions” in “allow” select “integer”; Set Data to Between. Set Minimum value to 18. Set Maximum value to 60.
The effect is as follows:
“Data validation” can also be used to verify decimals, times, dates, etc. If you are interested, you can try it out on your own.
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.