Ever wondered how to tackle the pesky issue of duplicate data in Microsoft Excel?

Well, you’re not alone! Excel has become a powerhouse tool, especially in Business Analysis, empowering users to handle vast amounts of information effortlessly. However, as the datasets grow, so does the likelihood of encountering duplicate entries. This can lead to redundancy, confusion, and inaccurate analyses. You need to know more Excel shortcuts to work efficiently and with ease

In this article, we’ll walk you through the step-by-step process of identifying and deleting duplicates in Excel, streamlining your data, and saving you precious time and effort. So, let’s get started on our journey to a cleaner and more efficient Excel experience!

Find Duplicates in Excel

Duplicate data can be both useful and burdensome, but it often hinders data understanding.

Before removal, reviewing and finding duplicates in Excel is better than deleting it immediately.

To identify duplicates in Excel, use conditional formatting as follows:

• Select the data you want to check for duplicates.
• From the Home tab, go to Conditional Formatting > Highlight Cell Rules > Duplicate Values.
• In the Conditional Formatting window, choose a colour scheme to highlight duplicates (opt for high contrast colours like Light red fill for better readability).
• Click “Done” to apply the formatting.
• Review the highlighted duplicate data and decide whether to remove any redundant information. This process ensures a more informed decision regarding data cleanup.

How to Remove Duplicate Values?

Here’s a step-by-step guide on how to remove Excel duplicates:

Step 1: Open your Excel file and select the cell or cell comprising the dataset from which you wish to eliminate the duplicate details.

Step 2: Navigate to the DATA tab at the top of the Excel window.

Step 3: Look for the “Remove Duplicates” option in the Data Tools section and click on it.

Step 4: A dialogue box will open, showcasing your dataset’s detailed list of columns. Here, you can choose the columns in Excel where you wish to identify and remove duplicates. If your data has headers (column names), check the “My data has headers” option.

Step 5: After selecting the appropriate columns, click on the “OK” button to proceed.

Step 6: Excel will now analyse your data depending on the specifically selected columns and eliminate the duplicate rows. Once the process is complete, a dialogue box will pop up, summarising the number of duplicate values found and removed and the count of unique values.

Step 7: Congratulations! Your duplicate records have now been successfully removed, leaving you with a cleaner and more streamlined dataset.

Let’s now explore another method for deleting duplicates in Excel by utilizing the Advanced Filter option. 

Understand Filtering for Unique Values or Removing Duplicate Values in Excel

Before proceeding to remove duplicates, it is highly recommended to double-check your data. You can use filtering or conditional formatting to identify unique values and ensure you get the expected results before making any changes to your dataset. This cautious approach will help maintain data accuracy and prevent unintended data loss.

How to Filter Specific Unique Values in Excel

Here’s a detailed guide on how to filter unique values in Excel:

Step 1: Select the column or columns comprising the data you aim to filter for unique values in your Excel sheet.

Step 2: Visit the “Data” tab in the Excel ribbon, and select the “Filter” button present within the “Sort & Filter” section. This will update filter arrows to the column headers.

Step 3: Click on the filter arrow in the column header to open the filter options for that column.

Step 4: Take your cursor to the “Number Filters,” “Date Filters,” or “Text Filters” options depending on the type of data you are working with.

Step 5: Select “Does Not Equal” in the pop-out menu. If you don’t find this option, choose “Custom Filter”, located at the bottom part of the menu.

Step 6: A new dialogue box will appear. Ensure that the initial drop-down shows the message “does not equal,” then fill in the specific value you want to filter out in the box on the right.

Step 7: Click “OK” to apply the filter.

Step 8: Your Excel sheet will now display only the data containing the unique values based on your filter criteria. This means you’ll see data that does not match your specified value.

Step 9: When you’re done using the filter, click the “Filter” button again in the Excel ribbon to turn it off. The sheet will return to its normal view, showing all the data without filtering.

Using the Advanced Filter Option

The Advanced Filter option in Excel is a powerful tool that allows you to filter duplicate values and extract unique values to a different location. Here’s a step-by-step guide on how to use the Advanced Filter:

• Begin by selecting a cell or range within the dataset from which you want to remove duplicates. If you select a single cell, Excel will automatically determine the range when you access the Advanced Filter.
• Locate the Advanced Filter option in the DATA tab under the Sort & Filter section. Click on “Advanced” to open the dialogue box containing various options for advanced filtering.
• In the dialogue box, choose the “Copy to another location” option. This selection will enable you to copy the unique values to a different location.
• Verify that the “List Range” field contains the correct range for your records.
• In the “Copy to:” field, specify the range where you want the resultant unique values to be copied.
• Crucially, inspect the box with the label “Unique records only.” This step ensures that only the unique values will be copied to the new location.
• Click “OK” to apply the Advanced Filter.
• After executing the filter, you will find the unique values copied to the specified location, such as cell G1.

