Remove Duplicates in Excel: Delete repeated column values

9
19699
Excel Spreadsheets

There are two main ways for you to find out whether there are duplicate values ​​in your Excel. The first and most straightforward is the remove duplicates feature, which once used, removes the entire line where the duplicate value was found.

Another option is conditional formatting with color highlighting for duplicate values. In this case, there is no line removal, only differentiated formatting is applied to the double values. Let's see how to do each case.

Excel Online Course

1. How to Remove Duplicates Step by Step

To start you need to have a data table. In our example, we have a staffing table for a company. See that it has columns of name, registration, sex, birth, etc. To use the feature, you must select all data in the table (CTRL + T shortcut):

Remove duplicates - employee table

As the data was filled by a person it is possible that errors occur in the process and some information may have been repeated. In that case, it would be awful for the parsing of the table to have duplicate values ​​in it. See that in the example I have the name Amanda Santos, as well as all your information, being repeated.

Remove duplicates - marked staff table

In this table it was quite simple to find the value repeated because we only have 11 names registered. Now imagine a spreadsheet with 1.000 entries, it would be impossible to verify name by name. In such cases we need to use the remove duplicates feature. It is in the DATA tab, within the data tools group:

Remove duplicates - data tab

When using this functionality, a window will open asking which column you want to check. Note that the window already comes with all values ​​marked. In this case, you need to select which column or columns you want to parse. Think about me, would it help me to see what are the duplicate values ​​of sex? Of course not?! After all, we only have two options (male and female), and so we would certainly have a number of items being removed in the wrong way.

Excel Online Course

The trick here is to understand which column contains values ​​that need to be unique. It could be the name, a unique product code, the RG or even an individual license plate.

Remove duplicates - remove duplicates window

In our case, I chose to choose the name. See that I just marked the name and no other item. I did this not to confuse my analysis of what is being removed from the table with the remove duplicates feature.

Remove duplicates - remove only one item

After pressing OK in the Remove Duplicates window a new window opens informing you how many values ​​have been removed. Notice that 3 repeated values ​​were found and removed. This was because I just showed the image of a piece of the table above

Remove Duplicates - Removed Values ​​Warning

In the end, this would be the final table that we would have without the entire lines where the name Amanda Santos appeared doubled.

Remove duplicates - values ​​removed

See that analysis left only the first entry of Amanda Santos, taking out the values ​​that were below. This occurred because the removal of duplicate values ​​is always done from the bottom up, leaving only the first value that was entered in the worksheet.

See how easy it is? But as I said at the beginning of the post, there is a second way for you to analyze duplicate values ​​in a worksheet without being able to remove duplicates directly.

Excel Online Course

2. Conditional Formatting of Duplicated Values

It may be that at some point you want to analyze what the duplicate values ​​are before you remove them from your spreadsheet. For these cases it is possible to use the highlighting of duplicate values ​​with conditional formatting.

The first step to this is to select the range of values ​​you are going to analyze. In our case it is the column with names of the employees:

Remove duplicates - conditional formatting select the desired column

After that, go to the HOME tab and choose the conditional formatting option

Remove duplicates - conditional formatting

Note that you will have a number of options, but the correct one for this case is the cell rules> duplicate values ​​highlighting.

Remove Duplicates - conditional formatting enhancement of duplicate values

After choosing this option Excel will open a conditional formatting window:

Remove Duplicates - Duplicate Value Enhancements

Note that when you open this option the duplicate values ​​are already checked before you even press OK. If you cancel the highlighting will disappear. If you want to move on, you can use light red with dark red font or change the formatting in the open window options.

Excel Online Course

If you want to learn this and many other features, try the free lessons of our courses. Beginner and Intermediate Excel or advanced Excel. If you want to learn more about other Excel features, just tell me in the comments below what you think is most important.

Excel Spreadsheets

9 COMMENTS

  1. If I am not mistaken, when he removes duplicates, he leaves only the first value that appears, which in this case is the oldest. Maybe if you use a filter before you can reorder it, you have the desired result by using the remove duplicates feature

  2. Can I delete duplicate values ​​and leave one?

    Example
    1346 A 01 / 07
    1346 A 03 / 07
    1346 A 05 / 07

    Three equal lines, exclude 2 and leave the latest one, is it possible?

  3. Hi Stanley, I believe you will only be able to do this with VBA code. You will probably have to write code that analyzes the 1 worksheet for equal values ​​and delete the ones you find.

  4. Good afternoon,

    I have an excel file that contains two pings, in one, there is a general listing of emails in the 1 pane, and in the 2 pane I have the emails that returned with server error. I'm looking for a command that removes the emails that are in the 2 pane of the address book (1 pane).

    Thank you in advance.

    Stanley (21) 96861-6174

  5. can you do this function horizontally?

    ex. instead of looking for the data to fill in a column (named column with the reference to be searched, in this case the name "Jonas") I need to fill in the data showing them in line and not column.

  6. You can use the advanced filter to pick up this list without repeating values, but it is a manual procedure, so to do it automatically you would need to create a macro

  7. Hello!
    I'm putting together a spreadsheet where in column A I have a list
    names, some repeated, I would like column B to appear
    column A without the repeated names. It's possible?

LEAVE AN ANSWER

Please, write your comment
Please enter your name