How to Remove Duplicates in Google Sheets

Google Sheets as a part of Google Workspace, is one of the popular cloud based spreadsheet application widely used for data management and analysis. It allows users to create and edit data on spreadsheets and enables us to share spreadsheets online which can be accessible from any device with an internet access.

However as we work with large data sets , duplicates data can often appear which can lead to confusions , incorrect results and cluttered sheets . So in this article we’ll learn How to Remove Duplicates from our Google Sheets.

How to Remove Duplicates In Google Sheets

Google Sheets offers simple yet powerful features to help us remove duplicates efficiently and avoid cluttering .There are two ways to remove duplicates from Google Sheets efficiently. Lets discuss both ways one by one.

1. Using Data Cleanup.

2. Using Google Sheets function.

Remove Duplicates In Google Sheets

Method 1: Using Data Cleanup

Data cleanup in Google Sheets refers to the process of organising, refining and standardising of data with the spreadsheets to improve its accuracy, consistency and its 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 appropriate state and for maintaining the quality of data .

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

Google Sheet (list of countries)

i) 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.

Google Sheet

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

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.

Google Sheet

ii) 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.

How to Remove Duplicates From Google Sheet

STEP 1: Select Data Range

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

Selecting the data

STEP 2: Select Data Cleanup

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

Remove Duplicates

STEP 3: Configure Remove Duplicates

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

How to Remove Duplicates From Google Sheet

STEP 4: Remove Duplicates

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

Method 2: 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.

How to remove Duplicates From Google Sheet

Conclusion

By following the mentioned steps you’ll be able to remove the duplicates in your Google Sheets , improve the quality of data and maintain a well organised spreadsheet. Do remember that the steps in this article may slightly differ depending on the version of the Google Sheet you are using but the overall and general process will remain the same.

FAQ’s

1: What are Google Sheet Functions?

Answer: Google sheets functions are the functions which are used to create formulas in order to manipulate a data .For example Average Function is used to calculate the Average between cells.

2: List some common functions used in Google Sheet?

Answer. Some common functions used in Google Sheets are-

• SUM
• CONCAT
• UNIQUE
• AVERAGE
• MIN
• MAX

3. What is data cleanup ?

Answer. Data cleanup in Google Sheets refers to the process of organising, refining and standardising of data with the spreadsheets to improve its accuracy, consistency and its 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 appropriate state and for maintaining the quality of data .