"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 - 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?
| 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) |

otherwise click here and you'll find lots more time-saving Excel tutorials like this one.