Remove Duplicates values in Excel

Remove Duplicates values in Excel
Last Updated on 12/05/2024
Reading time: 2 minutes

In Excel, you can remove duplicates in 2 different way

  1. By tool

    Remove duplicates from the ribbon Data

  2. By formula

    Excel 365 and Excel 2021 provide the UNIQUE function

List of values with duplicates

Remove Duplicates from the Ribbon

You can remove duplicate values from the ribbon with this menu Data > Remove Duplicates.

Menu Remove duplicates

To extract values without duplicates

  1. Select the column with your values, including the headers
  2. Click on the menu Remove Duplicates

In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be included in the process to remove duplicates.

Check the option if your list has headers

Click on OK. The process to remove the duplicates starts immediately. At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values that remain.

The list has only unique value

Remove duplicates on multi-column Excel

This tool is impressive because it also works if you select more than 1 column. For instance, you want to extract the unique Last Name + First name in this exercise.

  1. Select your 2 columns
  2. Activate the tool Remove duplicates
  3. Check the option My data has headers
  4. Click OK
Remove duplicates of 2 columns

This time, only two rows have been removed. This is because Bill MANVILLE and Bill JELEN are considered two different values. When you select many columns, Excel merges the content of all the cells as if they were only one cell.

Bill and Jon are not duplicates

Remove duplicates by formula

If you work with Microsoft 365 or Excel Online, you can return a list of data without duplicates with the UNIQUE function

How to use the UNIQUE function

Complex situation

But in some situations, you can not use this tool. It's not because there is a bug but because the data are too complex, like in this example.

Situation where the tool remove duplicates can not be used

2 Comments

  1. Navin Rao
    04/10/2020 @ 09:25

    Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. Thanks!

    Reply

  2. jraju
    23/08/2020 @ 13:33

    Hi, but this is not working for me. what am i missing
    I have the name in a column, with phone numbers in b,c,d,e f columns.
    if i do as in the instructions in this tutorial, the net reult i get is no duplicates found.
    the phone numbers you could take it as ten digit mobile numbers

    Reply

Leave a Reply

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

Remove Duplicates values in Excel

Reading time: 2 minutes
Last Updated on 12/05/2024

In Excel, you can remove duplicates in 2 different way

  1. By tool

    Remove duplicates from the ribbon Data

  2. By formula

    Excel 365 and Excel 2021 provide the UNIQUE function

List of values with duplicates

Remove Duplicates from the Ribbon

You can remove duplicate values from the ribbon with this menu Data > Remove Duplicates.

Menu Remove duplicates

To extract values without duplicates

  1. Select the column with your values, including the headers
  2. Click on the menu Remove Duplicates

In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be included in the process to remove duplicates.

Check the option if your list has headers

Click on OK. The process to remove the duplicates starts immediately. At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values that remain.

The list has only unique value

Remove duplicates on multi-column Excel

This tool is impressive because it also works if you select more than 1 column. For instance, you want to extract the unique Last Name + First name in this exercise.

  1. Select your 2 columns
  2. Activate the tool Remove duplicates
  3. Check the option My data has headers
  4. Click OK
Remove duplicates of 2 columns

This time, only two rows have been removed. This is because Bill MANVILLE and Bill JELEN are considered two different values. When you select many columns, Excel merges the content of all the cells as if they were only one cell.

Bill and Jon are not duplicates

Remove duplicates by formula

If you work with Microsoft 365 or Excel Online, you can return a list of data without duplicates with the UNIQUE function

How to use the UNIQUE function

Complex situation

But in some situations, you can not use this tool. It's not because there is a bug but because the data are too complex, like in this example.

Situation where the tool remove duplicates can not be used

2 Comments

  1. Navin Rao
    04/10/2020 @ 09:25

    Duplicate values can be annoying many of the times. As I work on excel regularly and seldom spending find few duplicate cells or vale kills a lot of time. This tutorial is handy to remove all the duplicates at once and be more productive. Thanks!

    Reply

  2. jraju
    23/08/2020 @ 13:33

    Hi, but this is not working for me. what am i missing
    I have the name in a column, with phone numbers in b,c,d,e f columns.
    if i do as in the instructions in this tutorial, the net reult i get is no duplicates found.
    the phone numbers you could take it as ten digit mobile numbers

    Reply

Leave a Reply

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