Duplicate values occur when a set of data appears on your sheet more than once on the excel sheet. Duplicate values may result in inconsistent data and can therefore lead to substantial problems. To avoid all these big problems, there is a need to learn how to locate and remove duplicate values. Finding duplicated words on excel is a pretty easy task that involves a few well-understood steps.
In this tutorial, we shall discuss some of the best methods to use when locating duplicated values.
Highlighting duplicate values using conditional formatting feature
A conditional formatting tool of excel can be used in identifying the duplicates. :
1. Launch the excel application by double-clicking the Excel icon on your device. Then, open a new workbook or open an existing document of your device by clicking Ctrl + O.
2. Once your data is set, go ahead and highlight the cells' range to find duplicates.
3. On the ribbon, go to the "Home" tab and then click on the "conditional formatting" button.
4. On the drop-down menu, hover the mouse on top of the "Highlight cells rule" button. And then select the "duplicate values" button.
5. A duplicate values dialogue box pops up, choose the custom format from the formatting options (drop-down menu). Select the format of how duplicates will be displayed on your sheet.
6. Finally, hit the ok button, and all duplicates will be highlighted, and you can easily differentiate them and remove them.
Highlighting duplicates using COUNTIF Formula
COUNTIF formula is an excel function-like tool that is used to return duplicate values with a specified column. If the count of occurrence is more than one, the formula highlights it, and the user can easily identify it. Here are the steps to follow using this method: =IF(COUNTIF($A$2:$A$8, $A2)>1, "Duplicate", "Unique")
1. Launch the excel application by double-clicking the Excel icon on your device. Then, open a new workbook or open an existing document of your device by clicking Ctrl + O.
2. On the active cell above workbook cells, select the column you want to find the duplicate and then input the formula to search for duplicates. In the example below, we are finding duplicates in column A.
3. All the duplicates will be highlighted; you can proceed and remove them if necessary.
Highlighting duplicates using the VLookup formula
VLookup formula compares two columns and finds the duplicates, and returns true if they are found. Here are the steps to follow using this method:
1. Launch the excel application by double-clicking the Excel icon on your device. Then, open a new workbook or open an existing document of your device by clicking Ctrl + O. Enter the following formula in an empty cell to highlight duplicate values =IFERROR(VLOOKUP(B2,$A2:$A$8,1,0),""). Customize the range to match your data
2. Enter the Following formula and drag it to highlight all the duplicates =IFERROR(VLOOKUP(B2,A2,1,0),"") . Remember to replace the cell range depending on the data you want to highlight.