How to remove duplicates in Excel using tables

Excel tables are not ordinary ranges. They are a data set with which you can perform a variety of actions, including the removal of duplicates. It is important to understand that cells that contain the same values ​​at first glance may actually turn out to be different formats. Obviously, Excel might think they are different when in fact they are the same.

To remove duplicate cells in Excel tables, you need to know how this is done. And the easiest way to understand this is on specific cases, which we will do now.

Removing Duplicates with Tables

First you need to learn how to create smart tables. Let’s assume that we have created a document that has a range with duplicate values. Moreover, the formats of these cells differ from each other in the number of decimal places.

There are several ways to eliminate duplicates.

To do this based on the contents of the text column, you need to do the following:

1. Turn a range into a smart table. To do this, simply press the key combination CTRL + T.
   

   

2. Next, just click on the “Remove Duplicates” item, which is located on the “Designer” tab.
   

   

3. To remove duplicates, you must first turn off the check on the fourth column (or, of course, the one that matches your case).
   

   

4. Now rows with identical values ​​have been removed from the table.

It is very important not to forget to turn off the check by column, otherwise the data will not be deleted.

Remove duplicate values ​​in Excel (2007 version)

Suppose we have created a table with three columns. They contain cells with the same readings that need to be removed. The first step is to select the area where there are repeating cells. It is possible to remove a selection of a variety of ranges, including individual columns or an entire table. 

Then we left-click on the “Data” tab, and on it there is a “Delete Duplicates” button.

It may be that the table has a row with headers. In this case, you need to check the corresponding box in the window that opens. In it, you also need to select those columns in which you want to look for duplicate values.

After the OK key is pressed, all lines containing duplicates are removed. 

With this function, you can delete cells that duplicate rows in full. However, if only a part of the columns has been selected, then the rows containing duplicate values ​​will still be removed.

Advanced filter to remove duplicates

There is another method for removing cells containing the same information – an advanced filter. To apply it, you need to select any cell that is within the required range, and then open the “Data” tab. We are interested in the “Advanced” button there.

After it is pressed, a window will open where you should set the option “Copy the result to another location”, and where you want to write the original range, you need to specify it. Also in this window there is a field “Place result in the range”, with which you can set the first cell for the future range. After that, you need to enable the display of only unique values, and then confirm your actions.

Now we have a table with filtered data. It will display only those values ​​that are unique. We did this using the corresponding button in the previous step.

Important! This method does not remove the repetitions completely, but simply hides them.

How to Highlight Duplicates with Conditional Formatting (2007+)

You can also select all existing duplicates and remove them manually. To do this, you can use a feature called conditional formatting. The first step is to select a table where you want to search for duplicate values. To do this, find the “Styles” group on the “Home” tab, and find “Conditional Formatting” there.

Next, you should choose directly the criteria by which the allocation will be carried out. We are interested in the “Duplicate Values” rule.

After that, a window will appear where you can make more flexible settings. 

It is important to bear in mind that in this case only a certain cell of the column will be formatted, not the row. Therefore, there may be a situation where duplicate values ​​in one column will be flooded by Excel even though they are not unique.

You can clearly see this effect in this screenshot.

Pivot tables to detect duplicate values

The PivotTable is another Excel tool that can be used to remove duplicates. Let’s open the table above (the one with three columns and make another one). Let’s call it “Counter”. We will use the unit as the content of the cells. After that, the entire table is highlighted. Next, look for the “Pivot Table” button. You can find it on the “Insert” tab.

The next step is to set up the table. First, the “Row Names” field opens, where three columns are placed. Then, as the contents of the “Values” field, select the column in which the counter will be located.

After that, a table will be generated, where those records that contain a number greater than one will indicate duplicates. In this case, the figure itself will show how many values ​​are repeated. To make it clearer, let’s sort the values ​​by the “Count” column so that all duplicates are together.

Finding and highlighting duplicates with color

To highlight duplicates against the background of other cells in some color, you need to use conditional formatting. This tool has many features, including the ability to set the color for the detected duplicates. 

In one column

Conditional formatting is the easiest way to find where duplicates are in Excel and highlight them. What needs to be done for this?

1. Find that duplicate search area and select it.
   

   

2. Switch your gaze to the Toolbar, and expand the “Home” tab there. After clicking on this button, a set of points appears, and, as it was already clear from the information above, we are interested in the item “Duplicate values”.
   

   

