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

2 Replies to “How to Flatten Data in Excel (With Example)”

  1. What if I want three columns? The data shown in your example from cells A6 to A23, and a second column with the row headings (in this case years) associated with the first column, and a third column with the column headings (in this case quarters) associated with the first column?

    1. Hi Ovalle…Flattening data in Excel refers to converting a multi-dimensional data structure (such as a table with rows and columns) into a single list of values. This is often necessary when you need to prepare data for analysis or import it into another application.

      ### Example Scenario

      Suppose you have a table like this:

      | A | B | C |
      |——|——|——|
      | 1 | 2 | 3 |
      | 4 | 5 | 6 |
      | 7 | 8 | 9 |

      You want to flatten this table into a single column:

      | Value |
      |——-|
      | 1 |
      | 2 |
      | 3 |
      | 4 |
      | 5 |
      | 6 |
      | 7 |
      | 8 |
      | 9 |

      ### Method 1: Using Formulas

      1. **Step 1: Use the `OFFSET` Function**
      – This function helps in retrieving data from the specified row and column of a range.

      2. **Step 2: Create a Flattened List**
      – In a new sheet or column, use the following formula to generate the flattened data:

      Assuming your original table is in `Sheet1` and starts from cell `A1`:

      “`excel
      =OFFSET(Sheet1!$A$1, INT((ROW(A1)-1)/3), MOD((ROW(A1)-1), 3))
      “`
      – Copy this formula down the column to get all values.

      **Explanation**:
      – `OFFSET(Sheet1!$A$1, INT((ROW(A1)-1)/3), MOD((ROW(A1)-1), 3))`
      – `Sheet1!$A$1` is the reference starting point.
      – `INT((ROW(A1)-1)/3)` determines the row offset.
      – `MOD((ROW(A1)-1), 3)` determines the column offset.

      ### Method 2: Using Power Query

      1. **Step 1: Load Data into Power Query**
      – Select your table and go to the `Data` tab.
      – Click on `From Table/Range`.

      2. **Step 2: Transform Data**
      – In Power Query, select all columns you want to flatten.
      – Right-click and choose `Unpivot Columns`.

      3. **Step 3: Load Data Back to Excel**
      – Click `Close & Load` to return the flattened data to Excel.

      ### Method 3: Using VBA (Macro)

      For a more automated approach, you can use VBA:

      1. **Step 1: Open the VBA Editor**
      – Press `Alt + F11` to open the VBA Editor.
      – Insert a new module by clicking `Insert > Module`.

      2. **Step 2: Write the VBA Code**
      – Copy and paste the following code into the module:

      “`vba
      Sub FlattenData()
      Dim ws As Worksheet
      Dim rng As Range
      Dim cell As Range
      Dim outputSheet As Worksheet
      Dim outputCell As Range

      ‘ Set the worksheet and range to flatten
      Set ws = ThisWorkbook.Sheets(“Sheet1”)
      Set rng = ws.Range(“A1:C3”) ‘ Adjust the range as needed

      ‘ Create a new sheet for output
      Set outputSheet = ThisWorkbook.Sheets.Add
      outputSheet.Name = “FlattenedData”
      Set outputCell = outputSheet.Range(“A1”)

      ‘ Flatten the data
      For Each cell In rng
      outputCell.Value = cell.Value
      Set outputCell = outputCell.Offset(1, 0)
      Next cell
      End Sub
      “`

      3. **Step 3: Run the Macro**
      – Close the VBA Editor.
      – Press `Alt + F8`, select `FlattenData`, and click `Run`.

      ### Summary

      – **Using Formulas**: Quick and easy for small tables.
      – **Using Power Query**: More flexible and powerful for larger datasets.
      – **Using VBA**: Ideal for automation and more complex tasks.

      Choose the method that best fits your needs and data size.

Leave a Reply

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