Excel Paste Unique Values: Remove Duplicates or Use a Formula

To paste unique values in Excel, use Data > Advanced to copy distinct records to another location, or use Remove Duplicates on a copy of your data. In Microsoft 365, the UNIQUE function can generate a distinct list; copy its results and choose Home > Paste > Values to make them fixed values.

What “paste unique values” can mean

The phrase can describe two different jobs: creating a list in which repeated entries appear only once, or pasting values without formulas or formatting. Excel handles these separately. Advanced Filter, Remove Duplicates, and UNIQUE identify distinct entries; Paste Values replaces formulas with their current results. Often the complete task is to create a distinct list first, then paste that list as values.

It also matters whether you mean unique values in one column or unique rows across several columns. For example, a list containing Ana, Ben, Ana has two distinct names. A table containing Ana / North, Ana / South, and Ana / North has three rows but only two distinct combinations. Choose the method and selection to match what counts as a duplicate in your data.

Use Advanced Filter to copy unique entries

Advanced Filter is a practical choice when you want to leave the source list alone and copy distinct entries to a different area. In Excel for Microsoft 365 on Windows, prepare a range with a header row, then follow these steps:

Click a cell in the source range, or select the whole range including its header.
Choose Data > Advanced in the Sort & Filter group.
In the dialog, select Copy to another location.
Check that List range covers the source, including its header. Leave Criteria range blank if you do not want to filter by a condition.
Set Copy to to the top-left destination cell, then select Unique records only and click OK.

The copied result includes a header and one instance of each distinct record. With a single-column source, distinct names are copied once. With a multi-column source, Excel compares complete rows: two records with the same name but different regions remain separate. If you need the output as fixed values, select the result, copy it with Ctrl+C, click the destination, and choose Home > Paste > Values. On Mac, use the menu command rather than assuming Windows dialog shortcuts; Excel for the web may offer a different Advanced Filter experience, so use its available Data tools or create the list with UNIQUE.

Advanced Filter is useful for a one-time extraction and does not delete entries from the original list. It is not a live connection: changes to the source do not automatically refresh the copied result. Repeat the filter when the source changes.

Remove duplicates while keeping the first occurrence

Use Remove Duplicates when you want Excel to delete repeated records from a selected range. Because it changes the data in place, first make a copy of the worksheet or duplicate the source range. Then select the data and choose Data > Remove Duplicates. In the dialog, confirm whether the first row contains headers and choose the columns Excel should compare. Click OK to remove later matching records.

If you select several columns, Excel treats rows as duplicates only when their values match in every checked column. If you select just the Name column, Excel removes later occurrences of each name, even if those rows contain different information in other columns. That can leave a name paired with the wrong address or account details. For a related table, compare all columns that define a duplicate record; do not choose a single column merely because it contains the field you noticed repeating.

Excel keeps the first occurrence it encounters and removes subsequent matches. Sort order therefore affects which record survives. If you want the newest record, sort by date first so the newest is first, then remove duplicates using the appropriate key columns. Check the result before relying on it, especially when blank cells or near-matching text are present.

Generate a distinct list with UNIQUE

In Microsoft 365 and Excel 2021 or later, UNIQUE returns a dynamic array of distinct entries. With names in A2:A8, where the cells contain Ana, Ben, Ana, Dev, Ben, Eli, and Ana, enter =UNIQUE(A2:A8) in an empty cell. The result is Ana, Ben, Dev, and Eli, in the order each name first appears. The source remains unchanged.

The result spills into the cells below the formula. Leave that output area clear; if another value blocks it, Excel displays a #SPILL! error. A UNIQUE result updates when its source data changes, which is helpful for a report or a working list. To turn it into a fixed list, select the spilled results, copy them, and paste with Home > Paste > Values. You can also use Ctrl+C followed by Ctrl+Alt+V, choose Values in the Paste Special dialog, and press Enter on Windows. On Mac, Command+C copies; the Paste Special shortcut is Control+Command+V in current Excel for Mac, though menu access is an alternative if the shortcut differs on your setup.

UNIQUE can compare rows as well as one-column lists. With customer names in A2:A20 and regions in B2:B20, enter =UNIQUE(A2:B20) in a clear area to return each distinct name-and-region combination. The output can occupy multiple rows and columns. The optional third argument can instead return entries that occur exactly once: with names in A2:A8, =UNIQUE(A2:A8,,TRUE) returns Dev and Eli from the example because each occurs once. That is different from the usual meaning of a distinct list, which includes each value once even if it appeared repeatedly in the source.

