Table of Contents
Google Sheets is a powerful tool for organizing and analyzing data. One useful feature of Google Sheets is the ability to flatten data, which involves merging multiple rows or columns into one. This can be helpful when working with large datasets and wanting to condense information for easier viewing and analysis. To flatten data in Google Sheets, go to the Data tab and select the “Flatten” option. This will prompt you to choose which rows or columns you want to flatten and will merge them into a single row or column. This feature can save time and improve the organization and clarity of data in Google Sheets.
FLATTEN
Flattens all the values from one or more ranges into a single column.
Parts of a FLATTEN function
=FLATTEN(range1, [range2, …])
Part | Description |
range1 | The first range to flatten. |
range2 | [optional] repeatable Additional ranges to flatten. |
Sample formulas
=FLATTEN(A1:B2)
=FLATTEN(“top”, A1:B2, “middle”, B3:B4, “bottom”)
Notes
- Values are ordered by argument, then row, then column. So, the entire first row of an input is added before the second row (also known as row-major order).
- Empty values are not skipped; the FILTER function can be used to remove those.
Examples
Make a copy
Flatten will append arguments in the order they are included in the formula. Arguments need not be range references.
A | B | C | D | |
1 | 1 | 2 | Formula in D1: =FLATTEN(A1:B2, “sample middle”, B3:B4) | 1 |
2 | 3 | 4 | 2 | |
3 | 5 | 3 | ||
4 | 6 | 4 | ||
5 | sample middle | |||
6 | 5 | |||
7 | 6 |
A more complex example, using the CONCAT (&) operator and SPLIT to do a simple cross join or Cartesian product on two lists.
A | B | C | D | E | |
1 | A | 1 | Formula in D1: =ArrayFormula(SPLIT(FLATTEN(A1:A3 & “|” & TRANSPOSE(B1:B2)), “|”)) | A | 1 |
2 | B | 2 | A | 2 | |
3 | C | B | 1 | ||
4 | B | 2 | |||
5 | C | 1 | |||
6 | C | 2 |
Related functions
- SPLIT
- TRANSPOSE
- SORT
- UNIQUE
- FILTER
Cite this article
stats writer (2024). How can I flatten data in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-flatten-data-in-google-sheets/
stats writer. "How can I flatten data in Google Sheets?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-flatten-data-in-google-sheets/.
stats writer. "How can I flatten data in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-flatten-data-in-google-sheets/.
stats writer (2024) 'How can I flatten data in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-flatten-data-in-google-sheets/.
[1] stats writer, "How can I flatten data in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I flatten data in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.