Removing duplicates in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula removing duplicates?

Given the list in cells A1:A6: {"Apple", "Banana", "Apple", "Orange", "Banana", "Grape"}, what will be the output of the formula =UNIQUE(A1:A6) when entered in cell B1?

A ["Apple", "Banana", "Orange", "Grape"]

B ["Apple", "Banana", "Apple", "Orange", "Banana", "Grape"]

C ["Apple", "Banana", "Orange"]

D ["Apple", "Orange", "Grape"]

💡 Hint

The UNIQUE function returns only the first occurrence of each item, removing duplicates.

The UNIQUE function scans the range and returns only unique values in the order they first appear. So duplicates like the second "Apple" and second "Banana" are removed.

❓ Function Choice

intermediate

Which function removes duplicates from a list?

You have a list of names in column A. Which Excel function will create a new list without any repeated names?

A COUNTIF

B FILTER

C SORT

D UNIQUE

💡 Hint

Think about the function that extracts only distinct values.

The UNIQUE function returns a list of distinct values from a range, effectively removing duplicates.

🎯 Scenario

advanced

How to remove duplicates but keep the last occurrence?

You have a list in A1:A7: {"Cat", "Dog", "Cat", "Bird", "Dog", "Fish", "Cat"}. You want to create a list without duplicates but keeping the last occurrence of each item. Which formula in B1 will do this?

A =SORT(UNIQUE(A1:A7, FALSE, TRUE))

B =INDEX(A1:A7, SORT(MATCH(UNIQUE(A1:A7), A1:A7, 0),, -1))

C =SORT(UNIQUE(A1:A7),, -1)

D =SORTBY(UNIQUE(A1:A7), SEQUENCE(COUNTA(A1:A7)), -1)

💡 Hint

UNIQUE has an option to keep last occurrences instead of first.

The UNIQUE function's third argument, when TRUE, keeps the last occurrence of each value. So =UNIQUE(A1:A7, FALSE, TRUE) returns unique values keeping last occurrences.

📊 Formula Result

advanced

What error occurs with this formula?

What error will this formula produce? =UNIQUE(A1:A5, TRUE, TRUE) if A1:A5 contains text values?

A #NAME? error

B #VALUE! error

C Returns unique values keeping last occurrences

D Returns unique values sorted ascending

💡 Hint

Check the meaning of the second argument in UNIQUE.

The second argument in UNIQUE is 'by_col' which expects TRUE or FALSE. Setting it TRUE means to check columns, but if the range is a single column with text, it causes a #VALUE! error.

❓ data_analysis

expert

How many unique values after removing duplicates?

Given this list in A1:A10: {"Red", "Blue", "Red", "Green", "Blue", "Yellow", "Green", "Red", "Yellow", "Black"}, how many unique values will the formula =COUNTA(UNIQUE(A1:A10)) return?

A 6

B 7

C 5

D 4

💡 Hint

Count distinct colors ignoring duplicates.

The unique colors are Red, Blue, Green, Yellow, Black, so 5 unique values. But counting carefully: Red, Blue, Green, Yellow, Black = 5 unique values. The correct count is 5, so option C is correct.

Practice

1. What does the Remove Duplicates feature in Excel do?

easy

A. It deletes repeated entries and keeps only unique values.

B. It sorts the data alphabetically.

C. It highlights duplicate cells without removing them.

D. It copies data to another sheet.

Solution

1. Step 1: Understand the purpose of Remove Duplicates

   This feature is designed to clean data by deleting repeated entries.

2. Step 2: Identify what remains after using Remove Duplicates

   Only unique values remain in the list after duplicates are removed.

3. Final Answer:

   It deletes repeated entries and keeps only unique values. -> Option A

4. Quick Check:

   Remove Duplicates = Keeps unique values [OK]

Hint: Remove Duplicates deletes repeats, leaving unique values only [OK]

Common Mistakes:

