In Excel for Microsoft 365, use =UNIQUE(A2:A100) to create a separate list containing each value once; the original data remains unchanged. To identify repeated entries in place, use a COUNTIF formula, or use Data > Remove Duplicates when you want Excel to delete duplicates from the selected data.
Choose the right way to remove duplicates
“Remove duplicates” can mean a few different things in Excel, and the best method depends on what you want to happen to the source data. A formula can create a clean output list or label repeated records, but formulas do not erase the source entries. Excel’s built-in Remove Duplicates command changes the selected data by deleting duplicate rows.
- Make a separate list of distinct values: Use
UNIQUE. This is convenient when the source should stay intact and the result should update as the data changes. - Find or filter repeated entries: Use
COUNTIFfor one column, orCOUNTIFSwhen a duplicate depends on several columns. - Permanently remove duplicate rows from the selected range: Use Data > Remove Duplicates. Make a backup first if you may need the deleted rows.
These methods use different definitions of a duplicate. For example, two records with the same customer name but different order numbers are duplicates if you are checking names alone, but not if you are checking the entire record. Decide which columns define a match before you remove anything.
Return unique values with the UNIQUE formula
With the values you want to clean in A2:A8, enter =UNIQUE(A2:A8) in an empty cell outside the source range. The formula returns each distinct value once, in the order in which it first appears. For example, if A2:A8 contains North, South, North, East, South, West, East, the result is North, South, East, West.
UNIQUE is a dynamic array function in Microsoft 365 and Excel 2021 or later. The results spill into as many cells below the formula as needed. Keep that spill area empty; if another value blocks it, Excel displays a #SPILL! error. The result changes when the source range changes, though a fixed reference such as A2:A8 will not automatically include data entered below row 8. For a growing list, use an Excel table column reference, such as =UNIQUE(Table1[Region]), after converting the data to a table with Insert > Table.
The formula produces a new list rather than cleaning the cells in place. If you need a static list to send or paste elsewhere, copy the spilled results and use Paste Special > Values. This separates the result from the source formula; it will no longer update when the source changes.
Remove duplicate rows, not just repeated values
When a record has several fields, use the whole set of columns to find rows that match across every field. With records in A2:C100, enter =UNIQUE(A2:C100) in an empty area. Excel returns one copy of each distinct row, preserving the relationship among the values in each record. For example, two rows with the same name but different email addresses remain separate because the rows are not identical across all three columns.
By default, UNIQUE compares rows when its input contains multiple columns. It can also compare columns, but that is less common for ordinary tables. The optional third argument changes the result to values or rows that occur exactly once. With values in A2:A8, =UNIQUE(A2:A8,,TRUE) returns only values appearing one time in the range. In the earlier North, South, North, East, South, West, East example, that result is West. This is different from a normal distinct list, which keeps one copy of every value regardless of how often it occurs.
Use a range that contains the fields you actually want to compare. Including a unique order ID means otherwise identical rows will not count as duplicates, while leaving out a meaningful field can combine records that should stay separate. A sensible check is to compare a few returned records against the source before using the cleaned list for reporting.
Flag duplicate entries with COUNTIF
With names in A2:A20, enter =COUNTIF($A$2:A2,A2)>1 in B2 and fill it down. The formula returns FALSE for the first occurrence of a name and TRUE for each later occurrence. For example, if A2:A5 contains Lee, Kim, Lee, Lee, the results are FALSE, FALSE, TRUE, TRUE. Filter column B for TRUE to inspect or delete the later copies without automatically removing the first one.
The expanding range, $A$2:A2, is important: when copied down, its ending row moves while its starting row stays fixed. Each formula therefore counts how many times the current value has appeared from the first data row through the current row. If you want a label instead of TRUE or FALSE, with names in A2:A20 and the formula in B2, use =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First entry") and fill down. The first occurrence is labeled First entry; subsequent occurrences are labeled Duplicate.
COUNTIF is not case-sensitive, so Lee and LEE are treated as the same value. It also supports wildcard characters in criteria, which can affect results if the data contains characters such as an asterisk or question mark. For ordinary names and IDs this is rarely an issue, but it matters when literal wildcard characters are part of the data.
Check duplicates using more than one column
Sometimes a duplicate is defined by a combination of fields rather than by one value. For example, the same product can appear on many different dates, but a repeated product-and-date pair may indicate a duplicate transaction. With product names in A2:A20 and dates in B2:B20, enter =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1 in C2 and fill it down. TRUE marks a product-and-date combination that has appeared earlier; the first matching pair remains FALSE.
For a full-row comparison, include each field that determines whether two records are the same. With values in columns A through C and data beginning in row 2, a formula in D2 can be =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)>1. Copy it down to mark later occurrences of matching A, B, and C combinations. If blank fields are valid parts of a record, decide whether two rows with the same blanks should count as duplicates; the formula treats matching blanks as matches.
For a simple multi-column cleanup, the built-in command is often easier: select the table, choose Data > Remove Duplicates, and tick the columns that define a duplicate. If you select only some columns in that dialog, Excel compares those fields and removes entire rows when it finds matches. That choice can discard records with different values in unchecked columns, so confirm the selected columns carefully.
Use Excel’s Remove Duplicates command
When the goal is to change the source table itself, select a cell in the data and go to Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers, select the columns to compare, and choose OK. Excel reports how many duplicate values or rows it removed and how many unique values remain. The command keeps one occurrence and deletes the other matching rows from the selected range.
Before running it, consider these safeguards:
- Save a copy of the workbook or duplicate the worksheet if you might need the removed records later.
- Check that the selection includes the full table. If you select only one column instead of the whole records, the remaining columns may no longer align correctly with the cleaned values.
- Review the column checkboxes. Selecting only Email, for instance, treats rows with the same email as duplicates even if their names or dates differ.
- Sort or inspect the data first if you need to control which occurrence is retained. Remove Duplicates keeps one matching entry, so arrange the rows before running the command if the retained record matters.
On Microsoft 365 for Windows, the menu path is Data > Remove Duplicates. Excel for Mac also provides Remove Duplicates on the Data tab; the dialog may look different, but the key choice is still which columns Excel compares. In Excel for the web, use the Data tab’s Remove Duplicates command when available in the current interface, or use a formula-based distinct list if you want to preserve the source.
Formula versus command: what changes?
A formula is usually safer for exploration because it leaves the original data alone. It is also repeatable: a UNIQUE result recalculates when its source changes. The tradeoff is that the result is a separate view, not a deletion from the source. It can also require room for a spilled array and a version of Excel that supports the function.
Remove Duplicates is direct and works on the selected data, which is useful when preparing a fixed dataset. But it is destructive in the sense that the extra rows are removed from that range. Undo may restore the change immediately with Ctrl+Z on Windows or Command+Z on Mac, but do not rely on Undo after making other edits or closing the file. A saved copy is the safer recovery option.
Conditional Formatting can highlight duplicate values without removing them: select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This visual method is helpful for review, but it does not define a multi-column record match as flexibly as COUNTIFS or the Remove Duplicates dialog.
Common issues and how to avoid them
- Extra spaces: “North” and “North ” may look alike but are different text values. Clean inconsistent spacing before deduplicating. With source text in A2:A100, a helper formula such as
=TRIM(A2)in B2 removes ordinary leading, trailing, and repeated internal spaces; fill it down, then use the cleaned column. - Numbers stored as text: A number and a text string that looks like that number can behave differently in comparisons and calculations. Check the cell format and convert inconsistent entries to a consistent type before relying on a duplicate check.
- Formula output versus source data: UNIQUE spills results but does not delete source rows. If a downstream process needs a fixed range, copy the result and paste values, or use Remove Duplicates on a copy.
- Blocked spill range: If UNIQUE returns #SPILL!, clear cells in the area where its results need to appear. A formula inside an Excel table also cannot spill as a dynamic array within that table; place it in the worksheet grid outside the table.
- Unexpected matches: Excel duplicate checks are generally not case-sensitive, and hidden spaces or inconsistent data types can complicate visual checks. Standardize the fields first, then test a small set of known examples.
Frequently Asked Questions
What formula removes duplicates in Excel?
Use =UNIQUE(A2:A100) to return distinct values from that range in a separate spill range. It does not delete the original duplicates. To remove rows from the source data, use Data > Remove Duplicates.
How do I keep the first duplicate and flag the rest?
With data in A2:A100, enter =COUNTIF($A$2:A2,A2)>1 in an adjacent column and fill down. FALSE marks the first appearance; TRUE marks later appearances.
Can I remove duplicates based on two columns?
Yes. Use =UNIQUE(A2:B100) to return distinct combinations of the two columns, or use Data > Remove Duplicates and select both columns. Excel then treats a row as a duplicate only when the selected values match.
Does UNIQUE delete the original data?
No. It creates a formula-driven result elsewhere. The source remains unchanged unless you delete it yourself or use the Remove Duplicates command on the source range.
Why does Excel say #SPILL! for UNIQUE?
The cells needed for the returned list are not empty, or the formula is placed somewhere dynamic arrays cannot spill, such as inside an Excel table. Clear the output area and place the formula in a normal worksheet cell outside the table.
Does UNIQUE work in older Excel versions?
UNIQUE is available in Microsoft 365 and Excel 2021 or later. Older versions can still use COUNTIF to flag repeated values and the Remove Duplicates command, but they do not support the UNIQUE dynamic-array function.
Related
- Excel Power Query Value Filtering Explained
- Excel Power Query: Split a Cell Into Multiple Rows
- Excel Power Query and SQL: How They Work Together
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- C++ String Built-In Functions and How to Use ThemC++ does not have a special set of built-in functions devoted to strings: its everyday string type is…
- Rank in Excel With Ties: RANK.EQ and Unique RanksTo give equal values the same rank in Excel, enter a RANK.EQ formula. With scores in B2:B6, put…
- How to Add Double Quotes in an Excel Formula for TextTo show double quotation marks in text produced by an Excel formula, type each quote mark twice inside…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- CONCAT in Excel: Join Text and NumbersYes. Excel’s CONCAT function can join numbers with text or other values, but it does not preserve a…
- Excel Table Names in Formulas: How to Use Them WellYes. In Excel for Microsoft 365 on Windows, use a table name and its column header in a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.