How to Prevent Duplicate Entries in Excel with Data Validation

Formula for Data Validation

Formula for Data Validation

Over time, it is easy for duplicate entries to creep into an Excel Data Set. In this video tutorial, I show you, step-by-step, how to apply Data Validation to a range of cells so that anyone who attempts to enter a duplicate value will be prevented from doing so.

Use a “Logical Formula” for Data Validation

In this lesson, I show you how to “customize” the COUNTIF Function in the Data Validation Dialog Box so that it returns the value of TRUE. Logical formulas return either TRUE or FALSE. You MUST use a Logical Formula in Data Validation.

What Else is Covered in this Tutorial?

  • Use Paste Special – Validation to extend Data Validation to a wider range of cells
  • Use Find – Go to Special – Validation to highlight all cells on worksheet that contain Data Validation
  • Use Circle Invalid Entries from the Data Validation menu to automatically “draw a red circle” around existing cells that contain duplicate values
  • How to quickly remove Data Validation from a Range of Cells

Related Topic – Conditional Formatting in Excel

Another way to highlight existing cells that contain duplicate values is to use Conditional Formatting. The improvements in Excel 2007 and Excel 2010 make this “a snap” because this is now a Menu Selection! In my next tutorial, I will show you how this is done in both Excel 2003 – with the COUNTIF Function – and from the new Menu Selections introduced in Excel 2007.

Watch Tutorial in High Definition

Follow this link to my YouTube channel – DannyRocksExcels – where you can watch this tutorial in High Definition mode.

How to Compare Two Excel Lists for Differences

Lists, lists, lists … It sometime seems that I am swimming in a sea of lists. Lists of customers, updated lists of customers, sales by week, sales for the current week, book lists, revised lists of books, etc. Lists seem to beget more lists and both beget problems – How to compare pairs of lists to avoid duplicates and redundancies!

If you can relate to this scenario, I know that you will enjoy this Excel Video Lesson.

Three Techniques Used in My Tutorial

1) Use the =MATCH() Function

2) Use the =VLOOKUP() Function

3) Use a Pivot Table in Excel 2007

Shop at My Secure Online Shopping Site

If you enjoyed this Excel Video Lesson, I am confident that you will enjoy the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.”  Click on this link to visit my secure online shopping website.

You can watch this Excel Video Lesson on YouTube – Subscribe to my YouTube Channel – DannyRocksExcels

Watch My Video Now

 

Related Excel Video Lessons: