Automatically Stop 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.

You can prevent duplicate values from being entered in a range of cells through Data Validation.

1) Select the range of cells where you want to prevent duplicate values (in this example, B3:B20).Stop Duplicate Values On Input Figure 1

2) In Excel, on the Data tab, click Data Validation.

3) Once in the Data Validation dialog box, under 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. When data is entered into a cell, this formula is evaluated. It looks for duplicate values. Absolute values are required here because you want to compare the top of the list to the data that appears below it. It is essential that you use Absolute referencing ($B$3 not B3) for your validation range (e.g. $B$3:$B$20) and relative referencing for your Active Cell (e.g. B3).

Stop Duplicate Values On Input Figure 2

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.

Stop Duplicate Values On Input Figure 3

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

Local Seminars Related to this Topic:

Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Jul. 26

Pacific Time Zone

Jul. 27

Central Time Zone

Aug. 4

Pacific Time Zone

Aug. 5

Central Time Zone

Aug. 9

Central Time Zone

Aug. 10

Eastern Time Zone

Aug. 11

Central Time Zone

Aug. 17

Eastern Time Zone

Aug. 19

Central Time Zone

Aug. 23

Central Time Zone

Aug. 25

Central Time Zone

Aug. 26

Pacific Time Zone

Aug. 30

Central Time Zone

Aug. 31

Eastern Time Zone

Sep. 8

Central Time Zone

Sep. 13

Central Time Zone

Sep. 14

Pacific Time Zone

Sep. 16

Eastern Time Zone

Sep. 20

Pacific Time Zone

Sep. 21

Central Time Zone

Sep. 22

Eastern Time Zone

Sep. 27

Eastern Time Zone

Sep. 29

Pacific Time Zone

Sep. 30

Central Time Zone

Oct. 4

Eastern Time Zone

Oct. 5

Central Time Zone

Oct. 7

Central Time Zone

Oct. 12

Central Time Zone

Oct. 13

Central Time Zone

Oct. 14

Pacific Time Zone

Oct. 18

Eastern Time Zone

Oct. 19

Central Time Zone

Oct. 21

Central Time Zone

Oct. 25

Pacific Time Zone

Oct. 27

Central Time Zone

Nov. 1

Central Time Zone

Nov. 2

Central Time Zone

Nov. 3

Eastern Time Zone

Nov. 4

Central Time Zone

Nov. 8

Central Time Zone

Nov. 9

Eastern Time Zone

Nov. 15

Pacific Time Zone

Nov. 17

Central Time Zone

Nov. 18

Central Time Zone

Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Jul. 27

Pacific Time Zone

Jul. 28

Central Time Zone

Aug. 5

Pacific Time Zone

Aug. 6

Central Time Zone

Aug. 10

Central Time Zone

Aug. 11

Eastern Time Zone

Aug. 12

Central Time Zone

Aug. 18

Eastern Time Zone

Aug. 20

Central Time Zone

Aug. 24

Central Time Zone

Aug. 26

Central Time Zone

Aug. 27

Pacific Time Zone

Aug. 31

Central Time Zone

Sep. 1

Central Time Zone

Sep. 9

Central Time Zone

Sep. 14

Central Time Zone

Sep. 15

Pacific Time Zone

Sep. 17

Eastern Time Zone

Sep. 21

Pacific Time Zone

Sep. 22

Central Time Zone

Sep. 23

Eastern Time Zone

Sep. 28

Eastern Time Zone

Sep. 30

Pacific Time Zone

Oct. 1

Central Time Zone

Oct. 5

Eastern Time Zone

Oct. 6

Central Time Zone

Oct. 8

Central Time Zone

Oct. 13

Central Time Zone

Oct. 14

Central Time Zone

Oct. 15

Pacific Time Zone

Oct. 19

Eastern Time Zone

Oct. 20

Central Time Zone

Oct. 22

Central Time Zone

Oct. 26

Pacific Time Zone

Oct. 28

Central Time Zone

Nov. 2

Central Time Zone

Nov. 3

Central Time Zone

Nov. 4

Eastern Time Zone

Nov. 5

Central Time Zone

Nov. 9

Central Time Zone

Nov. 10

Eastern Time Zone

Nov. 16

Pacific Time Zone

Nov. 18

Central Time Zone

Nov. 19

Central Time Zone

Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Aug. 3

Central Time Zone

Aug. 4

Eastern Time Zone

Aug. 12

Central Time Zone

Aug. 17

Central Time Zone

Aug. 23

Central Time Zone

Sep. 1

Pacific Time Zone

Sep. 9

Eastern Time Zone

Sep. 14

Central Time Zone

Sep. 15

Pacific Time Zone

Sep. 20

Central Time Zone

Oct. 1

Central Time Zone

Oct. 5

Pacific Time Zone

Oct. 6

Central Time Zone

Oct. 12

Central Time Zone

Oct. 21

Central Time Zone

Oct. 25

Eastern Time Zone

Oct. 29

Central Time Zone

Nov. 2

Eastern Time Zone

Nov. 8

Central Time Zone

Nov. 17

Central Time Zone

Nov. 19

Pacific Time Zone

Related Advanced Excel Articles

Leave a Reply

Your email address will not be published. Required fields are marked *