Step 1: Select the cells

Click and drag your mouse to select the range of cells that contains the data from which you want to remove duplicates.

Step 2: Open the Remove duplicates dialog box

In the top menu bar, click on “Data.” From the drop-down menu, choose “Data cleanup,” then select “Remove duplicates.”

Step 3: Configure the removal of duplicates

In the “Remove duplicates” dialog box that appears, you will find checkboxes next to each column in your selected range. Google Sheets will consider the combination of values in these columns to determine duplicates in your spreadsheet.

Check the "Data has header row" box if your selected dataset has column names so that Google ignores those rows when removing duplicates. Once you’ve configured the removal of duplicates, click on the "Remove duplicates" button at the bottom of the dialog box.

Step 4: Click on “OK”

Click on “OK” in the notification that appears showing you how many duplicates were found and removed to delete the duplicate rows.

Step 5: The duplicated data has been removed!

You’ve just finished removing all the duplicates with the remove duplicates tool. Now, all your data points are unique.

Step 1: Select data range

Select the row you want to check for duplicates.

Step 2: Create a new column

Create a new column next to it. In this example, to find duplicates in column A, we’ll create column B next to it.

Step 3: Apply the formula

In a cell of your new column—such as the B2 in our example—insert the formula =COUNTIF(A$2:A2, A2)>1 to highlight the duplicates in column A.

You will need to adjust the formula based on your needs, but here’s what the formula means:

• COUNTIF(A$2:A2, A2): This part of the formula counts the number of times the value in cell A2 appears within the range of cells from A$2 (the first row in the range, anchored) to the current row A2 (the row that the formula is in). So, it checks how many times the value in A2 occurs within the range A$2:A2.
• >1: This part of the formula checks if the count obtained in step 1 is greater than 1. If it is, it means that the value in cell A2 appears more than once in the range from A$2 to the current row A2.

Step 4: Copy and Paste the Formula

Once you've entered the formula in the cell of the new column, drag down the fill handle, which is a small square at the bottom-right corner of the selected cell, with your mouse to copy the formula to the cells which you want to remove duplicates.

When a value shows up for the first time, the count will be 1, so the formula will return “False.” But when the value shows up a second time, the count will be 2, so the formula will return “TRUE.”

Step 5: Remove the duplicates

In order to remove the duplicates in your Google Sheets spreadsheet, you will need to do some ordering of the rows, so that the values that are duplicated are together, making them easier to delete.

Step 5.1: Select your new column

Select your new column, the one with the “TRUE” and “False” values.

Step 5.2: Sort the data

Now, you need to right-click on the header of the column and click on “Sort sheet A-Z.” This will separate the “TRUE” from the “FALSE” values.

Step 5.3: Select the duplicated values and delete

Now you know that the cells on the left of the ones with the value “TRUE” are duplicates. This means you can highlight the cells in the original column—column A, in our example—and delete those.

You can then delete the column you created to apply the COUNTIF formula.

Step 1: Select data range

Select the data range from which you want to remove the duplicates.

Step 2: Open Google Workspace Marketplace

Go to the "Extensions" menu at the top, choose "Add-ons," and then click on “Manage add-ons.

Step 3: Choose an add-on extension

In the Google Workspace Marketplace, search for “ remove duplicates for sheets” to see all the add-ons you can choose from. In this example, we will use Power Tools, created by Ablebits.

Step 4: Install the add-on extension

Once you’ve chosen an add-on extension, click on it to install it. You will need to select your Google Account, follow the prompts to grant necessary permissions, and then click on “Done.”

Step 5: Open the add-on’s Interface

In the menu bar, click on “Extensions,” then “Add-ons,” and finally click on “View document add-ons” to open the add-ons interface. Select the option for the add-on you installed.

Step 6: Click on “Dedupe & Compare”

If you installed the Power Tool add-on, then on the Power Tool sidebar, click on “Dedupe & compare.”

