Is there a formula in Excel to remove duplicates?

All CoursesLog inData Science & Business AnalyticsData Science & Business AnalyticsAI & Machine LearningProject ManagementCyber SecurityCloud ComputingDevOpsBusiness an

Is there a formula in Excel to remove duplicates?
Simplilearn - Online Certification Training Course Provider

All CoursesLog inData Science & Business AnalyticsData Science & Business AnalyticsAI & Machine LearningProject ManagementCyber SecurityCloud ComputingDevOpsBusiness and LeadershipQuality ManagementSoftware DevelopmentAgile and ScrumIT Service and ArchitectureDigital MarketingBig DataCareer Fast-trackEnterpriseOther SegmentsArticlesEbooksVideo TutorialsLive WebinarsOn-demand WebinarsFree Practice TestsHomeResourcesData Science & Business AnalyticsA Step-by-Step Guide on How to Remove Duplicates in Excel

Top 33 IBM DataStage Interview Questions and AnswersArticle

Top Guesstimate Questions to Nail Your Next Interview | Guesstimate Questions and AnswersArticle

Introduction to Data Science: A Beginner's GuideEbook

Excel Vs. Google Sheets: What You Need to KnowArticle

10 Best Data Visualization Examples and How They Work (2022 Edition)Article

Top 80 Data Science Interview Questions and Answers for 2022Video Tutorial

Top 25 Excel Formulas You Should Know [Updated]Article

Data Science vs. Data Analytics vs. Machine Learning: Expert TalkArticle

Data Skills for Non-Data Scientists: Learning to Use Data in Your Everyday WorkWebinar

50 Excel Shortcuts That You Should Know in [2021]Article

A Step-by-Step Guide on How to Remove Duplicates in ExcelBy SimplilearnLast updated on May 17, 202248888

How to Remove Duplicates in Excel: A Step-By-Step Guide

Table of ContentsView More

Microsoft Excel is a widely used application, but it can be a little confusing when it comes to removing and eliminating duplicate data. Removing duplicates in Excel is a prevalent task for people working on huge datasets. When you combine different tables, or when many people have access to the same document, you might end up having repeated entries in your spreadsheet. Thus, making the data redundant. The larger the dataset, the higher are the chances of encountering duplicate records. It can be problematic if they are not identified and handled correctly.

We will illustrate how to remove duplicates in Excel using a Sports dataset. This dataset contains information about Olympic medalists for the year 2012.

dataset-RemoveDuplicatesInExcel

Post Graduate Program in Business AnalysisIn partnership with Purdue UniversityVIEW COURSE

Post Graduate Program in Business Analysis

Using Remove Duplicates Option on Data Tab

Excel has a built-in tool that helps delete repeated entries in your dataset. Lets have a look at the steps to be followed to remove duplicates in Excel.

  • First, click on any cell or a specific range in the dataset from which you want to remove duplicates. If you click on a single cell, Excel automatically determines the range for you in the next step.
remove_duplicates_command_1-RemoveDuplicatesInExcel
  • Next, locate the Remove Duplicates option and select it.

DATA tab  Data Tools section  Remove Duplicates

remove_duplicates_command_2-RemoveDuplicatesInExcel.
  • A dialog box appears, as shown below. You can select the columns you want to compare and check for duplicate data.

In case your data consists of column headers, select the My data has headers option, and then click on OK.

On checking the header option, the first row will not be considered for removing duplicate values.

remove_duplicates_command_3
  • Excel will now delete the duplicate rows and display a dialog box. The dialog box shows a summary of how many duplicate values are found and removed along with the count of unique values.
remove_duplicates_command_4
  • As you can notice, the duplicate records are removed.
remove_duplicates_command_5

Let's move forward and understand how to remove duplicates in Excel using the Advanced Filter option.

Using the Advanced Filter Option

The Advanced Filter option in Excel helps you filter duplicate values and copy the unique values to a different location. Look at the following steps to find out how the Advanced Filter option works.

  • First, click on a cell or range in the dataset from which you want to remove duplicates. If you click on a single cell, Excel automatically determines the range when you click on Advanced Filter.
advanced_filter_1-RemoveDuplicatesInExcel

Free Course: Introduction to MS ExcelMaster the Fundamentals of MS ExcelEnroll Now

Free Course: Introduction to MS Excel
  • Locate the Advanced Filter option.

DATA tab  Sort & Filter section  Advanced and click on it.

advanced_filter_2
  • You will see a dialog box. It consists of a list of options for advanced filtering.
  • Select the Copy to another location option to copy the unique values to a different location.
  • Check the range of your records in the List Range field and make sure it is the range that you have specified.
  • In the Copy to: field, enter the range where the resultant unique values must be copied.
  • Check the Unique records only box. This step is the most crucial.
  • Click on OK.
advanced_filter_3
  • The unique values will be copied to cell G1.
advanced_filter_4

These were the in-built functionalities in Excel that help us in removing duplicates. Now, lets move forward and learn how we can create our own function to do the same.

Free Course: Business Analysis BasicsMaster the Fundamentals of Business AnalysisEnroll Now

Free Course: Business Analysis Basics

How to Use Formulas to Remove Duplicates in Excel?

We will be using a simple example containing the columns: type of sport, athlete name, and medal won to demonstrate this approach.

formula_method_1-RemoveDuplicatesInExcel

This method involves combining the columns using an Excel formula and finding out the count. We will then filter out the duplicate values (ones that have a count greater than 1).

  • Lets combine the columns A, B, and C by using the concatenation operator &. So, the Excel formula would be:

=A2&B2&C2

This formula is entered into the cell D2 and then copied down to all the rows.

