How to Flatten Data in Excel (With Example)


You can use the TOCOL function in Excel to flatten an array into a single column.

For example, you can use the following formula to convert the array of values in the range B2:E4 into a single vertical column:

=TOCOL(B2:E4)

The following example shows how to use this formula in practice.

Example: How to Flatten Data in Excel

Suppose we have the following dataset in Excel that displays the total sales made by some company during each quarter of three consecutive years:

Suppose we would like to flatten this table into a single column.

We can type the following formula into cell A6 to do so:

=TOCOL(B2:E4)

The following screenshot shows how to use this formula in practice:

Excel flatten data

We can see that the formula has flattened the values from the table into a single column that shows the sales values for each quarter and year.

For example:

  • The first value in the column shows the sales for Q1 2021.
  • The second value in the column shows the sales for Q2 2021.
  • The third value in the column shows the sales for Q3 2021.
  • The fourth value in the column shows the sales for Q4 2021.

And so on.

Note: You can find the complete documentation for the TOCOL function in Excel here.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *