How to remove duplicate rows in Excel: two ways


When you work with a huge amount of data inprogram "Excel", then inadvertently it is very simple to make a mistake and enter the same data again. So there are duplicates. This, of course, raises the volume of all summary information, and in some cases knocks down formulas that must sum up and calculate various variables. You can manually find duplicates, but why, if there are several ways how in Excel to remove duplicate lines automatically.

 as in excel remove duplicate rows

Method 1: standard removal of duplicates

The easiest way, as in Excel to remove duplicate lines, is to use the appropriate tool located in the ribbon.

So, you need:

  1. Holding the left mouse button, select the necessary range of cells within which there will be a search and automatic removal of duplicate lines.
  2. Go to the "Data" tab.
  3. Click on the "Remove duplicates" tool, which is located in the "Working with data" group.
  4. In the appeared window, tick the box next to "My data contains headers".
  5. Install checkboxes next to the name of thosecolumns in which the search will be conducted. Take into account that if all the checkboxes are checked, then duplicate lines will be considered as duplicates in all columns. Thus, if you want to remove duplicates from one column, you must leave only one tick next to its name.
  6. Click "OK".

excel remove rows with duplicate values

Once you click on the button, the search will beginduplicates of the selected area, and then delete them. The result is a window in which the report will be displayed. Now you know how to remove duplicate lines in Excel, but only the first way, the second one is on the queue.

Method 2: Use the "smart table"

Duplicates can be deleted in a similar way that takes place in this article. This time, in the story, how to remove duplicate rows in Excel, the "smart table" will be used.

To remove duplicates in Excel, you need to do the following:

  1. As in the previous method, first select the range of cells where you want to remove duplicates.
  2. Click the "Format as a table" button, which is located on the "Home" tab in the "Styles" tool group.
  3. Choose from the drop-down menu of the style you like (any).
  4. In the window that appears, you need to confirmthe previously specified range of cells, and if it does not match, then reassign. Also check the box next to "Table with headers", if it is, then click "OK".
  5. "Smart Table" is created, but that's not all. Now you need to select any cell in the table so that the tab "Designer" appears in the tabs and directly go to this tab.
  6. In the toolbar, click the "Delete duplicates" button.

remove repeating lines excel 2010

After that, the Duplicate Removal window will appear.rows. It will be similar to what was presented in the first method, so follow all the following steps in accordance with the first instruction.


So, we have analyzed two methods, as in Exceldelete rows with duplicate values. As you can see, there is nothing complicated in this, and using the instruction, and at all, you can turn this operation on in a few seconds. The example was given in the 2016th version of the program, but you can also delete duplicate rows in Excel 2010 and other versions exactly the same.

Comments (0)
Add a comment