How To Find Duplicate Records In Excel

Watch Video – How to Observe and Remove Duplicates in Excel

With a lot of information…comes a lot of duplicate information.

Duplicates in Excel tin can cause a lot of troubles. Whether you lot import information from a database, get it from a colleague, or collate it yourself, duplicates data tin always creep in. And if the data you lot are working with is huge, then it becomes really difficult to find and remove these duplicates in Excel.

In this tutorial, I'll evidence you how to find and remove duplicates in Excel.

CONTENTS:

1. Discover and HIGHLIGHT Duplicates in Excel.
   • Discover and Highlight Duplicates in a Single Column.
   • Discover and Highlight Duplicates in Multiple Columns.
   • Observe and Highlight Duplicate Rows.
2. REMOVE Duplicates in Excel.
   • Remove Duplicates from a Single Cavalcade.
   • Remove Duplicates from Multiple Columns.
   • Remove Duplicate Rows.

Detect and Highlight Duplicates in Excel

Duplicates in Excel can come in many forms. Yous can have it in a unmarried cavalcade or multiple columns. There may too be a duplication of an entire row.

Finding and Highlight Duplicates in a Single Cavalcade in Excel

Conditional Formatting makes information technology simple to highlight duplicates in Excel.

Hither is how to practice it:

• Select the information in which you want to highlight the duplicates.

• Become to Dwelling house –> Conditional Formatting –> Highlight Jail cell Rules –> Duplicate Values.

• In the Duplicate Values dialog box, select Duplicate in the drib down on the left, and specify the format in which you lot want to highlight the duplicate values. You can choose from the set-made format options (in the drop down on the correct), or specify your ain format.

• This will highlight all the values that have duplicates.

Quick Tip: Call up to check for leading or trailing spaces. For example, "John" and "John " are considered unlike as the latter has an extra infinite graphic symbol in it. A good idea would exist to employ the TRIM function to make clean your data.

Finding and Highlight Duplicates in Multiple Columns in Excel

If you accept information that spans multiple columns and you demand to look for duplicates in it, the process is exactly the same as in a higher place.

Hither is how to practise it:

• Select the information.
• Get to Home –> Conditional Formatting –> Highlight Cell Rules –> Duplicate Values.
• In the Duplicate Values dialog box, select Duplicate in the drop down on the left, and specify the format in which y'all want to highlight the duplicate values.
• This will highlight all the cells that have duplicates value in the selected data set up.

Finding and Highlighting Duplicate Rows in Excel

Finding indistinguishable data and finding duplicate rows of data are ii different things. Have a look:

Finding duplicate rows is a flake more complex than finding indistinguishable cells.

Here are the steps:

• In an next column, utilise the following formula:
  =A2&B2&C2&D2
  Drag this down for all the rows. This formula combines all the prison cell values as a unmarried string. (You can also utilise the CONCATENATE function to combine text strings)

Past doing this, we have created a single string for each row. If in that location are indistinguishable rows in this dataset, then these strings would be exactly the same for it.

At present that we have the combined strings for each row, we can use conditional formatting to highlight duplicate strings. A highlighted string implies that the row has a indistinguishable.

Here are the steps to highlight duplicate strings:

• Select the range that has the combined strings (E2:E16 in this example).
• Go to Home –> Conditional Formatting –> Highlight Cell Rules –> Indistinguishable Values.
• In the Duplicate Values dialog box, make sure Duplicate is selected and then specify the colour in which you desire to highlight the duplicate values.

This would highlight the duplicate values in column Due east.

In the above approach, we accept highlighted only the strings that we created.

But what if you desire to highlight all the duplicate rows (instead of highlighting cells in ane unmarried column)?

Here are the steps to highlight duplicate rows:

• In an adjacent column, apply the following formula:
  =A2&B2&C2&D2
  Elevate this downwardly for all the rows. This formula combines all the cell values as a unmarried string.

• Select the data A2:D16.
• With the information selected, go to Home –> Provisional Formatting –> New Rule.

• In the 'New Formatting Rule' dialog box, click on 'Apply a formula to decide which cells to format'.
• In the field below, use the following COUNTIF function:
  =COUNTIF($Due east$2:$Due east$16,$E2)>1

• Select the format and click OK.

This formula would highlight all the rows that have a duplicate.

Remove Duplicates in Excel

In the above section, we learned how to find and highlight duplicates in excel. In this section, I will show you how to get rid of these duplicates.

Remove Duplicates from a Single Column in Excel

If you have the information in a unmarried column and y'all want to remove all the duplicates, here are the steps:

• Select the data.
• Go to Data –> Data Tools –> Remove Duplicates.
• In the Remove Duplicates dialog box:
  • If your data has headers, make certain the 'My data has headers' option is checked.
  • Make sure the cavalcade is selected (in this case there is only ane cavalcade).

• Click OK.

This would remove all the indistinguishable values from the column, and you lot would have only the unique values.

CAUTION: This alters your information set by removing duplicates. Make sure you lot have a dorsum-up of the original data gear up. If you want to excerpt the unique values at some other location, re-create this dataset to that location so use the higher up-mentioned steps. Alternatively, you tin can also use Advanced Filter to extract unique values to some other location.

Remove Duplicates from Multiple Columns in Excel

Suppose yous have the data as shown below:

In the above data, row #two and #16 have the verbal same data for Sales Rep, Region, and Amount, merely different dates (same is the instance with row #10 and #xiii). This could exist an entry error where the same entry has been recorded twice with different dates.

To delete the duplicate row in this case:

• Select the data.
• Get to Data –> Data Tools –> Remove Duplicates.
• In the Remove Duplicates dialog box:
  • If your information has headers, make sure the 'My data has headers' choice is checked.
  • Select all the columns except the Date cavalcade.

• Click OK.

This would remove the 2 indistinguishable entries.

Note: This keeps the first occurrence and removes all the remaining duplicate occurrences.

Remove Duplicate Rows in Excel

To delete duplicate rows, hither are the steps:

• Select the entire information.
• Go to Data –> Data Tools –> Remove Duplicates.
• In the Remove Duplicates dialog box:
  • If your information has headers, brand sure the 'My data has headers' choice is checked.
  • Select all the columns.

• Click OK.

Apply the higher up-mentioned techniques to make clean your data and become rid of duplicates.

You May Also Like the Post-obit Excel Tutorials:

• 10 Ways to Clean Information in Excel Spreadsheets.
• Remove Leading and Trailing Spaces in Excel.
• 24 Daily Excel Problems and their Quick Fixes.
• How to Detect Merged Cells in Excel.

Source: https://trumpexcel.com/find-and-remove-duplicates-in-excel/

Posted by: hillneho1973.blogspot.com

Share this post