Skills matrix
=UNIQUE(FLATTEN(Skills!B2:F)) lists every skill entered across five competency columns.
What is FLATTEN in Google Sheets?
FLATTEN converts a two-dimensional range into a single column, reading across rows then down. It is useful after SPLIT or when you need every value from a block in one list for UNIQUE, COUNTIF, or mail merge prep.
When to use it
Use FLATTEN when tags, skills, or matrix answers live in multiple columns and you need one vertical list for analysis.
When to skip it
Skip FLATTEN when structure matters, like keeping month columns separate for charts. TOROW or TOCOL offer more control.
How it works
-
1
Select the rectangular range containing values to collapse.
-
2
Write =FLATTEN(A2:D10) to stack all cells into one column in row-major order.
-
3
Wrap with UNIQUE or SORT for distinct alphabetized lists.
-
4
Filter empties with FILTER(FLATTEN(range), LEN(FLATTEN(range))) carefully or FLATTEN after IF blanks.
-
5
Check spill length so you do not overwrite totals below.
-
6
Combine with SPLIT first when one cell holds comma-separated tags.
Examples in Google Sheets
After SPLIT
=FLATTEN(SPLIT(A2:A, ",")) turns multi-tag cells into one tag per row.
Survey grid
=COUNTIF(FLATTEN(Responses!C2:H), "Yes") counts affirmative answers across a likert grid.
Build this without starting from a blank cell
Use a Better Sheets tool for FLATTEN, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Flattening when you needed TOCOL to preserve column-major order.
- Including blank cells that UNIQUE then treats as a distinct value.
- Flattening huge ranges without need, slowing recalc.
- Losing which source column a value came from with no helper index.
- Nesting FLATTEN inside FLATTEN expecting different shape.
Frequently asked questions
- FLATTEN vs TOCOL?
- FLATTEN always outputs one column in row-major order. TOCOL can choose column-major stacking.
- Does FLATTEN work on one row?
- Yes. A single row becomes a vertical list of those cells.
- FLATTEN and UNIQUE?
- Common pattern: UNIQUE(FLATTEN(range)) for distinct values from a matrix.
- Why is order wrong?
- FLATTEN reads left-to-right then down. Use TOCOL with column-major if needed.
- FLATTEN with text?
- Works on text and numbers alike.
- Can FLATTEN feed FILTER?
- Yes. FILTER the flattened column with conditions on values.
- FLATTEN size limits?
- Very large ranges produce long spills. Test on a sample first.
Related Tutorials
Watch how FLATTEN works
This Seems Like Automation
Related blog posts
Guides that explain FLATTEN in more depth.
Related glossary terms
UNIQUE
UNIQUE extracts a deduplicated list from a column or table. Optional arguments control whether duplicates appear by row, by column, or with occurrence counts. It powers dropdown source lists, category tabs, and data cleanup without manual copy-paste.
Read guide →TOROW
TOROW flattens a range into a single row spill. Like TOCOL but horizontal, it helps build header arrays, SPARKLINE source rows, and inputs for functions that expect one horizontal vector.
Read guide →TOCOL
TOCOL converts a range into a single column. You can choose whether it reads down each column first or across rows first. It replaces manual paste-special transpose chains when reshaping blocks for UNIQUE or FILTER.
Read guide →HSTACK
HSTACK appends arrays horizontally so columns sit side by side in one spilled table. It replaces fragile copy-paste when combining QUERY outputs, IMPORTRANGE blocks, or calculated columns with matching row heights.
Read guide →Done reading about FLATTEN?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.