How to Remove / Delete Duplicate Rows in Excel & Google Sheets

This tutorial demonstrates how to remove duplicate rows in Excel and Google Sheets.

 

 

Remove Duplicate Rows

In Excel, you can use the built-in functionality to delete duplicate rows comparing several columns. First, look at the data set below, containing information about product, month, and price.

 

 

As you can see in the picture above, there are multiple prices for the same product and during the same month. For example, the product keyboard in Jan-21 has two prices: $1,000 (in Row 2) and $595 (in Row 8). Again, for Feb-21, there are two prices: $595 (Row 6) and $ 600 (Row 16). To delete duplicate values comparing both fields (product and month) and get a unique price for this combination, follow these steps.

1. Click anywhere in the data range (here, B2:D17) and in the Ribbon, go to Data > Remove Duplicates.

 

 

2. Excel automatically recognizes how the data and headers are formatted, and all columns are checked by default. First, uncheck Product Price, as you want to compare data by product and month, and click OK.

 

 

3. The information message below pops up that eight duplicates are removed and eight unique rows are left.

 

 

Since all products initially had two rows for each month (Jan-21 and Feb-21), the first appearance of a product in Jan-21 and Feb-21 is kept, while the second is deleted.

 

 

Note: You can also use VBA code to delete duplicate rows.

Remove Duplicate Rows in Google Sheets

You can also remove duplicate rows based on one or more columns in Google Sheets.

1. Click anywhere in the data range (B2:D17) and in the Menu, go to Data > Remove duplicates.

 

 

2. Google Sheets takes the whole data range into account. First, check Data has header row to get columns description and uncheck Column D – Product Price under Columns to analyze. Finally, click Remove duplicates.

 

 

3. Like in Excel, you get the pop-up message below that eight duplicate rows were removed, while eight unique rows are kept.

 

 

The final output is the data range with unique combinations of product and month.