The `TOCOL` function in Excel is designed to transform an array or a range of cells into a single column. It’s particularly useful when you want to consolidate data from multiple rows and/or columns into a single list. The function is available in Excel 365 and Excel 2021 as part of the newer functions introduced to enhance array handling capabilities.
Here’s how to use the `TOCOL` function:
Syntax
TOCOL(array, [ignore], [scan_by_column])
Arguments
- array: This is the range or array you want to transform into a single column.
- ignore (optional): This argument specifies what to ignore when transforming the array. You can specify:
- `”blanks”` to ignore empty cells,
- `”errors”` to ignore cells with errors, or
- `”text”` to ignore text values.
- By default, nothing is ignored if you do not specify this argument.
- scan_by_column (optional): This determines the order in which the data is concatenated:
- If `FALSE` or omitted, the function processes the data row by row (i.e., it concatenates the first row, then the second row, and so on).
- If `TRUE`, it processes the data column by column (i.e., it concatenates the first column, then the second column, and so on).
Example
Suppose you have the following data in cells A1:C3:
| A | B | C |
|—-|—-|—-|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
To convert this range into a single column using the `TOCOL` function, you would enter the function like this:
=TOCOL(A1:C3)
This would result in a single column with the values:
1
2
3
4
5
6
7
8
9
If you wanted to scan by columns instead, you would use:
=TOCOL(A1:C3, , TRUE)
This would give you:
1
4
7
2
5
8
3
6
9
Tips
- `TOCOL` works well with dynamic arrays, meaning it automatically adjusts if the source data changes size.
- You can nest other functions inside `TOCOL` to further manipulate the data during the transformation.
By using the `TOCOL` function, you can efficiently reorganize and analyze your data by consolidating it into a more manageable format.
Unlock Your Potential
Excel
Access
Power BI
