In Microsoft 365, you can create a live list of distinct names with the UNIQUE function. With names in A2:A100, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) in an empty cell to return each nonblank name once. If your Excel version does not support that function, use Remove Duplicates or Advanced Filter instead.
Here, “unique names” usually means a list with one copy of each different name, even if the name appears several times in the source column. That is different from names that occur exactly once. The steps below explain both meanings and show how to choose a method that suits your task.
What You’ll Need
- An Excel workbook containing the names you want to check.
- The source column or range, including whether it has a heading.
- For a formula-based list, an empty area below or beside the formula where the results can spill. Excel for Microsoft 365 and Excel 2021 support UNIQUE and FILTER.
- A copy of the original data if you plan to use Remove Duplicates, since that command changes the selected cells.
Step-by-Step
-
Choose what “unique” means for your task. If you want a clean list of names with duplicates removed, you want distinct names: a name that occurs five times should appear once in the result. If you want names that appear only once in the source, you need the exactly-once option instead. This distinction matters because the two results can be very different. For example, if Ava appears twice and Ben appears once, a distinct list contains Ava and Ben, while an exactly-once list contains only Ben.
-
Check the source range and decide where results should go. Identify the first and last cells containing names, and note whether the top cell is a heading such as “Name.” Do not include the heading in the formula range. Pick a blank cell outside the source column, with enough empty cells below it for the result. A formula result can spill into several rows; existing values or merged cells in that space can block it. If the source is a table, put the formula outside the table rather than in a table column.
-
Create a live distinct list with UNIQUE. With names in A2:A100, enter
=UNIQUE(FILTER(A2:A100,A2:A100<>""))in an empty cell. The FILTER part excludes blank cells, and UNIQUE returns one of each remaining name. For example, if A2:A8 contains Ava, Ben, Ava, Cara, a blank, Ben, and Dev, the result is Ava, Ben, Cara, Dev in the order each name first appears. Press Enter; Excel fills the cells below automatically. If your data has no blanks and you do not mind a blank being represented in the result, you can use=UNIQUE(A2:A100)instead. -
Sort the results alphabetically if that is more useful. UNIQUE keeps the order in which names first occur. To display the distinct names in ascending order, wrap the result in SORT. With names in A2:A100, enter
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))in a clear cell. For the Ava, Ben, Ava, Cara, blank, Ben, Dev example, the result is Ava, Ben, Cara, Dev; a different set of names will be placed in alphabetical order. The list recalculates when values in the source range change. -
Return only names that occur exactly once, if that is what you mean. With names in A2:A8, enter
=UNIQUE(FILTER(A2:A8,A2:A8<>""),,TRUE). The final TRUE tells UNIQUE to return only values that appear once in the filtered range. For the example list Ava, Ben, Ava, Cara, blank, Ben, Dev, the result is Cara and Dev. This is not the same as removing duplicate entries: Ava and Ben are omitted because each occurs more than once. The formula is supported in Microsoft 365 and Excel 2021. -
Use Remove Duplicates for a one-time cleanup. If you want to change a copy of the names rather than maintain a live formula, copy the name column to a spare sheet or range first. Select the copied cells, including the heading if present, then choose Data > Remove Duplicates. In the dialog, confirm that the correct column is selected and set “My data has headers” appropriately. Choose OK. Excel keeps the first occurrence of each repeated value and removes later duplicates from the selected range. This is a permanent change to those cells, so check the result and keep the original data if you may need it again.
-
Use Remove Duplicates carefully when names belong to full records. If each name is part of a row containing other details, do not remove duplicates from just the name cells while leaving the rest of the table in place: that can misalign names and records. Instead, select or copy the entire table and choose Data > Remove Duplicates. In the dialog, select only the Name column as the basis for finding duplicate rows. Excel retains the first matching row and removes later rows for that name. This method discards the other repeated records, so use it only when keeping one record per name is intended.
-
Use Advanced Filter when you need a copied, one-time list. In Excel for Microsoft 365 for Windows, copy the source column including its heading to an area with room for the result. Select a cell in the source list and choose Data > Sort & Filter > Advanced. In the dialog, choose “Copy to another location,” confirm the List range, and set Copy to to the destination heading or cell. Select “Unique records only,” then choose OK. Excel copies one instance of each entry and leaves the source list intact. Advanced Filter is a desktop Excel feature; if you are using Excel for the web and do not see it, use UNIQUE or Remove Duplicates instead. Ribbon placement can vary slightly on Mac, but the formula method works there when your Excel version supports the functions.
Common Mistakes
Confusing distinct values with values that occur once. The default UNIQUE formula removes repeated copies from the output but still includes names that appeared repeatedly in the source. Use the third argument TRUE only when you specifically need names that occur one time.
Including the heading in the formula range. If you select A1:A100 when A1 says “Name,” Excel treats that heading as an item and may show it among the results. Start at A2 when row 1 is a heading. For Remove Duplicates and Advanced Filter, include the heading when appropriate and tell the dialog that the data has headers.
Assuming names that look alike are identical. “Mia” and “Mia ” have different values if one has an extra trailing space. Extra spaces, nonprinting characters, or inconsistent spelling can make a list appear to contain duplicates that Excel does not combine. Excel’s ordinary duplicate comparison is not case-sensitive, so capitalization alone generally will not make two entries distinct.
Removing duplicates from the original list without a backup. Remove Duplicates changes the selected data, and undo may not be available after you close or continue working in the workbook. Make a copy first, especially if the column is part of a larger table or the source is important.
Tips
Use a formula when you want a result that updates as the source changes. Use Remove Duplicates when you want to clean a copied list once, and Advanced Filter when you want a separate static copy while preserving the source. Keeping those purposes clear helps prevent an accidental loss of data.
If the names are in an Excel table, adding rows to the table is convenient, but a formula that refers to a fixed range such as A2:A100 will not include entries below row 100. Extend the range when necessary, or use a table column reference that matches your table and heading. Leave room for the output to expand as more distinct names are added.
For a quick check, compare the number of results with what you expect, and scan for blanks, spelling variants, and extra spaces. If the names are used for reporting or matching records, standardize the source values before deciding that two similar-looking entries are duplicates. Preserve the source column until you have confirmed the output is correct.
Troubleshooting
The formula displays #NAME? Your Excel version may not recognize UNIQUE or FILTER. These functions are available in Microsoft 365 and Excel 2021, but not in some older perpetual versions. Use Data > Remove Duplicates or Advanced Filter in desktop Excel, or update to a version with dynamic-array functions.
The formula displays #SPILL! or does not fill down. Clear cells beneath and around the formula where its results need to appear. Check for merged cells in the output area, too. Dynamic-array formulas cannot spill inside an Excel table, so move the formula to an ordinary worksheet cell outside the table. If the area is clear, check that the formula is not blocked by another result.
A blank appears in the results. Use the FILTER version shown above to exclude empty source cells. If a cell appears blank but is not excluded, it may contain spaces rather than being truly empty. Inspect the source or clean those entries before generating the list.
Names that look the same still appear more than once. Check for leading or trailing spaces, nonbreaking spaces copied from a website, and hidden characters. Correct a few entries directly or clean the source in a helper column, then run the unique-list method on the cleaned values. Also check for spelling differences such as “Jon” and “John”; Excel treats those as different names.
The results do not include new names. Confirm that the formula’s source range includes the new rows. A formula referring to A2:A100 will not include a name entered in A101. Expand the range or adjust the reference, and confirm that the result area has room for any additional names.
Frequently Asked Questions
Does UNIQUE remove duplicates from my original column?
No. UNIQUE returns a separate result and leaves the source values unchanged. Use Remove Duplicates on a copy if you want to remove entries from a list.
Can I find unique names without a formula?
Yes. On Windows desktop Excel, use Data > Sort & Filter > Advanced and select “Unique records only” to copy a distinct list. For a direct cleanup, use Data > Remove Duplicates on a copy of the names.
How do I get only names that appear once?
Use UNIQUE with its exactly-once argument set to TRUE, as shown in the fifth step. A repeated name will be excluded entirely rather than shown once.
Will the UNIQUE list update when I add names?
It recalculates when values inside its referenced range change. If you add rows outside that range, expand the reference or use a table-based reference that includes the new rows.
Can I use these steps in Excel for the web or Mac?
The UNIQUE and FILTER formula approach works in current Microsoft 365 versions on Windows, Mac, and the web. Ribbon commands can be arranged differently on Mac, and Advanced Filter may not be available in the web app; use the formula or Remove Duplicates there if needed.
Related
- How to Enable Track Changes in Excel
- Enable Scroll Lock in Excel: Keyboard and On-Screen Methods
- How to Enable the Share Option in Excel for Collaboration
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concat Excel Cell Range: Join Cells With Simple FormulasWith the values to join in A2:A5, enter =CONCAT(A2:A5) to combine the range into one text string; for…
- EDATE in Excel: How to Add Years to a Date CorrectlyExcel’s EDATE function adds months, not years. To add a whole number of years to a date in…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- Excel Formula to Remove Duplicates From a List AutomaticallyWith values in A2:A20, enter =UNIQUE(A2:A20) in an empty cell to return one copy of each distinct value.…
- Excel Filter: Exclude Specific Text from ResultsIn Excel for Microsoft 365 on Windows, open the filter arrow on the column you want to check,…
- Add Days to Date in Excel vs. EDATEIf A2 contains a date and B2 contains a number of calendar days, add days in Excel with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.