learn microsoft excel

How to Avoid Duplicate Entries in MS Excel Using Data Validation And COUNTIF

Home
Excel 2003
Macros Excel 2007
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Our Youtube Videos


How to avoid duplicate entries in an Excel worksheet using data validation & 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:
  1. Select the cells where you are going to make entries. You can later expand the range automatically
  2. Click on data tab in the ribbon, select data validation and in the new window under 'Allow' select 'custom'
  3. 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 us the absolute cell reference for the range where we will enter the data
  4. 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"




Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Our Youtube Videos