How to Copy Unique Values in Excel Without Duplicates

Formulas & functions · Updated November 20, 2026

To copy a deduplicated list in Microsoft Excel, enter the UNIQUE formula in an empty cell, then copy the complete result and paste it as values wherever you need it. With source values in A2:A8, enter =UNIQUE(A2:A8) in B2. Excel returns each different value once; copy the resulting cells and use Home > Paste > Values to make a fixed list. UNIQUE is available in Microsoft 365 and Excel 2021 or later. If you have an older version, use Advanced Filter instead.

What You’ll Need

  • An Excel workbook with the values you want to deduplicate. A single column is easiest to work with, but you can also find distinct rows across multiple columns.
  • For the formula method, Microsoft 365 or Excel 2021 and later. Older desktop versions can use Advanced Filter.
  • An empty area of the worksheet for the results. Leave enough space below and to the right of the formula cell for Excel to display the full result.

Step-by-Step

  1. Check and select your source data. Identify the cells that contain the values to copy. For example, suppose A2:A8 contains Pear, Apple, Pear, Plum, Apple, Kiwi, and Plum. The heading, if there is one in A1, is outside this example range. Decide whether blank cells should be included; UNIQUE can return a blank as a distinct result, so clean or exclude blank entries if you do not want one in the output. If your data is an Excel table, you can use its column as the source, but a regular cell range is simplest for a first attempt.
  2. Choose an empty output cell. Click a blank cell where the new list should start, such as B2. Make sure the cells beneath it are empty and that there is no content to the right that could obstruct the result. A formula-generated list needs room to expand; Excel displays an error if anything blocks the cells in its spill range.
  3. Enter the UNIQUE formula. With the example values in A2:A8, type =UNIQUE(A2:A8) in B2 and press Enter. Excel displays Pear, Apple, Plum, and Kiwi in B2:B5, keeping the first-seen order and removing later repeats. The formula returns distinct values, not a sorted list. If you want alphabetical order, sort the returned list after it appears or use Excel’s Sort command.
  4. Check the output before copying. Confirm the result contains the values you expect and that no warning says the array cannot spill. Compare the list with the source, especially if it contains blanks, extra spaces, inconsistent spelling, or numbers stored as text. UNIQUE compares cell values: it does not automatically treat entries with different spaces or spelling as the same value. Also note that Excel’s text comparison is not case-sensitive for this function, so Apple and APPLE are normally treated as the same value.
  5. Select all the returned cells, not just the formula cell. In the example, select B2:B5. If you select only B2, you may copy just the formula rather than the full displayed list. When the number of results is not known in advance, drag over all visible output cells or use the range shown by the spill border. Make sure the selection includes the last result and excludes unrelated cells.
  6. Copy the selected results. Press Ctrl+C on Windows. On a Mac, press Command+C. You can also right-click the selected range and choose Copy. If you want the list in another workbook, open that workbook and click the destination cell after copying.
  7. Paste values to make a separate, fixed list. Choose the destination cell, then use Home > Paste > Values. In desktop Excel for Windows, you can also press Ctrl+Alt+V, choose Values in the Paste Special dialog, and press Enter. On a Mac, use Home > Paste > Values; available keyboard shortcuts can vary by Mac keyboard and Excel release. Pasting values keeps the displayed results but removes the UNIQUE formula, so the copied list will not change when the source changes.
  8. Verify the pasted list. Click one of the destination cells and check the formula bar. A pasted value should appear as the value itself, rather than as a UNIQUE formula. Confirm that all expected entries were copied and that the destination did not overwrite existing data. Save the workbook when the list is correct.
  9. Use Advanced Filter if UNIQUE is unavailable. Select the source list, including its heading if it has one. Go to Data > Sort & Filter > Advanced. In the dialog, choose Copy to another location, check Unique records only, set Copy to to an empty destination cell, and click OK. Include a heading in the source range for the clearest results, and leave room for the output. This method produces a copied list without requiring a dynamic-array formula.

To return only entries that appear exactly once in the source, rather than one copy of every distinct value, use a different UNIQUE option. With the same values in A2:A8, enter =UNIQUE(A2:A8,,TRUE) in B2. The result is Kiwi, because Pear, Apple, and Plum each occur more than once. The empty argument between the commas leaves the by-column option at its default. This distinction matters: the regular formula removes duplicate copies, while the TRUE option excludes values that repeat at all.

