Unique Excel Values: Shortcuts and the UNIQUE Function

Formulas & functions · Updated November 18, 2026

Excel has no single keyboard shortcut that instantly lists unique values. In Microsoft 365 for Windows, the quickest repeatable method is to enter =UNIQUE(A2:A100) in an empty cell; for a one-time result, use Data > Advanced to copy distinct records, or Data > Remove Duplicates to edit a copied list.

What unique means in Excel

People use unique to describe two related results. A distinct list contains one copy of every different value, even if a value appears many times in the source. A list of values that occur exactly once excludes anything repeated. Excel’s UNIQUE function can return either result, but its default is distinct values.

For example, if a sales column contains North, South, North, West, South, East, a distinct list is North, South, West, East. A list of values occurring exactly once is West, East. This distinction matters when preparing customer lists, checking categories, or looking for one-off entries: removing duplicates and identifying values that appear once are not the same task.

There is also a practical distinction between creating a separate list and changing the original data. A formula or Advanced Filter can produce a separate result. Remove Duplicates deletes repeated records from the selected range, so it is best used on a copy when the original rows need to be preserved.

Use the UNIQUE function for a live list

In Microsoft 365 for Windows, select an empty cell outside the source data and enter a UNIQUE formula. With source values in A2:A8, enter =UNIQUE(A2:A8) in C2. If A2:A8 contains North, South, North, West, South, East, the result spills down from C2 as North, South, West, East, in the order each value first appears.

The cells below the formula are part of its spill range. Leave them empty so Excel can display the full result. If one of those cells contains content, Excel may show a #SPILL! error; clear the obstruction or move the formula. When a value in the source range changes, the result updates automatically.

UNIQUE accepts optional arguments to control what it returns. Its syntax is =UNIQUE(array,[by_col],[exactly_once]). The array is the range or array to inspect. The optional by_col argument determines whether Excel compares columns rather than rows. The optional exactly_once argument asks Excel to return only items that occur one time.

With the same values in A2:A8, enter =UNIQUE(A2:A8,,TRUE) in C2 to return only West and East, because each appears once. The blank second argument leaves by_col at its default. This is useful for finding one-off codes or categories, but it is not a replacement for a distinct list when the goal is simply to remove repeated labels.

Keyboard shortcuts that help with the task

Although there is no dedicated UNIQUE shortcut, a few keyboard actions make the workflow quicker. In Windows Excel, Ctrl+C copies a selected range and Ctrl+V pastes it; Ctrl+Z undoes an action. These are useful when preserving the source before using a destructive command. To select a contiguous data region, click inside it and press Ctrl+A; depending on the selection and worksheet, the first press selects the current data region and another press can expand the selection.

  • Open the formula route: type an equals sign in a blank cell, enter UNIQUE, and select the source range with the mouse or keyboard. Press Enter to calculate the result.
  • Turn filters on or off: Ctrl+Shift+L toggles AutoFilter for a selected range or table. Filtering can help inspect categories, but it does not by itself create a separate unique list.
  • Reach the ribbon: press Alt in Windows Excel to display Key Tips, then follow the letters shown on the screen for the current ribbon. Key Tips can vary by Excel version, language, and interface, so use the displayed letters rather than relying on a memorized sequence.

On a Mac, Command+C, Command+V, and Command+Z are the usual equivalents for copy, paste, and undo. Ribbon and filter shortcuts can differ by Excel release and keyboard layout. If a sequence does not work, use the visible Data tab command or check Excel’s current shortcut help. Excel for the web also supports many familiar shortcuts, but browser and operating-system combinations can intercept some keys.

Choose between UNIQUE, Advanced Filter, and Remove Duplicates

