February 25, 2014

Avoid Duplicate Entries in Excel

How to Avoid Duplicate Entries in MS Excel Using Data Validation And COUNTIF
How can you avoid duplicate entries right from the beginning of your work in Excel: Use data validation in combination with the countif function. References to cell ranges in the countif function must be ‘absolute’. Watch the Excel training video to see how the process can be easily implemented.
How to implement the process:

  • Select the cells where you are going to make entries. You can later expand the range automatically
  • Click on data tab in the ribbon, select data validation and in the new window under ‘Allow’ select ‘custom’
  • In the formula text box we write the formula =countif($A$5:$A$10,A5)<=1. This assumes, of course, that you start entering data in cell A5 and finish at cell A10. We use the absolute cell reference for the range where we will enter the data
  • This formula compares every new entry with the data already in the range and ensures no two IDs are the same in our example

Note: In the video we have said “comma” while entering the employee IDs. It should have been “dash”