Yes. In Microsoft 365 or Excel 2021 and later, use =UNIQUE(A2:A8) to return the distinct values from A2:A8 without repeating duplicates. Enter it in an empty cell; Excel spills the results into the cells below, updating when the source data changes.
What the UNIQUE function does
UNIQUE creates a separate list of distinct values from a range or array. It does not delete or alter the original data. That makes it useful for preparing category lists, checking which customers appear in a dataset, or feeding a drop-down list without maintaining a second list by hand.
For example, suppose A2:A8 contains Maya, Leo, Maya, Nia, Leo, Omar, and Nia. With those names in that order, the formula in the opening example returns Maya, Leo, Nia, and Omar, in their order of first appearance. Each value is shown once. The source cells remain unchanged.
UNIQUE is a dynamic array function: one formula can return several cells. The first result appears in the formula cell, and the remaining results spill into neighboring cells. You normally enter the formula once rather than copying it down. If the source list changes, the returned list recalculates automatically.
Formula syntax and its options
The function accepts an array and two optional settings: UNIQUE(array, [by_col], [exactly_once]). The brackets in this description mean that an argument is optional; they are not typed into a formula. The array is the range or data to examine.
- by_col controls whether Excel compares columns instead of rows. FALSE, the default, compares rows. TRUE compares columns.
- exactly_once controls whether the result includes every distinct value or only values that occur exactly one time. FALSE, the default, returns one copy of each distinct value. TRUE returns only values whose count in the source is one.
With names in A2:A8 in the order Maya, Leo, Maya, Nia, Leo, Omar, Nia, the usual formula returns Maya, Leo, Nia, Omar. If you need only names that occur once, with the same values in A2:A8, enter =UNIQUE(A2:A8,FALSE,TRUE); the result is Omar. Maya, Leo, and Nia are excluded because each occurs more than once.
Return unique rows from multiple columns
UNIQUE can compare whole rows, not just individual cells. This matters when a record is defined by a combination of fields. For example, two rows with the same product but different regions are distinct rows, even though the product name itself repeats.
With product names in A2:A6 and regions in B2:B6, where the rows are Tea–East, Coffee–West, Tea–East, Tea–West, and Coffee–West, enter =UNIQUE(A2:B6) in an empty area. The result contains three rows: Tea–East, Coffee–West, and Tea–West. Excel removes repeated combinations, not every repeated product name. The default setting compares rows, so you do not need to specify FALSE for by_col.
To compare columns instead, put records across columns and set by_col to TRUE. With values arranged in A1:C2 and each column representing one complete record, =UNIQUE(A1:C2,TRUE) returns each distinct column once. This option is less common for typical lists, which are usually arranged vertically, but it helps when data is laid out across a worksheet.
Sort the results or exclude blanks
UNIQUE preserves the order in which distinct values first appear; it does not automatically alphabetize them. You can combine it with SORT when a sorted result is more useful. With names in A2:A8 containing Maya, Leo, Maya, Nia, Leo, Omar, and Nia, enter =SORT(UNIQUE(A2:A8)). The result is Leo, Maya, Nia, Omar in ascending order.
Blank source cells can also affect the output. If you want a list that leaves out blank entries, first filter the source to nonblank cells, then pass that result to UNIQUE. With names or other values in A2:A10, including any blanks, enter =UNIQUE(FILTER(A2:A10,A2:A10<>"")). The output contains each nonblank value once. FILTER and UNIQUE are available in Microsoft 365 and Excel 2021 or later.
These combinations answer two common follow-up needs: a tidy alphabetized list and a list with empty entries removed. They do not change the original cells. If the source can contain errors, note that an error in the input may also appear in a dynamic-array result; clean or handle the source errors if you need a reliable list.
Use UNIQUE with an Excel Table
For a list that grows over time, an Excel Table is often more convenient than a fixed address such as A2:A100. Tables expand when new records are added and use readable column names in formulas.
Assume your table is named Sales and has a column named Customer. In a blank cell outside the table, enter =UNIQUE(Sales[Customer]). Excel returns each customer in the column once and includes new table rows as the table grows. Create or rename a table using Insert > Table, then use the Table Design tab to check its name and column headings.
Place the formula outside the table. Dynamic-array formulas cannot spill within an Excel Table, so entering this formula in a table cell can produce a spill error. Also ensure that the cells where the result needs to appear are empty; values or merged cells in the spill area can block the output.
UNIQUE versus Remove Duplicates
UNIQUE and Excel’s Remove Duplicates command solve related but different problems. UNIQUE generates a live result in a new area and leaves the source intact. Remove Duplicates changes the selected data by deleting duplicate rows, so it is better for permanently cleaning a copied dataset than for making a continuously updating list.
To remove repeated records from a range in Excel for Windows, select the data and choose Data > Remove Duplicates. Check which columns define a duplicate, then confirm. If you select multiple columns, Excel treats the selected combination of column values as the basis for identifying duplicate rows. Make a copy first if you may need the original records; the command is not a substitute for a formula-driven view.
Choose UNIQUE when you want a reusable list that responds to edits or new rows. Choose Remove Duplicates when the goal is to alter a dataset and keep only one copy of each record. A formula result is not a permanent cleanup: if you need a standalone list, copy the spilled results and use Paste Special > Values in the destination.
Common problems and practical checks
- #SPILL! usually means Excel cannot place the full result in the required cells. Clear any contents in the spill area, remove obstructing merged cells, or move the formula to a larger empty area. A spill formula inside a Table is another common cause.
- #NAME? can mean the Excel version does not recognize UNIQUE. The function is available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not support it.
- Values that look identical may remain distinct. Leading or trailing spaces, nonprinting characters, and inconsistent text can make entries differ. For example, a name with a trailing space is not necessarily the same text as the name without one. Clean inconsistent source values before relying on the result.
- Numbers and numbers stored as text may not behave as expected. Check the source data types if entries that look the same are not grouping as you intended. Formatting a cell to look like a number does not always convert text into a numeric value.
- Formula separators can vary by regional settings. The examples here use commas, as in the standard English-language syntax. Some Excel installations expect semicolons between arguments; if Excel rejects a formula, check the separator used by other formulas in that installation.
Excel’s duplicate comparisons are not a substitute for a deliberate data-cleaning policy. In particular, do not assume that different capitalization, spaces, punctuation, or spelling variations represent either the same real-world item or different ones without checking what the data means. The function returns distinct values based on Excel’s comparison behavior; it cannot decide which differences matter to your work.
Compatibility and alternatives
UNIQUE is available in Microsoft 365 and Excel 2021 or later, including supported Excel versions on Windows, Mac, and the web. The formula itself is generally the same across those editions. Microsoft 365 on Windows is the context for the steps above; the ribbon layout can differ on Mac or in Excel for the web, but the function remains a worksheet formula where that version supports it.
If you need to share a workbook with someone using an older Excel release, the formula may not calculate for them. One option is to generate the list in a compatible version and paste the results as values. For a one-time cleanup, use Data > Remove Duplicates on a copy. Older releases also offer more involved methods such as Advanced Filter or array formulas, but their steps and behavior differ from the simple, automatically updating UNIQUE formula.
For a current Excel workbook, UNIQUE is usually the clearest choice when the output should stay connected to the source. SORT and FILTER can refine the result, while the Table structured reference makes it easier to include records added later. Consider both the Excel version of every recipient and whether the output should update or remain fixed before choosing a method.
Frequently Asked Questions
How do I get a unique list in Excel without duplicates?
In Microsoft 365 or Excel 2021 and later, enter =UNIQUE(A2:A100) in an empty cell, replacing A2:A100 with your source range. Excel spills one copy of each distinct value into the cells below.
Does UNIQUE delete duplicates from my original data?
No. It returns a separate list and leaves the source unchanged. Use Data > Remove Duplicates if you intend to remove duplicate records from the selected data, preferably after making a backup copy.
How can I return only values that appear exactly once?
Use the exactly_once option set to TRUE. Unlike the default, this excludes values that occur more than once. It is useful for finding single-occurrence entries, not for creating the usual deduplicated list.
Can UNIQUE return distinct combinations across columns?
Yes. Give it a multi-column range with the records arranged in rows. By default, it returns one copy of each distinct row, so a repeated value in one column can still appear in separate results when another column differs.
Why does my UNIQUE formula show #SPILL!?
Excel cannot put the full result into the cells the formula needs. Clear the blocked cells, check for merged cells, and put the formula outside any Excel Table. Then confirm that the spill area is available.
Related
- Punch List Checklist Template for Excel: Setup and Use
- QGIS Copy Attribute Table to Excel: Steps & Tips
- Project Timeline in Microsoft Excel: Build and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIFS Does Not Contain: Excel Formula and ExamplesTo count cells that do not contain a particular text string in Excel, use a COUNTIFS criterion with…
- Excel Slicers: Use Custom Lists to Sort Items?Yes. In Excel for Microsoft 365 on Windows, a slicer can use Excel’s custom lists to order its…
- Microsoft Excel Filter Table: How to Filter Your DataTo filter a table in Microsoft Excel for Microsoft 365 on Windows, click a cell in the table…
- Paste a Formula in Excel Down a Column: Step-by-StepTo paste a formula down a column in Excel, enter it in the first cell, select that cell…
- TRIM in Excel for Text Cleanup: What It RemovesIn Excel for Microsoft 365 on Windows, use TRIM to remove ordinary spaces at the beginning and end…
- Excel PMT Function: Calculate Loan PaymentsTo calculate a fixed loan payment in Excel, divide the annual interest rate by the number of payments…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.