UNIQUE function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of UNIQUE with a simple list

Given the list in cells A1:A6:

A1: Apple
A2: Banana
A3: Apple
A4: Orange
A5: Banana
A6: Grape

What is the output of the formula =UNIQUE(A1:A6) when entered in cell B1?

Google Sheets

=UNIQUE(A1:A6)

A Apple, Banana, Orange

B Apple, Banana, Apple, Orange, Banana, Grape

C Apple, Banana, Orange, Grape

D Banana, Apple, Orange, Grape

💡 Hint

UNIQUE removes repeated values and keeps the first occurrence order.

The UNIQUE function returns only the distinct values from the list, preserving the order of their first appearance. So duplicates like the second 'Apple' and 'Banana' are removed.

📊 Formula Result

intermediate

UNIQUE with multiple columns

Given the data in cells A1:B5:

A1: Name, B1: City
A2: John, B2: NY
A3: Anna, B3: LA
A4: John, B4: NY
A5: Mike, B5: SF

What will be the output of =UNIQUE(A2:B5) starting at cell D1?

Google Sheets

=UNIQUE(A2:B5)

A NY<br>LA<br>SF

B John NY<br>Anna LA<br>Mike SF

C John<br>Anna<br>Mike

D John NY<br>Anna LA<br>John NY<br>Mike SF

💡 Hint

UNIQUE treats each row as a whole when given multiple columns.

When UNIQUE is applied to multiple columns, it returns unique rows. The duplicate row 'John NY' appears twice but only the first is kept.

❓ Function Choice

advanced

Choosing the right function to get unique values ignoring blanks

You have a list in column A with some blank cells. You want to get unique values from this list but exclude any blank cells from the result.

Which formula will correctly return unique non-blank values?

A =UNIQUE(FILTER(A1:A10, A1:A10<>""))

B =UNIQUE(A1:A10)

C =FILTER(UNIQUE(A1:A10), UNIQUE(A1:A10)<>"")

D =SORT(UNIQUE(A1:A10))

💡 Hint

Filtering out blanks before applying UNIQUE is the key.

Option A filters out blank cells first, then applies UNIQUE, so blanks are excluded. Option A includes blanks. Option A tries to filter after UNIQUE but UNIQUE returns blanks as well, so FILTER won't remove them properly. Option A sorts but does not remove blanks.

🎯 Scenario

advanced

Using UNIQUE with case sensitivity

You have a list in column A:
A1: apple
A2: Apple
A3: APPLE
A4: banana
A5: Banana

You want to get unique values treating different cases as different entries.

Which approach will achieve this in Google Sheets?

A Use =UNIQUE(A1:A5 & CHAR(ROW(A1:A5)))

B Use =UNIQUE(ARRAYFORMULA(EXACT(A1:A5, A1:A5)))

C Use =UNIQUE(A1:A5, FALSE)

D Use =UNIQUE(A1:A5) directly

💡 Hint

UNIQUE is case-insensitive by default; trick it by making entries unique with row numbers.

UNIQUE is case-insensitive, so 'apple', 'Apple', 'APPLE' are treated the same. Option A appends the row number to each entry, making them unique strings, so UNIQUE returns all. Options A and C treat cases as same. Option A is invalid syntax and logic.

❓ data_analysis

expert

Counting unique values with conditions

You have a table with sales data:

Column A (Product): A2:A10
Column B (Region): B2:B10

You want to count how many unique products were sold in the "East" region.

Which formula will correctly give this count?

A =COUNTA(UNIQUE(FILTER(A2:A10, B2:B10="East")))

B =SUM(UNIQUE(FILTER(A2:A10, B2:B10="East")))

C =COUNT(UNIQUE(FILTER(A2:A10, B2:B10="East")))

D =COUNTUNIQUE(FILTER(A2:A10, B2:B10="East"))

💡 Hint

COUNTUNIQUE counts unique values directly.

Option D uses COUNTUNIQUE with FILTER to count unique products in the East region correctly. Option D counts all unique values but COUNTA counts text correctly, so it seems valid but COUNTUNIQUE is more direct. Option D uses COUNT which counts numbers only, so returns 0. Option D sums text values causing error.

Practice

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

easy

A. It counts how many cells have numbers.

B. It sums all numbers in a range.

C. It returns a list of values without any duplicates.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of UNIQUE

   The UNIQUE function removes duplicate entries from a list or range.

2. Step 2: Compare with other options

   Other options describe different functions like SUM, SORT, and COUNT, not UNIQUE.

