Find and remove duplicates in Microsoft Excel
Contents
Find and remove duplicates in Microsoft Excel
When working with a table or database with a large amount of information, it is possible that some rows are repeated. This further increases the data array. In addition, if there are duplicates, incorrect calculation of results in formulas is possible. Let’s see how to find and remove duplicate rows in Microsoft Excel.
Search and delete
Find and remove table values that are duplicated, perhaps in different ways. In each of these options, the search for and elimination of duplicates are links in one process.
Method 1: Simple Removal of Duplicate Lines
The easiest way to remove duplicates is to use a special button on the ribbon designed for this purpose.
1. Select the entire table range. Go to the tab “Data” . Click on the “Remove Duplicates” button . It is located on the ribbon in the Data Tools toolbox .
2.The Remove Duplicates window opens. If you have a table with a header (and the vast majority always have), then there should be a checkmark next to the “My data contains headers” parameter. The main field of the window contains a list of columns that will be checked. A row will be considered a duplicate only if the data of all columns marked with a check mark match. That is, if you uncheck the name of a column, you thereby expand the likelihood of recognizing the record as a duplicate. After all the required settings are made, click on the button “OK” .
3. Excel performs a procedure to find and remove duplicates. After it is completed, an information window appears, which reports how many duplicate values were removed and the number of unique records left. To close this window, click the OK button .
Method 2: Remove Duplicates in Smart Table
Duplicates can be removed from a range of cells by creating a smart table.
1. Select the entire table range.
2. Being in the “Home” tab, click on the button “Format as table” , located on the ribbon in the tool block “Styles” . In the list that appears, select any style you like.
3. Then a small window opens in which you need to confirm the selected range to form a “smart table”. If you have selected everything correctly, then you can confirm, if you made a mistake, then you should correct it in this window. It is also important to pay attention to the fact that there is a checkmark next to the “Table with headers” parameter . If it’s not there, then it should be. After all settings are completed, click on the “OK” button . “Smart table” is created.
4. But creating a “smart table” is only one step towards solving our main task – removing duplicates. Click on any cell in the table range. In this case, an additional group of tabs “Working with tables” appears . Being in the tab “Designer” click on the button “Remove Duplicates” , which is located on the ribbon in the tool block “Service” .
After that, a window for removing duplicates opens, the work with which was described in detail when describing the first method. All further actions are performed in exactly the same order.
This method is the most versatile and functional of all described in this article.
Method 3: Apply sorting
This method is not exactly removing duplicates, since sorting only hides duplicate entries in the table.
1. Select the table. Go to the tab “Data” . Click on the button “Filter” , located in the settings block “Sort and filter” .
2. The filter is enabled, which is indicated by the icons that appear in the form of inverted triangles in the column names. Now we need to set it up. Click on the button “Advanced” , located next to everything in the same group of tools “Sort and Filter” .
3. The advanced filter window opens. We set a checkmark in it opposite the parameter “Only unique records” . We leave all other settings as default. After that, click on the button “OK” .
After that, duplicate entries will be hidden. But their display can be turned on at any time by pressing the “Filter” button again .
Method 4: Conditional Formatting
You can also find duplicate cells using conditional table formatting. True, they will have to be removed by another tool.
1. Select the area of the table. Being in the tab “Home” , click on the button “Conditional Formatting” , located in the settings block “Styles” . In the menu that appears, we sequentially go through the items “Highlight rules” and “Duplicate values …” .
2. The formatting settings window opens. The first parameter in it is left unchanged – “Repeating” . But in the selection parameter, you can either leave the default settings or choose any color that suits you, after that click on the button “OK” .
After that, cells with duplicate values will be selected. You can then manually delete these cells, if desired, in the standard way.
