Extract Unique Values in Google Sheets: A Quick Guide

To extract a live list of distinct values in Google Sheets, enter =UNIQUE(A2:A) in an empty cell, replacing A2:A with your source range. The results spill into cells below and update when the source changes. To leave out blanks, use =UNIQUE(FILTER(A2:A,A2:A<>"")). Before you start, decide whether you need a changing formula result or a one-time list with duplicates removed.

What You’ll Need

A Google Sheets file open in a web browser with the data you want to check.
A source range, such as A2:A100, and an empty area where the results can expand.
Permission to edit the sheet if you plan to use the Remove duplicates command or add a formula.

The examples below use a single list in column A with a header in A1. If your data is in another column, substitute that column in each formula. Google Sheets uses commas between formula arguments in many locales; if your sheet expects semicolons, use those separators instead.

Step-by-Step

Identify the values and the range. Find the column or block of cells you want to make unique. If A1 contains the heading Fruit and the entries begin in A2, use A2:A100 for a fixed set of rows or A2:A to include future entries in the column. Do not include the header unless you want the heading treated as a value. If you need unique records based on several columns, select the full record range rather than just one column.
Choose where the extracted list should appear. Click an empty cell outside the source data, such as C2, and make sure there are enough blank cells below it. A UNIQUE formula returns an array of results; Sheets fills adjacent cells automatically. Keep the result area clear of notes, totals, merged cells, or other data that could block the output.
Enter the basic UNIQUE formula. With source values in A2:A10 and the formula destination in C2, enter =UNIQUE(A2:A10). Press Enter. If the source values are Pine, Oak, Pine, Maple, Oak, and Birch, the result is Pine, Oak, Maple, Birch. The function keeps the first occurrence of each distinct value and preserves the order in which values first appear in the source range.
Use an open-ended range if the list will grow. With entries beginning in A2 and more rows likely to be added, put =UNIQUE(A2:A) in an empty result cell. The formula considers the rest of column A, so new entries can be included without changing the range. An open-ended range is convenient, but it may include a blank result if the source column has empty cells. Use the next step if you want to exclude blanks.
Filter out empty cells. With source entries in A2:A, use =UNIQUE(FILTER(A2:A,A2:A<>"")). FILTER first keeps only cells that are not empty, and UNIQUE then removes repeated values. In this formula, the two A2:A ranges must cover the same cells. If you have a defined endpoint, such as A2:A100, use that range in both places. This approach is useful when a list has gaps or when the source range is open-ended.
Sort the unique results if order matters. With the source list in A2:A100 and the output starting in C2, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))) to filter blanks, extract distinct values, and sort the result in ascending order. For the example values Pine, Oak, Pine, Maple, Oak, and Birch, the sorted output is Birch, Maple, Oak, Pine. Sorting changes the original first-seen order, so use the unsorted formula if that order is meaningful.
Extract values that occur exactly once, if that is what you mean by unique. A distinct list includes each value once even when it appears repeatedly in the source. A list of values that occur exactly one time is different. With values in A2:A10, enter =UNIQUE(A2:A10,FALSE,TRUE). The second argument tells Sheets to compare rows, and the third asks for values that appear exactly once. For example, from Pine, Oak, Pine, Maple, Oak, Birch, the result is Maple and Birch. Add a FILTER step if you also need to omit blanks.
Make unique records from multiple columns. If each record is a combination of a name and department in A2:B100, place the formula in an empty area and enter =UNIQUE(A2:B100). Sheets returns each distinct row combination, not a separate unique list for each column. For example, the same name in two departments counts as two different records because the whole row differs. Check that the output has room to expand both across columns and down rows.
Choose a permanent cleanup instead of a live formula when appropriate. If you want to remove repeated rows from data rather than create a separate result, first make a copy of the source range or duplicate the sheet. Select the copied range, then choose Data > Data cleanup > Remove duplicates. If the selection has a header, enable the option indicating that the data has a header row. Confirm which columns should be checked and run the cleanup. This changes the selected data, while UNIQUE leaves the original source untouched and recalculates as it changes.
Check and use the output. Compare the result with the source to confirm that the intended field or row combination was used. If you need to send a fixed list elsewhere, copy the formula results and use Edit > Paste special > Values only in the destination. This pastes the current displayed values rather than a formula that depends on the original range. Keep the formula version if you want future source edits to flow through automatically.

Common Mistakes