3. Final Answer:

   It returns a list of values without any duplicates. -> Option C

4. Quick Check:

   UNIQUE removes duplicates = It returns a list of values without any duplicates. [OK]

Hint: UNIQUE means no repeats in your list [OK]

Common Mistakes:

• Confusing UNIQUE with SUM or COUNT functions
• Thinking UNIQUE sorts data
• Assuming UNIQUE counts items

2. Which of these is the correct way to use the UNIQUE function on range A1:A10?

easy

A. =UNIQUE(A1:A10)

B. =UNIQUE(A1-A10)

C. =UNIQUE(A1;A10)

D. =UNIQUE(A1,A10)

Solution

1. Step 1: Check the syntax of UNIQUE

   The UNIQUE function takes a range as a single argument, written as A1:A10.

2. Step 2: Identify correct range notation

   Colon (:) is used to specify a range, so =UNIQUE(A1:A10) is correct. Other options use invalid separators or operators.

3. Final Answer:

   =UNIQUE(A1:A10) -> Option A

4. Quick Check:

   Range uses colon : = =UNIQUE(A1:A10) [OK]

Hint: Use colon : to select ranges in formulas [OK]

Common Mistakes:

• Using comma or semicolon instead of colon for ranges
• Using minus sign instead of colon
• Putting multiple arguments separated by commas

3. Given this data in column A:
A1: Apple
A2: Banana
A3: Apple
A4: Orange
A5: Banana
What will =UNIQUE(A1:A5) return?

medium

A. Apple, Banana, Apple, Orange, Banana

B. Apple, Banana, Orange

C. Banana, Orange

D. Apple, Orange

Solution

1. Step 1: List all unique values from A1:A5

   The values are Apple, Banana, Apple, Orange, Banana. Unique values are Apple, Banana, Orange.

2. Step 2: Understand UNIQUE output

   UNIQUE removes duplicates and returns only the first occurrence of each unique item.

3. Final Answer:

   Apple, Banana, Orange -> Option B

4. Quick Check:

   UNIQUE removes repeats = Apple, Banana, Orange [OK]

Hint: UNIQUE keeps first of each item only [OK]

Common Mistakes:

• Listing all items without removing duplicates
• Missing some unique items
• Assuming UNIQUE sorts the list

4. You wrote =UNIQUE(A1;A10) but got an error. What is the fix?

medium

A. Remove the equals sign: UNIQUE(A1;A10)

B. Add a second argument: =UNIQUE(A1:A10, TRUE)

C. Use commas instead: =UNIQUE(A1,A10)

D. Change semicolon to colon: =UNIQUE(A1:A10)

Solution

1. Step 1: Identify the error cause

   Using semicolon between A1 and A10 is invalid syntax for range selection in Google Sheets.

2. Step 2: Correct the range syntax

   Replace semicolon with colon to specify the range correctly: A1:A10.

3. Final Answer:

   Change semicolon to colon: =UNIQUE(A1:A10) -> Option D

4. Quick Check:

   Range uses colon : not semicolon ; [OK]

Hint: Ranges use colon :, not semicolon ; [OK]

Common Mistakes:

• Using semicolon instead of colon for ranges
• Adding unnecessary arguments
• Removing equals sign from formula

5. You have this data in columns A and B:
A1: John, B1: Apple
A2: Mary, B2: Banana
A3: John, B3: Apple
A4: John, B4: Orange
A5: Mary, B5: Banana
What will =UNIQUE(A1:B5) return?

hard

A. John, AppleMary, BananaJohn, Orange

B. John, AppleMary, Banana

C. John, AppleMary, BananaJohn, AppleJohn, OrangeMary, Banana

D. Apple, Banana, Orange

Solution

1. Step 1: Understand UNIQUE on multiple columns

   UNIQUE treats each row as a whole. It removes duplicate rows based on all columns.

2. Step 2: Identify unique rows in A1:B5

   Rows are:
   1) John, Apple
   2) Mary, Banana
   3) John, Apple (duplicate of row 1)
   4) John, Orange
   5) Mary, Banana (duplicate of row 2)
   Unique rows are 1, 2, and 4.

3. Final Answer:

   John, Apple
   Mary, Banana
   John, Orange -> Option A

4. Quick Check:

   UNIQUE removes duplicate rows = John, Apple
   Mary, Banana
   John, Orange [OK]

Hint: UNIQUE on multiple columns removes duplicate rows [OK]

Common Mistakes:

• Assuming UNIQUE works column-wise separately
• Listing duplicates again
• Only listing unique values from one column