There may be several occasions when you don’t want duplicate records in your Microsoft Excel spreadsheet. For instance you are preparing a list of employees in your company for the annual bonus payments and you certainly do not want an employee’s name occurring twice. In this article we’ll look at how duplicates can be detected and corrected.
Now let us assume that the Excel spreadsheet we are using contains employee codes in the first column and employee names in the second. The easiest way to identify duplicates is via Data Validation. To do a quick check if duplicates exist select the column containing the employee codes, and then click Data and then click Data Validation from the ribbon. Note that for Circle Invalid Data to work you must use it in conjunction with a Data Validation rule. For this click on cell A1 assuming the employee codes are in in the A column), click on Data Validation from the drop-down menu and the Data Validation dialog opens. In the dialog under Validation Criteria select Custom from the Allow drop-down box. Then in the Formula field enter the following formula =COUNTIF(A:A, A1)=1. Click OK and exit. Now select and click Data Validation from the ribbon under Data. From the menu that appears click on Circle Invalid Data. Instantly Excel draws red circles on the employee codes, wherever it found duplicates. Clear Validation Circles will clear the circled items. The trick in the COUNTIF formula is to return a TRUE by making the COUNTIF function return a True value if there is only one instance of the employee code. If it counts and finds a duplicate, then the count will be equal to False. This is a great way to spot duplicates and then edit them or manually remove them.
To remove duplicate items select the column of items where you want to remove duplicates and then from the ribbon, under Data, and click Remove Duplicates. The Remove Duplicates dialog opens. Check the selections made and click OK. Note that it is prudent to keep a copy of your Excel spreadsheet before you perform this operation and do a subsequent save. Now there is yet another thing that you need to bear in mind. Now suppose you find two employees have the same name. First of all is that possible? Yes it can though the chances of both of them working in the same company are very rare. Even in such a case they will not have the same employee id. However you can control the removal of the duplicates by checking the checkboxes for employee id or name or both and accordingly the records will be deleted.
Next we look at another efficient form of handling duplicates and that is conditional formatting. To use Microsoft Excel Conditional Formatting, select your cells where you would like to apply conditional formatting, then from the Home menu, click on Conditional Formatting on the ribbon. From the drop-down menu click on Highlight Cell Rules and from the sub-menu select Duplicate Values. The Duplicate Values dialog opens. In this dialog you can set to report for Duplicate or Unique values of the cells you have selected. You also have various options for coloring the duplicate or unique content including your own custom color format. Click OK and Excel will instantly apply conditional formatting and you will be able to see the duplicate or unique records marked by the color options you chose. Note that in Excel 2007 the conditional formatting has taken a further step from what it was in Excel 2003 where we had to include and write formulas too. In the next free Excel help article we’ll discuss how to prevent duplicate records from entering your Excel spreadsheet.
The free Excel help articles are posted in our corporate office in the Irvine area of Orange County California, between San Diego and Los Angeles. If you would like additional articles written on this topic, or if you have other topics to suggest for future free Excel help articles at Excel and Access experts, please send them today, and we will pst those for you.