Removing duplicates in Google Sheets - Step-by-Step Guide

Introduction

Removing duplicates helps you clean your data by deleting repeated rows. This makes your list easier to read and analyze without extra copies of the same information.

When you have a contact list with repeated email addresses and want only unique contacts.

When you import data from another source and want to remove repeated entries before analysis.

When you track sales and want to count each customer only once.

When you prepare a list for mailing and want to avoid sending duplicates.

When you want to clean survey responses that may have been submitted multiple times.

Steps

Step 1: Select

- the range of cells containing your data

The cells you want to check for duplicates are highlighted

Step 2: Click

- Data menu at the top

A dropdown menu appears with data options

Step 3: Select

- Data cleanup > Remove duplicates

A dialog box opens to configure duplicate removal

Step 4: Check or uncheck

- Columns to analyze in the dialog box

You choose which columns to consider when finding duplicates

💡 Select all columns to remove rows that are exactly the same

Step 5: Click

- Remove duplicates button in the dialog

Duplicates are removed and a message shows how many rows were deleted

Before vs After

Before

A list with 10 rows where 3 rows have the same name and email repeated

After

The list shows 7 rows with all duplicates removed, only unique rows remain

Settings Reference

Columns to analyze

📍 Remove duplicates dialog box

Decides which columns must match to consider rows duplicates

Default: All columns selected

Remove duplicates button

📍 Remove duplicates dialog box

Starts the process of deleting duplicate rows

Default: N/A

Common Mistakes

Not selecting the full data range before removing duplicates

Duplicates outside the selected range will not be removed, leaving repeated data

Always select the entire data range you want to clean before using Remove duplicates

Not selecting all relevant columns in the dialog

Rows may be considered unique if only some columns are checked, leaving partial duplicates

Select all columns that define a duplicate row to ensure full duplicates are removed

Summary

Removing duplicates deletes repeated rows to keep only unique data.

Use the Data > Data cleanup > Remove duplicates menu to start.

Select the full data range and all relevant columns to avoid missing duplicates.

Practice

1. What does the UNIQUE(range) function do in Google Sheets?

easy

A. It highlights duplicate values in the range.

B. It sorts the values in the range alphabetically.

C. It counts how many times each value appears in the range.

D. It returns only the unique values from the given range, removing duplicates.

Solution

1. Step 1: Understand the purpose of UNIQUE()

   The UNIQUE() function is designed to filter out repeated values and return only distinct entries from a range.

2. Step 2: Compare options with UNIQUE() behavior

   'It returns only the unique values from the given range, removing duplicates.' correctly describes that UNIQUE() returns unique values by removing duplicates.

3. Final Answer:

   It returns only the unique values from the given range, removing duplicates. -> Option D

4. Quick Check:

   UNIQUE(range) = unique values [OK]

Hint: Remember: UNIQUE() filters duplicates, not sorting or counting [OK]

Common Mistakes:

• Confusing UNIQUE() with SORT()
• Thinking UNIQUE() counts duplicates
• Assuming UNIQUE() highlights duplicates

2. Which of these is the correct syntax to remove duplicates from the range A1:A10 in Google Sheets?

easy

A. FILTER_DUPLICATES(A1:A10)

B. REMOVE_DUPLICATES(A1:A10)

C. UNIQUE(A1:A10)

D. DELETE_DUPLICATES(A1:A10)

Solution

1. Step 1: Identify the correct function name

   Google Sheets uses UNIQUE() to remove duplicates, not any other function names.

2. Step 2: Check the syntax format

   The correct syntax is UNIQUE(range), so UNIQUE(A1:A10) is valid and works as expected.

3. Final Answer:

   UNIQUE(A1:A10) -> Option C

4. Quick Check:

   Correct syntax = UNIQUE(range) [OK]

Hint: Use UNIQUE() exactly, no other function removes duplicates [OK]

Common Mistakes:

• Using non-existent functions like REMOVE_DUPLICATES()
• Adding extra parentheses or commas incorrectly
• Confusing with FILTER() function

3. Given the data in cells A1:A6 as:
1
2
2
3
1
4
What will be the result of =UNIQUE(A1:A6)?

medium

A. [1, 2, 3, 4]

B. [1, 2, 2, 3, 1, 4]

C. [4, 3, 2, 1]

D. [1, 3, 4]

Solution

1. Step 1: List unique values from the data

   The values are 1, 2, 2, 3, 1, 4. Unique values in order of first appearance are 1, 2, 3, 4.

2. Step 2: Understand UNIQUE() output

   UNIQUE() returns these unique values as a vertical array: 1, 2, 3, 4.

3. Final Answer:

   [1, 2, 3, 4] -> Option A

4. Quick Check:

   UNIQUE removes duplicates, keeps first order [OK]

Hint: UNIQUE() keeps first occurrence, removes repeats [OK]

Common Mistakes:

• Expecting sorted output instead of original order
• Including duplicates in the result
• Confusing UNIQUE() with SORT()

4. You wrote =UNIQUE(A1:A5, TRUE, FALSE, TRUE) but get an error. What is wrong?

medium

A. TRUE should be FALSE to remove duplicates.

B. UNIQUE() takes at most three arguments, extra TRUE causes error.

C. The range A1:A5 is invalid for UNIQUE().

D. UNIQUE() requires a second argument to work.

Solution

1. Step 1: Check UNIQUE() function arguments

   UNIQUE() accepts one required argument (range) and up to two optional boolean arguments ([by_column], [exactly_once]). Adding a fourth TRUE is invalid syntax.

2. Step 2: Understand error cause

   Passing more than three arguments causes a formula parse error in Google Sheets.

3. Final Answer:

   UNIQUE() takes at most three arguments, extra TRUE causes error. -> Option B

4. Quick Check:

   UNIQUE(range[, by_col][, exact]) only up to 3 args [OK]

Hint: UNIQUE() takes at most 3 arguments, no extras [OK]

Common Mistakes:

• Adding extra arguments to UNIQUE()
• Thinking TRUE/FALSE controls duplicate removal
• Using invalid ranges

5. You have a table with columns A (Name) and B (City) from rows 1 to 8. Some rows have duplicate Name and City pairs. Which formula removes duplicate rows considering both columns together?

hard

A. UNIQUE(A1:B8)

B. UNIQUE(A1:A8)

C. UNIQUE(B1:B8)

D. UNIQUE(A1:B8, TRUE)

Solution

1. Step 1: Understand removing duplicates by multiple columns

   To remove duplicates based on both Name and City, you must include both columns in the UNIQUE() range.

2. Step 2: Check options for correct range

   UNIQUE(A1:B8) uses UNIQUE(A1:B8), which considers both columns together and removes duplicate rows correctly.

3. Final Answer:

   UNIQUE(A1:B8) -> Option A

4. Quick Check:

   UNIQUE(multi-column range) removes duplicate rows [OK]

Hint: Include all columns in UNIQUE() to remove full-row duplicates [OK]

Common Mistakes:

• Using only one column range to remove multi-column duplicates
• Adding extra arguments to UNIQUE()
• Expecting UNIQUE() to sort automatically