Excel UNIQUE Function for Data Analysis

Formulas & functions · Updated November 18, 2026

In Microsoft Excel for Microsoft 365 on Windows, use the UNIQUE function to create a live list of distinct values for analysis. With product names in A2:A100, enter =UNIQUE(A2:A100) in an empty cell; Excel spills one copy of each value into the cells below. You can then count, filter, compare, or chart that list without editing the source data.

What “unique” means in Excel analysis

People use “unique” in two different ways. A distinct list contains one copy of every different value, even if that value appears many times in the source. A value that occurs exactly once appears only one time in the entire source range. Excel’s UNIQUE function can return either result, depending on its optional arguments.

This distinction matters when analyzing a customer, product, or transaction column. If Tea appears five times, it belongs once in a distinct product list, but it does not belong in a list of products purchased exactly once. A distinct list answers “Which products occur?” An exactly-once list answers “Which products occur only once?”

UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. Older perpetual versions, such as Excel 2019, do not support it. When a workbook containing the function is opened in an older version, the formula may show as an unsupported function rather than calculating normally.

Create a distinct list with UNIQUE

Suppose cells A2:A8 contain Tea, Coffee, Tea, Cocoa, Coffee, Juice, and Cocoa, in that order. With those values in A2:A8, enter =UNIQUE(A2:A8) in D2. The result is Tea, Coffee, Cocoa, and Juice, each shown once. The list follows the order in which each value first appears in the source; UNIQUE does not alphabetize it automatically.

The result is a dynamic array. Excel places the first result in the formula cell and spills the remaining results into nearby cells. You enter the formula once rather than copying it down. If the source values change, the spilled list recalculates. The spill area must be clear: a note or another value in its path can prevent the output from appearing.

UNIQUE also works across records, not just one column. If A2:B6 contains these name-and-city pairs: Ana/West, Bo/East, Ana/West, Ana/South, and Bo/East, enter =UNIQUE(A2:B6) in D2. The result is three distinct pairs: Ana/West, Bo/East, and Ana/South. Excel treats each row as a combination, so the same name paired with a different city remains a separate record.

Return values that occur exactly once

To find values that occur one time in the source range, use UNIQUE’s third argument, exactly_once. With the sample product values in A2:A8, enter =UNIQUE(A2:A8,,TRUE) in D2. The result is Juice, because it occurs once; Tea, Coffee, and Cocoa occur twice each. The blank second argument leaves by_col at its default setting.

This option is useful for identifying one-off entries that may need review, such as a product appearing in only one transaction or a name appearing once in an attendance list. It is not a method for removing duplicate rows from the source. The formula creates a separate result and leaves the underlying records unchanged.

Sort, filter, and count unique results

A distinct list becomes more useful when it is combined with other functions. To alphabetize the products from A2:A8, enter =SORT(UNIQUE(A2:A8)) in D2. The result is Cocoa, Coffee, Juice, and Tea. SORT organizes the output while UNIQUE determines which values belong in it. Both functions spill their results, so leave room below the formula.

You can also build a distinct list from records that meet a condition. Suppose A2:A6 contains Tea, Coffee, Tea, Cocoa, and Juice, while B2:B6 contains North, South, South, North, and North. With product names in A2:A6 and regions in B2:B6, enter =UNIQUE(FILTER(A2:A6,B2:B6="North")) in D2. The result is Tea, Cocoa, and Juice: Tea qualifies because at least one Tea record is in the North region.

To count how often each distinct product appears, keep the original products in A2:A8 and put the spilled distinct list beginning at D2. With the source in A2:A8 and the first distinct result in D2, enter =COUNTIF($A$2:$A$8,D2) in E2, then fill the formula down beside the list. The count beside Tea is 2; the counts beside Coffee and Cocoa are also 2, and Juice has a count of 1. The dollar signs keep the source range fixed as you copy the formula.