3. Next, a window appears in which you need to select the “Repeating” item and press the OK button.
   

   

The duplicates are now highlighted in red. After that, you just need to remove them, if necessary. 

Multiple columns

If the task is to identify duplicates located in more than one column, then there are no fundamental differences from the standard use of conditional formatting. The only difference is that you need to highlight multiple columns. 

The sequence of actions, in general, is as follows:

1. Select columns to search for duplicates.
2. Expand the Home tab. After that, we find the “Conditional Formatting” item and set the “Duplicate Values” rule as described above. 
3. Next, again select the “Repeating” item in the window that appears, and in the list on the right, select the fill color. After that, click on “OK” and enjoy life.
   

   

Duplicate rows

It is important to understand that there is a huge difference between searching for duplicate cells and rows. Let’s look at it in more detail. Look at these two tables.

A characteristic feature of those tables that were given above is that they contain the same values. This is because in the first example, duplicate cells were searched, and in the second we already see the repetition of lines with information. 

So, what should be done to find duplicate values ​​in series?

1. We create one more column in the right part in relation to the table with the initial information. It writes a formula that displays the combined information from all the cells that make up the row. =A2&B2&C2&D2
2. After that, we will see the information that has been merged.
   

   

3. After that, you should select an additional column (namely, those cells that contain the combined data).
4. Next, go to “Home”, and then again select the item “Duplicate values” in the same way as described above. 
5. Next, a dialog box will appear, where we again select the “Repeating” item, and in the right list we find the color with which the selection will be made. 

After the “OK” button is pressed, the repetitions will be indicated by the color that the user selected in the previous step.

Well, suppose we are faced with the task of selecting those rows that are located in the source range, and not in the auxiliary column? To do this, you need to take the following steps:

1. Similarly to the previous example, we make an auxiliary column, where we write the formula for combining the previous columns. =A2&B2&C2&D2
2. Next, we get all the values ​​contained in the row, specified in the corresponding cells of each of the rows.
   

   

3. After that, we select all the information contained, not including the additional column. In our case, this range is: A2:D15. After that, go to the “Home” tab and select the “Conditional Formatting” item – create a rule (we see that the sequence is slightly different).
   

   

4. Next, we are interested in the item “Use a formula to determine formatted cells”, after which we insert such a formula into the “Format values ​​for which the following formula is true” field. =COUNTIF($E$2:$E$15,$E2)>1
   

   

For duplicate lines, be sure to set the correct format. Using the above formula, you can check the range for duplicates and highlight them with a user-defined color in the table.

Other ways to remove duplicates in Excel

So, let’s sum up some things about how to remove duplicates in Excel in different situations. Let’s start with repetitions in one column.

Removing duplicates in one column

If all the necessary information is in one column, and you want to remove duplicate data, then you need to follow these steps:

1. Select the desired range. 
2. Expand the “Data” tab on the Toolbar and find the “Delete Duplicates” button.
   

   

3. After that, a window will appear where a checkbox is placed next to the item “My data contains headers” if there is a header in the corresponding range. In addition, you need to look at the “Columns” menu to make sure that the checkmark is next to the desired column.
   

   

After the “OK” button is pressed, the system will delete all copies in the column.

Removing duplicates in multiple columns

Let’s imagine that we have a table in which the names, the amount of sales and the region are the same, but the dates are different.

This can happen if an error was made when entering information into the table. If you need to remove such repetitions, then you need to do the following:

1. Highlight information in a table. 
2. Expand the “Data” tab, there find a part of the tools indicated by the heading “Working with data”, and then click on the familiar “Delete Duplicates” button. Further actions are carried out similarly, but if you do not need to check the box next to the column with the date.
   

   

After that, all duplicates will be removed by the system. 

Removing duplicate data rows

To automatically remove all repetitions of lines, you need to perform the following actions:

1. Select the part of the cells where you want to remove duplicates. 
2. After that, find the “Remove Duplicates” function and follow the same steps as described above. But in this case, you need to make sure that all the checkboxes are checked. Then Excel will automatically check all the information for duplicate information.
   

   

After clicking OK, we get the result we need.

Conclusions

Thus, in the arsenal of Excel there are a huge number of ways in which you can hide duplicates. Not in all situations it is necessary to remove them, often it is enough just to hide them or mark them with a color.