"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

Preventing Duplicate Entries In A Range Of Cells


If you want to prevent duplicate entries in a range of cells, you can use Excel's Data Validation feature.

Let's assume you want to prevent duplicate entries in cells B5:B50.

  1. Select cells B5:B50.
  2. Choose Data, Validation .
  3. Click the Settings tab .
  4. Click the dropdown arrow in the Allow: box and select Custom. A new Formula box appears.
  5. Click in the Formula box and enter the formula =COUNTIF($B$5:$B$50,B5)=1.
  6. Click on the Error Alert tab and type Duplicate Entry in the Title box.
  7. In the Error message box type You cannot enter duplicate values in this list!
  8. Click OK.


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)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.