How To Remove Excel Duplicate: Deleting Duplicates in Excel
Updated on Nov 14, 2024 | 8 min read | 1.9k views
Share:
For working professionals
For fresh graduates
More
Updated on Nov 14, 2024 | 8 min read | 1.9k views
Share:
Table of Contents
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!
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:
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.
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.
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.
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:
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.
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.
To highlight duplicate cells in Excel, follow these steps:
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.
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.
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.
Get Free Consultation
By submitting, I accept the T&C and
Privacy Policy
Start Your Career in Data Science Today
Top Resources