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 Find and Delete Duplicate Records in Excel 2007

It is easy  – much too easy – to allow duplicate records to creep into an Excel database. Fortunately, starting in Excel 2007, it is really easy to first find and then delete duplicate records.

Remove DUplicate Records

Remove Duplicate Records

With the price of postage climbing, you cannot afford to send duplicate copies of a mailing piece. Not to mention that doing so reflects poorly on your company’s organizational skills. Likewise, having duplicate account entries makes for extra work and confusion.

Use Conditional Formatting to Highlight Duplicate Records

Starting in Excel 2007, Conditional Formatting got a lot easier to use. One of the menu selections is “Show Duplicate Records.” Now, with just a few mouse clicks, you can format the records that show duplicate values in your data set. I like to use this technique prior to acutally duplicating records so that I can get a sense of the number of possible duplicate records in my data set.

Selecting Duplicate Records to Delete

As a best practice, I always make a backup copy of my data set before I actually delete records. This way, I can quickly recover from any accidental deletions of non-duplicate records. When you choose the command to “Remove Duplicates,” a dialog box opens to reveal each field in your data set. Generally, you want to select all of the fields to narrow down the list of records to delete. Since I have already previewed the possible duplicate records by using Conditional Formatting, I have a pretty good idea of how many records will be deleted. You do not see a preview of the exact records that will be deleted, so make sure that you have a backup copy of your original list in order to restore any “accidental” deletions.

These new commands and menus introduced in Excel 2007 make deleting duplicate records a very quick process. Use them – wisely – to save yourself time, confusion and money!

Learn how to “Master Excel in Minutes – Not Months”

Watch this video lesson in High Definition, Full Screen mode. Follow this link to view this on my YouTube Channel – DannyRocksExcels

Secure Shopping Cart

Secure Shopping Cart

This is one of the tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” CLick here to open a secure shopping cart to purchase it.