In this free Excel help article we’ll discuss the various methods that will ensure that you do not have duplicate entries in your Excel spreadsheet.
The method we are going to use is Data Validation where we can use a custom formula to prevent duplicates from being entered into the spreadsheet. Assume that we have a list of employee names and their codes (employee codes in column A and employee names in column B). To ensure that no two employee codes exist and that they also cannot be added, select the range of cells in column A where we would like to apply Data Validation, and then on Data from the menu. From the ribbon, click on Data Validation and from the drop-down menu click on Data Validation. The Data Validation dialog opens. Using the Data Validation dialog we’ll create a custom formula, and then we’ll also create a custom error message that will indicate to the user that duplicate employee codes aren’t allowed. First under the Settings tab, under Validation criteria first select Custom from the Allow drop-down menu. The Formula field is enabled allowing you to enter a formula. Here we’ll enter a formula. The formula we’ll be using is as follows. =COUNTIF(A2:A302, A2)=1. The COUNTIF function is made to return a logical answer i.e. True or False. Based on these answers we’ll check for duplicates and duplicate entry too.
An explanation of the COUNTIF function will add more clarity to our process. The function we are using checks to see in the column A, range A2 to A302 (assuming we have that many codes to check) if with the value of cell A2 if there are any duplicates. Now if the answer is 2 which means two cells of the same value have been counted then a 2 is returned and the situation is FALSE i.e. indicating a duplicate exists. If there are more than 2 instances that reply is also returned and that also is treated as duplicate. Otherwise 1 is returned on which means the situation is TRUE only one instance of the employee code exists.
Next we move on to the Input Message tab to put in a custom message. So click the Input Message tab of the Data Validation dialog. Ensure the Show input message when cell is selected checkbox is checked. Next enter a custom message in the Input Message box.
Next click the Error Alert tab. Here too ensure that the Show error alert after invalid data is entered checkbox is checked. Select the Stop style from the drop-down menu and enter a custom error message like “Duplicate – Enter a unique value” in the Error message box. Finally click OK.
Now as you point your mouse cursor in each of the cells in the A column i.e. A2 to A302 the input message that you entered under the Input Message tab is displayed in a call-out. Now if you try and enter a duplicate value (assuming there are already employee codes in the cells) Excel immediately prompts you with the error message that you custom created under the Error Alert tab and the code you entered is discarded.
What if you now have more than 300 cells where you want to copy the validation to? Before that let us find out how you can find which cells have data validation. Excel has a neat trick for this. From the Home menu’s ribbon on the far right is the Find and Select button. Click it and from the menu select Data Validation. Excel will immediately highlight the cells in the worksheet that has data validation. To copy validation just right click on a cell that has validation and select copy from the shortcut menu. Then go the cell or cells where you want to copy the validation and then click Paste Special from the menu obtained by clicking Paste (located top left of the ribbon under the Home menu). The Paste Special dialog opens. Under the Paste section select the Validation radio button. Now click ok and the validation is copied to your cell or cells. You can now try and enter a duplicate employee code in the cell and check if the validation works. It most certainly will.
Now what if you want to remove all the data validation? As we did before use the Find and Seek menu to locate cells that have data validation. Then select Data from the menu and from the ribbon click on Data Validation, and choose Data Validation from the drop-down menu. The Data Validation dialog opens. Under the Settings tab click the Clear All button. Now click Ok to exit the dialog and you’ll notice that the data validation for all the cells that have data validation has been removed. And if you need help with Microsoft Excel 2013 (Excel 15), let us know.
There are additional free Excel help articles on this topic, and in the corporate office in the Irvine area of Orange County California, near Los Angeles, we are working hard to create more. So check back frequently, and send your suggestions to Christopher at Excel and Access, LLC’s corporate office.