method_2-RemoveDuplicatesInExcel
  • Now, we will need another column named Count to find out the duplicates in Column D. Hence, we use the COUNTIF function on cell E2. The formula will be:

=COUNTIF($D$2:D2,D2)

This formula helps count the number of occurrences of each value in column D.

formula_method_3

If the value of Count is 1, then it has only appeared once and is unique. If the value is 2 or more, then it is considered a duplicate value.

  • Now add a filter to the Count column by selecting the Filter option.

You will find it in the DATA tab  Sort & Filter section  Filter

Click on the filter at the top of Column E. Select 1  to keep only the unique values and remove the duplicates.

formula_method_6
  • On clicking OK, the duplicate values will be removed from the table. You can copy these resultant unique records and paste them elsewhere.
formula_method_5

Lets proceed by understanding the final approach to delete duplicates: Using Power Query.

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

Power Query in Excel lets you import data from various sources, clean and transform your data. This tool makes it effortless to remove duplicates in Excel.

  • Select a cell or range, locate Data Tab  Get & Transform Data section  From Table/Range and click on it.
power_query_1-RemoveDuplicatesInExcel
  • On clicking, you will see a dialog box to create a power query table. Make sure that the range of values is correctly specified. Click on OK.
power_query_2
  • The following Power Query editor window appears.

Business Analyst Master's ProgramGain expertise in Business analytics toolsExplore Program

Business Analyst Master's Program

Now you have two options. You can remove duplicates based on:

  • One or more columns
  • Entire table

To remove duplicates based on one or more columns, right-click on the specific column header. You can select more than one column using the CTRL button and remove the duplicates accordingly.

power_query_3.

To remove the duplicate records based on the entire table, click on the button present on the top left corner of the data preview. And then select the Remove Duplicates option.

This way, the data will be free from duplicate values.

power_query_4

On clicking the Close & Load option, the data will be loaded onto your spreadsheet.

Get prepared for the role of a Business Analyst with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

In this write-up, we learned various approaches to delete duplicate records. Whether youre interested in learning the basics of Excel, or want to develop more advanced Microsoft Excel skills, Simplilearn has a Business Analytics Certification Course with Excel for you. It is a very tedious task to remove duplicates in Excel, and we hope this article has helped you learn how to eliminate duplicates efficiently.

Please feel free to post any questions in the comments section of How to Remove Duplicates in Excel?: A Step-By-Step Guide article. Our experts will get back to you on the same, at the earliest.

About the Author

Simplilearn

Simplilearn

Simplilearn is one of the worlds leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.View More

Business Analytics with Excel

Business Analytics with Excel36901 LearnersLifetime Access*

Post Graduate Program in Business Analysis

Post Graduate Program in Business Analysis7188 LearnersLifetime Access*

Business Analyst

Business Analyst18559 LearnersLifetime Access*

*Lifetime access to high-quality, self-paced e-learning content.Explore Category

Deleting Duplicate Rows in SQL

Next Article

Deleting Duplicate Rows in SQLBy Simplilearn29339May 23, 2022

Data Science Salary Guide 2021

Data Science Salary Guide 2021Ebook

Python Remove Duplicates From a List

Python Remove Duplicates From a ListArticle

Your One-Stop Solution to Know About Excel Worksheets

Your One-Stop Solution to Know About Excel WorksheetsVideo Tutorial

Managing Data

Managing DataEbook

A Guide to the Top 50 Excel Interview Questions

A Guide to the Top 50 Excel Interview QuestionsArticle

Your One-Stop Solution For Excel UserForms

Your One-Stop Solution For Excel UserFormsVideo TutorialprevNext

© 2009 -2022- Simplilearn Solutions

Follow us!Refer and Earn

CompanyAbout usCareers In the media Alumni speakContact us

Work with usBecome an instructorBlog as guest

DiscoverSkillupResourcesRSS feedSimplilearn Coupons and Discount OffersCity Sitemap

For BusinessesCorporate trainingPartnersDigital Transformation

Learn On the Go!Get the Android AppGet the iOS App

Trending Post Graduate ProgramsProject Management Certification Course | Cyber Security Certification Course | PG in Data Science Program | Data Analytics Bootcamp Program | Business Analysis Certification Course | Digital Marketing Certification Program | Lean Six Sigma Certification Course | Cloud Computing Certification Course | Data Engineering Bootcamp | AI and Machine Learning Course | Full Stack Web Development Course

Trending Master ProgramsPMP Plus Certification Training Course | Big Data Engineering Courses | Data Science Certification Course | Data Analyst Certification Course | Artificial Intelligence Course | Cloud Architect Certification Training Course | DevOps Engineer Certification Training Course | Advanced Digital Marketing Course | Cyber Security Expert Course | MEAN Stack Developer Course

Trending CoursesPMP Certification Training Course | Big Data Hadoop Certification Training Course | Data Science with Python Certification Course | Machine Learning Certification Course | AWS Solutions Architect Certification Training Course | CISSP Certification Training | Certified ScrumMaster (CSM) Certification Training | ITIL 4 Foundation Certification Training Course | Java Certification Course | Python Certification Training Course

Trending ResourcesPython Tutorial | JavaScript Tutorial | Java Tutorial | Angular Tutorial | Node.js Tutorial | Docker Tutorial | Git Tutorial | Kubernetes Tutorial | Power BI Tutorial | CSS Tutorial

  • Terms of Use
  • Privacy Policy
  • Refund Policy
  • Reschedule Policy
  • © 2009-2022 - Simplilearn Solutions. All Rights Reserved. The certification names are the trademarks of their respective owners.smpl_2022-06-05
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Video liên quan