# TOCOL function

This function transforms an array or range of cells into a single column. TOCOL can scan values:

• By column, top to bottom
• By row, left to right

The `scan_by_column` argument is a boolean value that controls how TOCOL reads values from the source array.

## Sample Usage

`TOCOL(A1:C3)`: A `TOCOL` function that keeps all values and scans by row.

`TOCOL(A1:C3, 1, TRUE)`: A `TOCOL` function that ignores blanks and scans by column.

## Syntax

`TOCOL(array_or_range, [ignore], [scan_by_column])`

• `array_or_range`: The array or range of cells to return as a column.
• `[ignore]`: By default, no values are ignored. Specify one of these values:
• 0: Keep all values
• 1: Ignore blanks
• 2: Ignore errors
• 3: Ignore blanks and errors
• `[scan_by_column]`: The boolean value of `scan_by_column` determines how the array is scanned. By default, the `TOCOL` function scans the array by row.
• True: Scans the array by column
• False: Scans the array by row

## Examples

### Use simple data transformation operation with TOCOL

Example data:

A

B

C

1

Ben

Peter

Mary

2

John

Hillary

Jenny

3

Agnes

Harry

Felicity

Example: Input this formula in `E1: =TOCOL(A1:C3)`

Result:

E

1

Ben

2

Peter

3

Mary

4

John

5

Hillary

6

Jenny

7

Agnes

8

Harry

9

Felicity

### Ignore blanks with TOCOL

Example data:

A

B

C

1

Ben

Peter

Mary

2

John

Jenny

3

Agnes

Harry

Felicity

Example:Input this formula in `E1: =TOCOL(A1:C3, 1)`

Result:

E

1

Ben

2

Peter

3

Mary

4

John

5

Jenny

6

Agnes

7

Harry

8

Felicity

### Scan by column with TOCOL

Example data:

A

B

C

1

Ben

Peter

Mary

2

John

Hillary

Jenny

3

Agnes

Harry

Felicity

Example: Input this formula in `E1: =TOCOL(A1:C3, 0, TRUE)`

Result:

E

1

Ben

2

John

3

Agnes

4

Peter

5

Hillary

6

Harry

7

Mary

8

Jenny

9

Felicity

## Related functions

• TOROW function: This function transforms an array or range of cells into a single row.

Search
Clear search
Close search