Paste only values, not formulas or formatting

Paste Values is useful after a formula has produced the right list, or whenever copied cells contain formulas and you want their current displayed results to remain. Copy the cells, select the destination, then choose Home > Paste > Values. This pastes the calculated contents without carrying over the original formulas. It does not remove duplicates by itself: a pasted list can still contain repeats unless you first use a distinct-list method.

On Windows, Ctrl+Alt+V opens Paste Special for copied cells; choose Values and confirm. Ctrl+Shift+V may be available as a direct paste-values shortcut in current Microsoft 365 builds, but it is not as consistent across Excel versions and platforms, so the ribbon command is the dependable option. On Mac, use Home > Paste > Values or the Paste Special dialog. In Excel for the web, the ribbon’s Paste Values command is the clearest route; keyboard shortcuts and dialog options can vary by browser and build.

Paste Values also does not necessarily preserve the source’s visual appearance. If you want fixed numbers and the same number format, use the relevant Paste Special option or apply the destination format separately. For example, a date may be stored as a serial number; pasting its value into a cell formatted as General can make it look like a number rather than a date. The underlying value may be correct even when the display format differs.

Choose the right method for your situation

Keep the source intact and make a one-time copy: use Advanced Filter with Copy to another location.
Clean a copied table by deleting duplicate records: use Remove Duplicates, checking every column that defines a duplicate.
Build a list that updates as the source changes: use UNIQUE, then leave it as a formula-driven result.
Freeze a formula result for sharing or export: copy the distinct output and paste values.
Return only entries that occur once in the source: use UNIQUE’s exactly-once option, rather than the default distinct list.

For a one-time list, Advanced Filter is often straightforward. For repeatable reports, UNIQUE avoids repeating manual steps. Remove Duplicates is best understood as a destructive cleanup tool, not a way to preserve the source. If you need an auditable result, keep the original data on a separate sheet and label the extracted list with its source or date.

Common problems and details to check

Apparent duplicates may not be identical to Excel. A trailing space, a nonbreaking space copied from a website, or different punctuation can make two entries compare as different. Conversely, Remove Duplicates may treat values as duplicates even when their formatting differs. If a result looks wrong, inspect the underlying text and consider cleaning the source before extracting distinct entries. Do not assume that matching appearance guarantees identical cell contents.

Headers also matter. Advanced Filter expects a header row for a normal table-style setup, and Remove Duplicates has a setting to identify headers. If a header is treated as data, it may appear in the output as an ordinary item or be included in duplicate comparisons. Check the selected range and the header option before confirming.

Finally, Excel’s default duplicate comparisons are not a case-sensitive way to distinguish names such as Ana and ANA. If capitalization is meaningful in your data, verify the output with a method designed for case-sensitive comparison rather than relying on the standard duplicate-removal dialog. Also consider whether blanks should count as one repeated value or be excluded; different workflows may leave or return a blank entry.

Frequently Asked Questions

How do I copy and paste only unique values in Excel?
Use Data > Advanced, select Copy to another location, set the source and destination, and check Unique records only. If you also need fixed values, copy the result and choose Home > Paste > Values.

Can I remove duplicates without changing the original data?
Yes. Use Advanced Filter and copy unique records to another location, or make a separate copy of the source before using Remove Duplicates. The Remove Duplicates command changes the selected range.

Does UNIQUE keep the first occurrence?
UNIQUE returns each distinct value once and, by default, preserves the order of first appearance. It does not sort the results alphabetically. Sort the output separately if you need a particular order.

Why does Remove Duplicates keep two rows with the same name?
If other checked columns differ, Excel considers the full rows different. If you compare only the name column, it can remove later rows with that name—but doing so may discard different information in the remaining columns.

Can I paste unique values in older Excel versions?
Advanced Filter and Remove Duplicates are available in many desktop versions of Excel. UNIQUE requires Microsoft 365 or Excel 2021 and later. If UNIQUE is unavailable, use Advanced Filter to copy unique records to another location.

What is the difference between unique values and values that occur once?
A distinct list includes one copy of every value found in the source, even if it appeared many times. Values that occur once excludes anything repeated. In UNIQUE, the optional exactly-once argument produces the latter result.

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.