Automatically Flag Duplicate Values On Input

Preventing duplicate values from being entered in a range of cells may be a requirement in some of your worksheets. Although not obvious, enabling this capability in Excel is very easy.

Here's how... 1) Select the range of cells where you want to prevent duplicate values (e.g. B3:B20); 

2) From the Data tab click Data Validation in the Data Tools group; 

3) On the Settings tab click the Allow dropdown and select Custom from the list. A Formula field will appear; 

4) In the Formula field type=COUNTIF($B$3:$B$20,B3)<2 .="" 2="" a="" absolute="" accepted.="" active="" adjust="" alert="" allow="" and="" appears="" as="" b3:b20.="" b3="" cell.="" cell="" count="" critical="" data="" display="" e.g.="" entered="" error="" evaluated.="" evaluates="" example="" for="" formula="" how="" if="" in="" into="" is="" it="" less="" many="" message.="" nbsp="" need="" otherwise="" p="" range="" referencing="" relative="" required.="" s="" says="" than="" that="" the="" this="" times="" to="" true="" use="" validation="" value="" when="" will="" you="" your="">

5) In the Data Validation dialog, click the Input Message tab 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.