#REMOVE DUPLICATE ROWS IN EXCEL HOW TO#

Now, click on the filter button in cell D1 and click Select All (0).įinally, the data range is filtered by Column D (0 values).Īll rows with zeros in Columns D and E are unique values in the range rows with ones in those columns (duplicate values) are deleted.

Select all filtered cells (4–13) and right-click anywhere in the selected area.

In the Go To Special window, select Visible cells only, and click OK.

To delete all filtered rows, select all visible rows (4–13), and in the Ribbon, go to Home > Find & Select > Go To Special.Now check the filter for Column E to be sure that only the value 1 is filtered.Since duplicates have ones in both Columns D and E, display and delete only values with ones in Columns D and E.Ĭlick on the filter button in Column D, uncheck 0, and click OK. To delete duplicate rows, filter out zeros from Columns B and C. Now, click anywhere in Column B with data (B2), and in the Ribbon, go to Data > Filter. A one means that there are duplicate values and that row should be deleted. A zero means that a value from Columns B or C is not repeating in the data range and should be kept. As a result of Step 2, there is a zero or a one in Columns D and E for each value in Columns B and C.Position the cursor in the bottom-right corner of cell D2 until the black cross appears and drag it through the end of the data range (D13).This formula outputs two values (in D2 and E2), one for each First Name and one for each Last Name. Therefore, the final result of the formula is zero if the COUNTIF returns one, and one if it returns anything else. If the COUNTIF returns one that means that the value is unique in the range, while all other number means that the value is appearing more than once. The IF Function only checks the result of the COUNTIF. The COUNTIF Function checks for every cell if it is in some other cell in a range. Columns B and C have a list of first and last names containing duplicate values.įollow these steps to remove all duplicate rows and keep only those with unique values: This can be achieved using a combination of the IF and COUNTIF Functions and the Go To Special feature. Generally, when you remove duplicates in Excel, the first occurrence of each duplicate is kept, and all others are deleted.Īnother valuable task is to remove both (or all) duplicate rows.

#REMOVE DUPLICATE ROWS IN EXCEL HOW TO#

This tutorial demonstrates how to identify duplicate rows and remove both of them in Excel and Google Sheets.

Remove duplicate rows in excel