Data validation can standardize the user’s text and number input formats, such as only inputting values in a specified range, only text data, limiting input spaces, limiting input of repeated values, etc. How to verify settings. After the data verification conditions are set, the data that meets the conditions is allowed to be input, and the data that does not meet the conditions is prohibited from being input. Therefore, this setting can be used to check the correctness and validity of the data and avoid inputting wrong data.
In addition, you can also set the input prompt information How to verify the settings. That is, it reminds the data input of the selected cell; at the same time, it can also set an error warning message, that is, a warning message pops up when the input is wrong. This article mainly introduces ten tips for basic data verification settings:
Give an input prompt when a cell is selected This example requires the input of “No. 1 High School Entrance Examination Results” in the list , in order to remind the inputter to input the data in the correct range, you can set the prompt text How to verify the settings to be displayed automatically when a cell in this area is selected.
The specific steps are: Select the B3:E10 cell range – click the “Data Validation” button in the “Data Tools” group of the “Data” tab to open the “Data Validation” dialog box – switch to “Input information” tab – Confirm that “Show input information when cell is selected” is checked – Enter “Please enter the score” in the “Title” text box Between 0-150! The comprehensive score is between 0-300!” – Click the “OK” button to complete the setting How to verify the settings. When any cell in the B3:E10 area is selected, the input prompt text will be displayed.
In addition to setting the input prompt, you can also set the input error prompt box to pop up automatically when the input number is not in a specific range , to prompt you to re-enter the number that meets the data range How to verify the settings. Taking the results of comprehensive science as an example, we can set the allowable range to 0-300. When the input data is not in this range, an input error prompt box will pop up.
The specific steps are: Select the E3:E10 cell range – click the “Data Validation” button in the “Data Tools” group of the “Data” tab to open the “Data Validation” dialog box – select ” Settings tab (default) – Under the “Validation Conditions” column, set the “Allow” condition to “Integer”, the “Data” condition to “Between”, and the “Minimum value” and “Maximum value” to “0” and “300” – Click the “OK” button to complete the setup How to verify the setup. When a value not between 0-300 is entered in any cell in the E3:E10 area, an error prompt box will pop up, prompting to re-enter the required data.
Input data does not exceed a certain value In this example, it is necessary to set the score of “Chinese/Mathematics/English” to not exceed 150.
Specific The steps are: Select the cell range B3:D10 – Click the “Data Validation” button in the “Data Tools” group of the “Data” tab to open the “Data Validation” dialog box – Select the “Settings” tab (default) – Under the “Validation Condition” column, set the “Allow” condition to “Integer”, the “Data” condition to “Less than or equal to”, and the “Maximum value” to “150” – click the “OK” button to complete the setting How to verify Settings. When a value greater than 150 is entered in any cell in the B3:D10 area, an error prompt box will pop up, prompting to re-enter the required data.
Restrict the cell to only input date. In this example table, only date data can be input in the “Order Date” column, and the date can only be between 2019/ How to verify settings between 1/1-2019/12/31.
The specific steps are: Select the cell range A3:A13 – click the “Data Validation” button in the “Data Tools” group of the “Data” tab to open the “Data Validation” dialog box – select ” Settings tab (default) – Under the “Validation Conditions” column, set the “Allow” condition to “Date”, the “Data” condition to “Between”, and the “Start Date” and “End Date” to “2019/1 /1” and “2019/12/31” – Click the “OK” button to complete the setup How to verify the setup. When entering a date with a different format (such as: 20190909) or a date that is not within the set range (such as: 2018/8/8) in any cell in the A3:A13 area, an error prompt box will pop up, prompting to restart Enter the required data.
Circling invalid data The function of “circling invalid data” refers to marking the data that does not meet the conditions with a red circle Circle and release How to verify the settings. To use this function, you must first enter data, then set the data verification conditions for the existing data, and then circle the data that does not meet the conditions. For example, this example Circle the invalid data with “Chinese Mathematics English” greater than 150.
The specific steps are: select the area (B3:D10) where the “Chinese Mathematics English” score has been entered – set the data according to the method described above The effective interval for verification is: 0-300 – Select the invalid data area to be circled (B3:D10) – Click the drop-down button on the right side of “Data Validation” in the “Data Tools” group of the “Data” tab- In the expanded drop-down In the list, select the “Circle Invalid Data” command How to verify the settings, and the cell data that does not meet the data verification conditions can be circled (the invalid data in this example are C5 and B9)
Clear data validation conditions If you no longer need to use data validation to limit the data entry of the form, you can clear the validation settings in the “Data Validation” dialog box after opening the form How to verify the settings. This example takes clearing the “Input Prompt” and “Data Valid Range” as an example to explain.
The specific steps are: select the area that needs to clear the “Data Verification” – in the ” Click the “Data Validation” button in the “Data Tools” group of the “Data” tab to open the “Data Validation” dialog box – directly click the “Clear All” button in the lower left corner to clear the “Data Validation” how to validate settings .
I am Excel’s V Class How to verify settings, focusing on getting started with Excel basics, sharing skills, follow me, Let’s grow together!
微信扫一扫打赏
支付宝扫一扫打赏
