You manage a small team; each member tracks their daily tasks in different columns. Columns A, B, and C represent Monday, Tuesday, and Wednesday, respectively. You need a list of all tasks at the end of the week. You can combine all tasks from these columns into a single column using the FLATTEN function.
What is Flatten Function?
In Google Sheets, the Flatten function is one of the most amazing functions I have found to clean and rearrange your data. Open the range of multiple columns or rows into a single column. Simply put, this function transforms a range of cells into a single column.
Syntax
The syntax of the flatten function is quite simple. You enter the function in a cell, and then you Radford to the range or multiple ranges you want to combine.
FLATTEN(range)
range: This is the range of cells you want to flatten into a single column.
Simple Example to Understand it
In the example below, we have values in the A1:C3 range. When I enter the Flatten function and refer to this range in the function, it combines all the range values and puts them into a single column.
This is a simple example of combining three different columns into one single column. You can see in the function referred to the range A1:C3, which has three rows and three columns.
When the Range Contains Empty Cells
When you use Flatten, as I have used in the below example, where I have values in the range A1:D3, you can see it has combined values from the range into a single column.
But there’s one thing that you need to know here: When you specify a range to convert, it combines it by using row orientation. If you refer to the range A1:D3, it will go from A1 to B1 to C1 to D1 and then move to the next row to combine the values and then the next row.
Now, in our example, we don’t have any value in the cells D2 and D3, and that’s why when it combines values in single columns, there’s a blank cell in between the values 1555 and 123
The easiest way to solve this problem is to change how you refer to the range in the function.
=FLATTEN(A1:A3,B1:B3,C1:C3,D1:D3)
Another way to solve this problem is to combine flatten with the FILTER function.
=FILTER(FLATTEN(A1:D3), FLATTEN(A1:D3) <> "")
This formula uses the FLATTEN to turn the range A1:D3 into a single column of values. Then, the FILTER function looks at this new single column and removes any blank cell.
The condition FLATTEN(A1:D3) <> “” ensures that only the cells with actual value will be taken. In the result, you have a column with all the non-blank values from the original range A1:D3.
Convert a Range into a Row instead of a Column with FLATTEN
You can combine FLATTEN with TRANSPOSE to combine a range into a row instead of a column, which this function does by default. You can use the following functions.
=TRANSPOSE(FLATTEN(A1:C3))
It lists all the values from A1, B1, C1, A2, B2, and so on in one vertical column. Then, TRANSPOSE changes this vertical column into a horizontal row. So, if you have a table with data in cells A1 to C3, this will list all those values in one row instead of a column.
Using Merged Cells
If you have a range with merged cells, you need to unmerge those cells first, then use the function. No other function can work, as FLATTEN can’t work with the merged cells. Select the range with merged cells, go to the toolbar, click the “Merge cells” button, and then select “Unmerge.”
Referring to Multiple Sheets
You can also refer to multiple sheets with more than one column value. In this case, you need to write a formula like the following:
=FLATTEN({Sheet1!A1:D3; Sheet2!A1:D3}) In this formula, when you use {Sheet1!A1:D3; Sheet2!A1:D3} it creates an array of two sheets and the range of cells that you have referred, and then FLATTEN combines values from both of the ranges into a single column.
Finally, I’d like to say that FLATTEN is an amazing function that helps you rearrange your data very easily and quickly. But sometimes, you must combine it with other functions like filtering to get the data the way you want.