Master the TOCOL Function: Transform Your Array Data into Vertical Columns
=TOCOL(array, [ignore], [scan_by_column])The TOCOL function is a powerful dynamic array formula introduced in Excel 365 that transforms multi-dimensional arrays into single-column formats. This function revolutionizes data manipulation by allowing you to restructure complex data layouts into vertical arrangements automatically. Whether you're consolidating data from multiple sources, reshaping matrices, or preparing data for analysis, TOCOL provides an elegant solution without manual intervention. TOCOL is particularly valuable for data analysts and Excel professionals who frequently work with array operations and need to standardize data structures. The formula intelligently handles various array configurations and offers optional parameters to customize the conversion process. By mastering TOCOL, you'll significantly enhance your ability to manipulate and organize data efficiently, saving countless hours on repetitive restructuring tasks. This intermediate-level function integrates seamlessly with Excel 365's dynamic array capabilities, making it an essential tool for modern spreadsheet management. Understanding TOCOL's parameters and practical applications will elevate your Excel proficiency and enable you to tackle complex data transformation challenges with confidence.
Syntax & Parameters
The TOCOL function follows the syntax: =TOCOL(array, [ignore], [scan_by_column]). The first parameter, array (required), specifies the source data you want to convert. This can be a range, a single array, or the result of another formula. The array parameter accepts any multi-dimensional structure and converts it into a vertical column arrangement. The second parameter, ignore (optional), allows you to exclude specific values from the conversion process. You can specify empty cells, error values, or specific data types to skip. This parameter accepts values like 0 for no ignoring, 1 to ignore empty cells, or 2 to ignore error values. When omitted, TOCOL includes all values from the source array. The third parameter, scan_by_column (optional), determines the scanning direction during conversion. Set this to FALSE (or 0) to scan by rows, reading left-to-right then top-to-bottom. Set it to TRUE (or 1) to scan by columns, reading top-to-bottom then left-to-right. This parameter significantly impacts the output sequence when working with multi-dimensional arrays. Practical tip: Always consider your data's logical structure when choosing scan_by_column. For matrix transposition, FALSE typically works better. For column-based data consolidation, TRUE is more intuitive. Test both options to determine which produces your desired output sequence.
arrayignorescan_by_columnPractical Examples
Converting a Sales Matrix to Single Column
=TOCOL(A1:C4, 0, FALSE)This formula takes the 4x3 sales matrix and converts it into a single column, scanning by rows (FALSE). The ignore parameter is set to 0, meaning all values are included. The resulting column will contain all 12 values in row-major order.
Removing Empty Cells During Conversion
=TOCOL(B2:E6, 1, TRUE)The ignore parameter set to 1 automatically removes all empty cells during conversion. The scan_by_column set to TRUE scans top-to-bottom, then left-to-right. This creates a clean, compact column containing only populated cells.
Filtering Errors While Converting Arrays
=TOCOL(D1:F10, 2, FALSE)Setting ignore to 2 removes all error values (#DIV/0!, #N/A, #VALUE!, etc.) from the conversion process. The formula scans by rows (FALSE), producing a clean column containing only valid numeric and text values.
Key Takeaways
- TOCOL is an Excel 365 exclusive function that converts multi-dimensional arrays into single-column formats efficiently
- The ignore parameter (0, 1, or 2) controls whether empty cells and errors are included or excluded from conversion
- The scan_by_column parameter determines the reading direction—FALSE for row-major order, TRUE for column-major order
- TOCOL integrates powerfully with other dynamic array functions like FILTER, SORT, and UNIQUE for advanced data manipulation
- Always test TOCOL's output on sample data first, especially when using scan_by_column, to ensure the sequence matches your analytical needs
Pro Tips
Use scan_by_column=TRUE when working with data organized vertically (like separate columns of customer lists). This preserves the logical grouping of column-based data better than row-based scanning.
Impact : Produces more intuitive output sequences that match your data's original structure, reducing post-processing work needed.
Combine TOCOL with the ignore parameter set to 1 (ignore empty cells) when consolidating data from multiple sources that may have inconsistent formatting or missing values.
Impact : Automatically cleans your data during transformation, eliminating blank rows and creating compact, professional-looking output columns.
Nest TOCOL within other dynamic array functions like SORT, FILTER, or UNIQUE for powerful multi-step transformations in a single formula cell.
Impact : Reduces formula complexity by eliminating intermediate helper columns and creates more maintainable, auditable spreadsheets.
Test the scan_by_column parameter with a small sample of your data before applying to large datasets. The output sequence difference between TRUE and FALSE can significantly impact downstream analysis.
Impact : Prevents data misinterpretation and ensures your converted column maintains the logical order your analysis requires.
Useful Combinations
TOCOL with FILTER for Conditional Conversion
=TOCOL(FILTER(A1:C10, A1:A10>100), 1, FALSE)Combines FILTER to select only rows where column A exceeds 100, then TOCOL converts the filtered result into a single column. This creates a powerful data extraction and restructuring pipeline in one formula.
TOCOL with UNIQUE for Deduplication
=TOCOL(UNIQUE(A1:C5), 0, TRUE)UNIQUE removes duplicate values from the array before TOCOL converts it to a column. This ensures your output column contains only unique values, eliminating redundant entries automatically.
TOCOL with SORT for Ordered Output
=SORT(TOCOL(A1:D8, 1, FALSE))TOCOL converts the array to a column, then SORT arranges the resulting values in ascending order. This combination is powerful for consolidating unsorted multi-dimensional data into an organized single column.
Common Errors
Cause: The ignore parameter contains an invalid value outside the range 0-2, or scan_by_column is not a boolean value (TRUE/FALSE or 0/1)
Solution: Verify that ignore is set to 0, 1, or 2, and scan_by_column is either TRUE/FALSE or 1/0. Check for typos in parameter values: =TOCOL(A1:C4, 1, TRUE) is correct; =TOCOL(A1:C4, 3, TRUE) produces #VALUE!
Cause: The array parameter references a deleted range or an invalid cell reference that no longer exists in the workbook
Solution: Verify that all referenced ranges in the array parameter are valid and haven't been deleted. Use the Name Box to navigate to the referenced range and confirm it exists. Recreate the formula with correct range references.
Cause: The formula is being used in Excel versions prior to Excel 365, as TOCOL is not available in older versions
Solution: Upgrade to Excel 365 or use alternative functions like TRANSPOSE or INDEX/SMALL combinations. Check your Excel version in File > Account > About Excel to confirm you have access to dynamic array functions.
Troubleshooting Checklist
- 1.Verify you're using Excel 365 or a later version—TOCOL is not available in Excel 2019 or earlier versions
- 2.Confirm the array parameter references a valid, existing range that hasn't been deleted or moved to another location
- 3.Check that the ignore parameter is set to 0 (include all), 1 (ignore blanks), or 2 (ignore errors)—no other values are accepted
- 4.Ensure scan_by_column is either TRUE/1 (column-based) or FALSE/0 (row-based)—verify the logic matches your desired output sequence
- 5.Test with a small dataset first to confirm the output order is correct before applying to large ranges
- 6.If combining TOCOL with other functions, verify each function individually works before nesting to isolate any errors
Edge Cases
Empty array passed to TOCOL
Behavior: Returns an empty column with no values, no error generated
This is expected behavior. Use IFERROR wrapper if you need to handle empty results differently: =IFERROR(TOCOL(range), "No data")
Single-cell array (e.g., A1:A1) passed to TOCOL
Behavior: Returns that single value in a column format unchanged
TOCOL safely handles single-cell arrays without errors. The output is simply that one value in column format.
All values ignored due to ignore parameter settings
Behavior: Returns a completely empty column when all values match the ignore criteria
Solution: Adjust the ignore parameter (0, 1, or 2) to include the values you need, or remove the ignore parameter entirely
For example, if ignore=2 and all cells contain errors, the output will be empty. Verify your ignore setting matches your data characteristics.
Limitations
- •TOCOL is exclusive to Excel 365 and unavailable in Excel 2019, Excel 2016, or earlier versions, limiting its use in organizations with legacy Excel deployments
- •The function can only process one array at a time; consolidating multiple separate arrays requires additional VSTACK or concatenation formulas
- •TOCOL cannot perform complex filtering or conditional logic independently; it must be combined with FILTER, IF, or other functions for sophisticated data selection
- •The output is always a single column; if you need multiple columns or specific column arrangements, you'll need to combine TOCOL with other restructuring functions
Alternatives
Compatibility
✓ Excel
Since Excel 365
=TOCOL(array, [ignore], [scan_by_column]) - Fully supported with all parameters functional✗Google Sheets
Not available
✗LibreOffice
Not available