UNIQUE function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get unique values from the range A1:A10.

Google Sheets

=UNIQUE([1])

Drag options to blanks, or click blank then click option'

AA1-A10

BA1+A10

CA1;A10

DA1:A10

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' or '-' instead of ':' to specify ranges.

Using semicolons ';' which are not valid for ranges.

The UNIQUE function takes a range as input. Using A1:A10 correctly selects the range.

2fill in blank

medium

Complete the formula to get unique values from the range B2:B20 and sort them.

Google Sheets

=SORT(UNIQUE([1]))

Drag options to blanks, or click blank then click option'

AB2:B20

BB2+B20

CB2;B20

DB2-B20

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' or '-' instead of ':' for ranges.

Using semicolons ';' which are invalid for ranges.

The UNIQUE function requires a range like B2:B20. Then SORT arranges the unique values.

3fill in blank

hard

Fix the error in the formula to get unique values from C1:C15.

Google Sheets

=UNIQUE([1])

Drag options to blanks, or click blank then click option'

AC1:C15

BC1;C15

CC1-C15

DC1+C15

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolons or arithmetic operators instead of a colon for ranges.

The correct range syntax uses a colon ':' between start and end cells, like C1:C15.

4fill in blank

hard

Fill both blanks to create a formula that extracts unique values from D1:D30 and counts them.

Google Sheets

=COUNTA(UNIQUE([1])) + [2]

Drag options to blanks, or click blank then click option'

AD1:D30

BD1;D30

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolons instead of colons for ranges.

Adding 1 changes the count incorrectly.

The UNIQUE function needs the range D1:D30. Adding 0 keeps the count unchanged.

5fill in blank

hard

Fill all three blanks to create a formula that extracts unique values from E2:E25, sorts them, and returns the third item.

Google Sheets

=INDEX(SORT(UNIQUE([1])), [2], [3])

Drag options to blanks, or click blank then click option'

AE2:E25

B3

C1

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using incorrect range syntax.

Mixing up row and column numbers in INDEX.

The formula extracts unique values from E2:E25, sorts them, then INDEX returns the 3rd row and 1st column value.

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