Excel Text Unique Values: Extract, Count, and Compare

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365, use UNIQUE to return a list of distinct text values from a range; for example, with text in A2:A20, =UNIQUE(A2:A20) spills one copy of each value into the cells below. Use its optional exactly-once setting when you want values that appear only once, rather than a deduplicated list.

What “unique values” means in Excel

The phrase can mean two different things. A distinct-values list contains one copy of every different value, even when that value occurs many times in the source. A values-that-occur-once list contains only items whose frequency in the source is exactly one. Excel’s UNIQUE function can produce either result, so it helps to decide which definition you need before choosing a formula.

For example, imagine A2:A8 contains Cedar, Birch, Cedar, Maple, Birch, Ash, and Maple. A distinct list is Cedar, Birch, Maple, Ash: each different name appears once, in the order it first appeared. The only value that occurs exactly once is Ash. Those outputs answer different questions, and selecting the wrong meaning is a common source of confusion.

“Unique text” can also mean text-only values from a mixed range, such as names alongside numbers or blanks. UNIQUE does not automatically mean “text only”; it returns distinct entries from the supplied array. If a range contains mixed data types, decide whether to filter the input first.

Extract distinct text with UNIQUE

With names in A2:A20, enter =UNIQUE(A2:A20) in an empty cell, such as C2. The results spill down from C2 automatically. In Microsoft 365 for Windows, you can type the formula in the formula bar or directly into the cell and press Enter. Leave enough blank cells below the formula for the results: a blocked spill range produces a #SPILL! error.

UNIQUE returns values in their first-seen order; it does not sort them alphabetically. If A2:A8 contains the example names above, the result is Cedar, Birch, Maple, Ash. This order can be useful when the source has a meaningful sequence, such as the order customers first appeared in a transaction log.

The formula can also work with rows. For example, if A2:B20 contains paired fields such as department and employee, UNIQUE applied to A2:B20 returns distinct row combinations. A department paired with one employee is treated as a different row from the same department paired with another employee. The optional second argument controls whether Excel checks down rows or across columns; its default is to compare rows.

Return values that appear exactly once

To exclude every value that has any duplicate, use UNIQUE’s third argument, exactly_once. With names in A2:A8 containing Cedar, Birch, Cedar, Maple, Birch, Ash, and Maple, enter =UNIQUE(A2:A8,,TRUE). The result is Ash, because Ash appears one time and every other name appears twice. The two consecutive commas are intentional: they leave the optional by_col argument at its default setting and supply TRUE for exactly_once.

This distinction matters in practical reports. If you need a clean category list for a dropdown or summary, use the ordinary distinct list; repeated categories should still be represented. If you are trying to identify one-off entries that may need review, such as a product code appearing in only one record, the exactly-once version is a better fit.

Exclude blanks and sort the results

Blank cells can appear in a spilled result when the source range contains blanks. To omit empty cells, first filter the source range to nonblank entries, then return unique results. With names in A2:A20 and possible blank cells within that range, enter =UNIQUE(FILTER(A2:A20,A2:A20<>"")). The comparison in FILTER removes empty-string results before UNIQUE builds the list. If the source contains formulas that return an empty string, this condition excludes those too.

To alphabetize the result, wrap UNIQUE in SORT. With names in A2:A20, enter =SORT(UNIQUE(A2:A20)). If the range contains Cedar, Birch, Cedar, Maple, Birch, Ash, and Maple, this returns Ash, Birch, Cedar, Maple. Sorting changes the display order, so use it when readers need an alphabetical lookup rather than the original first-seen sequence.

You can also combine sorting and blank removal. With names in A2:A20 and possible blanks, enter =SORT(UNIQUE(FILTER(A2:A20,A2:A20<>""))). These formulas use dynamic-array functions available in Excel 2021 and Microsoft 365. If you share a workbook with people using an older Excel version, the formulas may not calculate there; consider a compatible method or provide a pasted-values copy.

Count distinct text values

Sometimes the question is not which values are distinct, but how many distinct values exist. With names in A2:A20 and possible blank cells, enter =COUNTA(UNIQUE(FILTER(A2:A20,A2:A20<>""))). For the seven-name example with four distinct nonblank names, the result is 4. COUNTA counts the entries in the filtered unique list, while FILTER prevents blanks from inflating the count.

Check whether your definition includes blanks before using a count in a dashboard or report. If a blank is a meaningful category, do not filter it out. Conversely, if blanks simply represent missing data, excluding them usually makes the count easier to interpret. For data with errors, numbers, or formulas returning empty strings, inspect the source and adapt the filter condition rather than assuming every cell is an ordinary text entry.

Case, spaces, and text that looks different