Including the heading in the range: A formula that starts at A1 will treat the header as an ordinary entry and return it among the unique values. Start at A2 when row 1 is a heading, or deliberately include the heading only if it belongs in your output.

Confusing distinct values with values that appear once: UNIQUE normally returns one copy of every distinct value, even if it occurs many times. Use the exactly_once argument only when you want to omit values that repeat at all.

Putting data in the spill area: The formula may show an error if cells where its results need to appear are occupied. Clear the destination cells or move the formula to a larger empty area; do not delete source data to make room unless that is intended.

Removing duplicates from the original by mistake: The Data cleanup command changes selected cells. It is not the same as creating a formula-based list. Work on a copy when you may need the original order, repeated entries, or full records later.

Using the wrong range for a record list: Applying UNIQUE to only the name column discards distinctions that may matter in other fields. Use the complete set of columns that defines a record when combinations, not individual names, should be unique.

Tips

Use a clear label above the result, such as Distinct fruits, so readers know it is a generated list rather than the original data.
Use a bounded range for a small, stable table and an open-ended range when rows are regularly added. A bounded range is easier to inspect; an open-ended range needs more space for its output.
Keep the source range and output range separate. Placing a formula inside the cells it reads can create a circular reference or overwrite data.
If you want alphabetical results, wrap the UNIQUE formula in SORT. If first-seen order matters, leave SORT out.
For a one-time list, use Paste special > Values only after copying the results. This prevents the pasted list from depending on the original sheet or range.
Google Sheets formulas and menu paths differ from Excel. Excel versions that include the UNIQUE function also support dynamic results, but Excel uses its own interface and version availability; the menu path above is specifically for Google Sheets in a browser.

Troubleshooting

The formula says there is a parse error: Check that every parenthesis is paired and that the range references are valid. Your spreadsheet locale may require semicolons instead of commas between arguments. For example, replace the comma separators in the FILTER and UNIQUE formula with semicolons if the locale requires them.

The result is blocked or does not expand: Look below and beside the formula cell for existing content, including cells that look empty but contain a space or another formula. Clear the obstructing cells or move the formula. For a multi-column source, the result needs room across as well as down.

A blank appears in the output: Use the FILTER-and-UNIQUE version to exclude empty source cells. If a cell looks blank but still appears, inspect it for spaces or a formula that returns an empty string. Those are not always the same as a truly empty cell; clean the source data if needed.

Values that look identical both appear: Check for leading or trailing spaces, punctuation differences, or inconsistent data entry. A value with an extra space is not necessarily the same text as the version without it. Clean or standardize the source column before extracting the list, then review the output again.

The list is not sorted: UNIQUE preserves the order of first appearance, which may not be alphabetical. Wrap it in SORT if alphabetical order is desired. If SORT produces an unexpected order, check whether the source contains mixed data types or text formatted to look like numbers.

New entries do not show up: Confirm that the formula range includes the new rows. If you used a fixed range such as A2:A100 and entered data below row 100, extend the range or switch to A2:A. Also check that the formula cell has not been replaced by pasted values.

Frequently Asked Questions

Does UNIQUE update when I edit the source data?
Yes. A UNIQUE formula recalculates when cells in its referenced range change. Make sure the new entries fall inside the range used by the formula.

How do I get a unique list without blank cells?
Filter the source to nonblank cells before applying UNIQUE. For values in A2:A, use =UNIQUE(FILTER(A2:A,A2:A<>"")). If the source includes formulas returning empty text or cells containing spaces, clean those entries as well.

Can I extract unique rows instead of unique values in one column?
Yes. Use UNIQUE on a multi-column range, such as =UNIQUE(A2:C100), to return each distinct row combination once. A row is treated as unique based on the values across the selected columns.

What is the difference between UNIQUE and Remove duplicates?
UNIQUE produces a formula-based result in another area and can update as its source changes. Data > Data cleanup > Remove duplicates edits the selected cells and is better for a permanent cleanup. Copy the data first if you need to preserve the original.

Can I keep only values that occur once?
Yes. With the list in A2:A100, use =UNIQUE(A2:A100,FALSE,TRUE). That returns values appearing exactly once, rather than one copy of every repeated value. Add a filtering step if blanks should be excluded.

Why does my unique list change order?
The basic UNIQUE function preserves the order of first appearance in its source range. If you use SORT, the output is rearranged into sorted order. Choose the version that matches whether source order or alphabetical order is more useful.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.