Table of Contents
Flattening data in Excel refers to the process of reorganizing and rearranging data into a single row or column format, making it easier to analyze and present. This can be achieved by using the “Transpose” function, which allows users to switch the rows and columns of data. For example, if the data is arranged in a table with each row representing a different product and each column representing a different attribute, transposing the data would rearrange it so that each row represents a single attribute for all products. This feature can be useful for creating charts, graphs, and pivot tables, as well as for summarizing and visualizing large amounts of data.
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:
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: