How to find and remove duplicates in Excel. 5 methods to find and remove duplicates in Excel

It’s no secret that in Excel you often have to work with large tables that contain a huge amount of information. At the same time, such a volume of information during processing can cause failures or incorrect calculations when using various formulas or filtering. This is especially felt when you have to work with financial information.

Therefore, in order to simplify the work with such an array of information and eliminate the possibility of errors, we will analyze exactly how to work with rows in Excel and use them to remove duplicates. It may sound complicated, but figuring it out is actually quite simple, especially when there are as many as five methods of working with finding and removing duplicates at hand.

Method 1: Manually Remove Duplicate Rows

The first step is to consider using the simplest way to deal with duplicates. This is the manual method, which involves using the “Data” tab:

1. First you need to select all the cells of the table: hold down the LMB and select the entire area of ​​the cells.
2. At the top of the toolbar, you need to select the “Data” section to access all the necessary tools.
3. We carefully consider the available icons and select the one that has two columns of cells painted in different colors. If you hover over this icon, the name “Delete Duplicates” will be displayed.
4. To effectively use all the parameters of this section, it is enough to be careful and take your time with the settings. For example, if the table has a “Header”, then be sure to pay attention to the item “My data contains headers”, it must be checked.

5. Next comes a window that displays information by column. You need to select the columns that you want to check for duplicates. It’s best to select all to minimize skipped takes.

6. Once everything is ready, check the marked information again and click OK.
7. Excel will automatically analyze the selected cells and remove all matching options.
8. After a full check and removal of duplicates from the table, a window will appear in the program in which there will be a message that the process is over and information will be indicated on how many matching rows were deleted.

You just have to click on “OK” and you can assume that everything is ready. Carefully perform each action, and the result will certainly not disappoint you.

Method 2: Removing Duplications Using a Smart Table

Now let’s take a closer look at another useful method for removing duplicates, which is based on the use of a “smart table”. It is enough to follow these recommendations:

1. First of all, select the entire table to which you want to apply a smart automatic information processing algorithm.

2. Now use the toolbar, where you need to select the “Home” section, and then find “Format as Table”. This icon is usually located in the “Styles” subsection. It remains to use the special down arrow next to the icon and select the style of the table design that you liked the most.

3. Once everything is done correctly, an additional message about formatting the table will appear. It specifies the range for which the Smart Table function will be applied. And if you have previously selected the necessary cells, then the range will be indicated automatically and you will only have to check it.

4. It remains only to start searching and further removing duplicate lines. To do this, you need to take additional steps:
   • place the cursor on an arbitrary table cell;
   • in the top toolbar, select the “Table Design” section;
   • we are looking for an icon in the form of two columns of cells with a different color, when you hover over them, the inscription “Delete duplicates” will be displayed;
   • follow the steps we specified in the first method after using the given icon.

Pay attention! This method has a unique property – thanks to it, it will be possible to work with tables of different ranges without any restrictions. Any selected area while working with Excel will be carefully analyzed for duplicates.

Method 3: Using a Filter

Now let’s pay attention to a special method that allows you not to remove duplicates from the table, but simply hide them. In fact, this method allows you to format the table in such a way that nothing interferes with your further work with the table and it is possible to visually obtain only relevant and useful information. To implement it, you just need to follow these steps:

1. The first step is to select the entire table in which you are going to manipulate to remove duplicates.
2. Now go to the “Data” section and immediately go to the “Filter” subsection.

3. A clear sign that the filter has been activated is the presence of special arrows in the header of the table, after which it will be enough for you to use them and indicate information regarding duplicates (for example, a word or designation in the search).

Thus, you can immediately filter out all duplicates and perform additional manipulations with them.

Advanced filter to find duplicates in Excel

There is another additional way to use filters in Excel, for this you will need:

1. Perform all the steps of the previous method.
2. In the toolkit window, use the “Advanced” icon, which is located next to the same filter.

3. After using this icon, you just need to pay attention to the advanced settings window. This advanced toolkit will allow you to get acquainted with the initial information:
   • first, you should check the specified range of the table so that it matches what you noted;
   • be sure to check the box “Only unique records”;
   • once everything is ready, it remains only to click on the “OK” button.

4. Once all recommendations have been met, all you have to do is take a look at the table and make sure that duplicates are no longer displayed. This will be immediately visible if you look at the information at the bottom left, which reflects the number of lines displayed on the screen.

Important! If you need to return everything to its original form, then it is as simple as possible to do this. It is enough just to cancel the filter by performing similar actions that were indicated in the method instruction.

Method 4: Conditional Formatting

Conditional formatting is a special toolkit that is used in solving many problems. You can use this tool to find and remove duplicates in a table. To do this, you will need to do the following:

1. As before, you will first need to select the cells of the table that you plan to format.
2. Now you should go to the “Home” tab and find the special “Conditional Formatting” icon, which is located in the “Styles” subsection.

3. After completing the steps, you will have access to a window called “Cell Selection Rules”, then you need to select the “Duplicate Values” item.

4. Be sure to pay attention to the formatting settings, they should be left unchanged. The only thing that can be changed is the color coding according to your preferences. Once everything is ready, you can click “OK”.

5. Thanks to such actions, you can highlight all duplicates in a different color and start working with them in the future.

Attention! The main disadvantage of this method is that when using such a function, absolutely all the same values ​​are marked, and not only those options where the entire string matches. It is worth remembering this nuance in order to avoid problems with visual perception and understand exactly how to act and what to pay attention to.

Method 5: Formula to Remove Duplicate Rows

This method is the most difficult of all listed, as it is intended exclusively for those users who understand the functions and features of this program. After all, the method involves the use of a complex formula. It looks like this: =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»). Now you need to decide how exactly to use it and where to apply it:

1. The first step is to add a new column that will be dedicated exclusively to duplicates.

2. Выделите верхнюю ячейку и введите в нее формулу: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;А2:А90)>1;0;1);0));»»).
3. Now select the entire column for duplicates without touching the header.

• Put the cursor at the end of the formula, just be careful with this item, since the formula is not always clearly visible in the cell, it is better to use the top search bar and carefully look at the correct cursor location.
• After setting the cursor, you must press the F2 button on the keyboard.
• After that, you need to press the key combination “Ctrl + Shift + Enter”.

4. Thanks to the actions performed, it will be possible to correctly fill the formula with the necessary information from the table.

Finding matches with the Find command

Now it is worth considering another interesting option for finding duplicates. Especially for such a method, you will need another formula that looks like this: =COUNTIF(A:A, A2)>1.

Additional Information! In this formula, A2 means the mark of the first cell from the area in which you plan to search. As soon as the formula is entered into the first cell, you can drag the value and get the information you need. Thanks to such actions, it will be possible to distribute information into “TRUE” and “FALSE”. And if you need to search in a limited area, then mark the search range and be sure to secure these designations with a $ sign, which will confirm the commit and make it the basis.

If you are not satisfied with the information in the form of “TRUE” or “FALSE”, then we suggest using the following formula, which structures the information: =IF(COUNTIF($A$2:$A$17, A2)>1;”Duplicate”;”Unique”). Correct execution of all actions will allow you to get all the necessary actions and quickly deal with the existing duplicate information.

How to use a pivot table to find duplicates

An additional method of using Excel’s functions to find duplicates is the PivotTable. True, in order to use it, you still need a basic understanding of all the functions of the program. As for the main actions, they look like this:

1. The first step is to create a table layout.
2. You must use the same field as information for strings and values.
3. The selected match words will become the basis for automatic counting of duplicates. Just do not forget that the basis of the counting function is the “COUNT” command. For further understanding, keep in mind that all values ​​that exceed the value of 1 will be duplicates.

Pay attention to the screenshot, which shows an example of such a method.

The main distinguishing point of this method is the absence of any formulas. It can be safely adopted, but first you should study the features and nuances of using a pivot table.

Conclusion

Now you have all the necessary information regarding the methods of using the search and removal of duplicates, and you also have recommendations and tips that will help you quickly solve the problem.