ExcelFormulas logoExcelFormulas.
Ask AI
Dynamic ArraysVerified QA Tested

TOCOL.

Transforms a 2D table or array into a single vertical column.

SyntaxArgumentsExampleInteractive SandboxGeneratorCompatibilityFAQRelatedCheat Sheet
Category: Dynamic Arrays
ExcelExcel 365Google Sheets

How it works.

TOCOL flattens a multi-row and multi-column grid into a single column array, with options to skip blank cells and formula error codes.

Formula syntax.

Standard Excel Notation
=TOCOL(array, [ignore], [scan_by_column])
Tip:Arguments wrapped in square brackets [like_this] are optional; all other arguments are required.

Formula arguments.

Every parameter accepted by the TOCOL function is detailed below.

arrayRequired

The 2D array or range to flatten.

Example value: A2:B3
ignoreOptional

0 = Keep all (default), 1 = Ignore blanks, 2 = Ignore errors, 3 = Ignore blanks and errors.

Default: 0

Simple example.

Step-by-Step Scenario

Shift Schedule - Flatten Roster Grid

Reshape morning and evening shift staff into a single roster column.

Morning ShiftEvening ShiftCombined Roster
AliceBob—
CharlieDave—
Applied Formula
=TOCOL(A2:B3, 1)
Expected Result:Alice

Evaluates TOCOL over the sample table cells, placing the verified output in cell C2.

Formula generator.

Full Generator Wizard

Customize the argument fields below to generate a tailored TOCOL formula ready to paste directly into your worksheet.

Generated FormulaReady to copy
=TOCOL()

Expected result behavior.

Returns "Alice" based on sample evaluation criteria and active cell references.

Sample Output:Alice
Output Type:Text String

Best Practices

  • Use ignore = 1 to consolidate sparse data grids into clean lists without blanks.

Common Mistakes

  • Only available in Excel 365 and Excel for Web; older Excel versions return #NAME?.
Compatibility Database

Platform & version compatibility.

Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.

Excel✓
Supported
Excel 365✓
Supported
Excel 2021✕
No
Excel 2019✕
No
Google Sheets✓
Supported
Recommended Alternative for Unsupported Platforms

In Excel 2021 or 2019, use INDEX combined with SEQUENCE, or use Power Query (Unpivot Columns) to flatten grids into a column.

Microsoft Excel History & Version Availability

Introduced in Excel 365 (late 2022). Not available in standalone Excel 2021, Excel 2019, or older.

Google Sheets Compatibility Notes

Fully supported in Google Sheets (added late 2022). Uses identical arguments to Excel.

Frequently asked questions.

Technical Q&A
Can I use TOCOL across different worksheets in Excel?↓

Yes. You can reference cells located on other sheets by including the sheet name followed by an exclamation mark, for example: =TOCOL(Sheet2!A2:A10). If the sheet name contains spaces, enclose it in single quotes: ='Q1 Report'!A2:A10.

What is the most common reason for errors in TOCOL?↓

Only available in Excel 365 and Excel for Web; older Excel versions return #NAME?.

Does TOCOL work identically in Google Sheets?↓

Yes, TOCOL is supported in both Microsoft Excel and Google Sheets with standard evaluation behavior.