See all How-To Articles

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 initial data

 

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.

 

remove duplicate rows initial data

 

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.

 

remove duplicate rows 1

 

  1. 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.

 

remove duplicate rows 2

 

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

 

remove duplicate rows 3

 

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.

 

remove duplicate rows final data

 

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.

 

google sheets remove duplicates

 

  1. 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.

 

google sheets remove duplicates 2

 

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

 

google sheets remove duplicates 3

 

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

 

google sheets remove duplicates final data