How to find duplicates in Excel
Microsoft Excel

HOW TO FIND DUPLICATES IN EXCEL

If you’re dealing with large spreadsheets, manually searching for duplicates is near on impossible. Not only is it too time consuming, it’s tedious and easy to miss something. Luckily Excel has a function that does this task for you, fast and accurately. In this article you will learn:

  • How to find duplicates in Excel
  • How to highlight duplicates after the first instance
  • How to filter and sort duplicates
  • How to highlight unique values
  • How to remove duplicates

Duplicates in Excel

Having duplicates in your spreadsheets is a common occurrence, especially when extracting raw data. You can use Excel’s conditional formatting to help you identify and filter this. By familiarising with this tool, you’ll save yourself time.

Video breakdown: Find Duplicates 0.13, Duplicates After First Instance 0.40, Unique Values 1.13, Sort 1.34, Remove 2.00.

How to Find Duplicates in Excel

1. Select the range of cells you want to search

2. Styles group → Conditional Formatting

3. Highlight Cell Rules → Duplicate Values

conditional formatting - duplicate value

4. Select a formatting style/colour → OK

duplicate value colour selection

5. Excel will highlight all the cells that are duplicates (including the first instance).

duplicates highlighted result

Highlight Duplicates After the First Instance

Sometimes you might want to keep the first instance blank and highlight any duplicates after that. To do this, you need to create a formula in conditional formatting:

=COUNTIF(first cell of range fixed: first cell non-fixed, first cell non-fixed) greater than 1.

For example, my table begins in cell A1. So the formula will look like this: =COUNTIF($A$1:A1, A1)>1

  1. Select the range of cells you want to check
  2. Styles group → Conditional Formatting → New Rule
  3. Select ‘Use a formula to determine which cells to format’
  4. Type in the formula =COUNTIF($A$1:A1,A1)>1
  5. Go to Format → Fill → choose your colours and font → OK
  6. It will leave the first instance blank but highlight other duplicates.

new formatting rule

Keyboard shortcut: Alt+O then D for conditional formatting manager.

Too strapped for time? Subscribe for video tutorials

How to Find Unique Values

You can also highlight the unique cells rather than the duplicates.

1. Select the range of cells you want to search

2. Styles group → Conditional Formatting

3. Highlight Cell Rules → Duplicate Values

4. From the drop-down list, change to ‘Unique’

unique value

5. Select a formatting style/colour and click OK

6. The cells that aren’t duplicates will be highlighted.

unique values result

How to Sort Duplicated Cells

You may want to sort your highlighted cells. E.g. shift them all to the top and delete in bulk.

  1. Select your cells
  2. Go to Data tab → Sort
  3. Select column, cell colour and order (on top)
  4. All highlighted duplicates will be moved to the top of the spreadsheet.

sort duplicates data

How to Remove Duplicates

  1. Click on a sell within the range you want to search
  2. Go to the Data tab → Data Tools → Remove Duplicates
  3. In the pop-up box, keep all boxes are ticked and press OK.

remove duplicates

You can narrow this down further to specific columns. To do this, untick the columns you don’t need in the pop-up box.

E.g. if you want to remove people from the same town, untick all except the ‘City’ column. Excel will keep the first entry for that city and remove the rest.

remove duplicates unselect all

Looking for More?

These articles cover more advanced training with duplicates:

How to highlight duplicate cells and rows in Excel

How to use macro examples to delete duplicate items in a list in Excel


please like, comment, share and follow

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s