How to Remove Duplicates in Google Sheets

Google Sheets as a part of Google Workspace, is one of the popular cloud-based spreadsheet applications 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 internet access.

However as we work with large data sets, duplicate data can often appear which can lead to confusion, incorrect results, and cluttered sheets. So in this article, we’ll learn How to Remove Duplicates from our 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. Let’s discuss both ways one by one.

1. Using Data Cleanup.
2. Using the Google Sheets function.

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 .

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.

Press the Enter Key to Remove Duplicates

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.

How do I quickly delete duplicates in Google Sheets?

To quickly delete duplicates in Google Sheets:

• Select the range of cells you want to check for duplicates.
• Click on Data in the menu.
• Choose Data cleanup and then Remove duplicates.
• Confirm the columns to analyze and click Remove duplicates.

This will delete any duplicate rows, keeping only unique entries.

Is there a way to find duplicates in Google Sheets?

Yes, to find duplicates in Google Sheets:

1. Select the range of cells you want to check.
2. Click on Format in the menu.
3. Choose Conditional formatting.
4. Under Format cells if, select Custom formula is.
5. Enter the formula =COUNTIF(A:A, A1) > 1 (adjust column reference as needed).
6. Choose a formatting style and click Done.