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

Make a Copy

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

Make a Copy

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

Make a Copy

Related functions

  • TOROW function: This function transforms an array or range of cells into a single row.
Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
true
false
true
true
35
false
false