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.
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
17534717491887712543
true
Search Help Center
true
true
true
true
true
35
false
false