How to remove duplicates in excel

Duplicate values in Excel can be a real pain, especially if you are trying to sort or filter your data. Fortunately, there are several ways that you can remove duplicates from an Excel spreadsheet. In this tutorial, we will show you how to use the built-in filtering and sorting tools as well as how to write a simple VBA code to remove duplicate values in Excel.

 

To remove duplicates from your Excel data using the built-in filtering tool, follow these steps:

Open the spreadsheet that contains the duplicate values and make sure all of the columns containing text strings are selected. Click on the Data tab at the top of your screen and select the Advanced option in the Sort & Filter section of this menu. In the Advanced Filter window that appears, select Copy To Another Location and make sure the My Data Has Headers box is checked. Then click on the browse button next to the List Range field and select all of the data in your spreadsheet. Now click on the Unique Records Only checkbox and click OK. This will copy all of the unique values from your selected columns into a new location.

If you want to remove duplicates from your data using a VBA code, follow these steps:

First, open the Visual Basic Editor by pressing Alt + F11 on your keyboard. Next, insert a new module into your project by clicking on Insert > Module in the menu bar at the top of this window. Paste the following code into this new module:

Sub RemoveDuplicates() Dim lastRow As Long Dim i As Long With ActiveSheet lastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row For i = lastRow To 2 Step -1 If Application.WorksheetFunction.CountIf(Range(“A1:A” & i – 1), .Cells(i, “A”)) > 0 Then .Cells(i, “A”).EntireRow.Delete End If Next i End With End Sub

This code will remove all duplicate values from the column where your cursor is currently located. To use this code, simply select the column that contains the duplicates and run the code. This can be a great way to quickly remove duplicates in your data, but you should note that this does not work on sorted or filtered data.

 

 

 

  1. Select the cells you want to check for duplicates.

    Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.

  2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.Highlight cell rules
  3. In the box next to values with, pick the formatting you want to apply to the duplicate values, and then click OK.Duplicate Values dialog box

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

  1. Select the range of cells that has duplicate values you want to remove.

    Tip:  Remove any outlines or subtotals from your data before trying to remove duplicates.

  2. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.Remove DuplicatesFor example, in this worksheet, the January column has price information I want to keep.Duplicate values highlighted

    So, I unchecked January in the Remove Duplicates box.

    Remove duplicates dialog box

 

Hopefully, one of these methods will help you remove duplicate values from your Excel spreadsheet. If you have any questions about these techniques or want to suggest another method for removing duplicates in Excel, feel free to leave us a comment below!