QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Delete Duplicates in Excel 2016?

By Neha T Leave a Comment

It happens that many times we got stuck in an Excel workbook which contains duplicates cells. It is difficult to understand the data with duplicates. Well, we can delete duplicates in Excel. Let’s see how?

In this section, we will first learn to identify the duplicates in Excel and then we will see how to delete the duplicates? We will also discuss the scenario when there are multiple rows carry the duplicate data in several columns.

The topics to be discussed:

  1. Identify Duplicates
  2. Formula to find Duplicates
  3. Delete/Remove Duplicates
  4. Remove duplicates rows based on one or more columns

Identify Duplicates

To identify the duplicates, we have an inbuilt function in Excel and for that first select the range of the cells where you want to identify or find the duplicate values. Now, go to Styles group of Home tab and click on Conditional Formatting>Highlight Cells Rules>Duplicate Values.

Identify Duplicates

A Duplicate Values dialog box will pop out. Here, you have two drop-down boxes, in first you have to select Duplicate among the option Unique and Duplicate. In the second drop-down box select the colour format in which you have to highlight the duplicates and click OK.

See in the picture below, the duplicates are highlighted in red colour.

highlight duplicates

Formula to Find or Identify Duplicates

As such, there is not a unique or single formula to identify the duplicates in Excel. We can construct several formulas to find the duplicates using the combination of different functions available in Excel.

Now, we will discuss the construction of one such formula which will let us identify the duplicates in Excel.

=COUNTIF($A$2:$A2, $A2)>1

Here, the COUNTIF function will return the array of TRUE and FALSE. At the first occurrence of the cell content, COUNTIF counts FALSE and on further occurrences of the same content, it counts TRUE.

Formula for finding duplicates

Now, to put it in a proper format let us modify the formula in a way:

=IF(COUNTIF($A$2:$A2, $A2)>1,“Duplicate”,“-”)

Here, the IF function analyse the array returned by the COUNTIF function, for TRUE it returns Duplicate and for FALSE it returns “-”.

Formula for finding duplicates 1

Delete/Remove Duplicates in Excel

To remove the duplicates from the range of cells first select the range having the duplicates. Under Data tab in the Data Tools group click on Remove Duplicates.

Delete Duplicates

Remove Duplicates dialog box will pop up which will show the columns which should be considered while deleting the duplicates.

Delete Duplicates 1

As in our example, there is only one column it should be selected as the duplicate rows will be removed based on this column. The result is:

Delete Duplicates 2

Note: Before Removing duplicates, you must save the original data somewhere in the excel so that there is no data loss while doing this.

Remove Duplicate Rows Based on One or More Columns

Suppose, there are multiple columns in the workbook, where the several rows have duplicated data. For example, observe the data below:

Data with rows having duplicates in several columns

Now, see the duplicates in the red square carry the same data for Sales. Like Joseph has value 100 in Sales column 2 times.

But, the duplicates in the blue square have different values for the Sales column. Now, let us see how we can remove the duplicate rows based on several columns.

Go to the Data tab and in Data Tools group click on Remove Duplicates.

Delete Duplicates

It will pop out Remove Duplicates dialog box. Here, we will remove duplicate rows based on both the column i.e. Staff and Sales.

Data with rows having duplicates in several columns 1

So we will select both the columns Staff and Sales. The result would be:

REmove duplicate rows for multiple columns

Note: This would not remove the duplicated row’s first occurrence, it still retains in the result. But it removes all of its further occurrences.

So this is all about identifying, deleting duplicates in the range of cells. We have also achieved this using the formula. Further, you can explore more to find other methods for doing the same.

You Might Also Like:

compare two columns in Excel Feature imageHow to Compare Two Columns to Find Matches & Differences in Excel 2016? how-to-freeze-row-and-column-in-ms-excel-in-2016-featured-imageHow to Freeze Rows and Columns in MS Excel 2016? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? delete blank row feature imageHow to Delete Blank Rows in Excel 2016? Highlight Duplicates in Excel-feature imageHow to Highlight Duplicates in Excel? how-to-delete-macro-in-ms-excel-2016-featured-imageHow to Delete Macro in MS Excel 2016? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016? feature imageHow to COUNTIF in MS Excel 2016?

Leave a Reply Cancel reply

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

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to change the default file format in Microsoft Word
  • How to Install Java in Firefox
  • How to Add a Widget in Wordpress
  • How to update Mozilla Firefox to Latest Version?
  • How to enable Google Calendar in gmail

Copyright © 2023 · QueHow · Contact Us