4 Ways to Remove Duplicates in Google Sheets

Rate this post
4 Ways to Remove Duplicates in Google Sheets

Your Google Sheet, like any other part of your life, may get cluttered and need a thorough cleaning every now and again. Duplicate information in various spots across your spreadsheet might be one of the most aggravating concerns.

Here are some basic strategies for locating and removing the unwanted duplicate data from your Google Sheet.

The Remove duplicates tool is the most powerful and practical way to remove duplicates in Google Sheets, and it should be your first port of call. We picked a small contact list as an example since it is often the worst offender for duplicate data.

To use the Eliminate Duplicates tool to remove duplicates from your sheet, follow these steps:

  1. Open your Google Sheet.
  2. Select the area within the sheet you wish to remove duplicates from. You can quickly do this by clicking and dragging your mouse from the top-left cell to the bottom-right cell of your list highlighting all of the cells you want to scan in the duplicate removal process.
  3. Select Data from the menu bar.
  4. Select Remove duplicates from the menu options.
  5. You’ll be asked to choose which columns should be checked for duplication. You may want to delete duplicates if the rows are completely identical, or you may want to choose a single column, such as an address or name, regardless of what data is in the other columns.
  6. Choose Remove duplicates. You will then be shown a summary report detailing how many duplicates were deleted.

2. Removing Duplicates Using Formulas

There are various formulae in Google Sheets that you can use to help you locate and remove duplicate data, some more sophisticated than others. Here are the simplest ways to use formulae for duplication elimination.

  Speech Services by Google Won’t Stop Downloading? 7 Ways to Fix It

Using the UNIQUE Formula

This function removes duplicates from the data range you provide. It compares each row of data and eliminates any duplicate rows. It’s fairly simple to build since it uses a single formula with a single argument—the range from which you wish to delete duplicates.

Use the UNIQUE formula by following these steps:

  1. Open your Google Sheet.
  2. Select a blank cell.
  3. Enter the formula =UNIQUE(A2:D9) to choose the cells you want.
  4. Enter or click away on your keyboard. Duplicates will now be deleted from the second table.

Using the COUNTIF Formula

This approach identifies duplicates in your dataset first. When a value appears for the first time, the count is 1, and the formula result is false. However, if the value appears a second time, the count will be 2, and the formula result will be TRUE.

The benefit of this technique is that it allows you to evaluate the duplicated items before removing them.

To use the COUNTIF formula to eliminate duplicates from your spreadsheet, follow these steps:

  1. Open your Google Sheet.
  2. Create a new column next to the data column you wish to check for duplicates by selecting Insert left from the menu.
  3. To indicate duplicates, enter =COUNTIF(Range, Criteria)>1 in the top cell of the new column you generated. The formula to check for duplicate last names in our case would be =COUNTIF(B$2:B2,B2)>1. The range is B$2:B2, and the $ symbol keeps the range locked to the current row even as you replicate the formula along column C. So, this formula will check for duplicates in the current row all the way up to the top.
  Google Home Mini vs. Google Nest Mini: What Are the Differences?

3. Removing Duplicates Using Pivot Tables

Pivot Tables may be quite handy for looking for duplicates in your Google Sheet. It’s incredibly versatile and quick to use, so it’s typically a good place to start if you’re unclear if your data contains any duplicates.

To locate and eliminate duplicates from your sheet, follow these steps:

  1. Open your Google Sheet.
  2. Highlight your dataset.
  3. Select Pivot table from the Data menu.
  4. Select whether you want to create this new table in a new sheet or an existing sheet. To the right of your sheet, the Pivot Table editor will appear.
  5. Select Rows and the Column for which you wish to look for duplicates (e.g. Last Name).
  6. Choose VALUES, then the same column as before, and make sure it’s set to summarize by COUNT or COUNTA.

You can now quickly find these duplicate values in your original sheet and determine how to continue. As you can see, this strategy is ideal for searching for duplicates and investigating them further.

Related: How to Create Pivot Tables in Google Sheets

4. Removing Duplicates With Conditional Formatting

This approach consists of two steps: first, highlight your duplicates, and then remove the highlighted duplicates, enabling you to view your duplicate before eliminating it.

Follow these steps to use conditional formatting to eliminate duplicates from your spreadsheet:

  1. Open your Google Sheet.
  2. Choose the data from which you want to eliminate duplicates.
  3. Select Conditional formatting for the sidebar under Format.
  4. Select Custom formula from the Format cells if menu.
  5. Input the formula: =COUNTIF($B$2:$B2,B2)>1. This formula will check the specified column for duplicates for you.
  6. Formatting should be set to emphasize duplicate data.
  7. You may change your formula to =COUNTIF($B$2:$B2,$B2)>1 if you want your results to highlight the whole row.
  8. Duplicate deletion is optional, however it is now simple. Select the Filter menu option after selecting the data to be sorted.
  9. To sort a column by color, click the filter icon next to the header.
  10. Pick Sort by color then Fill Color from the dropdown menu, and then select the fill color you want to display on top.
  11. Select the block of duplicates and delete it.
  How to Highlight Duplicates in Google Sheets

Related: How to Sort Columns Like a Pro in Google Sheets

Other ways to remove duplicates in Google Sheets include utilizing a Google Apps Script and buying an add-on from Google’s Workspace Marketplace. If you aren’t receiving the desired outcomes, these are worth investigating.

Using Apps Script offers you the benefit of being able to execute the script whenever you wish. There are also add-on programs available to check for duplication on a regular basis.

Get a Clear View of Your Data

These techniques will maintain your spreadsheet clean of duplication, provide you with a clear perspective of your data, and save you time. Knowing these fast and simple techniques to delete duplicates will perhaps help your life seem a little more ordered as well.

Google is always improving its software, so be sure to remain up to speed on the newest updates.

You are looking for information, articles, knowledge about the topic 4 Ways to Remove Duplicates in Google Sheets on internet, you do not find the information you need! Here are the best content compiled and compiled by the achindutemple.org team, along with other related topics such as: Google.

Similar Posts