Copy Unique Values in Excel: A Step-by-Step Guide

Formulas & functions · Updated November 20, 2026

To copy distinct values from a list in Excel for Microsoft 365, enter =UNIQUE(A2:A100) in an empty cell, then copy the results and use Home > Paste > Values to create a separate, fixed list. If you need to remove duplicates from the original data or use an older Excel version, use the built-in Remove Duplicates or Advanced Filter tools instead.

What You’ll Need

  • An Excel workbook with the list you want to clean up. The examples below assume the values are in column A, with a heading in A1 and data in A2:A100.
  • A blank area for the result. Leave enough empty cells below the formula for the unique values to appear.
  • Excel for Microsoft 365 for the UNIQUE and FILTER formulas. UNIQUE is also available in Excel 2021; the VSTACK method for combining lists requires Microsoft 365 or Excel 2024.

Decide whether you want to preserve the original list. A formula keeps the source intact and updates when it changes; Remove Duplicates edits the selected data directly. For a separate one-time copy, use a formula or Advanced Filter.

Step-by-Step

  1. Check and select your source list. Identify the cells containing the values, excluding the heading if there is one. In the examples, the heading is in A1 and the list is A2:A100. Avoid including unrelated columns, since that can change what Excel treats as a duplicate record. If the list is an Excel table, you can select its data column instead; the instructions below use ordinary cell references for clarity.
  2. Choose where the results should go. Click a blank cell outside the source list, such as C2. Leave the cells below it clear, and make sure the destination is not inside a merged range or another table that would block the formula results. The source column will remain unchanged when you use UNIQUE.
  3. Enter the UNIQUE formula. With source values in A2:A100 and the destination at C2, enter =UNIQUE(A2:A100) and press Enter. Excel returns one copy of each distinct value in the order each value first appears. For example, if the source is Red, Blue, Red, Green, Blue, the result is Red, Blue, Green. This is a live formula result, not yet a separate static copy.
  4. Check the spilled results. Excel fills the cells below C2 automatically; this is called a spill range. Confirm that the list looks complete and that each value appears once. If Excel shows a spill error, clear any contents or merged cells in the intended output area, then check the formula again. Do not type into the middle of the results, because that can block the spill.
  5. Copy the results to a separate location as values. Select the displayed results, copy them, click a different blank destination, and choose Home > Paste > Values. On Windows, Ctrl+C copies; on Mac, use Command+C. Pasting to a different location matters: a spilled formula cannot normally be replaced by editing just part of its own spill range. The pasted list contains values rather than a formula, so it will not change when the source list changes.
  6. Decide whether to keep the formula. Keep the original UNIQUE formula if you want an automatically refreshed list as the source changes. Keep the pasted values if you need a fixed snapshot for a report, upload, or handoff. You can retain both: one live formula list and one static copy. If you no longer need the formula output, remove the formula cell only after confirming that the separate pasted copy is correct.
  7. Exclude blank cells if needed. UNIQUE can return a blank entry when the selected range contains empty cells. With the source in A2:A100 and the output in C2, enter =UNIQUE(FILTER(A2:A100,A2:A100<>"")) to filter empty cells out before returning distinct values. This also excludes cells whose formulas return an empty string. Use the regular UNIQUE formula if a blank is a meaningful list item you want to retain.
  8. Combine two lists before finding unique values. If one list is in A2:A20 and another in C2:C20, enter =UNIQUE(VSTACK(A2:A20,C2:C20)) in an empty cell. VSTACK places the second range beneath the first, and UNIQUE removes repeated values across the combined result. This method requires Microsoft 365 or Excel 2024. To make a fixed copy, copy the resulting spill range and paste values into a separate location as in step 5.
  9. Use Remove Duplicates for an edited source list. If you do not need to preserve the original, select the list and choose Data > Remove Duplicates. Confirm whether your selection has a header, leave only the relevant column checked, and select OK. Excel keeps the first occurrence and deletes later duplicates from the selected range. Copy the data to a spare worksheet or column first if you may need the full original later.
  10. Use Advanced Filter when you need a separate result or an older Excel version. Include a heading above the source values, then choose Data > Advanced. Select Copy to another location, set List range to the headed source range, enter a destination in Copy to, and check Unique records only before selecting OK. Use a matching heading at the destination if the dialog requires one. Advanced Filter is useful when UNIQUE is unavailable, but the result is a snapshot; run the filter again after changing the source.

Common Mistakes

Confusing distinct values with values that occur once. The normal UNIQUE formula returns one instance of every value, even if it appeared many times. For example, if Blue occurs four times, Blue still appears once in the result. The exactly-once option does something different: it returns only values that appeared once in the source.

Assuming a formula has made a permanent copy. UNIQUE creates a dynamic result linked to the source. If the source changes, the output can change too. Use Paste Values in another location when you require a fixed list; copying the formula itself will not make it static.

