How to Remove Duplicates in MS Excel | SoftwareKeep

Duplicate data in Microsoft Excel can be frustrating, particularly for those working with extensive datasets or collaborating on shared documents. Combining tables or multiple users accessing the same file can result in redundant entries, compromising data accuracy.

Detecting and managing duplicate records becomes increasingly important as dataset size grows. In this article, we will guide you through removing duplicates in Excel, helping you clean up your data and enhancing its reliability.

Trust us to provide step-by-step instructions that will empower you to identify and eliminate duplicate entries effectively. Let's dive in and optimize your Excel spreadsheets today.

Find and remove duplicates

You can use the built-in conditional formatting feature to find and remove duplicates in Excel. This allows you to highlight duplicate data, making it easier to identify and decide whether to remove them. Here's a straightforward guide to help you through the process:

Select the cells or range of data where you want to check for duplicates. This could be a single column, multiple columns, or the entire dataset.
Note that duplicate highlighting doesn't work in the Values area of a PivotTable report.
Navigate to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
Select "Highlight Cells Rules" from the drop-down menu and then choose "Duplicate Values."
A dialog box will appear with formatting options for the duplicate values. You can customize the formatting to suit your preferences. For example, you might want to apply a different font or fill color to the duplicates.
After selecting the formatting, click "OK." According to the chosen formatting, Excel will immediately highlight any duplicate values in the selected range.
Review the highlighted duplicates and decide whether to keep or remove them. You can manually delete the duplicates or use Excel's built-in remove duplicates feature.

Remove duplicate values

Before proceeding, creating a backup of your original data on a separate worksheet is recommended to avoid accidental data loss. Here's a straightforward guide on how to remove duplicates in Excel:

Select the range of cells containing the duplicate values you want to remove. This could be a single column or multiple columns.
Before removing duplicates, ensure that no outlines or subtotals are present in your data, as this may interfere with the process.
Go to the "Data" tab in the Excel ribbon and click on "Remove Duplicates" in the "Data Tools" group.
A dialog box will appear displaying the selected range. By default, all columns are checked for duplicate removal. Uncheck any columns where you want to retain the duplicates.
Click "OK" to initiate the removal process.

Excel will now analyze the selected range and remove duplicate values based on the specified columns. The remaining unique values will be retained, and the duplicate data will be permanently deleted.

Filtering vs. Removing Duplicates in Excel

Filtering for unique values and removing duplicate values serve the common purpose of obtaining a list of distinct values. However, there is an important distinction between the two processes. Filtering for unique values temporarily hides duplicate values while removing duplicate values permanently deletes them.

When identifying duplicate values, Excel compares the cell contents rather than the underlying stored values. For instance, if the same date is entered in different cells, but with different date formats, Excel considers them as unique values.

Before removing duplicate values, it is advisable to filter or apply conditional formatting to identify and confirm the unique values. This step ensures that you achieve the expected results and avoid unintended consequences.

Filter for unique values

To filter for unique values in Excel, follow these steps:

Select the range of cells you want to filter or ensure the active cell is within the table.
Go to the "Data" tab in the Excel ribbon and click on "Advanced" in the "Sort & Filter" group.
In the Advanced Filter popup box, you have two options:
To filter the range of cells or tables in place, click on "Filter the list, in-place."
To copy the filtered results to another location, click on "Copy to another location."
If you copy the results, enter the cell reference in the "Copy to" box to specify where the unique values will be copied. Alternatively, you can temporarily hide the popup window by clicking on the "Collapse Dialog Button" image, select a cell on the worksheet, and then click on the "Expand Button" image.
Check the "Unique records only" option to filter only the unique values.
Click "OK" to apply the filter.

Excel will now display the unique values in place or copy them to the specified location, depending on your selection.

Challenges Removing Duplicates from Outlined or Subtotaled Data in Excel

Removing duplicates from outlined or subtotaled data in Excel can be problematic. Excel does not allow you to directly remove duplicate values from data that is outlined or contains subtotals. In order to remove duplicates, you need first to remove both the outline and the subtotals.

When outlined, data is organized into groups with collapsed and expanded sections. Similarly, subtotals are calculated values within a dataset based on specified criteria. These features provide a structured view of the data but can complicate the process of removing duplicates.

To remove duplicates from outlined or subtotaled data, you must first remove the outline and the subtotals. This involves ungrouping the outlined sections and removing the calculated subtotals. Once the outline and subtotals are removed, you can proceed with the standard methods of removing duplicates in Excel, such as using the "Remove Duplicates" feature or applying filters for unique values.

It is important to note that removing the outline and subtotals will change the structure and organization of your data. If preserving the outline and subtotals is necessary, consider alternative approaches for handling duplicates, such as filtering for unique values within each group or subtotal category.

FAQs

What is the shortcut to remove duplicates from a list in Excel?

There is no specific shortcut to remove duplicates in Excel, but you can use the "Remove Duplicates" feature under the "Data" tab.

How do I remove duplicates in Excel without deleting cells?

To remove duplicates without deleting cells, you can use the "Filter" feature to display unique values and copy them to a new location.

How do you select duplicates in Excel?

To select duplicates in Excel, you can use conditional formatting to highlight the duplicate values.

Is there a fast way to remove duplicates in Excel?

Yes, Excel's "Remove Duplicates" feature allows you to quickly eliminate duplicate values from a list.

What is the fastest way to find duplicates in Excel?

Conditional formatting can be used to quickly identify duplicates in Excel by highlighting them in the worksheet.

Final Thoughts

Removing duplicates in Excel is a crucial step in data cleaning and analysis. You can enhance data accuracy and streamline your workflows by eliminating redundant entries.

Excel provides several methods to remove duplicates, such as using the "Remove Duplicates" feature or applying filters for unique values. Selecting the most suitable approach based on your specific needs and dataset is important.

Before removing duplicates, consider creating a backup of your original data to prevent accidental loss. Regularly checking for and removing duplicates in Excel will help maintain data integrity and improve the efficiency of your data-driven tasks.

One more thing

If you have a second, please share this article on your socials; someone else may benefit too.

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog post right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one.

» How to Remove Password from Excel: A Step-by-Step Guide
» How To Remove Bing “Discover” Button in Microsoft Edge
» How to remove whitespace and Empty lines in Excel with Regex

Find anything you need

How to Remove Duplicates in Excel

Table of Contents