In Microsoft 365, use =UNIQUE(A2:A100) to return a list of distinct values from A2:A100. This formula leaves the original data untouched; to delete duplicate entries from the source, select the range and choose Data > Remove Duplicates.
The UNIQUE formula: what it does
UNIQUE is the simplest Excel equation for getting a de-duplicated list. It checks the supplied range and returns each distinct value once, in the order in which it first appears. The results spill into cells below the formula automatically, so you enter the formula in one cell rather than copying it down.
For example, with the following values in A2:A7: Red, Blue, Red, Green, Blue, and Yellow, the formula returns Red, Blue, Green, and Yellow. The first Red and first Blue determine where those values appear in the result. If values are later added within the referenced range, Excel recalculates the list.
UNIQUE is available in Microsoft 365 and Excel 2021 or later. It also works in Excel for the web. If you have an older version without this function, use Excel’s Remove Duplicates command or one of the alternatives described below.
Return unique values from one column
With a single list in A2:A100, enter this formula in an empty cell outside the source data:
=UNIQUE(A2:A100)
The result is a dynamic array: it expands as far as needed and updates when the source values change. Leave the cells where the results may spill empty. If they contain other data, Excel displays a #SPILL! error instead of overwriting those cells.
The formula includes blank cells in its source range. If the range contains blanks, a blank can appear in the result. One simple way to avoid that is to choose a source range that does not include unused rows. If you need to exclude blanks from a changing range, you can combine UNIQUE with FILTER, but the right formula depends on how the source is laid out and whether blanks are meaningful.
Remove duplicate rows, not just duplicate cells
When your data has several columns, decide whether a duplicate means an identical row or a repeated value in one particular column. UNIQUE applied to a multi-column range compares complete rows. It keeps rows whose combinations of values differ, even if one field repeats.
With records in A2:B100, where column A contains a name and column B contains a department, enter this formula in an empty area:
=UNIQUE(A2:B100)
Excel returns one copy of each distinct name-and-department combination. If Jordan appears in both Sales and Support, both rows remain because the rows are not identical. If the same name-and-department pair occurs more than once, only its first occurrence appears in the result.
This distinction matters for customer lists, inventory, and transaction data. A repeated customer name does not necessarily mean the entire record is redundant: the customer might have different contact details or purchases. Compare all columns that define a genuinely duplicate record before deleting anything.
Return values that occur exactly once
Sometimes “remove duplicates” means show only values that appear one time, excluding every value that occurs more than once. UNIQUE has an optional exactly_once argument for this different task.
With values in A2:A8, where the list is Red, Blue, Red, Green, Blue, Yellow, and Yellow, enter:
=UNIQUE(A2:A8,FALSE,TRUE)
The result is Green only: Red, Blue, and Yellow each occur twice, so they are excluded. The second argument is FALSE because the formula is working with a single column rather than comparing rows; the third argument requests values that occur exactly once. This is not the same as keeping the first copy of each value. For the usual de-duplicated list, leave the third argument out.
Keep one record for each ID or key
A common spreadsheet task is to keep one record per ID while returning other columns from the first matching record. UNIQUE alone compares whole rows, so it will not collapse records just because their IDs match. In Microsoft 365 or Excel 2021 and later, XLOOKUP can return fields for each distinct key.
Suppose IDs are in A2:A6, product names in B2:B6, and prices in C2:C6. Enter this formula in a clear area:
=XLOOKUP(UNIQUE(A2:A6),A2:A6,B2:C6)
The formula returns the distinct IDs and the product name and price from the first matching row for each ID. If the IDs are P-1, P-2, P-1, P-3, and P-2, the returned records correspond to P-1, P-2, and P-3, in that order. XLOOKUP returns the first match by default. Check that this is the record you intend to keep; if later records are more current, sort or otherwise identify the preferred record first.
Formula versus the Remove Duplicates command
A formula and Excel’s built-in command solve related but different problems. UNIQUE creates a separate, calculated result. The source remains as it was, and the output changes when the source changes. This is useful for summaries, dropdown lists, reports, and a non-destructive view of distinct records.
The Remove Duplicates command changes the selected data by deleting repeated rows. In Excel for Microsoft 365 on Windows:
- Select the range or click inside the table you want to clean. If the range has headings, make sure Excel recognizes the header row.
- Choose Data > Remove Duplicates.
- In the dialog, select the columns Excel should compare. Select every column to remove only identical complete rows; select one or more key columns to treat repeated keys as duplicates.
- Choose OK and review Excel’s message showing how many duplicate values were removed and how many unique values remain.
This operation deletes rows from the selected data, so make a copy first if you might need the original. Excel for Mac also provides Remove Duplicates on the Data tab, though the ribbon and dialog presentation can vary by version. In Excel for the web, look for the command on the Data tab; if it is unavailable in your workbook or interface, use the desktop app or create a UNIQUE result instead.
On Windows, Ctrl+Z can undo a recent Remove Duplicates operation if it is still in the undo history. On Mac, use Command+Z. Undo is not a substitute for keeping a backup, especially if other edits have occurred since the data was removed.
Common problems and how to fix them
- #SPILL! appears: Clear the cells where the results need to appear. A merged cell or another obstruction in the spill area can also prevent the array from expanding.
- Values that look the same are both listed: They may differ because of leading or trailing spaces, nonprinting characters, punctuation, or different underlying values. For instance, “Maple” and “Maple ” are not identical text. Clean or standardize the source before deduplicating if those differences should not count.
- A formula result seems not to update: Confirm that the formula’s range includes the new data. A formula referring to A2:A100 will not include a new entry in A101. Excel tables can make expanding source data easier to manage, but verify the formula references the intended table column.
- The wrong rows disappear: When using Remove Duplicates, inspect the selected columns in the dialog. Comparing only an ID column deletes all but one row for each repeated ID, even when other fields differ.
- UNIQUE is not recognized: Your Excel version may not support dynamic-array functions. Check the version or use Remove Duplicates, Advanced Filter, or a PivotTable instead.
Excel’s duplicate comparisons are not a full data-cleaning system. In particular, two spellings that differ by a character are usually distinct values, not duplicates. Decide whether differences in capitalization, spaces, or formatting should matter, then normalize the data consistently before using a formula or deleting records.
Options in older Excel versions
Excel 2019 and earlier versions do not support the UNIQUE function. The most direct option is Data > Remove Duplicates, which is available in many older desktop editions as well. As with current Excel, it modifies the selected data, so copy the range or save a separate workbook first.
For a separate distinct list in older versions, Advanced Filter can copy unique records to another location. Select the list, open Data > Advanced, choose Copy to another location, specify the copy destination, and select Unique records only. The exact dialog layout can differ between releases. Advanced Filter is a command rather than a live formula: if the source list changes, run the filter again to refresh the copied results.
A PivotTable can also summarize distinct categories, but it is designed for arranging and summarizing data rather than simply replacing a duplicate-removal formula. The best choice depends on the goal: use UNIQUE for a live list, Remove Duplicates to clean the original range, and Advanced Filter when an older Excel version needs a separate copied list.
Frequently Asked Questions
Does UNIQUE delete duplicates from the original data?
No. UNIQUE displays a distinct result in another area and leaves the source unchanged. To delete repeated rows from the original selection, use Data > Remove Duplicates.
How do I remove duplicates but keep the first occurrence?
For a live list, use UNIQUE on the source range; it returns each value at its first-seen position. For a permanent change to the selected data, use Remove Duplicates and choose the columns that define a duplicate.
How do I remove duplicate rows across multiple columns?
Use UNIQUE on the full multi-column range to return distinct row combinations. With the Remove Duplicates command, select all columns if only fully identical rows should be removed.
Can Excel return only values that appear once?
Yes. In Microsoft 365 or Excel 2021 and later, use UNIQUE with its exactly_once argument set to TRUE. This excludes values that occur more than once instead of keeping one copy of each.
Why does Excel say UNIQUE is not a valid function?
The installed Excel version may be older than Excel 2021 or may not include the function. Use the Remove Duplicates command or Advanced Filter, or open the workbook in a version that supports dynamic-array functions.
Related
- Excel Consolidated Sum: Combine Data Across Sheets
- Excel: Consolidate Two Columns Into One Without Losing Data
- Excel Consolidate Worksheets Into One: Methods & Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Length: Character Limits and the LEN FunctionExcel cells can contain up to 32,767 characters, and the LEN function counts the characters in a cell’s…
- VLOOKUP and HLOOKUP in Excel: Differences and ExamplesVLOOKUP searches down the first column of a table and returns a value from a column to its…
- Excel Sum If Cell Is Yellow: Practical Ways to Do ItExcel cannot directly sum cells by fill color with SUMIF or another built-in worksheet formula. To total yellow…
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- Excel MATCH Expressions: Syntax and ExamplesExcel’s MATCH function finds a value in a one-dimensional range and returns its position within that range—not the…
- Excel Character Count: Use the LEN FormulaTo count characters in an Excel cell, use the LEN function: if the text is in A2, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.