{"id":179841,"date":"2023-12-07T06:15:00","date_gmt":"2023-12-07T06:15:00","guid":{"rendered":"https:\/\/www.henryharvin.com\/blog\/?p=179841"},"modified":"2024-12-11T12:40:53","modified_gmt":"2024-12-11T12:40:53","slug":"how-to-remove-duplicates-in-excel","status":"publish","type":"post","link":"https:\/\/www.henryharvin.com\/blog\/how-to-remove-duplicates-in-excel\/","title":{"rendered":"A Comprehensive Guide on How to Remove Duplicates in Excel"},"content":{"rendered":"\n

<\/p>\n\n\n\n

How to Remove Duplicates in Excel might be a confusing question for all Excel learners. Duplicates in Excel<\/a> may be a real pain, clogging up your spreadsheets and even leading to data analysis errors. Duplicate data can bias your analyses and compromise your spreadsheets’ integrity.<\/h2>\n\n\n\n

Fortunately, there are various strategies you may employ to remove duplicates and keep clean and accurate data. This comprehensive article will give you simple instructions on how to discover duplicates and how to remove duplicates in Excel. Whether you’re a newbie or an experienced user, by the end of this guide, you’ll have the skills to keep your Excel sheets free of excessive repetition.<\/p>\n\n\n\n

\"how
Excel<\/figcaption><\/figure><\/div>\n\n\n\n

You must first identify duplicates before you can eliminate them. Excel includes capabilities for detecting duplicate entries based on specific columns. Understanding the nature of your data allows you to select the most effective strategy for dealing with duplicates.<\/p>\n\n\n\n

How do I find duplicates in Excel?<\/strong><\/h2>\n\n\n\n

If you merely want to detect duplicates and determine whether or not to delete them, highlight all duplicate information with conditional formatting. Moreover, finding, highlighting, and examining duplicates before removing them is preferable to removing all copies simultaneously. <\/p>\n\n\n\n

Step 1:<\/strong>Choose the data you want to check for duplicates. Select Conditional Formatting > Highlight Cell Rules > Duplicate Values<\/strong> from the Home tab.<\/p>\n\n\n\n

\"how<\/figure><\/div>\n\n\n\n

Step 2:<\/strong>Click the Format using the dropdown menu in the Conditional Formatting window that appears to select the color scheme you want to use for highlighting duplicates. Then press the Done button.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Step 3:<\/strong>You can now go over the duplicate data and decide whether any should be deleted.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

How to Remove Duplicates in Excel<\/strong><\/h2>\n\n\n\n

Excel includes a feature that can help you eliminate duplicate entries in your dataset.  If you want to detect and delete duplicates in Excel without personally reviewing them first, you have two options.<\/p>\n\n\n\n

Let’s take a look at the procedures for deleting duplicates in Excel.<\/p>\n\n\n\n

Eradicating duplicates using Remove Duplicate Features in Excel<\/strong><\/h3>\n\n\n\n

Step 1:<\/strong> Choose any data-filled cell. Then, under the Data tab, select Remove Duplicates.<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Step 2:<\/strong> Select the columns you want to include in your search for redundant data in the Remove Duplicates window that displays. Select OK.  (Because the Remove Duplicates tool permanently deletes duplicate data, it’s a good practice to back up the original data to another worksheet.)<\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

If your data contains column headers, check the ‘My data has headers’ box and then click OK. <\/p>\n\n\n\n

When the header option is selected, the first row is not evaluated for deleting duplicate data.<\/p>\n\n\n\n

Step 3:<\/strong> Excel will display the number of duplicate values that were deleted.  <\/strong><\/p>\n\n\n\n

\"\"<\/figure><\/div>\n\n\n\n

Eradicating duplicates using the UNIQUE function in Excel<\/strong><\/h3>\n\n\n\n

The UNIQUE function in Excel generates unique values (i.e., data that isn’t repeated) from a list or range. The only limitation is that this will not replace your current data. It’s in the same spreadsheet as the original data source. <\/p>\n\n\n\n

You can either filter for unique values or eliminate duplicate values to produce a list of unique values. The tasks all have the same function. There is, however, a significant distinction. When you filter for individual data, duplicate values temporarily conceals, but eliminating duplicate values permanently deletes duplicate values.<\/p>\n\n\n\n

Choose an empty column in your spreadsheet to get a unique set of values. Then, using the cell range you want to check for duplicates, enter the UNIQUE function, leaving unique values remaining. For instance,<\/p>\n\n\n\n

=UNIQUE(A2:C14). <\/code><\/pre>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
NOTE:<\/strong> It is also critical to realize that a duplicate value comparison is based on what appears in the cell rather than the underlying value stored in the cell. For example, two cells with the same date value as “3\/9\/2022” and “Mar 9, 2022” are two distinct values. <\/p>\n\n\n\n
\n