discover duplicates in Excel

Picture: Viktor Pazemin/Adobe Inventory

Within the duplicate world, definition means every thing. That’s as a result of a replica is subjective to the context of its associated knowledge. Duplicates can happen inside a single column, throughout a number of columns or full information. There’s nobody function or approach that can discover duplicates in each case. On this article, I’ll present you the best way to discover duplicates in Excel.

SEE: Home windows, Linux, and Mac instructions everybody must know (free PDF) (TechRepublic)

I’m utilizing Microsoft 365 on a Home windows 64 bit-system, however you need to use earlier variations. On your comfort, you possibly can obtain the demonstration .xlsx and .xls information. Excel for the online doesn’t assist superior filters and limits formatting, however you possibly can apply conditional formatting guidelines.

filter for duplicate information in Excel

Maybe the best technique to discover duplicates is to make use of Excel’s superior filter. It’s versatile and might simply discover duplicate rows. What you do with the result’s as much as you. For instance, we’ll use a complicated filter to repeat the ensuing information — sans duplicates to a different location. That method you continue to have the unique knowledge and a separate set of distinctive information.

On this scenario, the phrase discover is a bit deceptive. This function gained’t discover the duplicates: It is going to filter them from the outcomes, supplying you with a singular set of information.

Let’s take a look at a sheet that has two information which can be duplicated in a Desk object (Determine A). I like to recommend utilizing Tables, however this function will work with an atypical knowledge vary. Even in a small sheet, discovering duplicates visually is a little bit of a activity, and also you’re apt to fail. To briefly take away duplicates from the info set, use Excel’s superior filter function as follows:

  1. Choose any cell inside the info set.
  2. Click on the Knowledge tab after which click on Superior Filter within the Kind & Filter group.
  3. Choose Copy To One other Location within the Motion part.
  4. Test the Record Vary to ensure Excel appropriately references the unique knowledge.
  5. Enter a duplicate vary within the Copy To manage. I selected H2.
  6. Test Distinctive Data Solely (Determine B) and click on OK.

Determine A

Picture: Susan Harkins/TechRepublic. These Microsoft Excel information have two duplicates within the Desk.

Determine B

ExcelDuplicateRedo_B
Picture: Susan Harkins/TechRepublic. You should definitely choose the Test Distinctive Data Solely possibility.

Determine C

ExcelDuplicateRedo_C
Picture: Susan Harkins/TechRepublic. The filtered checklist is brief two information as a result of this Excel function eliminated duplicates.

Excel will copy a filtered checklist of distinctive information (Determine C) to the vary you laid out in Step 5. At this level, you possibly can substitute the unique knowledge with the filtered checklist if you wish to take away duplicates. As a rule, I don’t suggest deleting knowledge, even if you happen to assume you’ll by no means discuss with it once more, however that’s as much as you.

One factor you may not discover straight away is that the Fee values within the filtered set are literal values. Within the authentic knowledge, that column comprises an expression. Be looking out for some of these points — if you happen to plan on utilizing the filtered set going ahead, you need to substitute the values with the expression so new information will appropriately calculate the commissions.

format duplicate values in Excel

Discovering duplicates in a single column or throughout a number of columns is a little more tough than filtering for a whole document. Utilizing Excel’s conditional formatting to spotlight duplicates in a single column is one technique to discover them shortly, though there’s much less motive to delete duplicates on this scenario. Let’s format duplicate fee values as follows:

  1. Choose cell F3:F13.
  2. On the Residence tab, click on Conditional Formatting within the Kinds group.
  3. Select New Rule within the dropdown.
  4. Within the high pane, choose the Use A Method to Decide Which Cells to Format possibility.
  5. Within the decrease pane, enter =COUNTIF(F:F,F3)>1. (The interval on the finish is grammatical and never a part of the Excel system.)
  6. Click on the Format button, click on the Font tab, select Purple, and click on OK (Determine D).
  7. Click on OK to return to the worksheet.

Determine D

ExcelDuplicateRedo_D
Picture: Susan Harkins/TechRepublic. Enter the Excel system and select a format.

Determine E

ExcelDuplicateRedo_E
Picture: Susan Harkins/TechRepublic. The conditional formatting rule highlights duplicate commissions.

The conditional format will spotlight any worth in column F that’s repeated (Determine E). The Excel perform, COUNTIF() returns a conditional rely. On this case, it compares the present fee to all the opposite fee values and returns True if there’s multiple. If you would like Excel to spotlight solely the copies, leaving the primary prevalence of the worth unaltered, enter the Excel system =COUNTIF($F$3:$F3, F3)>1 in Step 5.

This conditional rule works nice for a single column. How can we discover duplicate values throughout a number of columns? For this activity, we’ll use two Excel helper formulation: One to concatenate the columns you’re evaluating; a second to rely the duplicates. Let’s suppose you need to discover duplicates for the identify and fee. To start, enter the primary expressions into H3 and duplicate to the remaining cells:

=Commissions8[@Personnel]&Commissions8[@Commission]

The structured referencing is the results of utilizing a Desk object to retailer the info. If you happen to’re utilizing a knowledge vary, enter =D3&F3.

Subsequent, in cell I3 enter the next Excel system and duplicate it to accommodate the remaining checklist:

=IF(COUNTIF(H3:H13,H3)>1,"Duplicate","")

There are actually two helper columns in place (Determine F). The Excel IF() perform returns “Duplicate,” when the Excel perform, COUNTIF() finds multiple prevalence of the concatenated values in column H.

Determine F

ExcelDuplicateRedo_F
Picture: Susan Harkins/TechRepublic. We’ll base a conditional formatting rule on column H.

You can cease right here or apply a brand new conditional format primarily based on the Excel system in column I to spotlight duplicates as follows:

  1. Choose cell B3:F13.
  2. On the Residence tab, click on Conditional Formatting within the Kinds group.
  3. Select New Rule within the dropdown.
  4. Within the high pane, choose the Use A Method To Decide Which Cells To Format possibility.
  5. Within the decrease pane, enter =$I3="Duplicate".
  6. Click on the Format button, click on the Font tab, select Purple, and click on OK (Determine G)
  7. Click on OK to return to the worksheet.

Determine G

ExcelDuplicateRedo_G
Picture: Susan Harkins/TechRepublic. Enter the rule and format.

The conditional rule highlights the primary prevalence of a replica in each the Title and Fee columns (Determine H). As a result of the rule codecs your entire document, customers would possibly assume that your entire document has a replica, so that may require a bit of coaching. Within the second helper system, you may show DuplicateNameCommission as a substitute of solely Duplicate, however that appears a bit excessive.

Determine H

ExcelDuplicateRedo_H
Picture: Susan Harkins/TechRepublic. Two information have duplicate values in each the Personnel and Fee columns.

None of those methods works with all sorts of duplicate knowledge. Every scenario, whether or not you’re evaluating a whole document, a single column or a number of columns will dictate which methodology you select. There are different methods to search out duplicates, however these strategies are fast and simple.

Editor’s word: This text has been up to date.