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!

4 ★ | 1 Vote

May be interested

  • The best 5 apps to delete photos on AndroidThe best 5 apps to delete photos on Android
    today, smartphones are a popular means of taking photos of amateurs. after a while, your device will become messy with the same or not-so-good photos. the article will introduce you to the 5 best duplicate photo removal apps on android.
  • How to filter duplicate data on 2 Excel sheetsHow to filter duplicate data on 2 Excel sheets
    to filter duplicate data from 2 sheets in excel, you can use the vlookup function.
  • Warning of duplicate values ​​in Excel - Warning of data duplicationWarning of duplicate values ​​in Excel - Warning of data duplication
    microsoft excel gives you lots of useful features to help your data entry work best. one of those features is the duplicate value warning feature.
  • MS Excel 2007 - Lesson 5: Edit WorksheetMS Excel 2007 - Lesson 5: Edit Worksheet
    editing a spreadsheet in excel 2007 includes operations like: inserting cells, inserting rows / lines and inserting columns, deleting rows, deleting cells, deleting columns, finding data, replacing data or checking spelling, go to a certain cell in the spreadsheet.
  • This is a simple way to find and delete Hyperlink in ExcelThis is a simple way to find and delete Hyperlink in Excel
    if spreadsheet excel has many hyperlinks, then you want to remove these hyperlinks. however, with so many hyperlinks, removing hyperlinks will take a long time. so how to find and delete all hyperlinks on excel file, please refer to the following article of network administrator.
  • How to Delete Duplicate Records in OracleHow to Delete Duplicate Records in Oracle
    when working in oracle, you may find that some of your records have duplicates. you can delete these duplicate rows by identifying them and using its rowid, or row address. before you begin, you should create a backup table in case you...
  • How to delete duplicate songs on iTunesHow to delete duplicate songs on iTunes
    if you download songs outside the itunes store, your library may be deleted, there are many duplicate songs.
  • Instructions for finding and merging duplicate contacts in GoogleInstructions for finding and merging duplicate contacts in Google
    if you have used the google contacts app for years, then your contacts will have lots of duplicate contacts. therefore, google integrates a feature that helps you find and merge those duplicate contacts. please follow the article below to know how to do it in detail!
  • How to Find Duplicates in ExcelHow to Find Duplicates in Excel
    when working with a microsoft excel spreadsheet with lots of data, you'll probably encounter duplicate entries. microsoft excel's conditional formatting feature shows you exactly where duplicates are, while the remove duplicates feature...
  • How to delete dots in a series of numbers on ExcelHow to delete dots in a series of numbers on Excel
    to quickly remove the dot in the numeric sequence in excel, we can use the search or use function in excel.