For a live summary that adjusts as data changes, you can use a dynamic-array formula such as =COUNTIF(A2:A100,D2#). With source values in A2:A100 and the UNIQUE results spilling from D2, enter this in E2. The # refers to the entire spill range, so the counts return beside the distinct values without copying the formula. This approach is convenient for a compact summary, although a PivotTable may be easier to share or rearrange.

Use a unique list for practical questions

A distinct list is a starting point for questions that a raw column makes difficult to answer at a glance. For example, an inventory manager can identify the products represented in a stock log, a project lead can see which departments submitted work, and a researcher can inspect the categories present in a survey. Counting the list against the source then shows frequency; filtering first can reveal how the categories differ across regions or periods.

  • Check category coverage: Compare a unique list of submitted categories with the expected categories to spot missing or unexpected labels.
  • Prepare a dropdown source: Use the spilled list as a source for Data Validation, so a selection list can stay current as the underlying values change.
  • Inspect data quality: Look for near-duplicates such as North, north, and North  with a trailing space. Excel may treat these as different text values.
  • Build a quick summary: Pair distinct labels with counts, then use the summary as the basis for a chart or further review.

For dependable analysis, clean inconsistent text before interpreting the output. UNIQUE distinguishes values according to Excel’s comparison behavior, but it does not decide whether two differently formatted labels mean the same thing. A trailing space, a spelling variation, or inconsistent category names can create separate entries. Review the source or standardize it with appropriate cleanup steps before relying on totals.

UNIQUE versus Remove Duplicates, filters, and PivotTables

UNIQUE is best when you want a formula-driven result that updates as the source changes. The Data tab’s Remove Duplicates command is different: it removes repeated records from the selected data range. In Excel for Microsoft 365 on Windows, the path is Data > Remove Duplicates. Use it only when changing the source is intended, and keep a copy first if the original records matter. It is not a live analysis formula.

A filter can temporarily hide rows that do not meet a condition, but it does not automatically create a separate, deduplicated list. A PivotTable is often a better choice when you need to group records, compare categories across fields, or summarize totals interactively. UNIQUE is lighter and direct for a simple distinct list; a PivotTable offers more ways to explore structured data without building several formulas.

Power Query can be more appropriate for repeatable data preparation, especially when combining files or applying multiple cleanup steps. Its interface differs from a worksheet formula, and the prepared output is generally refreshed when the query is refreshed. Choose based on the task: a live list in a worksheet, an interactive summary, or a repeatable import-and-clean process.

Common problems and how to fix them

If Excel displays #SPILL!, inspect the cells where the results need to appear. Clear obstructing values, merged cells, or other content in the spill area, then let Excel recalculate. A formula entered inside an Excel Table may also be unsuitable when its result needs to spill into multiple cells; place the formula outside the table, with sufficient empty space.

If the output seems to contain duplicates, check for small differences in the original values: spaces at the beginning or end, inconsistent spelling, or other text variations. If the list is unexpectedly blank, check whether a filter condition actually matches the source and whether the referenced ranges cover the intended rows. When source data grows, a fixed range such as A2:A100 will not include rows below row 100; an Excel Table column reference can help the source range expand with added records.

Regional settings can affect how formulas are entered. The examples use commas between arguments, as in the standard English-language syntax. Some Excel installations use semicolons as separators; if Excel rejects a formula with commas, use the separator configured for that installation. On Mac, Microsoft 365 supports dynamic-array functions such as UNIQUE, though ribbon layout and some interface details can differ from Windows. Excel for the web also supports dynamic arrays, but its menus and editing experience are browser-based.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original data?
No. It returns a separate list and leaves the source cells as they are. Use Data > Remove Duplicates only when you intend to change the selected source data.

How do I get a unique list in alphabetical order?
With the source values in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. SORT orders the distinct output alphabetically for text; UNIQUE alone preserves first-occurrence order.

Can UNIQUE return distinct rows from multiple columns?
Yes. Select a two- or multi-column source range in the formula, and UNIQUE returns distinct row combinations. A row is considered distinct when its combination of values differs from another row.

Why does UNIQUE return a #SPILL! error?
Excel cannot place the full result in the required cells. Clear the spill range, check for merged cells, and move the formula outside an Excel Table if the array needs to expand across the worksheet.

What can I use if my Excel version does not have UNIQUE?
In older Excel versions, a PivotTable can display categories without repeating them, or Power Query can remove duplicate values in a prepared output. The best alternative depends on whether you need an interactive summary or a refreshed data-cleaning workflow.

Is a distinct value the same as a value that appears once?
No. A distinct list includes one copy of every different value, even when it repeats in the source. The exactly_once option returns only values whose source frequency is one.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.