Home › Functions › Lookup & Reference › =TOCOL()
Lookup & Reference Excel 365 ◉ 30 views Dynamic Array

=TOCOL()

Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for Mac
=TOCOL(array, [ignore], [scan_by_column])
Hover argument names in the table for details.
Output typeArray
Popularity45 / 100
VolatileNo
Dynamic ArrayYes
Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =TOCOL() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
Download TOCOL.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
array Array Required The array or reference to return as a column.
ignore Number Required Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following: Values: 0 = Keep all values (default); 1 = Ignore blanks; 2 = Ignore errors; 3 = Ignore blanks and errors.
scan_by_column Range Required Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.

▣ Examples

▦ Data
CellValue
A2 Ben
B2 Peter
C2 Mary
D2 Sam
A3 John
B3 Hillary
C3 Jenny
D3 James
A4 Agnes
B4 Harry
C4 Felicity
D4 Joe
A5 Ben
B5 Peter
C5 Mary
D5 Sam
A6 John
B6 Hillary
C6 Jenny
D6 James
A7 Agnes
B7 Harry
A8 Ben
B8 Peter
C8 Mary
D8 Sam
A9 John
B9 Hillary
C9 Jenny
D9 James
A10 Agnes
B10 Harry
A11 Ben
B11 Peter
C11 Mary
D11 Sam
A12 John
B12 Hillary
C12 Jenny
D12 James
A13 Agnes
B13 Harry
A14 Ben
B14 Peter
C14 Mary
D14 Sam
A15 John
B15 Hillary
C15 Jenny
D15 James
A16 Agnes
B16 Harry
C16 Felicity
D16 Joe
A17 Ben
B17 Peter
C17 Mary
D17 Sam
A18 John
B18 Hillary
C18 Jenny
D18 James
A19 Agnes
B19 Harry
A20 Ben
B20 Peter
C20 Mary
D20 Sam
A21 John
B21 Hillary
C21 Jenny
D21 James
A22 Agnes
B22 Harry
A23 Ben
B23 Peter
C23 Mary
D23 Sam
A24 John
B24 Hillary
C24 Jenny
D24 James
A25 Agnes
B25 Harry
Example 1 =TOCOL(A2:D4)
Example 2 =TOCOL(A2:D4,1)
Example 3 =TOCOL(A2:D4,1,TRUE)

§ Remarks

  • If scan_by_column is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.

✦ Tips

  • If scan_by_column is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.

⚠ Common Errors

#VALUE! Excel returns a #VALUE! when an array constant contains one or more numbers that are not a whole number.
Fix: بررسی کنید ثابت آرایه فقط اعداد صحیح داشته باشد؛ مقادیر اعشاری را گرد یا اصلاح کنید.
#NUM! Excel returns a #NUM when array is too large.
Fix: Make the range smaller

⇄ Related Functions

=SORT() Learn about the SORT function, which sorts the contents of a range or array. SORT is in a class of functions called dynamic arrays. =UNIQUE() Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays. =TOROW() Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column. =TRANSPOSE() How to use the TRANSPOSE function in Excel to switch or rotate cells. =WRAPCOLS() WRAPCOLS function =WRAPROWS() WRAPROWS function

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.