How to Remove Duplicates in Excel Sheet (7 Methods)

Duplicates in Excel worksheets are a big issue for the users. It is very tiresome work to find duplicates and remove them. In this tutorial, we will show some easy methods about how to remove duplicates from Excel sheet.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


7 Methods to Remove Duplicates in Excel Sheet

Before removing duplicates, we need to find the duplicates first. Then we will remove those duplicates. We will explain all the processes of removing duplicates in the following methods.


1. Conditional Formatting to Remove Duplicates in Excel Sheet

By conditional formatting, we will identify the duplicate first. Then remove those duplicates. The following data set will be considered to find and remove duplicates.

Step 1:

  • First, select all cells containing data from our dataset.

Step 2:

  • In the Home tab, select the Conditional Formatting tool from the Styles group.
  • Choose Highlight Cells Rules from the given options.
  • Click on Duplicate Values finally.

Conditional Formatting to Remove Duplicates in Excel Sheet

Step 3:

  • A new dialog box appears. Choose Duplicate values in the box.
  • You can also change the highlighting color. Then press OK.

Conditional Formatting to Remove Duplicates in Excel Sheet

We find two duplicate rows in our data. Look at the following image.

Step 4:

  • Now, enable the filter by pressing Ctrl+Shift+L.

Step 5:

  • Apply the Filter option now. Choose the Filter by Color option.
  • Select the default color as we choose in the previous step.

Conditional Formatting to Remove Duplicates in Excel Sheet

Now, the identical rows are showing only.

Step 6:

  • Select any of the rows. If we have more than two duplicate rows, then we must select all the rows leaving one single row.

Step 7:

  • Now, press the right button of the mouse.
  • Choose the Delete Row option.

Conditional Formatting to Remove Duplicates in Excel Sheet

Step 8:

  • A pop-up will show for permission. Press OK there.

One row remains in the data set. Look at the image below.

Step 9:

  • Now, disable the Filter feature again by pressing Ctrl+Shift+L.

Conditional Formatting to Remove Duplicates in Excel Sheet

We removed all the duplicates from our data set.

Read More: How to Remove Duplicates Based on Criteria in Excel (4 Methods)


2. Remove Duplicate Values Using the Remove Duplicates Command

Using the Remove Duplicates command, we can remove duplicate values easily. We get this tool in the Data tab.

Step 1:

  • First, select all the cells from where to remove replicates.

Remove Duplicate Values Using the Remove Duplicates Command

Step 2:

  • Go to the Data tab now.
  • Choose the Remove Duplicates command from the Data Tolls group.

Remove Duplicate Values Using the Remove Duplicates Command

Step 3:

  • Remove Duplicates dialog box will appear.
  • Select the Columns from the list. We select both columns.

Remove Duplicate Values Using the Remove Duplicates Command

Step 4:

  • Now, click OK.
  • A pop-up will show mentioning the duplicate value removing and unique values remaining. Press OK there.

Remove Duplicate Values Using the Remove Duplicates Command

Look at the image below.

Replicated values are removed from both columns.

We can also remove replicated values from a single column.

Step 5:

  • Again, go to the Remove Duplicates window.
  • Choose the Fruit column and press OK.

Remove Duplicate Values Using the Remove Duplicates Command

Step 6:

  • Press OK on the pop-up.

2 duplicates and 7 unique values are found here.

Notice the Fruit column. No duplicate values are present here. But in the Customer column, duplicate values may exist.

Read More: Fix: Excel Remove Duplicates Not Working (3 Solutions)


3. Apply Excel Advanced Filters to Withdraw Duplicates

The Advanced filter is another cool path to remove duplicates from Excel sheets.

Step 1:

  • Select the cells first.

Step 2:

  • Go to the Data tab.
  • Choose Advanced from the Sort & Filter group.

Apply Excel Advanced Filters to Withdraw Duplicates

A new dialog box will appear. In the List Range, our selected range is shown. We have two options. One is to remove the duplicate and show it in the present list. And the other is copying the unique data to a new location.

Step 3:

  • Choose Filter the list, in-place option.
  • Put a tick mark on the Unique records only.
  • Then press OK.

Apply Excel Advanced Filters to Withdraw Duplicates

Look at the data set.

The duplicate data is not showing here. It hides the duplicate rows.

But when we copy data in a new location, the duplicate data is purely removed from the list.

Step 4:

  • Again, go to the Advanced Filter option.
  • Select Copy to another location option.
  • Choose the new location on the Copy to box.
  • Also, tick on the Unique records only.
  • Then, press OK.

Apply Excel Advanced Filters to Withdraw Duplicates

Notice the following image.

Apply Excel Advanced Filters to Withdraw Duplicates

All unique data are copied to Cell E5 and the rest.

Duplicate data was absolutely removed from here.

Read More: Excel Formula to Automatically Remove Duplicates (3 Quick Methods)


4. Extract Duplicates Using Excel Formula

We can use a formula to extract duplicates from Excel sheets.

Step 1:

  • We add both columns in the Combine column.

Step 2:

  • Go to Cell D5 and apply the simple formula using the ampercent sign(&).
=B5&C5

Extract Duplicates Using Excel Formula

Step 3:

  • Then, press Enter.

See, data from both columns are combined.

We can apply another TEXJOIN function to perform this.

Step 4:

=TEXTJOIN("",FALSE,B5,C5)

