How to Remove Duplicates in Excel

Remove Duplicates in Excel. If you want to display and remove unnecessary duplicates in Excel, you can easily do this with a little tweak. The instructions can be found in this article. If you work in Excel with large datasets and want to avoid duplicates, the program gives you a corresponding option for that purpose. Read how to view duplicates in Excel and remove them.

How to Remove Duplicates in Excel

There is almost no office without Excel from the Microsoft Office family. Whenever it comes to creating any lists, practical organizational talent is used. It is used among others for

Remove Duplicates in Excel

  • customer data
  • stock lists
  • To-do lists
  • And much more

In particular, if several employees maintain the same list, it can not be ruled out that one or the other entry will be duplicated. This can lead to inconsistencies in that completed tasks or jobs still appear in the file because they have been deleted only once.

It is therefore advisable to check each table regularly for duplicate entries and delete them if necessary. Of course, this does not have to be done manually. Our guide shows you step by step how to find, mark and delete duplicate values ​​in Excel – all within a few seconds.

Show Duplicates in Excel

To delete duplicate entries in Excel, you must first find them. The easiest way is with the help of conditional formatting. As an example we use this table with fictitious customer data:

To examine the file for duplicate entries, proceed as follows:

  1. Select the entire table
  2. On the “Start” tab, in the “Styles” group, click the “Conditional formatting” button.
  3. In the submenu that opens, go to “Highlight Cell Rules” and then “Duplicate Values”.
  4. In the next dialog box, select the desired formatting (we left it at the default red) and click on “OK”. Now Excel will be able to show you all duplicates.

You can then decide which of these columns you want to delete. In our example, these would be lines 5 and 12. In lines 2 and 7, we are dealing with the same customer, but with different orders.

Remove Duplicates in Excel

If you not only want to find duplicates, but also want to delete them directly, you can do that in a number of ways. You can only go the easiest way if you use Excel 2007 or younger, but on older Windows PCs there are hardly any older versions of the program anyway. Deleting duplicate entries works as follows:

  1. Activate any column of your data record
  2. In the “Data” tab in the “Data Tools” menu group, click on “Remove Duplicates”
  3. A dialog window opens in which you can select in which columns to search for duplicate values.
  4. Click “OK” after making the selection.

As mentioned above, this fastest way to delete duplicate values ​​in Excel is only available to users of the more recent versions.

If you are still using Excel 2003, it takes a little extra effort. To clean up records here, you must use the special filter.

  1. Activate any row in your table
  2. Click on “Filter” in the “Data” tab and then on “Special filter”
  3. In the dialog box that opens, activate the option “No duplicates”
  4. In the “List area” field you can select which parts of the table are to be searched.
  5. Click on “OK”

How to find duplicates in Excel

  • In Microsoft Excel, first mark all numbers that you want to examine for duplicates.
  • For example, if you want to filter out the duplicates of the complete data series, click on the box between “A” and “1” (diagonally next to cell A1).
  • Then select the “Conditional Formatting” button under “Styles” in the “Start” menu (see picture).
  • Now click on “Highlight Cell Rules” and then on “Duplicate Values”.
  • All cell duplicates will now be displayed in red.

Show and remove duplicates in Excel

This will show you all the duplicates in Excel

To find and mark duplicates, follow these steps:

  1. Likewise, mark the cells in which you want to search for duplicates directly for an entire column or row.
  2. Click on “Start” in the upper Excel menu bar and under “Styles” on “Conditional Formatting”.
  3. Set the “Highlight Cell Rules”: Select “Duplicate Values”.
  4. In the following window you can set how the cells should be marked with duplicates. Take, for example, a red or yellow filling.
  5. Then click OK”.

All duplicate values ​​in the selected cells are now clearly marked. In the next step we will show you how to remove the duplicate entries .

How to remove duplicates from your Excel spreadsheet

To remove the found duplicates, you only need a few clicks:

  1. Select all cells from which you want to remove duplicates.
  2. In the Excel menu bar, click Data, and then click Remove Duplicates under Data Tools.
  3. You can specify the columns from which duplicates should be removed or not removed. Check the corresponding columns and confirm with “OK”.

