Flag Duplicate Values Automatically / Find Duplicate Values Automatically

Automatically Flag Duplicate Values On Input / Know Duplicate Values Automatically while Inputing Value / Find Duplicate Values Automatically in Excel

Sometimes you may be a requirement of preventing duplicates values from being entered in a range of cells in excel worksheet. Enabling this capability in Excel is not tough.

Here is how we can automatically flag duplicate values while inputing values in a specific range of cells in your excel worksheet. Follow the given below method to implement this:-

1) Select the range of cells where you want to prevent duplicate values (e.g. C3:C20);

2) Click on Data Validation button in the Data Tools group From the Data tab;

3) Click the Allow drop-down and select Custom from the list on the Settings tab. A Formula field will appear;

4) Type =COUNTIF($C$3:$C$20,C3)<2 in the Formula field. When you will enter the data into a cell, this formula will be  evaluated. If the formula will evaluate / return to TRUE, the entered value will be accepted. The formula in this example says, count how many times the Active Cell's value appears in the range C3:C20. If it will less than 2, will allow it, otherwise, the Error Alert message will display which you entered. In this example, C3 is the Active Cell. You will need to adjust your formula as required. It is critical that you use Absolute referencing for your validation range (e.g. $C$3:$C$20) and relative referencing for your Active Cell (e.g. C3);

5) Click the Input Message tab, in the Data Validation dialog and, if desired, type a message that will be displayed when the user selects a cell within the range of the Data Validation rule. Otherwise, if you don't want a message to appear every time someone selects a cell in the range, clear the 'Show input message...' option.

6) Click the Error Alert tab and type the message that will be displayed if the user enters a duplicate value in the range;

7) Click OK.

Now test your Data Validation rule by entering values, including some duplicates, within the range.

Edit or Delete Existing Data Validation Rules:-
 
To edit or delete existing validation rules, you start by selecting a cell within the validation range. Note that the formula in the Data Validation dialog will now be relative to the currently active cell. To ensure that the rule is applied to all of the cells in the validation range, select the 'Apply these changes to all other cells with the same settings' option before clicking OK.

How to find / know for which cells you have been applied Data Validation Rules:-
After you have applied Data Validation rules to your worksheet, you may not remember for which cells or range you have applied Data Validation rules to. Thankfully, there is an simple method to find the Data Validation rules in your Excel worksheet.

Finding ranges containing Data Validation:
1) Press the F5 key on your keyboard;

2) click the Special... button which is on the GoTo dialog;

3) In the bottom right corner of the dialog select the Data Validation option. Click All to select all Data Validation ranges in the worksheet or Same to highlight just the cells that have the same Data Validation rules as the active cell.

NOTE:-
But, there is one major drawback when using Data Validation. It does not stop a user from pasting invalid data into the range. When pasting into a cell containing Data Validation, the validation is removed because the cell's formatting is overwritten. For the most part, however, Data Validation is very useful for preventing invalid entries.

And as you can see in the Settings tab of the Data Validation dialog, there are many other validation options that you can apply to your data.