How to Remove Duplicates in Google Sheets

Google Sheets, part of Google Workspace, is a cloud-based spreadsheet tool widely used for organizing and analyzing data. When working with large datasets, duplicate entries can reduce accuracy and clarity, making data cleanup essential.

Methods to Remove Duplicates in Google Sheets

Google Sheets offers two primary methods to manage duplicates:

1. Data Cleanup: Permanently removes duplicate rows from your dataset.
2. UNIQUE Function: Creates a dynamic table of unique values without altering the original data.

Below, we explore both methods in detail with practical examples.

Method 1: Remove Duplicates Using Data Cleanup

Data cleanup in Google Sheets refers to the process of organizing, refining, and standardizing data with spreadsheets to improve its accuracy, consistency, and usability. Data Cleanup involves tasks like removing duplicates, correcting errors, formatting the data, and dealing with empty cells. This process is important to ensure that data is in an appropriate state and to maintain the quality of data.

As you can see there are two duplicates rows in the table below - Finland And Sweden.

1. Deleting Duplicates from all rows and columns

It means it will delete a row if both its country and happiness index value is same as previous.

Step 1: Select Data Range

Open your sheet from which you want to remove duplicates and select the cells from which you want to remove duplicates. In this case we have selected the entire table.

Step 2: Select Data Cleanup And Remove Duplicates

Go to Data menu at the top as shown on your desktop screen. As you click on data tab a drop down menu will appear with options and select Data Clean-up option from those and then select Remove Duplicates.

Data -> Data Clean-up> Remove Duplicates

Step 3: Configure Data Cleanup

In the pop up box, select {Data has Header Row} if your selection contains header rows like country and happiness index. Then Select {Select All} as we have to remove duplicates from all.

Step 4: Remove Duplicates

Click on Remove Duplicates and it will remove duplicates from our table. It will show a message indicating how many rows have been removed. In this case there were 2 duplicate rows that have been removed.

2. Deleting Duplicates Based on Particular Column

In the previous step if we have a duplicate country but their happiness index is different then it will not be removed,

For example - here we have a duplicate value Finland but its Happiness Column is different so it will not be removed .In order to remove it we have to remove duplicate based on only the Country column.

Step 1: Select Data Range

Select all the cells from which you want to delete duplicates in your document.

Step 2: Select Data Cleanup

Select Data-> Data Clean-up -> Remove Duplicates

Step 3: Configure Remove Duplicates

Select the column to analyse for duplicates. In this case we have only selected Column A (Country)

Step 4: Remove Duplicates

Click on Remove Duplicates. It will remove the duplicate cell Finland and its happiness index .

How to Remove Duplicates Using Google Sheet Function?

• Functions in Google Sheets are built-in formulas that perform specific tasks and calculations.
• They are used to manipulate data, perform mathematical operations, analyse information and automate tasks in Google Sheets.
• Google sheet has a UNIQUE function which we can use to create a separate table which is dynamic (means it will automatically change when we change the original data) based on the columns.
• However it will not remove the duplicates but will show a different table which has only Unique Values.

Step 1: Select the Starting Cell

Select a cell which will be the starting point of our new table. Select a cell and put your cursor on the selected cell. In this case cell D3 has been selected.

Step 2: Enter The Unique Formula

In the cell write " =UNIQUE(COLUMN_START: COLUMN_END) ". Replace COLUMN_START and COLUMN_END with your column start and end.

In this case I wrote =UNIQUE(A2:B10)

Step 3: Press Enter

After writing the formula you need to press the Enter key and now you'll see a separate table has been created from the cell you choose which does not contains any duplicates.