How Do I Flatten Data in Excel? (With Example)

By /

Flattening data in Excel refers to the process of restructuring data from a multi-level or hierarchical format to a single, two-dimensional table. This involves organizing the data in a way that each row represents a unique observation, and each column represents a specific attribute or variable. This can be done by using Excel functions such as INDEX, MATCH, and VLOOKUP, or by using the built-in PivotTable feature. For example, if you have a table with product categories as the main headers and subcategories as subheaders, flattening the data would involve creating a new table with each product listed in a single row, along with its corresponding category and subcategory. This makes the data easier to analyze and manipulate for further analysis or visualization.

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 .

Additional Resources

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

x

Related terms:

  1. How to Flatten MultiIndex in Pandas (With Examples)
  2. Fix: pandas data cast to numpy dtype of object? Check input data with np.asarray(data).
  3. Excel: Convert Monthly Data to Quarterly Data
  4. Excel: Convert Quarterly Data to Annual Data
  5. Excel: Categorize Data Based on Values
  6. Save Filtered Data in Excel (With Example)
  7. How do I Group Data by Quarter in Excel?
  8. How can I plot multiple data sets on the same chart in Excel?
  9. How to Export Data from SAS to Excel (With Examples)
  10. How to Winsorize Data in Excel