Find and delete duplicate data in Excel

In the process of working with Excel spreadsheets, there will be Excel files with large amounts of data so duplication of data is very common.

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.

Find and delete duplicate data in Excel Picture 2

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

Find and delete duplicate data in Excel Picture 3

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:

1. Light Red Fill with Dark Red Text: Pour a light red background with dark red text.
2. Yellow Fill with Dark Yellow Text: Fill the yellow background with dark yellow text.
3. Green Fill with Dark Green Text: fill the green background with dark blue text.
4. Light Red Fill: pour light red background.
5. Red Text: red text.
6. Red Border: red border.

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

Find and delete duplicate data in Excel Picture 4

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

Find and delete duplicate data in Excel Picture 5

Method 2: Use VBA

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

Find and delete duplicate data in Excel Picture 6

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

Find and delete duplicate data in Excel Picture 7

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.

Find and delete duplicate data in Excel Picture 8

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

Find and delete duplicate data in Excel Picture 9

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

Find and delete duplicate data in Excel Picture 10

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.

Find and delete duplicate data in Excel Picture 11

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

Find and delete duplicate data in Excel Picture 12

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 .

Find and delete duplicate data in Excel Picture 13

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

Find and delete duplicate data in Excel Picture 14

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

Find and delete duplicate data in Excel Picture 15

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!