Excel’s ordinary text comparisons are generally not case-sensitive, and UNIQUE follows that practical behavior: entries such as “North” and “north” are normally treated as the same text value. If capitalization represents a genuine distinction in your data, do not assume that UNIQUE alone will preserve it as two separate categories. A case-sensitive solution needs a different comparison approach, typically involving functions such as EXACT and a carefully designed formula.

Leading or trailing spaces are a more frequent cause of apparent duplicates. “Birch” and “Birch ” can be treated as different strings because the second value contains an extra space. Nonbreaking spaces copied from websites may also be difficult to spot. If the source is inconsistent, make a cleaned helper column using TRIM for ordinary extra spaces, then run UNIQUE on that cleaned range. TRIM does not remove every invisible character, so imported data may require additional cleanup.

Also check for values that look alike but are stored differently. A number stored as text is not always equivalent to the numeric value, and punctuation, accents, and different Unicode characters can make entries distinct even when they appear similar. Before deduplicating a customer or product list, standardize spelling, spacing, and data types if those differences are not meaningful to the task.

Formula results versus removing duplicates

UNIQUE creates a separate, formula-driven result and leaves the source unchanged. When source data changes, the spilled list can update automatically. This is usually the safer choice for a live report, a reusable summary, or a list used elsewhere in the workbook.

Remove Duplicates edits the selected source data instead. In Excel for Microsoft 365 on Windows, select the data and choose Data > Data Tools > Remove Duplicates. In the dialog, select the columns Excel should compare, then confirm. Excel keeps one occurrence of each matching record and deletes the other rows from the selected range. Make a copy first if the original records matter, because this operation is not a dynamic view and the deletions are not undone by changing the source later.

Use Remove Duplicates when the goal is to clean a working table permanently and you have a backup. Use UNIQUE when you want an extract that stays linked to the source. For entire records, be deliberate about which columns are checked: comparing only the customer name can remove rows with different orders, while comparing every column removes only fully repeated rows.

Advanced Filter and older Excel alternatives

If your Excel version does not support dynamic arrays, the Advanced Filter tool can copy distinct records to another location. On Windows, select the data, including its header row, and choose Data > Sort & Filter > Advanced. Choose Copy to another location, specify the list range and destination, and select Unique records only. Include the header so Excel can identify the fields. This produces a static extract, so repeat the operation when the source changes.

Excel for Mac also includes UNIQUE in Microsoft 365 and supported recent Excel versions, although ribbon placement and dialog details can differ slightly by release. Excel for the web supports dynamic-array formulas such as UNIQUE, but the desktop Advanced Filter workflow and some data tools may be presented differently. If a workbook must work in older desktop editions, test the selected method in the oldest version your audience uses.

Older formulas can generate distinct lists, but they tend to be longer and may require array-entry behavior that varies by Excel version. For a one-time task, Advanced Filter is often simpler; for a repeatable task, Power Query can also remove duplicates through its data-cleaning workflow. Choose based on whether the result must update automatically, whether source rows can be changed, and which Excel versions need to open the workbook.

Common problems and how to fix them

  • #SPILL! appears: Clear cells in the expected output area, including cells that look blank but contain spaces or formulas. A merged cell in the spill range can also prevent the result from expanding.
  • The result is not alphabetized: UNIQUE preserves first-seen order. Wrap it in SORT if alphabetical order is required.
  • Blanks appear in the output: Filter empty entries before applying UNIQUE, or confirm whether blanks are legitimate values for your report.
  • Two apparent duplicates remain: Compare spaces, punctuation, hidden characters, and whether the entries have different underlying data types. Clean the source before deduplicating.
  • The formula displays as text or does not calculate: Ensure the cell is not formatted as Text, remove any leading apostrophe, and confirm that calculation is enabled. Also check that the Excel version supports UNIQUE.
  • The formula uses semicolons: Excel’s argument separator depends on regional settings. The examples here use commas as required for standard English-language formula syntax; some installations display semicolons instead.

Frequently Asked Questions

How do I get unique text values in Excel? With text in A2:A20, enter =UNIQUE(A2:A20) in a blank cell. Excel returns one copy of each distinct value and spills the list into the cells below.

How do I get values that appear only once? With the source in A2:A20, enter =UNIQUE(A2:A20,,TRUE). Unlike the usual distinct list, this excludes any value that appears more than once.

Does UNIQUE ignore blank cells? Not automatically in every situation. To exclude blank entries, filter them out first; with data in A2:A20, use =UNIQUE(FILTER(A2:A20,A2:A20<>"")).

Can Excel count unique text values? Yes. With text and possible blanks in A2:A20, use =COUNTA(UNIQUE(FILTER(A2:A20,A2:A20<>""))) to count distinct nonblank entries. The count will include blanks if you do not filter them out.

Why does UNIQUE return items in an unexpected order? It retains the order in which values first appear in the source rather than sorting them. Use SORT around UNIQUE when you want an alphabetical result.

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.