How to Use TOCOL and TOROW Functions in Excel (With Examples)

Excel 365 and Excel 2021 introduce powerful dynamic array functions like TOCOL and TOROW, which help you reshape arrays into a single column or row effortlessly. Let’s explore how they work and when to use them.

---

🔷 1. TOCOL Function – Convert to Column

📌 Purpose:

TOCOL transforms a 2D array or table into a single vertical list.

🧮 Syntax:

excelCopyEditTOCOL(array, [ignore], [scan_by_column])

Parameter	Description
array	The range to convert
ignore	0 = none, 1 = ignore blanks, 2 = ignore errors
scan_by_column	TRUE = by column (default), FALSE = by row

---

📊 Example:

A	B	C
1	2	3
4	5	6

excelCopyEdit=TOCOL(A1:C2)

Result:

CopyEdit1  
4  
2  
5  
3  
6

With blank cells ignored:

excelCopyEdit=TOCOL(A1:C2, 1)

---

🔷 2. TOROW Function – Convert to Row

📌 Purpose:

TOROW turns a 2D array into a single horizontal list.

🧮 Syntax:

excelCopyEditTOROW(array, [ignore], [scan_by_column])

---

📊 Example:

Using the same data:

excelCopyEdit=TOROW(A1:C2)

Result:

CopyEdit1   4   2   5   3   6

Row-wise scan:

excelCopyEdit=TOROW(A1:C2, 0, FALSE)

Result:

CopyEdit1   2   3   4   5   6

---

✅ Why Use TOCOL/TOROW?

• Flatten 2D ranges for lookup or processing
• Prepare lists for filtering or advanced formulas
• Save time over manual copy-paste or TRANSPOSE hacks

---

🎓 Take Your Excel Skills to the Next Level!

Want to master functions like TOCOL, TOROW, XLOOKUP, FILTER, TEXTSPLIT, and more?

🚀 Join my best-selling Excel course:
👉 Mastering MS Excel – A Comprehensive Training Course

✅ Available in both Online & Pen Drive formats
📈 Suitable for students, professionals & business users

---