Find and Delete Duplicate Data in Excel

This guide provides a practical overview of Find and Delete Duplicate Data, including its main features, benefits, limitations, and important considerations.

There are cases where duplicate data is useful, but sometimes the duplicate values do not help you but it also messes up the data in the spreadsheet.

So how do you find and remove the duplicate data? You can use conditional formatting to find and highlight duplicate data areas, or use Visual Basic to find duplicate data. Then you perform deleting duplicate data in Excel.

Here is a detailed guide on how to find and delete duplicate data in Excel, please follow along.

FIND THE DUAL DATA IN EXCEL

Method 1: Use Highlight Cells Rules in Conditional Formatting

Step 1: Select (black out) the data area on the Excel worksheet where you want to find duplicate data.

Step 2: Select the Home tab -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.

Step 3 : The Duplicate Values window appears, select how to mark the duplicate values in the values with section.

There are 6 types of markup that you can apply to highlight duplicate values:

• Light Red Fill with Dark Red Text: Pour a light red background with dark red text.
• Yellow Fill with Dark Yellow Text: Fill the yellow background with dark yellow text.
• Green Fill with Dark Green Text: fill the green background with dark blue text.
• Light Red Fill: pour light red background.
• Red Text: red text.
• Red Border: red border.

After choosing the type of markup, select OK to mark.

Thus the marker data has been found and marked differently from the duplicate data.

Method 2: Use VBA

Step 1: On Excel worksheet, select View -> Macros or combination of Alt + F8.

Step 2 : Enter a name for the Macro (immediately without accents) in the Macro name box and click Create .

Step 3 : Copy the following code into the middle region Sub timdulieutrung (). End Sub

Dim ws As Worksheet Dim cell As Range Dim myrng As Range Dim clr As Long Dim lastcell As Range Dim lastrow As Long Set ws = ThisWorkbook.ActiveSheet 'Spread the word about my value Set myrng = ws.Range ("A1: C18 ") With myrng Set lastcell = .Cells (.Cells.Count) End With myrng.Interior.ColorIndex = xlNone clr = 3 For Each cell In myrng 'Check to see if the values ??are the same for each other, if the two are the same values If the application is running If Application.WorksheetFunction.CountIf (myrng, cell)> 1 Then 'Neu is the first place where the values ??of each other are the same in the If myrng field.Find (what: = cell, lookat: = xlWhole, MatchCase: = False, after: = lastcell) .Address = cell.Address Then 'Set up cell.Interior.ColorIndex = clr Else' Set up time with 2 different values ??of cell.Interior.ColorIndex = myrng.Find (what: = cell, lookat:= xlWhole, MatchCase: = False, after: = lastcell) .Interior.ColorIndex End If End If Next

Note: In setting the range to find the duplicate values, the value highlighted in A1: C18 you replace with the data range to find the matching values that match the data in your Excel worksheet.

After entering the code, click Run -> Run Sub / UserForm or F5 key to run the program.

Close Visual Basic and you will see all the duplicate values will be dumped in red.

REMOVE DATA COMBINED IN EXCEL

After finding duplicate data in Excel, if you want to delete duplicate data, you can delete it manually based on the marked duplicates. Or you can perform the following steps:

Step 1 : Select the data area to delete the duplicate values.

Step 2 : Click the Data tab -> Remove Duplicates.

Step 3 : Remove Remove Duplicates you select the fields (columns) that if duplicates data between the rows will delete all then click OK to delete.

For example, the Date of T2 column has many duplicate values, but TipsMake. vn does not want to delete, so we will uncheck in the Date of T2 column .

A message appears that the duplicate data has been deleted, click OK .

So you will see the duplicate values have been removed from the data area.

Above is how to find and delete duplicate data in Excel, you can refer to how to do it. So every time you need to find and delete duplicate data in Excel, you can apply the above and do it quickly. Good luck!