Excel Formula to Find Unique Names in a List With Examples

Formulas & functions · Updated November 19, 2026

In Microsoft 365 or Excel 2021 and later, enter =UNIQUE(A2:A100) to return each distinct name from the list in A2:A100 once. The results spill into the cells below automatically; use =SORT(UNIQUE(A2:A100)) if you also want them alphabetized.

The basic formula and what it returns

Suppose column A contains names, with a header in A1 and records in A2:A100. Select an empty cell outside the source list, such as C2, and enter =UNIQUE(A2:A100). Excel returns one copy of each distinct entry in the source range, in the order each distinct value first appears. If the entries are Maya, Liam, Maya, and Noor, the result is Maya, Liam, Noor.

UNIQUE is a dynamic array function. You enter the formula in just one cell, and Excel fills the needed cells beneath it. The output area is called a spill range. When names are added to or removed from the referenced range, Excel recalculates the result. The formula does not delete or change the original list.

The range in the example is fixed at 100 rows. If you regularly add records below row 100, adjust the range or use an Excel Table so the source expands more conveniently. Avoid including the header unless you want the header text treated as one of the names.

Sort the unique names and exclude blanks

UNIQUE preserves the order of first appearance; it does not alphabetize the results. With names in A2:A100 and the output starting in C2, use =SORT(UNIQUE(A2:A100)) to return the distinct names in ascending order. SORT changes only the displayed result, not the source list. For reverse alphabetical order, SORT has an optional sort-order argument, but most name lists are easiest to scan in the default ascending order.

Empty cells in the source range may result in a blank entry in the output. To exclude empty cells, filter the input before passing it to UNIQUE. With names in A2:A100 and the result starting in C2, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")). To exclude blanks and alphabetize the remaining names, use =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))) with the same source and output locations.

These formulas exclude cells whose value is genuinely empty or an empty text string. They do not automatically remove cells containing spaces: a cell with one space is not blank to Excel. If your data may contain accidental spaces, clean it first or use a helper column, as described below.

Unique names versus names that occur exactly once

“Unique” can mean two different things in everyday conversation. A distinct list contains one copy of every name, even if that name occurs many times in the source. A list of names occurring exactly once excludes names that appear more than once. The basic UNIQUE formula produces the first kind of list.

For example, if A2:A10 contains Ava, Ben, Ava, Cole, Dina, Ben, Eli, Fay, and Gia, the distinct list includes Ava and Ben once each. The names occurring exactly once are Cole, Dina, Eli, Fay, and Gia. With that source in A2:A10 and the output beginning in C2, use =UNIQUE(A2:A10,,TRUE) to return entries that appear exactly once. The omitted second argument keeps the function working down the rows, and TRUE requests values that occur only once.

This distinction matters when making a contact list or checking a roster: most people want one row per person, not only people who happen to appear once in the raw data. If the goal is to identify duplicates for investigation, a distinct list is not itself a duplicate report; compare the source counts or use a separate check.

Enter the formula and make room for its results

  1. Keep the original names in one column, ideally with a single header and no merged cells.
  2. Click an empty cell where the results can extend downward, such as C2.
  3. Enter the formula that matches your need: UNIQUE for distinct values, SORT with UNIQUE for an alphabetized list, or FILTER with UNIQUE to omit blanks.
  4. Press Enter. Excel displays the result across the spill range without copying the formula down.
  5. Leave the cells below the formula clear. If Excel reports a spill problem, move or clear the obstructing content.

When using Microsoft 365 for Windows, the formula is entered in the worksheet grid as usual. UNIQUE and dynamic arrays are also available in supported Microsoft 365 versions of Excel for Mac and Excel for the web; the formula behavior is substantially the same. The ribbon layout can differ between Windows, Mac, and the web, but no special menu command is needed to enter the formula.

Clean inconsistent names before deduplicating

Excel can only identify duplicates as they are represented in the cells. “Jordan Lee” and “Jordan Lee ”, with a trailing space, may therefore appear as separate entries. Different punctuation, extra internal spaces, spelling variations, and inconsistent abbreviations can also keep records apart. A unique-list formula does not determine whether two differently written entries refer to the same person.

