Excel 2010 Data Validation Actions

Excel's powerful tabulation function, to bring convenience to our work, but in the form of data entry process will inevitably error, inadvertently will input some wrong data, such as duplicate ID number, out of range of invalid data. In fact, as long as a reasonable set of data validation rules, you can avoid errors. Let's go through two examples to experience the magical use of Excel 2010 data validation

Example one: Refuse to input duplicate data

ID number, ID card number and other personal IDs are unique, not allowed to repeat, if the duplicate ID in Excel input, it will bring inconvenience to information management, we can set up Excel 2010 data validity, refuse to enter duplicate data.

Run Excel 2010, switch to the data ribbon, select the columns you want to enter data (such as column A), click the Data Validation button, and pop up the data Validation window.

Figure 1 Data Validation window

Switch to the Settings tab, lay the Allow Drop-down box, select Custom, and enter "=countif (A:A,A1) =1" In the Formula field (without double quotes, entered in the English half-width state).

Figure 2 Setting data validation conditions

Switch to the Error Alert tab, select the style of the error warning message, fill in the title and error message, and then click OK to complete the data validation settings.

Figure 3 Setting Error warning messages

In this way, enter information such as ID card in column A, and when the information entered repeats, Excel immediately pops up the error warning and prompts us to enter it incorrectly.

Figure 4 Pop-up error warning

At this point, just click "No", close the prompt message box, re-enter the correct data, you can avoid the entry of duplicate data.

Example two: Quickly pull out invalid data

With Excel processing data, some data is limited, for example, the test results with hundred score must be a data between 0-100, input this range of data is invalid data, if the use of manual audit method, from the vast data to find invalid data is a troublesome matter, We can use the data validation of Excel 2010 to quickly identify invalid data in the table.

Open an Excel table with Excel 2010 that you want to audit. Select the area you want to audit, switch to the data ribbon, click the Data Validation button, pop the data Validation window, switch to the Settings tab, open the Allow Drop-down box, select Decimal, open the Data drop-down box, select "Between", the minimum value is set to 0, the maximum is set to 100, and click OK (Figure 5).

Figure 5 Setting data validation rules

After you set up the data validation rules, click the data ribbon, the Data Validation button on the right side, select "Loop invalid Data" from the Drop-down menu, and all invalid data in the table is released by a red oval circle with the wrong data at a glance.

Figure 6 Circle-Release Invalid data

Here are two examples of how Excel 2010 data validation is used. In fact, this is only the tip of the iceberg, data availability and many other aspects of the application, we need to explore in the actual use of the process.