How To SheetsHow To Sheets
Formulas and Functions

unique function google sheets: A practical guide

Learn how the unique function google sheets can extract distinct values, combine with FILTER and SORT, and build dynamic deduplicated lists with practical, real-world examples for students, professionals, and small business owners.

How To Sheets
How To Sheets Team
·5 min read
Sort-Google-SheetsFind-Duplicates-Google-SheetsWhat-Is-Google-SheetsFormula-Errors-Google-Sheets
Unique Function Guide - How To Sheets
Photo by RDNE Stock projectvia Pexels
Quick AnswerDefinition

Definition: The unique function google sheets returns distinct values or entire rows from a chosen range. Use syntax like =UNIQUE(A2:A10) for a single column or =UNIQUE(A1:C20) for multiple columns. You can also enable by_col and exactly_once to customize behavior, and combine with FILTER or SORT for dynamic deduplicated lists.

What the unique function google sheets does

According to How To Sheets, the unique function google sheets is a powerful data-cleaning tool that returns distinct values when applied to a range. It lets you pull a list of unique items from a column or rows from a table, which helps remove duplicates from datasets used for reporting, inventory, or contact lists. This capability is essential for ensuring data integrity before analysis. In practice, UNIQUE acts as a first line of defense against inconsistent records and duplicated entries, which can skew results and mislead decisions.

Excel Formula
// Basic usage: single column =UNIQUE(A2:A10) // Deduplicate across multiple columns (rows) =UNIQUE(A1:C20) // Deduplicate by columns (advanced) =UNIQUE(A1:C20, TRUE, FALSE)
  • When you run it, UNIQUE spills the results into neighboring cells, listing each unique value exactly once.
  • By default, duplicates are ignored along the dimension you specify (rows or columns).
  • With by_col = TRUE, UNIQUE compares columns, which is useful for transposed data or column-based deduplication.

titleBlockRemoved":false

Steps

Estimated time: 15-25 minutes

  1. 1

    Identify data range

    Locate the column or table that contains duplicates. Note the start and end rows to form a stable range for the UNIQUE formula.

    Tip: Include or exclude headers consistently so the results spill correctly.
  2. 2

    Enter a basic UNIQUE formula

    In a new column, enter =UNIQUE(A2:A10) to extract distinct values from a single column.

    Tip: Press Enter and let Google Sheets spill the results automatically.
  3. 3

    Extend to multiple columns or rows

    If you want unique rows instead of values, use =UNIQUE(A1:C20).

    Tip: Ensure you reference the full range to capture all fields.
  4. 4

    Optional: exact-one and by_col flags

    Experiment with =UNIQUE(A2:A20, FALSE, TRUE) or =UNIQUE(A1:C20, TRUE, FALSE) to tailor deduplication.

    Tip: Exact-one helps identify only entries that appear once.
  5. 5

    Combine with FILTER or SORT

    Create dynamic, ordered deduplicated lists: =SORT(UNIQUE(FILTER(B2:B100, C2:C100="Active"))).

    Tip: FILTER narrows the data before deduplication for cleaner results.
  6. 6

    Validate and reuse

    Check the spill range for completeness and copy results to a reporting sheet if needed.

    Tip: Keep the source data linked to update automatically.
Pro Tip: Always exclude headers from the range or handle them separately to avoid mixed results.
Warning: UNIQUE will include blanks if present; use LEN or FILTER to remove empty entries.
Note: When data updates, UNIQUE results spill automatically without re-entering formulas.
Pro Tip: Combine with SORT to generate predictable, ordered lists for dashboards.

Prerequisites

Required

Optional

  • Optional: familiarity with FILTER, SORT, and ARRAYFORMULA
    Optional

Keyboard Shortcuts

ActionShortcut
CopyCopy a value or formula from a cellCtrl+C
PastePaste into a cell or formula barCtrl+V
Paste values onlyPaste only the value, not the formulaCtrl+⇧+V
Fill downExtend a formula down a columnCtrl+D

FAQ

What is the purpose of the UNIQUE function in Google Sheets?

The UNIQUE function returns distinct values or rows from a range, helping remove duplicates for clean lists and accurate analysis. It can operate on columns or rows and works well with other functions like FILTER and SORT.

UNIQUE removes duplicates in Google Sheets, giving you clean lists or rows you can rely on for analysis.

Can I use UNIQUE to find values that appear exactly once?

Yes. By using the exactly_once flag, you can filter for entries that occur only once in the data, which is useful for identifying unique occurrences.

Yes, you can target items that appear exactly once using the exactly_once parameter.

How do I deduplicate data across columns instead of rows?

Set the by_col parameter to TRUE to deduplicate across columns rather than rows. This is helpful when your data is arranged with distinct columns representing different fields.

Set by_col to TRUE to deduplicate by columns.

Does UNIQUE remove blank cells from the results?

UNIQUE can return blanks if the input range contains empty cells. Use FILTER or LEN to exclude blanks from the final results.

Blanks can appear; filter them out if needed.

How can I keep the original data intact while generating a deduplicated list?

Place the UNIQUE formula in a separate sheet or column to preserve the source data, ensuring the original dataset remains editable and unchanged.

Create a separate output area for the deduplicated results.

Can I sort the unique results automatically?

Yes. Wrap UNIQUE with SORT to automatically order the deduplicated results, e.g., =SORT(UNIQUE(A2:A10)).

Sort the unique results for a clean, ordered list.

The Essentials

  • Use UNIQUE to deduplicate a range or rows
  • Combine with FILTER or SORT for dynamic lists
  • Use by_col and exactly_once for advanced control
  • Ensure headers are managed to avoid misalignment

Related Articles

← More in Formulas and Functions