• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Find and Remove Duplicates

You are here: Home / Excel / Excel Find and Remove Duplicates
6 WAYS TO FIND OR REMOVE DUPLICATES
August 15, 2017 by Mynda Treacy

Sometimes we want to find and remove duplicates in Excel, other times we just want to identify or highlight duplicates. There are loads of ways to tackle this in Excel, so let’s look at the options.

Download the Workbook

The workbook contains screenshots and notes so you can use it as a reference guide later, or share it with co-workers.

Enter your email address below to download the workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Highlight Duplicates with Conditional Formatting

Conditional Formatting can quickly highlight duplicates in a column. Simply select the column of cells containing the suspected duplicates > Home tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values:

highlight duplicates with conditional formatting

Tip: You can change the format by clicking the drop down for ‘Values with’ (see image above).

Once the formatting is applied you can use filters (Data tab > Filters), based on the cell fill color or font color to display or hide the duplicate values:

Excel Find and Remove Duplicates

Pros: Great for visually highlighting duplicates in a column while retaining them in the dataset. You can use filters to hide duplicates or focus on them.

Cons: Duplicates remain in the dataset, and that may be exactly what you want, but if you just want to get rid of them, then keep reading.

This method also doesn’t highlight the row and only identifies duplicates in a single column.

Highlight Duplicate Rows with a Conditional Formatting Formula

Let’s say you want to highlight rows that contain duplicates across a row. For example, rows 9 and 11 have the same Date and ID:

duplicate rows

For this we need to apply the conditional formatting using a formula:

apply conditional formatting

Read this article for an in depth understanding of how to write formulas for conditional formatting.

Pros: Highlights the whole row and takes into consideration more than one column. Filters can be used to hide duplicates from view.

Cons: Formula can be difficult to remember. Duplicates remain in the dataset.

Identify Duplicates with a Formula

You can add a column to your data table to tag rows containing duplicates. The formula below is looking for duplicate rows, i.e. where both the Date and ID values are duplicated:

identify duplicates with a formula

The formula in cell C7:

=IF(COUNTIFS($A$7:$A$11,$A7,$B$7:$B$11,$B7)>1,"Duplicate","")

uses COUNTIFS to check both the Date and ID columns are the same, if the count is greater than 1, then ‘Duplicate’ is returned, otherwise the cell is left blank.

Tip: If you only want to check a single column, let’s say the ID column, then you could use the COUNTIF formula like so:

using COUNTIF to identify duplicates

Pros: Column containing duplicate tag can be used in PivotTables or other formulas to ignore or focus on duplicate rows.

Cons: Formula can be difficult to remember. Requires an extra column in your dataset. Could be cumbersome in large files.

Remove Duplicate Values

We’ve looked at highlighting or tagging cells or rows containing duplicates, but sometimes you want to remove duplicates so you have a unique list of values. There are a few ways to tackle this.

Let’s say we want to remove duplicate rows from the table below i.e. we want to retain row 7 with ‘Produce’ and ‘Richard’, but we want to remove one of the duplicate rows (9 or 11) containing ‘Produce’ and ‘Rachel’:

remove duplicate values

We can use the Remove Duplicates tool on the Data tab of the ribbon:

Remove Duplicates tool

By selecting both the Department and Name columns I’m telling Excel that I want it to find duplicates where the values in both columns are the same. Note that I also have the ‘My data has headers’ box checked so it ignores my headers.

And I’m left with a list of unique rows:

remove duplicate rows

Pros: Quick and easy to use.

Cons: Removal of duplicates is permanent. If your data gets updated then you need to run the Remove Duplicates process again.

Power Query Remove Duplicates

Power Query (available in Excel 2010 onwards), also has a Remove Duplicates tool.

Format your data in an Excel Table then load the data into Power Query:

Excel 2010 & 2013: Power Query tab > From Table:

load data in Power Query 2010 and 2013

Excel 2016: Data tab > Get & Transform group: From Table:

load data in Power Query 2016

This will load the data into Power Query and open the Power Query Editor window. In the Power Query Editor simply select the columns you want it to find duplicates for (hold Ctrl to select multiple columns, or Shift to select contiguous columns) > Home tab > Remove Rows > Remove Duplicates:

remove duplicates

Pros: The great thing about using Power Query is if your source data gets updated you can Refresh the query and it will remove duplicates again, with just the click of a button. Original data remains intact, plus you have a new view of the data that excludes the duplicates.

Cons: Requires a few more steps than the previous example. Retaining original data may make the file unnecessarily large. If so, the original data can be stored in a separate file.

*Versions of Excel supporting Power Query. Download Power Query here.

Remove Duplicates with Advanced Filter

Advanced Filter can extract a list of unique items from a column or columns. First select the data, then Data tab > Advanced:

remove duplicates with advanced filter

In the Advanced filter dialog box (image above) choose to copy the list to another location (4 & 5), and check the box for ‘Unique records only’. And voila, we now have two lists, the original, and the list excluding duplicates in columns E & F:

unique records only

Pros: Reasonably easy to use. Also has an option to just filter the list to hide duplicates. Can handle multiple columns of data.

Cons: No link is maintained between the original data and the filtered data. If the original data gets updated then the Advanced Filter must be run again.

Identify Duplicates with PivotTables

A PivotTable is an excellent way to quickly identify if you have any duplicates in a column.

Place the field you want to check for duplicates in both the Rows and Values areas, in my case it’s the Name field. The PivotTable gives you a list of unique names and the count:

identify duplicates with PivotTables

Tips: sort the PivotTable Count column in descending order to bring the duplicates to the top; right-click a cell in the values area > Sort > Sort Largest to Smallest:

Sort the PivotTable Count

Or filter the Count column to only show records greater than 1:

filter the Count column

Pros: Quick and easy to do and great for large datasets because you can sort the count in descending order to bring any duplicates to the top, or filter to only show duplicates. The PivotTable also provides the count of an item so you can see how many times it is duplicated.

Cons: Doesn’t remove duplicates, only highlights them.

So, there you have 6 ways to identify or remove duplicates. Depending on my needs I like to use Power Query to remove duplicates, Conditional Formatting to visually indicate duplicate records and PivotTables to identify if large datasets contain duplicates.

Update: If you have Office 365 you can use the new UNIQUE Function to extract a distinct or unique list.

Related Lessons

Tutorials:

  • Conditional Formatting
  • Conditional Formatting with Formulas
  • Advanced Filters
  • Advanced Filter Unique Records
  • Excel Tables
  • PivotTables

Courses:

  • Advanced Excel
  • Power Query
6 WAYS TO FIND OR REMOVE DUPLICATES

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:VBA Select CaseVBA SELECT CASE
Next Post:Excel Extract a Unique List

Reader Interactions

Comments

  1. Mehdi HAMMADI

    August 15, 2017 at 10:12 pm

    Once is not custom, I know all the methods presented but as always the way to showcase them is formidable.
    Thanks for sharing, this article is very interesting and very well presented.
    Regards
    Mehdi

    Reply
    • Mynda Treacy

      August 16, 2017 at 12:31 pm

      Hi Mehdi,

      Glad you found it interesting 🙂

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x