Excel 2010: Remove/Delete Duplicate Rows

Microsoft has made it quicker to remove duplicate rows in Excel 2010, all it takes now is two simple steps. The best part is that you don’t need to select any specific row before removing the duplicate rows, the build-in Remove Duplicate feature takes care of it.

For demonstration purpose I created a quick data table, note that rows 2 and 5 have same values. Head over to the Data tab and click Remove Duplicate button. This will open a dialog window where you can select the columns from which you want to delete the duplicate values.

If you want to get rid of all duplicate columns in an excel spreadsheet, click Select All, so that all columns are selected and then click OK.


excel 2010 remove duplicate

After the process is complete, you will be shown a notification window with the results. Note in the screenshot below that the duplicate in row 5 is removed.

Duplicate Values Removed

This guide only covers method to remove duplicate rows, if you are looking to find and remove duplicate values in the whole spreadsheet, visit the guide here.

Advertisement
  • Pingback: How To Delete/Remove Blank Rows & Columns In Excel 2010 Spreadsheet

  • Chris

    Thanks!

  • Hali

    This is exactly what I need to know how to do, but on a Mac using excel. No one seems to know how to do it on a Mac! Any tips? Thanks!

  • JV

    Thanks!! You Rock!

  • Srini Jupudi

    Nice Explanation

  • Lisa

    Thank you, but my job requires me to remove BOTH, the duplicate field and the field it repeats. Is there a way to do this without manually scrolling through the spreadsheet and going through each row of records one-by-one?

    • http://www.facebook.com/Eka123 Ekaterine Kovziridze

      I have the same problem lisa did you ever find out what is the way?

      • Lisa

        Hi Ekat,
        Yes, I have! While I have found the Remove Duplicates feature to be very helpful for most of my tasks, I found another Excel command that points out duplicates for me, and then I manually remove them. Remember, the cells must have the same exact information, though. So if you have one field that says, “Louie G” and another that says “Louis G” Excel won’t see them as duplicates.
        Keeping that in mind:
        Go to the HOME tab
        About 1/3 of the way in from the right, choose the down arrow from CONDITIONAL FORMATTING
        Then choose the first option down: HIGHLIGHT CELLS RULES
        That opens another window from the command. Choose the one on the bottom that says DUPLICATE VALUES.
        I usually keep the default values: light red fill with a dark red text
        Once I select those items, all the duplicates appear in a light red fill with a dark red text. Then I simply delete them.

        If I find they are scattered all over the place, I do one more step before deleting.
        I use the SORT function (DATA, SORT) and then choose the column header that has the duplicate fields. There is the option to sort on values. I use the drop down menu and then choose cell color or font color. Then the option for cell color (or font color) appears on the right of that menu screen.

        All the common colors will be grouped together, where you can select and delete.

        Hope that helps! : )

  • Know Fool

    I don’t understand why you’d ever want to do this…

    • Sven Johannsen

      Because you are presented with a 5000 name mailing list in Excel, that has been appended to by various people over multiple years, and you want to trim it down to just the unique entries. That’s one.