Shulou(Shulou.com)11/24 Report--

"Beep. Beep. Beep. The number you dialed is empty, please check it and dial again.

What? What's going on?

Looking up, it turned out that the phone number in the form was misregistered.

In fact, this kind of thing is not uncommon.

When we collect some information, we often encounter mistakes that fill in less and more, such as mobile phone number, ID number, and even gender.

:

How to avoid the above situation?

There is a simple but practical method-data validation (versions prior to Office 2013 were also called data validation).

1. What is data validation?

Data validation is used to restrict the data type or the value of the cell entered by the user.

Select an area of the worksheet, locate the data tools under the data tab, and open the data validation window.

You can configure the conditions that restrict the input in this area, the prompt message that appears before the input, the warning message that pops up after the error, and the input method mode used in the current input location.

The input method mode is easy to understand, but I won't talk about it later. Let's talk about the first three functions.

2. When an automatic reminder before input is selected for a cell that has been set for [input information], a prompt box will be automatically displayed next to it.

Select other cells and the prompt box will not appear and will not affect the reading experience.

❶ select the area to be set, and click the "data" tab-"data tools"-"data Verification".

❷ in the pop-up data validation window, click enter Information.

❸ enters prompt information in the dialogs under "title" and "input Information" respectively, which is not limited to the content form, specification requirements, etc., that are expected or required by other users. (it feels a bit like a comment in PPT, 🙊)

❹ finally clicks [OK].

And check to see if the setting is successful.

When the cell is selected, there will be a message indicating that the operation is fine.

3. Automatic alarm after input when you enter information that does not meet the requirements, Excel will automatically jump out of the alarm pop-up window and ask you to re-enter it.

However, if you want to achieve this feature, you must set the verification conditions in advance.

Take, for example, the number of ID card numbers I just mentioned.

The ID card number must be 18 digits, no matter one more digit or one less digit.

So we can limit the "text length" of this entry to 18.

❶ selects the table area that needs to be set, opens the data validation window, and you can see the interface we need to operate at a glance.

❷ [allows] "text length" equals "" 18 ".

Click [OK], and the validation conditions are set here.

Next is the error warning.

Open the error warning window, check "Show error warning when entering invalid data", select "stop" and fill in "title" and "error message".

Finally, don't forget to click OK.

In this way, the alarm in the D2:D11 area is done.

Did you learn it?

By the way, quietly remind me that the "style" of [error warning] is not only the "stop" just used, but also "warning" and "message".

Their functions are more or less the same, if you are interested, you can feel it.

4. The conditions for restricting input see here, you may wonder, why do I have to pull this item out and say it again?

Because through it, we can implement rich data entry restrictions on date, time, text and other types respectively.

For example, the "text length" mentioned above is limited to 18.

Also, combined with the sequence feature, you can create a drop-down list, which is one of the most common uses of data validation.

Setting some data into a drop-down list can not only improve the efficiency of input, but also effectively limit the scope of data filling and ensure consistent norms.

Look at the effect picture:

The picture above shows the first-level drop-down list in Excel. After in-depth setting, we can also create a second-level drop-down list, a three-level drop-down list, and a search drop-down list.

5, write at the end of today's "limit input" function has a big BUG, that is, it is only valid for manual input data, has been entered, copied and pasted data is not valid.

In other words, it does not completely prevent some people from "misbehaving".

If you have any other way, feel free to leave a message.

If you want to verify that the data you have entered meets the verification criteria, you can use this feature-- [circle invalid data].

Finally, let's sum up ↓.

Learn a little Excel every day, work efficiency up~up~

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan, Editor: Yali Zi, Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.