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

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.

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

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

1. Ovalle says:

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. James Carmichael says:

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