Remove Duplicates in Excel to ensure your data is clean, accurate, and reliable. This essential feature is key to effective data management, enabling you to streamline databases, enhance analytical accuracy, and improve overall decision-making processes. By eliminating redundant information, you can focus on unique, valuable data points that drive insightful analyses and strategic outcomes. Embrace the power of Excel to transform cluttered datasets into organized, actionable information, paving the way for clearer insights and more efficient operations.
This Tutorial Covers:
- Remove duplicates in Excel by built in feature
- Understand Filtering for Unique Values or Removing Duplicate Values
- Using Advanced Filter Option
- How to use Formulas to Remove Duplicates
- Conditional Formatting to identify and remove Duplicate Values
1. Remove duplicates in Excel by built in feature
A built-in feature of Excel makes it simple to remove redundant data points from your collection.
Step 1: The first step is to click on any cell or a certain range in the dataset that contains duplicates that you wish to eliminate. Excel will choose the range for you automatically in the following step if you click just one cell.
Step 2: Under “Data” tab, select “Remove Duplicates”, outlined in Red below.
Step 3: As demonstrated below, a dialog window displays. The columns you want to compare and look for duplicate data in can be chosen. If your data contains column headers, check the box next to “My data has headers,” then click OK, outlined in Red below.
The first row will not be taken into account for removing duplicate values when the header option is checked.
Step 4: The duplicate rows will now be deleted by Excel, and a dialog box will appear. Along with the count of unique values, the dialog box displays a summary of how many duplicate values were discovered and eliminated.
Step 5: After clicking ok, observably, the duplicate records have been eliminated.
2. Understand Filtering for Unique Values or Removing Duplicate Values
In order to get a list of unique values, you can either delete duplicate values or filter for them. The objectives of all the tasks are the same. However, there is a crucial difference. Duplicate values are briefly hidden when you filter for unique values, but if you decide to remove them, they are gone forever.
The comparison of duplicate values must also be understood to be based on the contents of the cell rather than its underlying value. When two cells with the same date value are formatted, for instance, as “31/10/2022” and “Oct 10, 2022,” those two values are regarded as separate values. Make it a routine to check before deleting duplicates as a result. To obtain the desired outcomes, try filtering or conditionally formatting unique values.
3. Using Advanced Filter Option: Procedure of removing duplicates using advanced filter option in Excel
You can remove duplicate values using Excel’s Advanced Filter feature and copy the unique values to another location.
Step 1: To start, select the dataset’s cell or range from which duplicates should be eliminated. When you select Advanced Filter and click on a single cell, Excel instantly determines the range.
Step 2: Select the “Advanced” option under the “Sort & Filter” section on the “Data” tab, outlined in Red below.
Step 3: A dialogue box will appear. It comprises a menu of advanced filtering choices. The unique values can be copied to another location by choosing the “Copy to another location” option, outlined in Red below.
Step 4: Verify that the records’ range in the “List Range” field corresponds to the range you specified.
Step 5: Enter the range where the resulting unique values must be copied in the “Copy to:” field.
Step 6: Select “Unique records only” in the checkbox. The most important step comes next. then click “ok”, outlined in Red below.
Step 7: Cell L1 will get copies of each unique value.
4. How to use Formulas to Remove Duplicates?
This method calculates the count by combining the columns using an Excel formula. The redundant values will then be removed by filtering.
Step 1: Let’s use the concatenation operator “&” to merge columns A, B, and C. The Excel formula would be as follows:
=A2&B2&C2
The cell D2 is used to insert the formula, outlined in Red below.
Step 2: After entering the formula, copy it down to all the rows. The result looks like below.
Step 3: In order to identify the duplicates in Column D, we now need a new column called “Count.” Therefore, on cell E2, we apply the COUNTIF function. It will be written as =COUNTIF($D$2:D2,D2)
The number of observations of each value in column D can be determined with the aid of this formula, outlined in Red below.
Step 4: After entering the formula, copy it down to all the rows. The result looks like below.
If Count has the value “1,” then the item has only ever existed once and is unique. Duplicate values are those that have a value of “2” or higher.
Step 5: Under the “Data” tab, select “Filter” option in the “Sort & Filter” section.
A filter will now be added to the Count column, outlined in Red below.
Step 6: Select the top-most filter in Column E. Select “1” to keep only the unique values and remove duplicates. Then click ok, outlined in Red below.
Step 7: After clicking ok, the result looks like below.
The table will be cleared of duplicate values. These resulting unique records can be copied and pasted elsewhere.
5. Conditional Formatting to identify and remove Duplicate Values
Finding and spotting duplicate text is made simpler by conditional formatting. However, Excel is unable to draw attention to duplicate entries in the PivotTable report’s Values section.
Step 1: Start by choosing the cells whose values you want to compare for duplication.
Step 2: Go to “Home” tab, then select “Conditional Formatting”, then go to “Highlight Cells Rules’ and then select “Duplicate Values”, outlined in Red below.
Step 3: The Duplicate Values option will prompt you with a popup box once you click it.
Select the formatting for the duplicate values from the drop-down menu in the box next to the “values with” option. then press OK, outlined in Red below.
Step 4: After clicking ok, the result looks like below.
Step 5: Now, click on any cell or a certain range in the dataset that contains duplicates that you wish to eliminate.
Remember that all duplicate data will be permanently erased before using the remove duplicates tool. It is therefore advised to duplicate the original data to a new worksheet in order to prevent the unintended loss of any information.
Step 6: Under “Data” under the Developer tab, select “Remove Duplicates”, outlined in Red below.
Step 7: As demonstrated below, a dialog window displays. The columns you want to compare and look for duplicate data in can be chosen. Check or uncheck the columns where you want to eliminate duplicates under Columns.
If your data contains column headers, check the box next to “My data has headers,” then click OK, outlined in Red below.
The first row will not be taken into account for removing duplicate values when the header option is checked.
Step 8: The duplicate rows will now be deleted by Excel, and a dialog box will appear. Along with the count of unique values, the dialog box displays a summary of how many duplicate values were discovered and eliminated.
Step 9: After clicking ok, observably, the duplicate records have been eliminated.
Application of Remove Duplicates in Excel
- Data Cleaning: Utilize the Remove Duplicates feature to clean your dataset, ensuring accuracy by eliminating redundant entries, which is crucial for analysis and reporting.
- Customer Database Management: Streamline customer databases by removing duplicate records, ensuring each customer is represented once, enhancing CRM efficiency and communication strategies.
- Inventory Management: Keep inventory records concise and accurate by eliminating duplicate entries of items, aiding in precise inventory tracking and order fulfillment.
- Financial Records Auditing: Use Remove Duplicates to audit financial records, ensuring transactions are not counted more than once, which is vital for accurate financial reporting and analysis.
- Email List Optimization: Clean up mailing lists by removing duplicate email addresses, improving marketing campaign efficiency and reducing the risk of being marked as spam.
- Survey Data Analysis: Eliminate duplicate responses in survey data to ensure the integrity and accuracy of analysis results, providing a true representation of respondent feedback.
You may be interested:
