Excel does not remove duplicates. Duplicate values ​​in Excel: how to remove duplicates, or just find and highlight

The little son came to his father, and the little one asked...

No not like this. In fact, an employee came up and said - shouldn’t we install Excel 2010? I know from experience that he needs to fill out a small table a couple of times a day, nothing too complicated. Therefore, a logical question immediately arose - why do you need it? To which the logical answer is - and there you can delete duplicate cells with one command. Yes. That is, 3-4 tr. for removing duplicates. But I must say, I generally have a very bad attitude towards unjustified expenses in business. It is one thing when something is required to directly perform a function that cannot be performed in anything else. Or it takes so much time that it’s cheaper to optimize it, or write a special program for it - now, for example, we’re writing one of these for one and a half bucks. But it’s another matter when someone wants to spend 10 minutes longer on VKontakte during working hours, and is simply too lazy to figure out how to press a couple of buttons.

Well, okay, now I’ll tell you how to remove duplicates in Excel 2003, and you can go on and figure out why else you might need 2010 (not why I need it - I know perfectly well :-)).

The easiest way a) is how to remove duplicate excel values:

1. Take, select the range of cells with duplicates, click on Data -> Filter -> Advanced filter...

3. We get the result, which can be done by Ctrl+C - Ctrl+V to the desired place/sheet.

Now option B), for those who are not afraid of difficulties :)

1. To the left of the leftmost column of our table, insert an additional column (let’s say we had A - insert another A so that ours becomes B), and enter serial numbers in it (by simply entering the numbers 1 and 2 in the cells, highlighting these two cells and double-clicking on the black dot in the lower right corner extends everything to the end of the range). We will need this later if we want to restore the order of the records; if it is not important, we don’t have to do this. It will look something like this:

3. Sort the list by column B, say ascending.

4. In cell C2, insert the formula =IF(B2=B1;0;1), which compares each value with the previous one. If the row is a duplicate, then it will be assigned the value 0, if not, then 1. Well, of course, the values ​​B2 and B1 are in my example, it all depends on how many columns there are in the table.

5. Click on the point circled in red in the lower right corner of the cell to extend the formula to the entire column (similar to how we inserted serial numbers):

6. With the result obtained, do Ctrl+C, go to Edit -> Paste Special

7. In the dialog that opens, select - Insert Values

8. Now select the first three cells in row 2, with Shift held down, click on the lower border of the selection, thus selecting everything from A2 to C12. Click Data -> Sorting, sort by column C, in descending order (this is important - sort in descending order! If we assigned duplicates 1, not 0, then we would have to sort the other way around, in ascending order). I will not provide a screenshot, since it is absolutely the same as steps 2 and 3.

9. Select column C, press Ctrl-F, enter 0 in the search form, and look for the first cell in this column with zero.

10. Select the entire line, from A to C, in which the zero was first found, with the Shift key held down, click the mouse on the lower border of the selection, thus selecting all the values ​​below. Then we do whatever we want with them: we can delete them to hell, or we can copy these duplicates somewhere. Let's assume it was deleted.

11. We delete the values ​​from column C - it also played a role.

12. Select the entire columns A and B, click Data -> Sorting, and sort by column A (in my case, by numbers) in ascending order.

Finding and manually removing duplicate values ​​in an Excel spreadsheet, especially in large documents, is a very risky and impractical task. It is possible that when you visually check the cells, you may miss duplicates. And then, a colossal amount of time is spent deleting each cell.

In this article, we will look at how you can remove duplicates in Excel using various automated standard functions.

Advice! To make it easier to familiarize yourself with ways to remove duplicate rows, columns, and cell values, open a draft template or a copy of the project you are working on in the editor, so that by mistake during editing you do not lose valuable data or break the format of the original.

Method No. 1

1. While holding down the left mouse button, use the cursor to mark the boundaries of the table area (individual rows, columns) where repetitions need to be removed. Or select the entire project.

2. In the editor menu, go to the “Data” tab.

3. In the “Working with Data” block, click the “Remove Duplicates” button.

4. In the panel that appears, select the columns in which you want to remove identical values ​​by setting labels. If there are a lot of elements, use the “Select All” and “Deselect” options to quickly configure deletion. Click OK.

5. Once the table rows and columns are cleared of duplicates, a message will appear indicating how many unique values ​​are left.

Advice! Repetitions can be restored immediately after deletion by clicking the left arrow icon in the upper left corner of Excel.

Method No. 2

1. Click on the table you are editing.

2. On the “Data” tab, in the “Sorting and Filter” block, click the “Advanced” subsection.

If you need to create a new table containing only unique source cells:

1. In the “Advanced Filter” panel, click the “Copy result to another location” radio button.

2. Click the button located on the right side of the “Place result in range” field.

3. Click on a free Excel workspace where you will need to place the filtered table. After clicking, the cell code will appear in the field. Close it and go to filter options.

4. Click the “Unique records only” box and click “OK”.

5. After filtering, a version of the original table without duplicates will appear in the specified location.

To edit a document without making copies:

• in the “Advanced Filter” panel, set the processing mode to “Filter list in place”;
• Click the mouse to enable the “Only unique records” add-on;
• Click “OK”.

We all know that large data sets are best stored in spreadsheets, because then we are provided with a wonderful information processing tool. Let's look at how you can eliminate information that is repeated in lines. In MS Excel files, duplicates occur when we create a compound table from other tables, or when different users work with one file, filling the table with the same type of information. MS Excel offers several options for eliminating or identifying duplicate information, namely: searching, highlighting and, if necessary, deleting duplicate values. Let us consider in detail each of the actions using MS Excel 2007 as an example.

1. Removing duplicate values ​​in Excel

You have a two-column table that contains identical records, and you need to get rid of them. Place the cursor inside the area in which you want to remove duplicate values. Open the tab Data, in Group Working with data, left-click on the command button Remove duplicates.

If each table column has a header, set the marker My data contains headers. We also place markers opposite those columns in which we need to search for duplicates. In the figure, these elements are highlighted with a colored frame.

RESULT: This function is designed to delete records that completely duplicate rows in the table.

2. Advanced (optional) filter to remove duplicates

Select the table column that contains repeating information, go to the tab Data and then to the group Sorting and Filter, left-click on the button Additionally.

In the dialog box that appears Advanced filter you need to set the switch in the line Copy the result to another location and indicate the required range (in our case, a column) in the field Original range, in field Place result specify the range where the filtering result will be placed and set the marker Only unique values. Confirm the installed commands with the OK command button.

In the place specified for placing the results of the advanced filter, another column will be created, but with unique values; in our case, a column with the authors of the works.

3. Conditional formatting in Excel

Select a table that contains duplicate values. In the tab home go to the group Styles, select Conditional Formatting, Further Cell selection rules and in them - Duplicate values.

In the dialog box that opens Duplicate values select a format for highlighting duplicate entries. By default, MS Excel has a light red fill and a dark red text color. In this case, Excel will compare not the entire table row for uniqueness, but only the column cell, so for us, who are tracking duplicate records in only one column, this is convenient. In the figure you can see how Excel has filled in some cells of the column with the names of book authors, although the entire row of this table is unique.

RESULT: For large amounts of information, this method does not give a clear picture. All names of authors of books that have repetitions are highlighted in the same color.

4. Pivot tables to identify repeating values

In the table with the list of authors and the names of the works of these authors, add a column Serial number before the names of the authors and a column Counter after the names of the works. Fill in the column Counter units (1), and the column Serial number– serial numbers of authors and their works. Select the entire table and go to the tab Insert to the group Tables. Using the left mouse button, click on the button Pivot table. In the window that opens, check the boxes as shown in the figure, i.e. We place the pivot table on a new sheet.

Don't forget to click on the OK command button and continue creating the pivot table on a new sheet. In the window List of Pivot Table Fields check the boxes in all fields as indicated in the figure. In this case, the selected field names will appear in the windows Line names And å Values. Drag the field p/p to column Line names.

In field Values there should be a column with a counter. As the lines are installed and placed in the window List of Pivot Table Fields the material of the original table will change. In the created pivot table, records with a value greater than one will be duplicates; the value itself will indicate the number of duplicate values. For greater clarity, you can sort the table by column Counter to group duplicates.

Material prepared by L.A. Shutilina, methodologist of the State Medical Center for Dog and Medical Medicine

When you work with a huge amount of data in Excel, it is very easy to accidentally make a mistake and enter the same data again. This is how duplicates arise. This, of course, increases the volume of all summary information, and in some cases confuses the formulas that are supposed to summarize and calculate various variables. You can find duplicates manually, but why, if there are several ways to remove duplicate rows automatically in Excel.

Method 1: Standard removal of duplicates

The simplest way, like a line, is to use the appropriate tool located in the ribbon.

So, you need:

1. Holding down the left mouse button, select the required range of cells within which to search and automatically delete duplicate rows.
2. Go to the "Data" tab.
3. Click on the "Remove Duplicates" tool, which is located in the "Data Tools" group.
4. In the window that appears, check the box next to “My data contains headers.”
5. Check the boxes next to the names of the columns in which the search will be carried out. Please note that if all the boxes are checked, rows that are completely repeated in all columns will be considered a duplicate. Thus, if you want to remove duplicates from one column, then you need to leave only one checkbox next to its name.
6. Click "OK".

As soon as you click on the button, the search for duplicates of the selected area will begin, and then delete them. As a result, a window will appear in which the report will be displayed. Now you know how to remove duplicate rows in Excel, but only the first method, the second is next.

Method 2: Using a Smart Table

Duplicates can be removed in a similar way as described in this article. This time, in the story of how to remove duplicate rows in Excel, a “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 need to remove duplicates.
2. Click the "Format as Table" button, which is located on the "Home" tab in the "Styles" tool group.
3. Select the style you like (any) from the drop-down menu.
4. In the window that appears, you need to confirm the previously specified range of cells, and if it does not match, then reassign it. Also check the box next to “Table with headers”, if so, click the “OK” button.
5. The Smart Table has been created, but that's not all. Now you need to select any table cell so that the “Designer” item appears in the tabs and go directly to this tab.
6. In the tool ribbon, click the "Remove duplicates" button.

After this, a window for removing duplicate lines will appear. It will be similar to what was presented in the first method, so carry out all subsequent actions in accordance with the first instructions.

Conclusion

So we looked at two ways to delete rows with duplicate values ​​in Excel. As you can see, there is nothing complicated about this, and using the instructions, you can complete this operation in a few seconds. The example was given in the 2016 version of the program, but you can delete duplicate rows in Excel 2010 and other versions in the same way.

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 set. In addition, if there are duplicates, the results in the formulas may be calculated incorrectly. Let's figure out how to find and remove duplicate rows in Microsoft Excel.

Finding and deleting table values ​​that are duplicates is possible in different ways. In each of these options, the search and elimination of duplicates are parts of one process.

Method 1: Simply Remove Duplicate Rows

The easiest way to remove duplicates is to use a special button on the ribbon designed for this purpose.

Method 2: Removing duplicates in the smart table

Duplicates can be removed from a range of cells by creating a smart table.

This method is the most universal and functional of all described in this article.

Method 3: Apply sorting

This method is not exactly removing duplicates, since sorting only hides duplicate records in the table.

After this, duplicate entries will be hidden. But their display can be turned on at any time by pressing the button again "Filter".

Method 4: Conditional Formatting

You can also find duplicate cells using conditional table formatting. True, you will have to remove them with another tool.

After this, cells with duplicate values ​​will be highlighted. You can then delete these cells manually in the standard way if you wish.

Attention! Searching for duplicates using conditional formatting is performed not for the row as a whole, but for each cell in particular, so it is not suitable for all cases.

Method 5: Applying the Formula

In addition, you can find duplicates by applying a formula using several functions at once. With its help, you can search for duplicates on a specific column. The general appearance of this formula will look like this:

IFERROR(INDEX(column_address,MATCH(0,COUNTIF(column_header_address: duplicate_column_header_address (absolute), column_address;)+IF(COUNTIF(column_address;; column_address;)>1,0,1),0));"")

After these actions in the column "Duplicates" Duplicate values ​​will be displayed.

But this method is still too complicated for most users. In addition, it only involves searching for duplicates, but not deleting them. Therefore, it is recommended to use the simpler and more functional solutions described earlier.

As you can see, Excel has many tools designed to find and remove duplicates. Each of them has its own characteristics. For example, conditional formatting involves searching for duplicates only for each cell separately. In addition, not all tools can not only search, but also remove duplicate values. The most universal option is to create a “smart table”. When using this method, you can configure the search for duplicates as accurately and conveniently as possible. In addition, their removal occurs instantly.