• Thinking it only highlights duplicates
• Confusing it with sorting
• Assuming it copies data instead of removing duplicates

2. Which of these is the correct way to start removing duplicates in Excel?

easy

A. Go to the Data tab and click Remove Duplicates.

B. Go to the Home tab and click Sort.

C. Right-click a cell and select Insert Comment.

D. Use the Formulas tab and select Calculate Now.

Solution

1. Step 1: Locate the Remove Duplicates feature

   It is found under the Data tab in Excel's ribbon menu.

2. Step 2: Confirm the correct action to remove duplicates

   Clicking Remove Duplicates under Data tab starts the process.

3. Final Answer:

   Go to the Data tab and click Remove Duplicates. -> Option A

4. Quick Check:

   Remove Duplicates is under Data tab [OK]

Hint: Find Remove Duplicates under Data tab, not Home or Formulas [OK]

Common Mistakes:

• Looking under Home tab instead of Data
• Confusing Remove Duplicates with Sort
• Trying to remove duplicates via formulas tab

3. You have this list in column A:
Apple
Banana
Apple
Orange
Banana
After using Remove Duplicates on column A, what will the list look like?

medium

A. Apple, Banana, Apple, Orange, Banana

B. Apple, Banana, Orange

C. Orange, Banana, Apple

D. Apple, Orange

Solution

1. Step 1: Identify duplicates in the list

   The list has Apple twice and Banana twice.

2. Step 2: Remove repeated entries keeping only unique values

   After removal, only one Apple, one Banana, and one Orange remain.

3. Final Answer:

   Apple, Banana, Orange -> Option B

4. Quick Check:

   Duplicates removed = Apple, Banana, Orange [OK]

Hint: Remove duplicates keeps one of each unique item [OK]

Common Mistakes:

• Expecting duplicates to remain
• Thinking order changes randomly
• Removing unique items by mistake

4. You tried to remove duplicates from a table with columns Name and Email, but duplicates still appear. What is the likely mistake?

medium

A. You sorted the table instead of removing duplicates.

B. You used the Undo button after removing duplicates.

C. You only selected one column instead of both before removing duplicates.

D. You applied Remove Duplicates on a blank sheet.

Solution

1. Step 1: Understand how Remove Duplicates works with multiple columns

   It checks duplicates based on selected columns only.

2. Step 2: Identify the mistake of selecting only one column

   If only one column is selected, duplicates in other columns remain.

3. Final Answer:

   You only selected one column instead of both before removing duplicates. -> Option C

4. Quick Check:

   Select all relevant columns to remove duplicates correctly [OK]

Hint: Select all columns to check before removing duplicates [OK]

Common Mistakes:

• Selecting only one column in multi-column data
• Confusing sorting with removing duplicates
• Trying to remove duplicates on empty data

5. You have a table with columns: Product, Color, Price. You want to remove rows where both Product and Color are duplicates, but keep rows with same Product but different Color. How do you do this?

hard

A. Sort by Price and then remove duplicates.

B. Select all three columns in Remove Duplicates dialog and click OK.

C. Select only Product column in Remove Duplicates dialog and click OK.

D. Select only Product and Color columns in Remove Duplicates dialog and click OK.

Solution

1. Step 1: Identify which columns define duplicates

   Duplicates are rows where both Product and Color match.

2. Step 2: Select only Product and Color columns in Remove Duplicates

   This ensures rows with same Product but different Color stay.

3. Step 3: Confirm that Price is ignored in duplicate check

   Price differences won't affect duplicate removal.

4. Final Answer:

   Select only Product and Color columns in Remove Duplicates dialog and click OK. -> Option D

5. Quick Check:

   Choose columns defining duplicates to remove correctly [OK]

Hint: Select only columns that define duplicates before removing [OK]

Common Mistakes:

• Selecting all columns removes more rows than needed
• Selecting only Product removes rows with different colors
• Sorting does not remove duplicates