"How To Excel" Mini-Tutorials by TheExcelAddict.com "Helping Average Spreadsheet Users Become Local Spreadsheet Experts" Prevent Duplicate Entries In A Range If you want to prevent duplicate data from being entered into a range, here is a pretty simple solution by using Data Validation. Select the range of cells in which you want to prevent duplicates (for example, A1:A50). From the Data menu select Validation. Click on the Settings tab. From the Allow dropdown box select Custom. In the formula box type =COUNTIF($A$1:$A$50,A1)=1 (see explanation below) Click on the Error Alert tab. In the Title box type "Duplicate Entry!" In the Error Message box type "You cannot enter a value already in the list." Explanation: This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($A$1:$A$50,A1)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert. Instead of typing the cell ranges in the formula, you can select them with the mouse. For the validation range (i.e. A1:A50) select the range, then press the F4 key to make it an absolute reference (includes dollar signs). For the criteria range (i.e. A1) click on the first cell in the validation range (no dollar signs). You do not need to copy this formula. When you select the range first and then apply Data Validation, the validation applies to the whole range. The cell reference you use in the formula is not limited to one column. You can use a range such as $A$1:$C$50. Why not print (CTRL+P) this tip and share it with your friends and associates? Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm. You'll be amazed how much time you'll save just by learning a few of these tips. Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)