UNIQUE Function in Google Sheets

When you are working with large datasets in Google Sheets, one of the most common problems is dealing with duplicate entries. For example, you may have the same customer name, employee ID, or ticket number appearing multiple times in your sheet. Cleaning this manually is not only boring but also increases the risk of mistakes.

That’s where the UNIQUE function in Google Sheets becomes a lifesaver. It automatically removes duplicate values and gives you a clean, dynamic list that updates whenever your data changes. Unlike Excel’s “Remove Duplicates” button (which is a one-time action), Google Sheets’ UNIQUE function is fully dynamic — meaning no matter how many new rows you add, your clean list always stays updated.

Table of Contents

Syntax of UNIQUE Function in Google Sheets

=UNIQUE(range, [by_column], [exactly_once])

range → The cells you want to check for duplicates.
by_column → (Optional) TRUE means check column-wise, FALSE (default) means row-wise.
exactly_once → (Optional) TRUE returns only values that appear once, FALSE (default) removes duplicates but keeps one copy.

Example 1: Simple UNIQUE List

You have department names in column A, with repeats:

=UNIQUE(A2:A20)

👉 Instantly gives you one copy of each department (Sales, HR, IT, Finance, etc.).

Example 2: UNIQUE Across Multiple Columns

If your data is in two columns (Name + Department):

=UNIQUE(A2:B20)

👉 It checks both columns together and removes duplicate rows.

Example 3: UNIQUE with SORT

Get a clean, alphabetically sorted list of departments:

=SORT(UNIQUE(A2:A20))

Example 4: Helpdesk Queries Example (MIS Use Case)

Suppose you are handling a helpdesk sheet where users submit queries. Many users might raise the same issue multiple times:

User Name	Query
Amit	Password reset issue
Riya	Email not working
Amit	Password reset issue
Sunil	Login failed
Riya	Email not working

If you want to know unique queries received:

=UNIQUE(B2:B20)

👉 This will return:

Password reset issue
Email not working
Login failed

Now you can focus on solving unique problems instead of wasting time on repeated entries.

Example 5: Count Unique Queries

To get how many unique queries came in:

=COUNTA(UNIQUE(B2:B20))

Example 6: UNIQUE + FILTER (Active Employees Only)

If you want a list of unique departments but only for Active employees:

=UNIQUE(FILTER(B2:B100, C2:C100="Active"))

Read More: FILTER function in Google Sheets

Example 7: UNIQUE + QUERY (Department Summary)

Get unique department names along with total employees:

=QUERY(A2:C100,"select B, count(A) group by B",1)

👉 While UNIQUE alone gives you the list, QUERY can give you summary counts.

Read More: QUERY Function in Google Sheets

Example 8: UNIQUE + ARRAYFORMULA (Auto Serial Number)

Create a serial number for your unique values:

=ARRAYFORMULA(ROW(UNIQUE(A2:A20))-1)

Read More: ARRAYFORMULA in Google Sheets

Limitations of UNIQUE Function

Returns blank if range is empty.
Cannot directly count duplicates (only removes them).
Sometimes slows down if the range is very large (use A2:A10000 instead of A:A).
Case sensitive → “HR” and “hr” are treated as different.

MIS Use Cases

Helpdesk Queries → Identify unique issues raised by users.
Attendance Data → Get unique employee IDs to check duplicate punch-ins.
Customer Database → Create a unique customer list from sales records.
Survey Data → Identify unique responses to avoid double counting.
Finance Reports → List unique invoice numbers from payment sheets.
Procurement → Get a clean list of vendors from purchase records.

Conclusion

The UNIQUE function in Google Sheets is one of the simplest yet most powerful tools for cleaning data. Whether you are working on a helpdesk report, HR attendance sheet, finance ledger, or sales database, UNIQUE helps you remove duplicates and focus only on what matters.

Unlike Excel’s one-time “Remove Duplicates”, Google Sheets’ UNIQUE is dynamic and automatic — making it a must-use function for MIS reporting.
👉 Once you learn to combine UNIQUE with FILTER, QUERY, and SORT, it becomes a complete solution for creating smart, real-time reports without manual effort.

What’s Next?

In the next post, we’ll learn about the SPLIT Function in Google Sheets

Spread the love