These built-in functionalities in Excel effectively remove duplicates and work with unique data. Now, let’s move on to explore how to use formulas to remove excel duplicates. 

Learn data science courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

How to Use Formulas to Delete Duplicates in Excel?

Removing duplicates in Excel can be easily accomplished using built-in options in the Excel ribbon. 

Simply go to the “Data” tab and select “Remove Duplicates” in the “Data Tools” group. This method works well for a one-time operation.

However, if you want a dynamic solution that automatically updates when you insert or delete values in the table, consider using the =UNIQUE() function. This formula takes a range of values and returns only the unique values, eliminating the need to redo the operation every time.

Another approach involves identifying duplicate values using the IF() and COUNTIF() functions. You can create a formula like =IF(COUNTIF($A$2:$A$7, A2) > 1, “Duplicate”, “Unique”) to mark duplicates as “Duplicate” and unique values as “Unique”. 

After finding the duplicate values, you can use the Home tab’s Filter option to segregate, delete, or save them separately.

Check out our free datascience courses to get an edge over the competition.

Conditionally Format Unique or Duplicate Values

To highlight duplicate cells in Excel, follow these steps:

• Select the data you wish to check for duplicates: a single column, a row, or a range of cells.
• Visit the “Home” section, and in the “Styles” group, select the “Conditional Formatting” option. After that, select “Highlight Cells Rules” and click on “Duplicate Values.”
• The “Duplicate Values” dialogue box will open, with the default format of Light Red Fill and Dark Red Text already selected. Click “OK” to apply this default format.
• Alternatively, choose other predefined formats from the dropdown list or click “Custom Format” to select your desired fill and font colours.
• If you want to highlight unique values instead, choose “Unique” from the left-hand box in the “Duplicate Values” dialogue box.
• The built-in rule can highlight duplicates in one column or across multiple columns.

Note that when using the built-in rule for multiple columns, Excel highlights all duplicate instances in the range without comparing values in those columns.

• To highlight duplicate rows or find matches and differences between two columns, you must create custom conditional formatting rules based on specific criteria.
• Remember that the built-in rule highlights all duplicate occurrences, including their first instances. You can create a conditional formatting rule based on a formula if you want to highlight duplicates except for the first occurrences.

Read our popular Data Science Articles

How to Use the Power Query Tool to Remove Duplicates in Excel?

Power Query is an advanced Excel tool for Extract, Transform, and Load (ETL) operations. With Power Query, you can import data from various sources and apply transformations, including data cleansing and reshaping.

Here’s a step-by-step guide on removing duplicates using Power Query:

Step 1: Create a table by selecting the rows you want to work with, then go to the “Insert” tab and choose “Table.” Alternatively, you can press “CTRL+T” to create a table and make sure to check the option “My table has headers.”

Step 2: Now, navigate to the “Data” tab and click on “Get & Transform Data,” then select “From Table/Range.”

Step 3: The Power Query Editor will open, allowing you to perform the necessary data transformations.

Step 4: In the Power Query Editor, go to the “Home” tab and click on “Remove Rows,” then choose “Remove Duplicates.”

Step 5: After removing duplicates, you’ll see a “Query Settings” message stating ‘Removed Duplicates.’

Step 6: Once you’ve confirmed the duplicates are removed, click “Home” again and select “Close & Load.”

Step 7: Power Query will load the cleaned data into a new sheet, with the duplicates successfully removed.

Following these steps, you can efficiently clean your data and remove duplicate records using Power Query in Excel. This helps ensure data accuracy and enhances your data analysis and reporting capabilities.

Top Data Science Skills to Learn

Conclusion

Microsoft Excel is an indispensable tool that offers a multitude of functionalities, making it highly useful for various sectors. Its ability to handle complex data, perform calculations, and visualise information efficiently benefits businesses, students, and professionals alike. 

To unlock the full potential of data management and analysis, upGrad presents you with the opportunity to pursue a Master of Science in Data Science from Liverpool John Moores University. This comprehensive program equips students with advanced skills and knowledge to excel in the dynamic field of data science opening doors to exciting career opportunities.  

FAQs