redo-remove-duplicates-excel
Image: Viktor Pazemin/Adobe Stock

In the duplicate world, definition means everything. That's considering a duplicate is subjective to the context of its related data. Duplicates can occur within a single cavalcade, across multiple columns or complete records. There's no one characteristic or technique that will find duplicates in every case. In this article, I'll show you how to find duplicates in Microsoft Excel.

SEE: Windows, Linux, and Mac commands anybody needs to know (free PDF) (TechRepublic)

I'k using Microsoft 365 on a Windows 64 scrap-system, just yous can employ earlier versions. For your convenience, you tin download the demonstration .xlsx and .xls files. Excel for the web doesn't support avant-garde filters and limits formatting, but you can apply conditional formatting rules.

How to filter for indistinguishable records in Excel

Perhaps the easiest mode to discover duplicates is to use Excel's advanced filter. It'southward flexible and tin can hands find duplicate rows. What you practise with the upshot is up to you. For instance, we'll use an advanced filter to copy the resulting records — sans duplicates to another location. That way you all the same accept the original data and a dissever set up of unique records.

In this situation, the word find is a bit misleading. This feature won't find the duplicates: It will filter them from the results, giving you a unique set of records.

Let's await at a sheet that has 2 records that are duplicated in a Table object (Figure A). I recommend using Tables, only this feature volition piece of work with an ordinary data range. Fifty-fifty in a small canvass, finding duplicates visually is a bit of a task, and yous're apt to fail. To temporarily remove duplicates from the data set, utilise Excel'due south advanced filter feature as follows:

  1. Select any cell inside the data set.
  2. Click the Data tab and then click Advanced Filter in the Sort & Filter grouping.
  3. Select Copy To Another Location in the Action section.
  4. Cheque the List Range to make sure Excel correctly references the original data.
  5. Enter a copy range in the Re-create To control. I chose H2.
  6. Check Unique Records Just (Figure B) and click OK.

Figure A

Epitome: Susan Harkins/TechRepublic. These Microsoft Excel records take ii duplicates in the Table.

Figure B

ExcelDuplicateRedo_B
Image: Susan Harkins/TechRepublic. Be sure to select the Check Unique Records Just option.

Figure C

ExcelDuplicateRedo_C
Paradigm: Susan Harkins/TechRepublic. The filtered list is brusque two records because this Excel feature removed duplicates.

Excel will copy a filtered list of unique records (Figure C) to the range you lot specified in Step five. At this indicate, yous tin can supervene upon the original data with the filtered listing if y'all desire to remove duplicates. As a rule, I don't recommend deleting data, even if you think you'll never refer to information technology over again, but that's up to y'all.

One thing you might non notice right away is that the Committee values in the filtered set are literal values. In the original information, that cavalcade contains an expression. Be on the lookout for these types of issues — if you plan on using the filtered gear up going forwards, you must supersede the values with the expression and then new records volition correctly calculate the commissions.

How to format indistinguishable values in Excel

Finding duplicates in a single column or across multiple columns is a bit more difficult than filtering for an unabridged tape. Using Excel'southward provisional formatting to highlight duplicates in a single column is one manner to find them quickly, although there'south less motive to delete duplicates in this situation. Permit's format duplicate commission values as follows:

  1. Select cell F3:F13.
  2. On the Home tab, click Provisional Formatting in the Styles group.
  3. Choose New Dominion in the dropdown.
  4. In the top pane, select the Use A Formula to Make up one's mind Which Cells to Format choice.
  5. In the lower pane, enter =COUNTIF(F:F,F3)>ane. (The period at the terminate is grammatical and not part of the Excel formula.)
  6. Click the Format push button, click the Font tab, choose Red, and click OK (Figure D).
  7. Click OK to return to the worksheet.

Figure D

ExcelDuplicateRedo_D
Image: Susan Harkins/TechRepublic. Enter the Excel formula and choose a format.

Figure E

ExcelDuplicateRedo_E
Image: Susan Harkins/TechRepublic. The conditional formatting dominion highlights duplicate commissions.

The conditional format will highlight any value in cavalcade F that'south repeated (Effigy E). The Excel function, COUNTIF() returns a conditional count. In this case, information technology compares the current commission to all the other committee values and returns Truthful if at that place'due south more one. If you want Excel to highlight simply the copies, leaving the first occurrence of the value unaltered, enter the Excel formula =COUNTIF($F$3:$F3, F3)>1 in Step v.

This conditional dominion works great for a unmarried column. How tin can nosotros observe duplicate values across multiple columns? For this task, we'll use 2 Excel helper formulas: One to concatenate the columns yous're comparing; a 2d to count the duplicates. Allow'due south suppose yous want to detect duplicates for the name and commission. To begin, enter the offset expressions into H3 and copy to the remaining cells:

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

The structured referencing is the upshot of using a Table object to store the data. If you're using a data range, enter =D3&F3.

Adjacent, in cell I3 enter the following Excel formula and copy information technology to accommodate the remaining list:

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

There are now two helper columns in place (Figure F). The Excel IF() office returns "Duplicate," when the Excel function, COUNTIF() finds more than one occurrence of the concatenated values in cavalcade H.

Figure F

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

Yous could stop here or apply a new provisional format based on the Excel formula in column I to highlight duplicates as follows:

  1. Select cell B3:F13.
  2. On the Habitation tab, click Conditional Formatting in the Styles grouping.
  3. Choose New Dominion in the dropdown.
  4. In the peak pane, select the Employ A Formula To Determine Which Cells To Format option.
  5. In the lower pane, enter =$I3="Duplicate".
  6. Click the Format button, click the Font tab, cull Ruby-red, and click OK (Figure G)
  7. Click OK to return to the worksheet.

Figure Grand

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

The conditional dominion highlights the starting time occurrence of a indistinguishable in both the Name and Commission columns (Figure H). Because the dominion formats the unabridged record, users might assume that the unabridged tape has a duplicate, and so that would crave a scrap of training. In the second helper formula, yous could display DuplicateNameCommission instead of merely Indistinguishable, but that seems a bit over the top.

Run across: Why Microsoft Lists is the new Excel (TechRepublic)

Figure H

ExcelDuplicateRedo_H
Epitome: Susan Harkins/TechRepublic. Ii records take duplicate values in both the Personnel and Committee columns.

None of these techniques works with all types of duplicate data. Each state of affairs, whether you're comparing an entire tape, a single cavalcade or multiple columns volition dictate which method you choose. In that location are other ways to find duplicates, but these methods are quick and easy.

Editor'due south note: This commodity has been updated.