Including the heading or extra columns by accident. Selecting a header as part of a one-column formula can make the heading appear among the results. With Remove Duplicates, selecting multiple columns tells Excel to compare whole rows, not just one field. Check the selected range and the column checkboxes before confirming.

Expecting Excel to treat nearly identical text as identical. A trailing space, a different spelling, or an invisible character can make two entries appear distinct. For example, Excel may see a value with a trailing space as different from the same word without it. Review suspicious entries and clean the source data before drawing conclusions from the unique list.

Trying to type into a spill range. Cells filled by a dynamic-array formula belong to the formula in its upper-left cell. Typing into one of those cells can trigger a spill error or prevent the output from expanding. Make edits in the source data or move the output to a clear area.

Tips

Use a range that allows for the expected growth of your list. A reference such as A2:A100 is easy to understand, but new entries below row 100 will not be included. If the source is an Excel table, table columns expand as you add records; you can use the table column as the formula input, for example =UNIQUE(Table1[Item]) when the table is named Table1 and its column is named Item.

Sort the unique results only if a particular order is useful. With values in A2:A100 and the formula entered in C2, use =SORT(UNIQUE(A2:A100)) to return the distinct values in sorted order. This changes the output order from first-appearance order to Excel’s sort order, which can be easier to scan but may not match the sequence of the source list.

If you need to identify values that occur exactly once, rather than one representative of each distinct value, use the third UNIQUE argument. With the list in A2:A100 and the result starting at C2, enter =UNIQUE(A2:A100,FALSE,TRUE). For a source of Red, Blue, Red, Green, this returns Blue and Green because Red appears more than once. This is not the same as the usual task of making a duplicate-free list.

For a quick quality check, compare the number of results with what you expect and scan for blanks, spelling variations, or extra spaces. If the list feeds a report, consider keeping the source data and the final pasted values on clearly named worksheets. That makes it easier to tell which list is live and which is a saved snapshot.

When copying with the keyboard, Windows uses Ctrl+C and Mac uses Command+C. Paste Values is available from the Home tab; the exact placement of its button can vary with the ribbon layout. If you cannot see it, open the Paste dropdown and choose the values-only option rather than ordinary Paste.

Troubleshooting

The formula displays #SPILL!. Check the cells below and beside the formula for existing content, merged cells, or another obstruction in the spill area. Clear the obstruction or move the formula to a larger empty area. A cell that looks blank may contain a space or an old formula, so select it and clear its contents if appropriate.

Excel displays #NAME? for UNIQUE or VSTACK. The installed Excel version may not support that function, or the function name may be misspelled. UNIQUE is available in Microsoft 365 and Excel 2021 or later; VSTACK requires Microsoft 365 or Excel 2024. For unsupported versions, try Data > Advanced or Data > Remove Duplicates instead.

The output includes a blank or zero. Check whether the source range contains empty cells or formulas that return empty text. Use the FILTER version shown above to exclude blanks. If a zero is a real value in the source, do not exclude it unless that is what you intend.

Values that look the same still appear twice. Inspect the source cells for leading or trailing spaces, hidden characters, and inconsistent spelling. Edit or clean those source entries, then let the dynamic formula recalculate or run Advanced Filter again. If you used Remove Duplicates, undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the result is wrong, then correct the source and repeat.

Remove Duplicates changed the original list. That command deletes duplicate rows from the selected range; it is not a copy operation. If you need the original, undo the command if it is still available, or restore a saved copy of the workbook. For next time, duplicate the data to another sheet first or use UNIQUE or Advanced Filter with a separate destination.

Frequently Asked Questions

Does UNIQUE remove duplicates from the source list?
No. It displays a separate result and leaves the source cells as they are. Use Data > Remove Duplicates if you want Excel to delete duplicate entries from the selected data.

How do I copy the result without keeping a formula?
Copy the full displayed result range, click a different destination, and choose Home > Paste > Values. The destination will contain fixed values and will not update with the source.

Can I copy unique values from two columns or lists?
Yes. In Microsoft 365 or Excel 2024, combine vertical ranges with VSTACK inside UNIQUE, as shown above. If your lists are arranged horizontally, consider first placing them in one column or using a suitable stacking function for their layout.

Does UNIQUE keep the first value’s position?
Yes. By default, it returns distinct values in the order of their first appearance. Wrap it in SORT if you prefer an alphabetical or numerical ordering.

Can I get only entries that occur exactly once?
Yes. Use the third argument set to TRUE, such as =UNIQUE(A2:A100,FALSE,TRUE) when the source is A2:A100. That filters out values with two or more occurrences; it does not simply make one copy of every distinct value.

What is the best option for older Excel?
Use Data > Advanced and select Unique records only to copy a duplicate-free list elsewhere. Use Data > Remove Duplicates if you want to clean the selected range itself. Save or copy the source first if it must be preserved.

Related

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.