Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to flatten a multi-column range into one column in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=TRANSPOSE(FILTER(FLATTEN(A2:B4), FLATTEN(A2:B4)<>""))Google Sheets
=ARRAYFORMULA(TRANSPOSE(FILTER(FLATTEN(A2:B4), FLATTEN(A2:B4)<>'')))Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select an empty cell where you want the flattened data to start.
- 2In Excel, use the formula =TRANSPOSE(FILTER(FLATTEN(A2:B4), FLATTEN(A2:B4)<>"")) to flatten the range A2:B4.
- 3In Google Sheets, use the formula =ARRAYFORMULA(TRANSPOSE(FILTER(FLATTEN(A2:B4), FLATTEN(A2:B4)<>''))) to achieve the same result.
- 4Press Enter to see the flattened data in a single column.
Tips
- Ensure your range does not include headers unless you want them included in the flattened output.
- Adjust the range (A2:B4) in the formula to match your actual data range.
- Be aware that these formulas may behave differently with larger datasets or in different Excel/Google Sheets versions.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my data has blank cells?
The formulas provided automatically filter out blank cells, ensuring only populated cells are included in the flattened output.
Can I flatten more than two columns?
Yes, simply adjust the range in the formula to include all desired columns, e.g., A2:D4 for four columns.
How do I handle headers when flattening?
If you want to exclude headers, ensure they are not included in the specified range in the formula.