Step 7: Click on “Remove duplicate cells”

Next, select the option for the duplicates you want to remove. Power Tools gives you the option to remove duplicate rows and duplicate cells.

In this example, we will remove duplicate cells. If that’s your goal, click on the “Remove duplicate cells” option.

Step 8: Select range

In the dialog box that appears, confirm the range you want to check duplicate cells in. If they are not the correct ones, use the grid icon to manually select your desired range, then click on “Next >.”

Step 9: Choose the type of cells you want to find

Click on the drop-down arrow and select “Duplicates,” if it doesn't automatically appear, then click on “Next >.”

Step 10: Choose what to do with the found cells

Select the “Clear values” option, and then click on “Finish >.”

Step 11: The duplicates have been removed

The duplicate cells in your sheet in Google Sheets have now been removed.

Step 1: Select your data range

Select the data on your sheet in Google Sheets from where you want to remove duplicate data.

Step 2: Open the Create a pivot table dialog box

On the top menu, click on “Insert,” and then click on “Pivot Table” on the drop-down menu.

Step 3: Create a pivot table

A dialogue box will appear, and you should see the data range you selected and choose where you want to place your pivot table. You can place it on your existing sheet or a new sheet.

Step 4: Design your pivot table

Now you need to design your pivot table in a way that will detect the duplicates. In order to do so, follow the below steps.

Step 4.1: Add the rows to the pivot table

On the pivot table sidebar, you can add the rows to your pivot table, by clicking on “Add” next to “Rows,” where you can add the rows from which you want to delete duplicate data.

Step 4.2: Add the columns to the pivot table

Now, you need to add the columns to the pivot table. To do so, click on “Add” next to “Columns.”

Step 4.3: Set up the values of your pivot table

In order to set up the pivot table to count the duplicates, so that you can then remove them, click on “Add” next to “Values.”

Step 5: Identify your duplicate

On the pivot table, you will see all your duplicates. The value represents the number of times the items have been duplicated. For example, the item blinder has been duplicated 6 times.

Step 6: Remove duplicates

Return to your original data and select the rows corresponding to the duplicate value you found in the pivot table, such as “Binder” in our example. After you’ve selected the row, right-click and click on “Delete row.”

Step 7: The duplicates have been removed

Once you’ve finished removing all the rows with duplicates, you can be assured that your dataset contains only unique values. See in the before and after image

Step 1: Open the script editor

On the top menu, click on “Extensions,” then click on “Apps Script” on the drop-down menu.

Step 2: Code the script to check for duplicates

You can copy the below script to help you check for duplicate entries in your Google Sheets.

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  var seen = {};

  data.forEach(function(row) {
    var key = row[0]; // Adjust index for the column you want to check for duplicates
    if (!seen[key]) {
      seen[key] = true;
      newData.push(row);
    }
  });

  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Step 3: Customize the script

You should customize your script by replacing “[0]” with the index column you want to check for duplicates. Index columns refer to the column on the spreadsheet that provides a unique label for each row of data. In a spreadsheet, the first column is marked with a [0], [1] for the second column, and so on.

You can now adjust the script to handle different columns when needed. In our case, we want to remove duplicates on column D. Since it’s the fourth from the beginning, it will be marked with a [3] (the fourth column, as indexing starts from 0) on the script.

Go ahead and replace the [0] with the [3] on your script.

Step 4: Run the script

Save your script and click on the Run button at the top to run the script to remove duplicates.

Step 5: Click on “Advanced”

Google will ask for permission to access and change your spreadsheet. In order to grant the necessary permission, click on “Advanced.”

Step 6: Click on “Unsafe”

After you’ve clicked on “Advanced,” click on “Unsafe.” This will take you to the Apps Script editor, where you will see your duplicate removal function has been executed.

Step 7: The duplicates have been removed

After running the script, it will remove all duplicated rows based on the column you specified in the code.

‍