A practical approach is to create a cleaned helper column, inspect it, and then make the distinct list from that column. With a name in A2 and the cleaned value to appear in B2, enter =TRIM(A2) and fill the formula down. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to single spaces. It does not correct misspellings or every kind of nonbreaking space copied from a website.

After cleaning the names, put the unique formula in another empty cell. With cleaned names in B2:B100 and the output starting in D2, enter =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). Keeping the helper column makes it easier to see which source records were changed and to fix inconsistent spellings deliberately rather than silently treating them as equivalent.

Capitalization and duplicate matching

Excel’s ordinary text comparisons are generally case-insensitive, so capitalization by itself usually does not create separate entries in a UNIQUE result. For instance, a list containing “sam” and “Sam” will normally produce one entry rather than two. UNIQUE does not standardize the capitalization of the value it returns; the displayed version reflects the value Excel retains from the source.

If the difference between two names is meaningful in a particular dataset, do not assume UNIQUE is a case-sensitive identity check. Review the source and decide on a consistent rule. For a staff directory, for example, you might standardize capitalization and spelling before deduplicating. Names are not reliable unique identifiers by themselves: two different people can share a name, and one person may appear under different names.

Excel versions and alternatives

UNIQUE, SORT, and FILTER are available in Microsoft 365 and Excel 2021 or later. In older perpetual versions, the formula may return #NAME? because the function is not supported. If you share a workbook with people using older Excel, check their version before relying on a dynamic-array formula.

For a one-time list in older Excel, use the built-in Remove Duplicates command on a copy of the source data. On Windows, select the copied range and choose Data > Remove Duplicates, then confirm which column contains the names. This removes duplicate rows from the selected range, so do not run it on the only copy if you need to preserve every original record.

Another built-in option is Advanced Filter, which can copy unique records to another location rather than replacing the source. On Windows, open Data > Advanced in the Sort & Filter group, select Copy to another location, specify the list and destination ranges, and select Unique records only. The exact ribbon arrangement can differ on Mac and in Excel for the web. These tools produce a snapshot; unlike a UNIQUE formula, they do not automatically refresh when the source list changes.

Common problems and fixes

  • #SPILL!: One or more cells where the result needs to appear are occupied, merged, or otherwise blocking the spill range. Clear the obstruction or move the formula to a larger empty area.
  • #NAME?: The Excel version may not support UNIQUE, or the function name may be misspelled. Confirm that you are using Microsoft 365 or Excel 2021 or later.
  • A blank appears in the list: Use FILTER to exclude empty values. Check for cells containing spaces, which are not truly empty.
  • Names that look the same appear separately: Check for leading or trailing spaces, nonbreaking spaces, punctuation, or spelling differences. Clean and inspect the source rather than assuming the formula can identify people.
  • The output stops updating: Confirm that the formula’s source range includes the new rows. A reference such as A2:A100 does not include data entered in A101.

One other common surprise is trying to type into a cell inside the spill range. Only the formula cell controls that output, so edit the source data or the formula instead. You can refer to the entire output from C2 in another formula with the spill-reference operator, written as C2#, but that reference is not itself a standalone formula.

Frequently Asked Questions

What is the simplest Excel formula to list unique names?
With names in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. It returns each distinct value once and spills the results into the cells below.

How do I get unique names in alphabetical order?
With names in A2:A100, enter =SORT(UNIQUE(A2:A100)) in an empty cell. SORT orders the distinct results without changing the original list.

How do I ignore blank cells?
With names in A2:A100, use =UNIQUE(FILTER(A2:A100,A2:A100<>"")). If cells contain spaces rather than being empty, clean those values too.

Does UNIQUE remove duplicates from my original column?
No. It creates a separate formula result and leaves the source data intact. Use Remove Duplicates only when you intend to change the selected data, preferably after making a copy.

Can I use UNIQUE in older Excel?
Not in versions that lack dynamic-array functions, including many older perpetual releases. Use Advanced Filter or Remove Duplicates for a static result, or open the workbook in Microsoft 365 or Excel 2021 or later.

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.