Buzz
While working with Excel spreadsheets, encountering duplicate data is common due to the large volume of information. Although some instances of duplicate data may be beneficial, often they only serve to clutter the spreadsheet. So, how can you locate and eliminate these duplicate entries? You can utilize conditional formatting to highlight duplicate data or employ Visual Basic to identify duplicate records. Subsequently, you can proceed to remove the duplicate data in Excel.
Below is a comprehensive guide on how to locate and remove duplicate data in Excel. Feel free to follow along.
LOCATING DUPLICATE DATA IN EXCEL
Method 1: Using Highlight Cells Rules in Conditional Formatting
Step 1: Select the data range in your Excel worksheet where you want to find duplicate data.
Step 2: Navigate to Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.
Step 3: In the Duplicate Values dialog box, choose how you want to mark the duplicate values under values with.
Method 2: Utilizing VBA
Step 1: On the Excel spreadsheet, navigate to View -> Macros or press Alt + F8.
Step 2: Input a name for the Macro (without accents) into the Macro name box and click Create.
Step 3: Copy the following code snippet into the middle of the Sub findduplicatedata()......End Sub block.
REMOVE DUPLICATE DATA IN EXCEL
After identifying duplicate data in Excel, if you wish to remove it manually, you can delete based on the marked duplicate data areas. Alternatively, follow these steps:
Step 1: Select the data range where you want to remove duplicate values.
Step 2: Go to Data -> Remove Duplicates.
Step 3: When Remove Duplicates appears, select the fields (columns) where if data is duplicated across rows, it will be deleted entirely. Then press OK to delete.
For example, column Workday T2 contains many duplicate values, but Mytour does not want to delete them. Therefore, we will deselect the Workday T2 column.
A message will appear indicating the number of duplicate data entries deleted. Press OK.
As a result, you will see the duplicate values removed from the data range.
Above is the method for finding and removing duplicate data in Excel. You can refer to it for implementation. This way, whenever you need to find and remove duplicate data in Excel, you can apply these methods quickly. Wishing you success!
