how to remove duplicates in google sheets

You can remove duplicates in Google Sheets in a few different ways, depending on whether you want to delete them in-place or just get a unique list.

Quick Scoop

Here’s the fast, no-nonsense way to handle duplicates in Google Sheets:

1. One-click “Remove duplicates” (in-place delete)

Use this when you want duplicates gone from the original data.

1. Select the range that may contain duplicates (for example A1:D500).

2. In the top menu, go to Data → Data cleanup → Remove duplicates.

3. In the popup:
   • Check Data has header row if your first row is column titles.

 * Tick the columns to use for detecting duplicates (all selected = “duplicate row” must match across all columns).

4. Click Remove duplicates. Google Sheets will tell you how many duplicates were removed and how many unique rows remain.

This is the simplest “I just want them gone” method.

2. Using the UNIQUE function (keep original, create clean list)

Use this when you want a separate list of unique values and prefer formulas.

• For one column (say column A):

  gs
  
  =UNIQUE(A2:A)
  

This returns each distinct value once in the new column, ignoring duplicates.

• For multiple columns (treat entire rows as duplicates):

  gs
  
  =UNIQUE(A2:D)
  

Each unique row from A2:D will appear only once in the result.

Because this is a formula, if new data is added to the source range, the unique list updates automatically.

3. Find duplicates first, then delete (COUNTIF or conditional

formatting)

If you’re nervous about deleting things blindly, you can flag duplicates before removing them. A. Mark duplicates with a helper column (COUNTIF)

1. Suppose your data is in A2:A. In B2, enter:

   gs
   
   =COUNTIF($A$2:$A2, A2) > 1
   

2. Fill the formula down. Rows that are duplicates will show TRUE in column B.

3. Sort or filter by column B and delete the rows marked TRUE.

B. Highlight duplicates with conditional formatting

1. Select the range (e.g. A2:A1000).

2. Click Format → Conditional formatting.

3. Under “Format cells if”, choose Custom formula is.

4. Use a formula like:

   gs
   
   =COUNTIF($A$2:$A,$A2) > 1
   

5. Choose a fill color, click Done. All duplicates in that range will be highlighted so you can inspect and manually delete if needed.

4. More advanced options (QUERY, add-ons, scripts)

For bigger or more complex sheets, there are a few power-user options:

• QUERY function to return grouped, de-duplicated data, for example:

  gs
  
  =QUERY(A2:D, "select A,B,C,D group by A,B,C,D", 1)
  

This groups identical rows so each appears once in the result.

• Add-ons like “Remove Duplicates” or “Power Tools” give you point-and-click control to find, merge, or remove duplicates with more scenarios.

• Apps Script can loop through rows, build a “seen” list in memory, and keep only the first instance of each row, which is useful for automated cleanups on large datasets.

Simple example

Imagine a list of emails in column A, with a few repeated addresses.

• To quickly clean the list in place, select the column and use Data → Data cleanup → Remove duplicates.

• To keep the original list and build a unique mailing list in a new column, put =UNIQUE(A2:A) in B2.

Bottom note: Information gathered from public forums or data available on the internet and portrayed here.