Your Excel record has now been cleaned up and you can continue with your work.

Remove Duplicates in Excel 2013, Excel 2010 and Excel 2007

In the first example, the duplicate records are to be identified by a single column. The table looks like this:

You can use conditional formatting to check if specific customer numbers appear in the list multiple times:

  1. Select all cells containing customer numbers.
  2. Click on the tab in the Ribbon Start .
  3. On the group Styles button Conditional Formatting .
  4. First click on the menu item Rules for Highlighting Cells and then on the menu item Double Values .
  5. Change the formatting if necessary. By default, a light red fill is applied.
  6. Then click the OK button . The two duplicate customer numbers are visually highlighted.

To automatically delete all duplicate customers from the list, please proceed as follows:

  1. Activate any cell in the list of customer data.
  2. Click the Data tab in the Ribbon .
  3. In the Data Tools group, click the Remove Duplicates button . The following dialog screen appears:
  4. Decide which columns are used to identify multiple customers.
  5. Then click the OK button . The line 14 with the duplicate customer number 120 is deleted from the worksheet without further

If you accidentally delete too many records from the list, you can undo this action.

In the second example, the duplicate records should be identified by the contents of 3 columns.

If the first name, last name and location are identical, they should be duplicate records. Please follow these steps to mark them in the table:

  1. In the first column to the right of the table, enter a formula that makes up the search term you want to validate. In column D2, enter the following formula: = CHAIN ​​(A2; “<space>”; B2; “, <space>”; C2)
  2. Copy the formula down.
  3. Mark the area D2: D9.
  4. Click on the tab in the Ribbon Start .
  5. On the group Styles button Conditional Formatting .
  6. First click on the menu item Rules for Highlighting Cells and then on the menu item Double Values .
  7. Change the formatting if necessary. By default, a light red fill is applied.
  8. Then click the OK button .

Remove Duplicates in Excel 2003

In Excel 2003, this easy way to delete duplicates does not exist yet. Here are two ways to detect and delete duplicate records.

New column

In the first approach, a new column is added. The steps in detail:

  1. Sort the data by the column where you can see that it is a duplicate record (eg customer ID).
  2. Add another blank column after this column and give it the title Double .
  3. In the first empty cell of the Duplicate column, enter the following formula: = IF (A1 = A2; “j”; “”) The formula assumes that the duplicate data is in column A.
  4. Copy the formula by double-clicking down.
  5. Select the column labeled Duplicate .
  6. Copy the full column labeled Duplicate .
  7. Now, without unmarking, invoke the Edit , Paste Special command and select the Values option in the dialog that opens .
  8. Now place the cursor on the cell with the title Double .
  9. Sort the table by this column in descending order so that the records with the entry j are at the top.
  10. Delete all lines in which a j exists.

special filters

In the second approach, a special filter can be used to automatically remove duplicates. Duplicates are the rows that have the same entry in all columns.

Please proceed as follows:

  1. Activate any cell in the list of customer data.
  2. In the menu Data click on the menu item Filter and then on the menu item Special filter . The following dialog screen appears:
  3. Activate the options Copy to another place and No duplicates .
  4. In the Copy to field, enter the top left cell from which the list with the unique customer numbers should appear.
  5. Finally, click on the OK button .

Tips Again Data Loss

Of course, when deleting data, it can happen again and again that lines that are actually still needed disappear. If this is immediately noticeable, this problem is very easy to solve simply by clicking “Undo”.

In most cases, however, the lists are so extensive that errors do not jump directly into the eye. We therefore strongly recommend that you search the appropriate table using conditional formatting before each deletion to determine whether all duplicate content can be deleted. You should also make a backup copy. In any case, you still have the source file and can accidentally manually insert deleted columns or rows.

Conclusion

Deleting duplicate content is one of the most important processes when maintaining Excel data records. The larger a spreadsheet is and the more people are working on it, the higher the likelihood of duplication.

Regardless of the age of your version of Excel, you can search, view, and delete records for duplicate values. In newer versions there is a separate menu item, in Excel 2003 it works via the special filter.

However, do not forget to check the contents to be deleted carefully and create a backup copy in advance in order not to lose important data!

How to Remove Duplicates in Excel
Rate this post