In this tutorial, you will learn How to use Google Sheets Flatten function.
If you have ever worked with a dataset that contains nested data, you would know how time-consuming and complex it can be to extract meaningful insights.
Fortunately, Google Sheets provides a powerful function called “Flatten” to help you flatten your data and make it more accessible.
Understanding the Google Sheets Flatten Function
The flatten function in Google Sheets is a formula that allows you to flatten arrays of arrays. In simpler terms, it takes a table with nested arrays and transforms it into a single table with all the data at the same level.
As a result, analyzing the data is made simpler because you can access the necessary data without having to go through a number of levels.
Flatten Function Syntax :
=FLATTEN(range1,[range2,...])
How to use Google Sheets Flatten function: Step by Step Guide
Enclosed are the steps to use Google Sheets Flatten function.
Step 1: Open the Google Sheets document that contains the data you want to flatten.
Step 2: Select the cell where you want to output the flattened data
Step 3: Type the following formula in the cell: =FLATTEN(array1), where array1 is the range
of cells that contain the nested data you want to flatten.
Step 4: Press Enter.
The function will then return a flattened version of the selected range.
Examples of Using the Google Sheets Flatten Function
Let’s look at some examples to see how you can use the flatten function in Google Sheets.
Example 1: Flattening a Table with Nested Data
Suppose you have a table with nested data, like the one below.
To flatten this table, follow these steps:
- Select a cell where you want to output the flattened data.
- Type the following formula: =FLATTEN(A2:A4,B2:B4,C2:C4)
3. Press Enter.
The function will return a single column with all the fruits listed for each person.
Example 2
When dealing with untidy data, the FLATTEN function comes in handy.
Consider the following dataset, where emails are dispersed throughout columns A, B, and C. We want to combine these emails into a single column.
The FLATTEN function is a quick and straightforward technique. Use the following formula to flatten the above data.
Select the Cell where you need to get the result and type the below formula.
=FLATTEN(A:C)
Following is the output of the above formula:
Advanced FLATTEN Formula with Examples
How to Unpivot Data With FLATTEN
Unpivoting in Google Sheets is probably the FLATTEN function’s most practical application.
In contrast to a pivot table, which converts data from a tall format (database) to a wide table format, it does the exact reverse (good for humans and charts to read).
Before the FLATTEN function was introduced, unpivoting data in Google Sheets was challenging, but happily, it is now possible with just one array formula.
The unpivot is achieved by this array formula that joins the data together, flattens it, and then splits it into multiple columns.
To get the unpivot data you can use the below array formula which joins the data and flattens it and later splits the data into multiple columns.
=ArrayFormula(SPLIT(FLATTEN(A2:A5 & "😀" & B1:D1 & "😀" & B2:D5), "😀"))
You might be thinking why we have used a smiley 😀 in the formula.
You can use any character you prefer as the delimiter . The only condition is, it should not be already present in the dataset. Hence we have chosen a smiley emoji.
How to use Google Sheets Flatten function(Conclusion)
In conclusion, the Flatten function in Google Sheets is a powerful tool that can help you simplify your data and make it easier to analyze.
By following the steps outlined in this post, you can quickly and easily flatten any nested arrays or structures in your spreadsheet.
We hope this tutorial on How to use Google Sheets Flatten function.
Related Posts :
How to Convert Numbers to Strings in Google Sheets(Quick & Easy Guide)
How to Calculate Average If Cell Contains Text in Google Sheets
How to Combine First and Last name in Google Sheets(Quick & Easy Guide)
How to Round to Significant Figures in Google Sheets(Quick & Easy Guide)
