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 Prevent Duplicate Entries in Excel with Data Validation [ 10:23 ] Play Now | Play in Popup | Download (1824)
