Customizing Excel formulas to avoid duplicate data

When you use Excel 2007 to edit a worksheet, you often have to enter a large amount of data, and sometimes require that the data entered is not allowed to duplicate, such as the common ID number, and so on, once the wrong input to find it is a rather troublesome thing. For this problem, we can actually customize the formula so that Excel can automatically eject the warning message for repeated data to alert the operator in a timely manner.

STEP1: Run the Excel 2007 program and create a new work table, and then press the "CTRL + a" key combination to select all of the table.

STEP2: Click the Data menu, and then select the Data validation option in the Data Tools feature area. Next, in the Data Validation dialog box that pops up, select the Settings tab, select the Custom tab in the Allow Drop-down list box, and type in the text box under formula. COUNTIF ($1:$65535,A1) =1 "(excluding double quotes). The "$1:$65535" here indicates that a full sheet range is checked repeatedly. If you need to check only a specific range of cells, you can adjust it to the actual situation, but you must use an absolute method.

STEP3: Switch to the error Warning tab, in this case, the program has automatically checked the checkbox "Show error warning when entering invalid data", then set style to stop option, and then in the title and error message two text boxes on the right, you can enter the relevant content, Specific warning messages, and so on, and then click the OK button.

After Setup, the program automatically pops up a dialog box that prompts "duplicate data" to provide "retry", "Cancel", and "help" three options when the data is repeatedly entered in the future. Select Retry to check the existing data and select Cancel to re-enter it.