TOCOL vs TOROW in Excel + Tips

Hello everyone! Today i will show you how to use TOCOL and TOROW functions in Microsoft Excel step by step. Very modern and useful formula in Excel 365 and Excel 2024 for flatten table grid into one clean line!

1. What is TOCOL and TOROW Function?

• TOCOL: Convert 2D table range (multi rows and multi columns) into 1 vertical column.
• TOROW: Convert 2D table range (multi rows and multi columns) into 1 horizontal row.

Think of TOCOL and TOROW like flattening a box of items:

• If you want all items stacked straight down in 1 column -> Use TOCOL.
• If you want all items lined up side-by-side in 1 row -> Use TOROW.

2. 🛠️ Formula Syntax & Parameters

=TOCOL(array, [ignore], [scan_by_column])
=TOROW(array, [ignore], [scan_by_column])

• array: Required. The range of cells or 2D table you want to convert (e.g., A2:C4).
• [ignore]: Optional. Tell Excel which values to skip:
  • 0 = Keep all values (Default).
  • 1 = Ignore empty cells.
  • 2 = Ignore error cells (#N/A, #VALUE!).
  • 3 = Ignore both empty cells AND errors!
• [scan_by_column]: Optional. How Excel reads your table:
  • FALSE (or omit) = Read by row (left to right, then down).
  • TRUE = Read by column (top to bottom, then right).

3. 📋 Practical Examples

Example 1: Convert Table to Single Column with TOCOL

Suppose you have 3x3 table of student names in range A2:C4:

=TOCOL(A2:C4)

👉 Result: Returns 1 single column with 9 names in order: John, Alex, Sara, David, Emma, Mark, Lisa, Tom, Nina.

Example 2: Convert Table to Single Row with TOROW

Using the same table A2:C4, if you want horizontal list:

=TOROW(A2:C4)

👉 Result: Spills all 9 names across 1 single row from left to right!

Example 3: Ignore Blank Cells & Errors (Super Useful!)

If your table has empty cells or errors like #N/A:

Use ignore code 3 to automatically clean your data:

=TOCOL(A2:C3, 3)

👉 Result: Returns clean 1 column without empty cell and without #N/A error: Apple, Banana, Orange, Mango!

Example 4: Scan by Column (TRUE) vs Scan by Row (FALSE)

By default Excel scans row by row. But if you want Excel read Column A first, then Column B, set third argument to TRUE:

=TOCOL(A2:C4, 0, TRUE)

👉 Result: Reads down Column A first (John, David, Lisa), then Column B (Alex, Emma, Tom), then Column C (Sara, Mark, Nina).

💡 Quick Tips for TOCOL & TOROW

1. Use 1 or 3 for ignore to remove blanks instantly: No need to write complex FILTER or IF formulas just to remove blank spaces from a range!
2. Combine with UNIQUE to get unique list: =UNIQUE(TOCOL(A2:D10, 1)) gets a clean list of unique values from a multi-column table!
3. Combine with SORT for alphabetical list: =SORT(TOCOL(A2:D10, 1)) flattens your grid and sorts all names A-Z in 1 step!
4. Excel Version: These functions are dynamic array formulas, available in Microsoft 365, Excel 2024, and Excel for Web.

📚 Related Resources & Tutorials

• How to Use TAKE Function in Excel
• How to Use CHOOSE Function in Excel
• Top 15 Basic Excel Formulas for Beginners ↗