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 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

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?

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.