Use the method that matches whether the result should update and whether the source must remain intact:

  • UNIQUE: best for a dynamic list that should respond when source values change. It requires Microsoft 365 or Excel 2021 or later. It returns an array in worksheet cells and does not remove anything from the source.
  • Advanced Filter: useful for a one-time distinct copy without a dynamic formula. Select the data, choose Data > Advanced, select Copy to another location, specify a Copy to destination, and check Unique records only. Include the header row in the list range when the data has headers. The copied result will not automatically refresh when the source changes.
  • Remove Duplicates: useful when the intended result is a cleaned range with repeated records deleted. Choose Data > Remove Duplicates and select the columns Excel should use when deciding whether rows match. It changes the selected data, so copy the range or worksheet first if the original needs to be retained.

For instance, to create a distinct customer-region list while keeping the original sales table, use UNIQUE on the relevant columns or Advanced Filter with a separate destination. To remove repeated full rows from a disposable copy of a downloaded file, Remove Duplicates may be more direct. The right choice depends on what counts as a duplicate: matching customer names alone is different from matching every field in a row.

Return distinct rows or sort the results

UNIQUE can compare complete rows, not only a single column. With customer names in A2:A6 and regions in B2:B6, enter =UNIQUE(A2:B6) in D2. Excel returns each different name-and-region combination once. If a customer appears in two regions, those rows remain distinct because the full row differs.

If the desired result is a unique list in alphabetical order, combine UNIQUE with SORT. With categories in A2:A100, enter =SORT(UNIQUE(A2:A100)) in C2. The formula first removes repeated entries and then sorts the resulting list. A sorted output may be easier to scan, but it will not retain the original first-occurrence order.

These examples require dynamic-array support. UNIQUE, SORT, and similar modern functions are available in Microsoft 365 and Excel 2021 or later. Older perpetual versions may display a _xlfn prefix or fail to calculate the function. In those versions, Advanced Filter is a practical built-in alternative; a helper formula or Power Query may also suit a larger repeatable cleanup, depending on the workbook.

Common problems and how to avoid them

A #SPILL! error usually means the cells where the array result needs to appear are not available. Clear those cells, remove merged cells blocking the output, or move the formula to a wider or longer empty area. The formula itself can be valid even when Excel cannot place its result.

Values that look identical may not be identical to Excel. A trailing space, a nonbreaking space copied from a web page, or a number stored as text can create separate entries. Check the source cells, standardize imported text where appropriate, and confirm whether differences in capitalization or formatting matter to the task. Cell formatting alone does not necessarily change the underlying value.

Another frequent mistake is selecting the wrong range. Including a heading in the source can make the heading appear as an item in a UNIQUE result. Leaving out a relevant column can also make unlike records appear equivalent. For Advanced Filter and Remove Duplicates, verify the selected range and column checkboxes before running the command. In Remove Duplicates, Excel treats the chosen columns together when deciding which rows are repeats.

Finally, a dynamic list is not a static snapshot. If a report needs a fixed list, copy the formula result and use Paste Special > Values, or use Advanced Filter to copy records to another location. Keep the formula version when ongoing updates are more useful than a frozen result.

Frequently Asked Questions

Is there a keyboard shortcut for unique values in Excel?
There is no dedicated built-in shortcut that directly generates a unique list. Use the UNIQUE function for a dynamic result, Data > Advanced for a distinct copy, or Data > Remove Duplicates to delete repeats from a selected range.

How do I get unique values in Excel without a formula?
Use Data > Advanced, choose Copy to another location, set a destination, and select Unique records only. This creates a separate result, but it does not update automatically when the source changes.

Does Remove Duplicates keep the first occurrence?
Excel removes duplicate rows from the selected range and retains the first occurrence it encounters, based on the columns selected for comparison. Make a copy first if you need to preserve the original records or verify the result.

Why is UNIQUE not working in my Excel version?
UNIQUE is available in Microsoft 365 and Excel 2021 or later, not in every older desktop edition. If the function is unsupported, use Advanced Filter or another method available in that version.

Can UNIQUE return values that appear only once?
Yes. Set its exactly_once argument to TRUE, as in =UNIQUE(A2:A8,,TRUE). That returns items occurring once, rather than one copy of every distinct item.

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.