Important Steps To Find And Remove Duplicates In Excel

In this blog we are going to learn and discuss how to find and remove duplicates in excel, so without further adieu let us go through it.

Shortcuts and Formulas to Find Duplicates in Excel

To find duplicate values in Excel, execute the subsequent steps.

1. Select the range A1:C10.
2. Click ‘conditional formatting’ on the house tab.
3. Click Highlight Cells Rules, Duplicate Values.
4. Select a formatting style and click on OK.

Result. Excel highlights the duplicate names.
Note: select Unique from the primary drop-down list to spotlight the unique names.

Triplicates

By default, Excel highlights duplicates, triplicates, etc. Execute the subsequent steps to spotlight triplicates only.

1. First, clear the previous conditional formatting rule.
2. Select the range A1:C10.
3. Click ‘conditional formatting’ on the house tab.
4. Click New Rule.
5. Select ‘Use a formula to see which cells to format’.
6. Enter the formula =COUNTIF($A$1:$C$10,A1)=3
7. Select a formatting style and click on OK.

Result. Excel highlights the triplicate names.

Explanation: =COUNTIF($A$1:$C$10, A1) counts the number of names within the range A1:C10 that are adequate to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats cell A1. Always write the formula in excel to remove duplicate values in excel for the upper-left cell within the selected range (A1:C10). Excel automatically copies the formula to the opposite cells. Thus, cell A2 contains the formula in excel to remove duplicates =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to mend this reference.

Note: you’ll be able to use any formula you wish. For instance, use this formula in excel to remove duplicate values in excel =COUNTIF($A$1:$C$10, A1)>3 to focus on names that occur quite 3 times.

Duplicate Rows

To find and highlight duplicate rows in Excel, use COUNTIFS (with the letter S at the end) rather than COUNTIF.

1. Select the range A1:C10.
2. Click ‘conditional formatting’ on the house tab.
3. Click New Rule.
4. Select ‘Use a formula to see which cells to format’.
5. Enter the formula =COUNTIFS(Animals,$A1,Continents,$B1,Countries,$C1)>1
6. Select a formatting style and click on OK.

Note: The named range Animals refers to the range A1:A10, the named range Continents refers to the range B1:B10 and also the named range Countries refers to the range C1:C10. =COUNTIFS(Animals,$A1, Continents,$B1, Countries,$C1) counts the number of rows supported by multiple criteria.

Result. Excel highlights the duplicate rows.

Explanation: Always write the formula for the upper-left cell within the selected range (A1:C10). Excel automatically copies the formula to the opposite cells. As a result, cell A1, B1, and C1 will contain the identical formula, cell A2, B2, and C2 contain the formula =COUNTIFS(Animals,$A2, Continents,$B2, Countries,$C2)>1, etc.

Finally, you’ll use the Remove Duplicate values in Excel to quickly remove duplicate rows. On the information tab, within the Data Tools group, click Remove Duplicate values in Excel.

Shortcuts to Remove Duplicates in Excel

Option 1 – Remove duplicates in excel

If you’re using Microsoft Office Suite you’ll have a touch of a plus because there’s an inbuilt feature for locating and deleting duplicates.

Begin by selecting the cells you wish to focus on for your search. During this case, we’ll select the whole table by pressing “Control” and “A” at the identical time (Ctrl + A). Once you’ve got successfully selected the table, you’ll click on the “Data” tab on the highest of the screen then select the “Remove Duplicates” function.

Once you’ve got clicked on that, a little panel will appear. You may notice that the primary row has automatically been deselected. The rationale for this can be that the “My data has headers” box is ticked. In this case, we don’t have any headers since the table starts at “Row 1.” we are going to deselect the “My data has headers” box. 

Now that the complete table is chosen, you only press the “OK” button to delete all duplicates. During this case, all the rows with duplicate information apart from one are deleted and therefore the details of the deletion are displayed within the popup window.

Option 2 – Advanced filtering in excel

The second tool you’ll be able to use in Excel to spot and delete duplicates is the “Advanced Filter.” This method also applies to Excel 2003. Allow us to start again by opening up the Excel spreadsheet. To sort your spreadsheet, you may have to first select all using “Control” and “A” as shown earlier. Once you have selected your table, you need to click on the ‘data’ bar. 

In the “Sort & Filter” section, you have to click on the “Advanced” button. If you’re using excel 2003, click on the “Data” change posture menu then “Filters” then “Advanced Filters…”

After this, you have to click on the ‘unique records only’ check box. Once you click on “OK,” your document will consist of all the duplicates except one removed. During this case, two were left because the primary duplicates were found in row 1. If you wish the primary row to be deleted, you’ll delete it manually during this case. If you truly had headers instead of duplicates within the first row, just one copy of the prevailing duplicates would are left.

Option 3 – Replace

During this case, we are going to be using the straightforward “replace” function that’s built into all Microsoft Office products. You may begin by opening the spreadsheet you wish to figure on. Once it’s open, you would like to pick a cell with the content you would like to seek out and replace and replica it. Click on the cell and press “Control” and “C” (Ctrl + C).

Once you have got copied the word you wish to look for, you’ll press “Control” and “H” to cite the replace function. Once it’s up, you’ll paste the word you copied into the “Find what:” section by pressing “Control” and “V” (Ctrl + V). Now that you just have identified what you’re trying to find, press the “Options>>” button. Select the “Match entire cell contents” checkbox. The explanation for this can be that sometimes your word could also be present in other cells with other words. 

If you are doing not select this selection, you’ll inadvertently find yourself deleting cells that you just have to keep.  Now you may enter a worth within the “Replace with” box. For this instance, we’ll use the quantity “1.” Once you have got entered the worth, press “Replace all.” You will notice that every one of the values that matched “duplicate” is changed to 1. The explanation we used for amount 1 is that it’s small and stands out. Now you’ll easily identify which rows had duplicate content. To retain one copy of the duplicates, simply paste the first text into the primary row that has been replaced by 1’s.

Conclusion

Now that you simply have identified all the rows with duplicate content, bear the document and hold the “Control” button down while clicking on the quantity of every duplicate row. Once you’ve got selected all the rows that require to be deleted, right-click on one among the greyed-out numbers, and choose the “Delete” option. 

The rationale you would like to try to do this rather than pressing the “delete” button on your computer is that it’ll delete the rows instead of just the content. Once you’re done you’ll notice that each one of your remaining rows has a unique value. Thank you for reading our blog. We hope you found what you were looking for and we could help you with finding and removing duplicate values in Excel.

Related Articles:

Best ATS Resume Scanner in USA
Creative Career Change Resume in 2024
Font For Cover Letter
Ways To Deal With A Negative Coworker