Unique Google Sheets Across Multiple Columns: A Practical Guide
Explore practical, step-by-step techniques to identify and remove duplicates across several columns in Google Sheets, with formulas, examples, and robust handling for blanks and empty cells.
Unique google sheets multiple columns refers to removing or identifying duplicate row combinations when data spans more than one column. In Google Sheets, you achieve this with the UNIQUE function applied to an array of columns, for example =UNIQUE({A2:A, B2:B, C2:C}). This guide explains practical patterns, common pitfalls, and ready-to-apply templates for robust multi-column deduplication. According to How To Sheets, mastering multi-column uniqueness saves time and reduces errors.
Understanding multi-column uniqueness in Google Sheets
At its core, unique google sheets multiple columns means identifying and removing duplicates when considering more than one column. When data is spread across columns, a simple UNIQUE(A:A) would not catch duplicates that appear across column pairs. The recommended approach is to apply UNIQUE to an array that combines the relevant columns, producing rows that reflect the combined value across those columns.
=UNIQUE({A2:A, B2:B, C2:C})=UNIQUE(A2:C)=SORT(UNIQUE({A2:A, B2:B, C2:C}), 1, TRUE)- The first formula returns unique row combinations across A, B, and C.
- The second treats the entire range as a set of rows and deduplicates accordingly.
- The third adds a deterministic sort by the first column. Variations exist depending on whether you want row-based or value-based uniqueness.
Practical use cases: de-duplicating across multiple columns
Common scenarios include deduplicating person records by FirstName, LastName, and Email (A2:C). A multi-column UNIQUE returns only distinct rows considering all three fields. You can also create a single-key representation for readability by concatenating columns, then applying UNIQUE to the key.
=UNIQUE({A2:A, B2:B, C2:C})=ARRAYFORMULA(UNIQUE(A2:A & " | " & B2:B & " | " & C2:C))=COUNTA(UNIQUE({A2:A, B2:B, C2:C}))- The first pattern preserves all three original columns.
- The second builds a human-friendly key for quick scanning.
- The third counts the number of unique rows.
Keeping data aligned: preserving the original columns while extracting unique rows
If you want to keep the original data intact and present only unique rows elsewhere, couple UNIQUE with an extraction sheet or a QUERY to only pull non-empty rows. This approach avoids mutating the source data while providing a deduplicated view for reporting.
=QUERY({A2:C}, "select * where Col1 is not null", 0)=SORT(UNIQUE({A2:A, B2:B, C2:C}), 1, TRUE)- The QUERY-based approach helps filter blanks and focus on meaningful rows.
- Sorting afterward delivers predictable results for dashboards and reports.
Handling blanks and data cleanliness
De-duplication can be thrown off by blank cells. A robust approach is to filter out all-blank rows before applying UNIQUE. This prevents empty rows from appearing in your deduplicated output and keeps downstream calculations clean.
=UNIQUE(FILTER({A2:A, B2:B, C2:C}, LEN(A2:A) + LEN(B2:B) + LEN(C2:C) > 0))=ARRAYFORMULA(UNIQUE(IF({A2:A, B2:B, C2:C}="", , {A2:A, B2:B, C2:C})))- The FILTER version is usually simpler and faster for large ranges.
- The IF approach can help when you’re dragging formulas across multiple columns.
Counting and sorting unique results
Once you have a deduplicated set, you often want to count or sort it for reporting. COUNT and SORT combinations are common, especially for dashboards and KPI sheets.
=COUNTA(UNIQUE({A2:A, B2:B, C2:C}))=SORT(UNIQUE({A2:A, B2:B, C2:C}), 1, TRUE)=QUERY({A2:A, B2:B, C2:C}, "select * where Col1 is not null order by Col1", 0)- Sorting ensures a readable, deterministic presentation of results.
- You can adapt the sort key to column position or a concatenated key for multi-dimensional ordering.
Advanced technique: flatten values to compare across all columns
If you want to treat every value in A2:C as a candidate for uniqueness regardless of column, flatten the range to a single column, then apply UNIQUE. This is handy for inventories or tag-like data where value position is irrelevant.
=UNIQUE(FLATTEN(A2:C))=FILTER(UNIQUE(FLATTEN(A2:C)), FLATTEN(A2:C) <> "")=ARRAYFORMULA(QUERY({A2:A, B2:B, C2:C}, "select * where Col1 is not null", 0))- FLATTEN-based approaches give a different flavor of uniqueness, great for value-level deduplication.
- They should be used when column identity matters less than the presence of a value.
Apps Script approach for large datasets
For very large datasets, a formula-based approach may hit performance limits. A small Apps Script can deduplicate across columns and write results to a separate sheet, keeping the UI responsive.
function dedupeAcrossColumns() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const src = ss.getSheetByName("Data");
const data = src.getRange(2, 1, src.getLastRow()-1, 3).getValues();
const seen = new Set();
const out = [];
for (const row of data) {
const key = row.map(v => String(v).trim()).join("||");
if (key && !seen.has(key)) {
seen.add(key);
out.push(row);
}
}
const dst = ss.getSheetByName("Unique") || ss.insertSheet("Unique");
dst.clearContents();
if (out.length > 0) {
dst.getRange(1, 1, out.length, out[0].length).setValues(out);
}
}- Apps Script runs once to generate a deduplicated dataset and can be bound to a button for ease of use.
- It’s faster for large ranges than repeatedly recalculating complex formulas.
Performance considerations and pitfalls
Formula-based dedup across columns scales with the range size. As data grows, consider the following:
- Use compact ranges (e.g., A2:A1000) instead of whole columns when possible.
- Prefer array literals over dynamic ranges for predictable performance.
- Avoid nesting many CONCAT and UNQIUE operations in single cells; split into steps where helpful.
- For large data, Apps Script can offload work from the sheet while keeping results accessible.
=UNIQUE({A2:A1000, B2:B1000, C2:C1000})=SORT(UNIQUE({A2:A1000, B2:B1000, C2:C1000}), 1, TRUE)- Tests show that splitting the task into two helper columns can improve readability and reduce recalculation time.
Real-world templates and best practices
To deploy reliably, keep a separate data entry sheet and a separate deduplicated view. Use named ranges for the data column groups (e.g., DataA, DataB, DataC) and reference them in your UNIQUE formulas. Document your approach so teammates understand how duplicates are defined and how the deduplicated view is refreshed.
=UNIQUE({DataA, DataB, DataC})=FILTER(UNIQUE({DataA, DataB, DataC}), LEN(DataA) + LEN(DataB) + LEN(DataC) > 0)=QUERY({DataA, DataB, DataC}, "select * where Col1 is not null", 0)- Clear naming and documentation reduce confusion when data schemas change.
Summary of best practices and quick-reference
- Define the exact columns to compare and decide whether to deduplicate by row or by value across columns.
- Use UNIQUE with array literals for robust multi-column deduplication.
- Filter blanks before dedup to avoid empty results.
- Sort results for consistent output.
- Consider Apps Script for large datasets or repeatable automation.
- Keep a separate deduplicated view to avoid mutating source data.
Steps
Estimated time: 30-60 minutes
- 1
Identify target columns
Decide which columns participate in the uniqueness check (e.g., A, B, C). Determine whether to deduplicate by row or by values across columns.
Tip: Document the columns and the intended uniqueness rule. - 2
Write the multi-column UNIQUE formula
Use an array to combine the columns, e.g., =UNIQUE({A2:A, B2:B, C2:C}).
Tip: Prefer a range that won’t shift unexpectedly. - 3
Handle blanks and data quality
Apply FILTER to drop rows where all columns are blank before dedup, if needed.
Tip: Filter out empty rows to improve reliability. - 4
Sort and present results
Wrap UNIQUE with SORT to provide deterministic ordering for dashboards.
Tip: Choose sort keys that reflect your primary dimension. - 5
Automate if data updates
Optionally add an Apps Script to refresh a deduplicated view on demand.
Tip: Schedule or attach to a button for repeat usage.
Prerequisites
Required
- Required
- Basic knowledge of Google Sheets formulas (UNIQUE, FILTER, SORT, ARRAYFORMULA)Required
- Internet connectionRequired
Optional
- Access to sample data or a test sheetOptional
- Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste into a range | Ctrl+V |
| Enter formulaCommit formula in active cell | ↵ |
FAQ
How do I deduplicate across three columns in Google Sheets?
Use a multi-column array with UNIQUE, such as =UNIQUE({A2:A, B2:B, C2:C}). This returns distinct row combinations across the three columns.
Use a single formula like =UNIQUE({A2:A, B2:B, C2:C}) to get distinct rows across all three columns.
How can I keep the original data intact while showing a deduplicated view?
Deduplicate in a separate sheet or area using a formula like =UNIQUE({A2:A, B2:B, C2:C}). Reference the result in dashboards instead of mutating the source data.
Show a deduplicated view on another sheet without changing the original data.
What about blanks when deduplicating across columns?
Filter out blanks before applying UNIQUE, e.g., =UNIQUE(FILTER({A2:A, B2:B, C2:C}, LEN(A2:A) + LEN(B2:B) + LEN(C2:C) > 0)).
Skip empty rows by filtering before dedup.
Can I count the number of unique rows across multiple columns?
Yes. Use =COUNTA(UNIQUE({A2:A, B2:B, C2:C})). This counts distinct row combinations.
Count unique combinations with COUNTA and UNIQUE.
Is FLATTEN suitable for cross-column uniqueness?
FLATTEN treats all values equally across columns. Use =UNIQUE(FLATTEN(A2:C)) to get distinct values regardless of column position.
Flatten values first, then deduplicate.
The Essentials
- Identify the key columns for comparison
- Use UNIQUE with multi-column arrays to deduplicate by rows
- Filter blanks to avoid false positives
- Sort results for consistent reporting
- Consider Apps Script for large datasets