Common Mistakes

  • Copying only the formula cell. A spilled result may show several values even though there is only one formula. Select the whole displayed range before copying if you need every result as a separate value.
  • Expecting the copied list to update. Paste Values creates a snapshot. If the source changes, the pasted list stays as it was. Keep the formula in place if you need a list that recalculates automatically.
  • Including a heading in the formula range. If you use a range such as A1:A100, the heading can appear in the result as if it were a value. Start at the first data row, or use Advanced Filter with a proper heading and source range.
  • Assuming similar-looking text is identical. A trailing space, a spelling variation, or a number stored as text can lead to unexpected results. Clean the source first if those differences should be treated as duplicates.
  • Confusing distinct values with values that occur once. The standard UNIQUE formula keeps one instance of every different value. It does not remove values that were repeated in the source. Use the exactly-once option only when you want to exclude every repeated item.
  • Overwriting existing cells. A UNIQUE formula needs a clear spill area. Check for hidden content, formulas, or merged cells beneath the output cell before entering it, and choose a clear destination for pasted values.

Tips

  • Keep a live list when the source changes. Leave the formula in its output cell if you want Excel to update the distinct list as source entries change. Paste values only when you need a one-time copy, such as a handoff, report, or import into another system.
  • Sort after removing duplicates. UNIQUE preserves the order in which values first appear in the source. If you need alphabetical or numerical order, select the output and use Data > Sort A to Z or Data > Sort Smallest to Largest, as appropriate. Do not sort only part of a multi-column record.
  • Use a whole-row range for distinct records. To identify distinct combinations across several columns, select the full row-width range in the formula, such as A2:C100. UNIQUE then returns each distinct row combination, not a separate list of unique entries from each column. Keep related columns together so each result remains a complete record.
  • Make a backup before replacing source data. If the purpose is to remove duplicates from the original list rather than make a copy, work on a duplicate of the data first. The Remove Duplicates command changes the selected data, while the formula and Advanced Filter approaches can put results elsewhere.
  • Use a table for expanding source data. An Excel table can make it easier to manage a growing source list. For a live formula, place the output outside the table because spilled array formulas cannot spill inside table cells. Check that the formula’s source covers new rows if your list grows.

Troubleshooting

  • The formula shows #SPILL!. Excel cannot place the full result in the cells it needs. Clear any contents in the spill range, unmerge cells in that area, or move the formula to a larger empty area. A cell that looks blank may still contain a space or another value.
  • Excel displays #NAME? or does not recognize UNIQUE. Your version may not include dynamic-array functions. Use Data > Sort & Filter > Advanced and select Unique records only, or update to Microsoft 365 or Excel 2021 or later.
  • The result includes a blank or zero that you did not expect. Inspect the source range for empty cells and formulas that return an empty string. Narrow the range to actual data or remove unwanted blanks from the source before making the distinct list. Do not delete a result until you know whether the blank reflects real source data.
  • Two entries that look alike both appear. Check for leading or trailing spaces, nonprinting characters, different punctuation, or a text-versus-number mismatch. Edit or clean the source entries so they use the same underlying value, then let the formula recalculate or run Advanced Filter again.
  • Values change after pasting. This usually means the destination still contains a formula or you pasted normally instead of as values. Undo if appropriate, copy the complete result again, and choose Home > Paste > Values. Check the formula bar at the destination to confirm the formula has been replaced.
  • Advanced Filter returns the heading as a result. Include the heading as the first cell of the List range and make sure the data range has a clear column label. If you selected only the values, remove the heading from the selection or repeat the operation with a properly headed range.

Frequently Asked Questions

Does UNIQUE remove duplicates from my original cells?
No. It creates a separate result and leaves the source unchanged. To replace the source with the deduplicated list, copy the output, paste values into the intended location, and only then remove the old data if you are certain it is no longer needed.

Can I copy unique values to another worksheet or workbook?
Yes. Select the complete result, copy it, go to the destination sheet or workbook, and choose Home > Paste > Values. This gives you a fixed list that does not rely on the original formula or source range.

Why does the result keep changing?
If the output cell contains a UNIQUE formula, Excel recalculates it when the source changes. That is useful for a live list. For a one-time copy, paste the results as values; that disconnects them from the source.

Can I get unique rows instead of unique entries in one column?
Yes. Select all the columns that make up a record in the UNIQUE formula’s source range. Excel returns each distinct row combination once. If even one column differs, those rows count as different records.

Can I use this in Excel for the web or on a Mac?
UNIQUE is available in current Microsoft 365 versions of Excel for the web and Mac as well as Windows. The formula works the same way, though ribbon layouts and keyboard shortcuts can differ. Use the Paste Values command in the ribbon if the Windows shortcut does not apply.

What is the difference between UNIQUE and Advanced Filter?
UNIQUE creates a formula-based result that can recalculate. Advanced Filter copies distinct records to another location without a dynamic-array formula and is useful in older desktop versions. Both can leave the original source list intact when you copy results elsewhere.

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.