Table of Contents
How to Remove Duplicates in Excel might be a confusing question for all Excel learners. Duplicates in Excel 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.
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.
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.
How do I find duplicates in Excel?
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.
Step 1:Choose the data you want to check for duplicates. Select Conditional Formatting > Highlight Cell Rules > Duplicate Values from the Home tab.
Step 2: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.
Step 3:You can now go over the duplicate data and decide whether any should be deleted.
How to Remove Duplicates in Excel
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.
Let’s take a look at the procedures for deleting duplicates in Excel.
Eradicating duplicates using Remove Duplicate Features in Excel
Step 1: Choose any data-filled cell. Then, under the Data tab, select Remove Duplicates.
Step 2: 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.)
If your data contains column headers, check the ‘My data has headers’ box and then click OK.
When the header option is selected, the first row is not evaluated for deleting duplicate data.
Step 3: Excel will display the number of duplicate values that were deleted.
Eradicating duplicates using the UNIQUE function in Excel
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.
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.
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,
=UNIQUE(A2:C14). 
NOTE: 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.
Formulas for Removal of Duplicates in Excel
Consider the following dataset, which provides information about Olympic medalists for the year 2015.
This method entails merging the columns with an Excel formula and calculating the count. there will be removal of the duplicate values (those with a count greater than 1).
- Let’s use the concatenation operator “&” to join columns A, B, and C, for which the formula will be:
=A2&B2&C2This formula is typed into cell D2 and then copied to all rows.
We’ll need another column called ‘Count’ to locate the duplicates in Column D. As a result, we employ the COUNTIF function on field E2. The formula will be as follows:
=COUNTIF($D$2:D2,D2)This formula counts the number of times each value appears in column D.
If the value of the Count is “1,” it appears only once and is unique. If the value is “2” or above, it is a duplication.
Use the Filter option to apply a filter to the Count column
It is located in the DATA tab, Sort & Filter section, Filter.
At the top of Column E, choose the filter. Choose “1” to keep only the unique values and discard the duplicates.
When you click OK, the duplicate values in the table will be erased. Copy and paste the unique records wherever you wish.
Eradicating Duplicates in Excel Using Power Query Tool
Power Query, like Excel, allows you to remove duplicates from data. Power Query in Excel allows you to import data from a variety of sources as well as clean and alter it. This tool makes it simple to eliminate duplicates in Excel.
Below is a list of names in which we have some duplicates, and you must now use Power Query to erase these duplicates.
1st Step: Select the data first, then navigate to the Data Tab > Data Collection and Transformation > Get Data > From Other Sources > Table/Range.
2nd Step: After that, click OK to convert your data into an Excel table (if it isn’t already an Excel table).
3rd Step: When you click OK, the Power Query editor opens, and the data is loads into it.
4th Step: Then, from the Home Tab, select the “Remove Rows” drop-down and then the “Remove Duplicate” option.
When you click on it, there is a removal of duplicate names leaving only the unique names. The status bar above displays the number of rows with and without duplicate values
Besides the Home Tab, you can remove duplicates by right-clicking the column heading and selecting “Remove Duplicates.”
It’s the same choice you made in the previous steps and achieves the same thing.
Advanced Excel Course From Henry Harvin
How to remove duplicates in Excel is a small concept. If you want to master Excel and become a data analyst, you should take Henry Harvin’s advanced Excel course. This course will teach you how to develop complex spreadsheets and reports using formulae, functions, charts, pivot tables, macros, and other tools. You’ll also discover how to analyze data, automate activities, and improve your productivity. By the end of this course, you will be confident and efficient in handling any Excel project. You can also access online seminars, live projects, assignments, quizzes, and a completion certificate. You will also benefit from expert instructors’ assistance, peer support, and the ability to work on real-world issues. This course is appropriate for anyone looking to enhance their Excel skills, whether they are novices or advanced users. Henry Harvin
Check out the video link for your reference–
.webp)