Extract Duplicates Using Excel Formula

Step 5:

  • Again, press Enter.

Step 6:

  • Pull the Fill Handle icon towards the last cell.

Extract Duplicates Using Excel Formula

Step 7:

  • Now, add a column to count the number of objects in the Combine column using the COUNTIF function.
  • Put the formula below on Cell E5.
=COUNTIFS($D$5:D5,D5)

Extract Duplicates Using Excel Formula

Step 8:

  • Press the Enter button.

Step 9:

  • Drag the Fill Handle icon again.

Extract Duplicates Using Excel Formula

In the count column, we counted the object that how many times found on the Combine column. If the value is 1 means that is unique. But it that is 2 or more means that object is repeated.

Step 10:

  • Now, press Ctrl+Shift+L and enable the filter mood.

Step 11:

  • Now, click on the arrow of the Count column.
  • Leave 1 from the filter list.
  • Then press OK.

Extract Duplicates Using Excel Formula

Now, the presented values are the duplicates values.

Step 12:

  • Now, delete the duplicate values and disable the Filter mood by pressing Ctrl+Shift+L.

Extract Duplicates Using Excel Formula

Here, only the unique values are presented.

Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)


Similar Readings


5. Delete Duplicates Using the Pivot Table

The Pivot table is an interesting tool. We can delete duplicates using the Pivot Table also.

Step 1:

  • Go to the Insert tab first.
  • Select PivotTable from the Table group.
  • Click on From Table/Range option from the list.

Delete Duplicates Using the Pivot Table from Excel Sheet

A new dialog box of PivotTable input options will appear.

Step 2:

  • In the Table/Range, select our desired data range.
  • We can place the newly from PivotTable in a new sheet or within this sheet. We select Existing Worksheet and put the location here.

Delete Duplicates Using the Pivot Table from Excel Sheet

Step 3:

  • Then, press OK.

We can see PivotTable1 with its properties.

Step 4:

  • Put a tick on Customer and Fruit options and set them on the Rows field.

Delete Duplicates Using the Pivot Table from Excel Sheet

Step 5:

  • Now, go to the Design tab.
  • Then click on Report Layout from the Layout group.

Delete Duplicates Using the Pivot Table from Excel Sheet

Step 6:

  • From the list first, click on Show in Tabular Form.
  • Again repeat this process and click on Repeat All Item Labels.

Step 7:

  • Now, go to Subtotals in the Layout group.
  • Choose the Do Not Show Subtotals option.

Delete Duplicates Using the Pivot Table from Excel Sheet

Look at the PivotTable1 now.

Now, in PivotTable1 no duplicate values were presented.

Read More: How to Remove Duplicate Rows in Excel Table


6. Eliminate Duplicates Using the Power Query from Excel Sheet

The Power Query is a Data tool by which we can remove duplicates. Power Query has a default option to remove duplicates.

Step 1:

  • Go to the Data tab first.
  • Choose From Table/Range from the Get and Transform Data group.

Eliminate Duplicates Using the Power Query From Excel Sheet

Step 2:

  • Input the cell reference in the Create Table window.
  • Tick on My table has headers option.
  • Then press OK.

Eliminate Duplicates Using the Power Query From Excel Sheet

Now, a new window will appear with the data in Power Query mood.

Step 3:

  • Go to the header of the Fruit column.
  • Click the right button of the mouse.
  • Choose Remove Duplicates from the list.

Eliminate Duplicates Using the Power Query From Excel Sheet

See the following image.

Duplicate data from the Fruit column has been removed.

We can remove duplicate data from the whole table also.

Step 4:

  • Go to the left upper corner of the table.
  • Press the right button of the mouse.
  • Choose Remove Duplicates.

Eliminate Duplicates Using the Power Query From Excel Sheet

Now, duplicate data from the whole table has been removed.

Step 5:

  • We have another option to remove duplicates in Power Query.
  • Go to the Home tab of the Power Query.
  • Click on the Remove Rows tool.
  • Now, select Remove Duplicates from the list.

Eliminate Duplicates Using the Power Query From Excel Sheet

We have the facility to remove duplicates from the single column or from the whole table in Power Query.


7. Apply Excel VBA Macro to Remove Duplicates

We can remove the duplicates with a simple VBA code.

Step 1:

  • Go to the Developer mode first.
  • Click on Record Macro.
  • Put Remove_Duplicate in the Macro name box.
  • Then press OK.

Apply Excel VBA Macro to Remove Duplicates

Step 2:

  • Now, press on Macros.
  • Choose Remove_Duplicates and then press the Step Into option.

Step 3:

  • Put the VBA code on the command module.
Sub Remove_Duplicate()
Range("B4:C13").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Apply Excel VBA Macro to Remove Duplicates

Here, Columns:=1 defines duplicates that will be removed based on the 1st column of our given range and Header:=xlYes defines we have header cell.

Step 4:

  • Click on the marked box to run the code or press the F5.

Now, look at the data set.

Apply Excel VBA Macro to Remove Duplicates

Duplicated data are removed from the data set.

Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)


Things to Remember

  • When applying the Advanced Filter method, carefully input the cell references.
  • In PivotTable take the decision where to place the table. Two options: Existing worksheet or new worksheet.
  • You can make the formula with other functions too in removing duplicates. We showed two functions here.

Conclusion

In this article, we described how to find and remove duplicates from Excel sheet. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo