Remove duplicate values ​​in Excel

When we have data in Excel that contains repeated values ​​and we need to get the unique values, we can use the command found in the tab and within the group .

Before using the command , you must remember that duplicate values ​​will be removed completely. So, although the use of this command is extremely easy and fast, you should bear in mind that your original data will be modified.

Remove duplicates in Excel

Before running the command , it is necessary to select one of the cells of the data range so that Excel can detect them and show the possible columns by which we can determine the duplication.

If you do not want Excel to automatically detect columns, then you will have to select the range of cells on which the action will be applied.

For our first example we have a list of names in column A of our sheet:

Remove duplicates in Excel

I activate any of the cells that contain the data and go to the tab , and within the group , I will click on the command .

Remove duplicates command in Excel

The dialog box will appear and in the section you can select one or more columns of data.

To select all the columns quickly you can click the button and to remove the selection you can click on .

If your data has headers, you should make sure to check the selection box so that this value is omitted when evaluating and removing duplicates.

Remember that the command it will run on those columns you have selected in this dialog box.

Remove duplicates dialog box in Excel

You will need to click the OK button and Excel will display a message indicating the number of duplicate values ​​that have been found and removed, as well as the number of unique values ​​that remain.

How to remove duplicates in Excel

Finally you must click on the OK button to close the dialog box and you will be able to see the result of the command .

Remove duplicates in Excel

Notice that the unique values ​​have been placed in the top cells of the original range and this happens because the command adds them to the list of unique values ​​as they appear.

Duplicate values ​​are removed and there will be no way to get them back unless you use the command which is in the Quick-access tool bar or by pressing the keyboard shortcut Ctrl + Z.

Remove duplicates in multiple columns

The dialog box allows us to choose the columns on which the duplication of information will be evaluated.

By default, Excel will look for the repeating values ​​in all the columns of our data, but there will be times when we need to indicate only some of the columns.

For example, in the following image you can see the data of several students of a school, with their first and last names, as well as the subject and the grade for each of them.

Remove duplicate values ​​in Excel

To obtain the list of unique students (Name and Surname) I start by activating a cell of the data and then I go to the tab and hit the button .

In the dialog box displayed you must select the First Name and Last Name columns as shown in the following image:

Delete repeats in multiple Excel columns

When you press the OK button, you will see the list of unique students and you can verify that the command it has relied exclusively on the First Name and Last Name columns to determine whether a value is duplicated.

As with the previous example, the first and last names will be displayed in the order of appearance within the original data range.

Remove duplicate data in Excel

It is very important that you make a copy of your data since the command it will remove duplicate values.

In fact, it is highly recommended that you only copy the data on which the duplication evaluation will be made and that you execute the command over that new range so that the original data remains intact.

The command it will be of great help to eliminate duplicate values ​​in your excel sheets.

Download workbook

Leave a Comment