In this video I'll show you how to prevent duplicate entries in your Microsoft Excel spreadsheet.

Timestamps:
Introduction: 0:00
Steps to Prevent Duplicate Entries in Microsoft Excel: 0:27
Conclusion: 2:34

Support this channel by becoming a member:    / @maxdalton  

Video Transcript:
The more data you have in your spreadsheet, the greater the risk of duplicates being entered. One option to prevent duplicate entries and keep your spreadsheet data clean is to use Excel's data validation functionality to only allow for an entry to appear once in a column or row.

Now let's walk through the steps to prevent duplicate entries in your Microsoft Excel spreadsheet.

Step 1. Open the Microsoft Excel spreadsheet where you want to prevent duplicate entries. As an example, in this spreadsheet, I've got a list of superhero names and their associated aliases, and I want to prevent duplicates from being entered in the Alias column.

Step 2. Highlight the column or row you want to prevent duplicate entries from being created in. Make sure you highlight empty cells in the column or row where you think future entries will be created. As a warning, if you don't highlight those cells, then these rules won't be applied, and as a result duplicate entries could be typed into those cells.

Step 3. Click "Data" in the menu along the top of the screen. Data options will be shown in the ribbon bar.

Step 4. Find the Data Tools section in the ribbon bar, and then click "Data Validation" in that section. The Data Validation window is displayed.

Step 5. Click to expand the dropdown menu beneath Allow in the Validation Criteria section, and then choose "Custom." Next, in the Formula field we'll enter a formula that will check newly entered values against all of the other values that are entered in that range, and will reject any new entries that already appear in that cell range. Here's an example of how that formula looks. You'll enter =COUNTIF(. From there you'll enter your cell range, using dollar signs to lock your cells. After that you'll enter the cell at the top of the range as a formality. And finally, you'll close the parenthesis and enter =1 to define how many times an entry should appear in your defined range. Click "OK" when you're finished.

Going forward when you try and enter a duplicate value that already exists in your defined range, you'll receive a message that the value doesn't match the data validation restrictions for the defined cell range.