SOLVED

Duplicates

Copper Contributor
What is the simplest way to prevent duplicates entries in a Excel column, when data is entered?
2 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Tbarber 

A very simple solution:
Select the column (or columns) in which you want to enter the words.
Click Home > Conditional Formatting > Cell Highlighting Rules > Duplicate Values ​​> OK.
As soon as you enter a word a second time, it will be marked by a light red background color.

 

Slightly more complicated:
Select the column (or columns) in which you want to enter the words.
As an example, let's assume it's column A, with A1 as active cell.
Select Data > Data Validation.
On the Settings tab, from the Allow list, choose Custom.
Click in the Formula box.
Enter the following formula - it refers to the selected column and the active cell:

=COUNTIF($A:$A;$A1)=1

Activate the Error Message tab.
Enter an instructional error message if you want; it will be shown if you enter a duplicate word.
Click OK.

Thank you, that works great!
1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Tbarber 

A very simple solution:
Select the column (or columns) in which you want to enter the words.
Click Home > Conditional Formatting > Cell Highlighting Rules > Duplicate Values ​​> OK.
As soon as you enter a word a second time, it will be marked by a light red background color.

 

Slightly more complicated:
Select the column (or columns) in which you want to enter the words.
As an example, let's assume it's column A, with A1 as active cell.
Select Data > Data Validation.
On the Settings tab, from the Allow list, choose Custom.
Click in the Formula box.
Enter the following formula - it refers to the selected column and the active cell:

=COUNTIF($A:$A;$A1)=1

Activate the Error Message tab.
Enter an instructional error message if you want; it will be shown if you enter a duplicate word.
Click OK.

View solution in original post