Fresh Excel Tips

For this example, we are going to implement a formula to check for duplicate entries while data is being entered into a column. To achieve this task, we will be using the COUNTIF function. Also, each time a duplicate entry is found, we will display a confirmation in an adjacent column. Here are the steps:

1. We will be assuming that your data starts from A1.
2. Click on cell B1 and enter the following formula: =IF(A2=”",”",IF(COUNTIF($A$2:A2,A2)>1,”Yes”,”No”))
3. Use the fill handle to copy the formula down as far as is necessary.

Each time a duplicate entry is found, excel will display YES in the adjacent column. Of course, you can modify and apply this formula for your personal use. This tip should work in Excel 97, 2000, 2003 and 2007.

1. How to Use the AND Function to Compare Columns in Excel
2. Use the IF function to compare columns and return a result
3. How to use a Validation List in Excel to Validate Entries
4. How to Utilize Data Entry Rules to Validate data in Excel 2007
5. How to Check for Valid Numbers Using the T Function in Excel