Remove Duplicates values in Excel

Remove Duplicates values in Excel
Last Updated on 09/11/2023
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 remain.

The list has only unique value

Remove duplicates on multi-column Excel

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

  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 2 rows have been removed. It's because Bill MANVILLE and Bill JELEN are considered as 2 different values. When you select many columns, Excel merges the content of all the cells like if it was only one cell.

Bill and Jon are not duplicates

Remove duplicates with a formula

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

Demo of 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 09/11/2023

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 remain.

The list has only unique value

Remove duplicates on multi-column Excel

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

  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 2 rows have been removed. It's because Bill MANVILLE and Bill JELEN are considered as 2 different values. When you select many columns, Excel merges the content of all the cells like if it was only one cell.

Bill and Jon are not duplicates

Remove duplicates with a formula

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

Demo of 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 *