Key Takeaways:The UNIQUE function extracts distinct values from a range instantly, no manual filtering needed.It supports three parameters: range, by_column (optional), and exactly_once (optional).Combine UNIQUE with SORT, FILTER, or QUERY for dynamic, self-updating lists.For CSV files with thousands of duplicates, SmoothSheet handles deduplication before import.
What Is the UNIQUE Function in Google Sheets?
The UNIQUE function in Google Sheets returns only the distinct values from a range or array. If you have a column of 10,000 customer names with repeats, UNIQUE gives you a clean list of every name that appears—without a single duplicate. It is one of the most practical functions for data cleanup, reporting, and building dynamic dashboards.
Unlike the manual Remove Duplicates tool (Data > Data cleanup > Remove duplicates), UNIQUE is a formula. That means your deduplicated list updates automatically whenever source data changes. No re-running a menu command, no risk of accidentally deleting rows you still need.
UNIQUE Syntax and Parameters
Here is the full syntax (see also the Google Sheets function list for all available functions):
=UNIQUE(range, [by_column], [exactly_once])| Parameter | Required? | Description | Default |
|---|---|---|---|
| range | Yes | The data range or array to filter for unique values. | — |
| by_column | No | If TRUE, compares columns instead of rows. Useful for horizontal data layouts. | FALSE |
| exactly_once | No | If TRUE, returns only values that appear exactly one time (no repeats at all). | FALSE |
Most of the time you will only use the first parameter. The optional flags unlock edge cases like horizontal deduplication and finding truly one-of-a-kind entries.
Basic UNIQUE Examples
Get Unique Values from One Column
Suppose column A contains department names with many repeats:
=UNIQUE(A2:A)This returns a vertical list of every department that appears at least once. The result spills downward automatically—you do not need to drag the formula. If someone adds a new department in column A, it appears in your UNIQUE output immediately.
Unique Rows Across Multiple Columns
UNIQUE is not limited to single columns. Pass a multi-column range to deduplicate entire rows:
=UNIQUE(A2:C100)Google Sheets treats each row as a unit. Two rows are considered duplicates only when every cell in the range matches. This is useful for order data where you want unique combinations of customer, product, and date.
UNIQUE with SORT
UNIQUE does not guarantee any particular order. Wrap it with SORT to get an alphabetical (or numerical) list:
=SORT(UNIQUE(A2:A))This is one of the most common patterns in Google Sheets. You get a clean, sorted list that updates itself—perfect for report headers or chart labels. For more powerful sorting and filtering, see our guide on the Google Sheets QUERY function.
UNIQUE with FILTER
Combine UNIQUE with FILTER to extract distinct values from a subset of your data:
=UNIQUE(FILTER(A2:A, B2:B="Active"))This formula first filters column A to only rows where column B equals "Active," then removes duplicates from that filtered set. It is especially handy for dashboards where you need a dynamic list based on a status column or date range.
Advanced Techniques
Build a Dynamic Dropdown from UNIQUE
One of the best practical uses of UNIQUE is powering a data validation dropdown that always stays current:
- In a helper cell (say E1), enter:
=SORT(UNIQUE(A2:A)) - Select the cell where you want the dropdown.
- Go to Data > Data validation.
- Set criteria to Dropdown (from a range) and point it to
E1:E.
Now your dropdown automatically includes every unique value from column A. When new entries appear, they show up in the dropdown without any manual updates. This technique eliminates the tedious process of maintaining static dropdown lists—a real time-saver for teams managing inventory categories, project tags, or client names.
Count Unique Values with COUNTA + UNIQUE
Need to know how many unique values exist rather than list them? Nest UNIQUE inside COUNTA:
=COUNTA(UNIQUE(A2:A))This returns a single number—the count of distinct entries. It is useful for KPI dashboards: "How many unique customers ordered this month?" or "How many distinct product SKUs are active?"
If your column contains blank cells, use UNIQUE inside FILTER first to exclude empties:
=COUNTA(UNIQUE(FILTER(A2:A, A2:A<>"")))Combine UNIQUE with QUERY
The QUERY function can replicate UNIQUE behavior and add grouping or aggregation in the same step:
=QUERY(A1:B, "SELECT A, SUM(B) WHERE A IS NOT NULL GROUP BY A LABEL SUM(B) 'Total'")This groups by column A (effectively deduplicating it) and sums column B for each group. When you need both deduplication and aggregation, QUERY is often cleaner than chaining UNIQUE with additional formulas.
The exactly_once Parameter
Setting the third parameter to TRUE changes the behavior significantly:
=UNIQUE(A2:A, FALSE, TRUE)Instead of returning every distinct value, this returns only values that appear exactly once in the range. If "Sales" appears three times and "Legal" appears once, only "Legal" is returned. This is useful for finding anomalies—entries that were only recorded a single time, which might indicate data entry errors or one-off transactions.
UNIQUE vs the Remove Duplicates Tool
Google Sheets offers two ways to deduplicate data. Here is how they compare:
| Feature | UNIQUE Function | Remove Duplicates (Menu) |
|---|---|---|
| Type | Formula (non-destructive) | Manual action (destructive) |
| Updates automatically | Yes | No—must re-run each time |
| Keeps original data | Yes—outputs to a new range | No—deletes duplicate rows in place |
| Works on subsets | Yes—nest with FILTER | Limited—select columns to compare |
| Collaboration-safe | Yes—no risk of accidental deletion | Risky in shared sheets |
| Best for | Dynamic reports, dashboards, dropdowns | Quick one-time cleanup |
For most workflows, the UNIQUE function is the better choice because it preserves your source data and reacts to changes. The Remove Duplicates menu is fine for a quick, one-off cleanup when you genuinely want to delete rows permanently.
Working with large CSV files before they even reach Google Sheets? SmoothSheet's CSV Duplicate Remover lets you strip duplicates from files of any size right in your browser—no upload to a server, no row-limit headaches.
Common Issues and How to Fix Them
Expanding into Occupied Cells
UNIQUE is a dynamic array function—its output spills into multiple cells. If any of those cells already contain data, you get a #REF! error. The fix is straightforward:
- Clear the cells below (and to the right, for multi-column results) so the array has room to expand.
- Place UNIQUE formulas in a dedicated "output" column or sheet to avoid collisions.
As your source data grows, the UNIQUE output may expand too. Always leave enough empty space or use a separate sheet tab for formula outputs.
Case Sensitivity
The UNIQUE function in Google Sheets is case-sensitive. It treats "Sales," "sales," and "SALES" as three separate values. That means all three will appear in your results as distinct entries, which is often not what you want.
If you want a truly case-insensitive unique list, convert everything to the same case first:
=UNIQUE(ARRAYFORMULA(LOWER(A2:A100)))This wraps each value in LOWER before passing it to UNIQUE, ensuring that "Sales" and "SALES" collapse into a single "sales" entry. Use UPPER or PROPER instead if you prefer a different casing in your output. Note that this technique uses ARRAYFORMULA to apply LOWER across every cell in the range.
Blank Cells in Results
If your source range contains empty cells, UNIQUE may return a blank row in its output. Filter them out:
=UNIQUE(FILTER(A2:A, A2:A<>""))This is a best practice whenever your data range might have gaps—especially when working with imported data. If you regularly import large CSV or Excel files into Google Sheets, SmoothSheet handles files that would normally crash your browser, processing them server-side so your sheet stays responsive.
Performance with Large Datasets
UNIQUE is efficient, but applying it across 100,000+ rows with multiple columns can slow your spreadsheet. Tips for better performance:
- Limit the range to only the rows you need (e.g.,
A2:A5000instead ofA2:A). - Avoid nesting too many array functions inside each other.
- For truly large datasets (500K+ rows), consider deduplicating the CSV before import with SmoothSheet's CSV Duplicate Remover.
Frequently Asked Questions
How do I get unique values in Google Sheets?
Use the formula =UNIQUE(A2:A) where A2:A is your data range. The function returns a dynamic list of distinct values that updates automatically as your data changes. You can also go to Data > Data cleanup > Remove duplicates for a one-time manual cleanup.
Can UNIQUE work across multiple columns?
Yes. Pass a multi-column range like =UNIQUE(A2:C100) and Google Sheets will return rows where the combination of all columns is unique. Two rows are only considered duplicates if every cell in the range matches.
Is the UNIQUE function case-sensitive?
Yes, UNIQUE in Google Sheets is case-sensitive. "Apple" and "apple" are treated as different values. To ignore case, wrap your range in LOWER or UPPER: =UNIQUE(ARRAYFORMULA(LOWER(A2:A100))).
What is the difference between UNIQUE and Remove Duplicates?
UNIQUE is a non-destructive formula that outputs deduplicated values to a new range and updates automatically. Remove Duplicates (Data menu) permanently deletes duplicate rows from your sheet and must be re-run manually each time new data arrives.
Conclusion
The UNIQUE function is one of the simplest yet most powerful tools in Google Sheets. Whether you are building a dynamic dropdown, counting distinct customers, or cleaning up imported data, UNIQUE does the heavy lifting in a single formula. Pair it with SORT, FILTER, or QUERY and you have a self-maintaining data pipeline right inside your spreadsheet.
For datasets too large for Google Sheets to handle comfortably, SmoothSheet processes CSV and Excel imports server-side—so you can deduplicate, split, and clean files of any size before they ever hit your sheet. Try it free and skip the browser crashes.