Numbers TOCOL: Flatten Arrays into a Column and Handle Blanks
Flatten Numbers arrays with TOCOL, choose row or column scan order, preserve zero values and check record alignment with explicit INDEX examples.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
TOCOL turns a rectangular array into one column. By default it reads left to right across the first row, then across the next row. You can instead read down each column, and choose whether to remove blanks or errors.
Flattening changes arrangement, not just appearance: removing blanks shortens the result and may shift later entries away from their original partners.
Availability and version history
- Introduced: The original introduction release and date are not established by the checked references.
- Availability: Currently documented in Apple’s Functions Help 15.4 documentation edition; no final supported-version endpoint is established.
- Scope: Shared iWork reference. Function-specific historical Mac, iPad, iPhone and iCloud endpoints are unverified.
- Checked: October 14, 2026, against the official TOCOL reference. Examples were independently calculated; no native Numbers test was performed.
Syntax, scan order and ignored values
TOCOL(array, ignore, scan-by)
Apple’s TOCOL reference documents the last two arguments as optional through their defaults:
| Argument | Meaning and documented default |
|---|---|
array |
The array or reference to flatten. |
ignore |
0 or omitted keeps all values; 1 skips blanks; 2 skips errors; 3 skips both blanks and errors. |
scan-by |
FALSE or omitted reads each row in turn; TRUE reads each column in turn. |
The output’s direction and the input’s scan order are separate choices. Skipping a blank removes a position; keeping it returns zero in Apple’s examples. A genuine numeric zero is not a blank. Formula-generated empty text, whitespace text and imported empty-looking cells are not established as equivalent to genuinely empty cells by the checked entry.
A complete numeric worksheet with a true blank
Create ColumnOrder, with eleven columns A:K and thirteen rows, including one header row. Enter the following grid in B2:D3. Leave C2 genuinely empty; do not type the word Blank, a space or a formula returning empty text. D2 is the numeric value zero.
| Row | B | C | D |
|---|---|---|---|
| 2 | 8 | Leave empty | 0 |
| 3 | 5 | 12 | 9 |
Put Position in F1 and the integers 1,2,3,4,5,6 in F2:F7. Label G1 Default, H1 Skip blanks by row, I1 Skip blanks by column and J1 Keep all by column.
- In G2 enter
=INDEX(TOCOL($B$2:$D$3),$F2,1)and fill through G7. - In H2 enter
=INDEX(TOCOL($B$2:$D$3,1),$F2,1)and fill through H6 only. - In I2 enter
=INDEX(TOCOL($B$2:$D$3,1,TRUE),$F2,1)and fill through I6 only. - In J2 enter
=INDEX(TOCOL($B$2:$D$3,0,TRUE),$F2,1)and fill through J7.
| Position | G: default | H: skip by row | I: skip by column | J: keep by column |
|---|---|---|---|---|
| 1 | 8 | 8 | 8 | 8 |
| 2 | 0 | 0 | 5 | 5 |
| 3 | 0 | 5 | 12 | 0 |
| 4 | 5 | 12 | 0 | 12 |
| 5 | 12 | 9 | 9 | 0 |
| 6 | 9 | No sixth result | No sixth result | 9 |
Leave H7 and I7 empty. Do not type the explanatory text from the table into those cells. The blank at C2 becomes a zero in the default result; the original zero at D2 remains present when blanks are skipped.
Why equal totals can hide different data handling
Enter =SUM(G2:G7) in G9 and =SUM(H2:H6) in H9. Both are 34. Enter =AVERAGE(G2:G7) in G10 and =AVERAGE(H2:H6) in H10. Expect approximately 5.6666666667 and 6.8.
The default result has six numeric positions because the blank became zero. The compacted result has five. The total alone does not reveal that change in the denominator. SUM and AVERAGE therefore answer different checks here.
These are arithmetic consequences of the stated blank-handling rule, not a recommendation to treat a missing measurement as zero.
Advanced: keep identifiers and quantities aligned
Create Pairing, with twelve columns A:L and eight rows, using no header rows. Put A,B in A1:B1 and C,D in A2:B2. Put 4 in D1, leave E1 empty, and put 7,2 in D2:E2. These are four record identifiers and their corresponding quantities.
Enter indices 1,2,3,4 in G2:G5. In H2 enter =INDEX(TOCOL($A$1:$B$2,1),$G2,1) and fill through H5. In I2 enter =INDEX(TOCOL($D$1:$E$2),$G2,1) and fill through I5. In J2 enter =INDEX(TOCOL($D$1:$E$2,1),$G2,1) and fill through J4 only.
H contains A,B,C,D. I contains 4,0,7,2, preserving positions but representing the missing quantity as zero. J contains 4,7,2. Pairing H and J row by row would falsely assign 7 to B and 2 to C, while D loses its quantity.
Neither conversion validates the missing measurement. Preserve the original two-dimensional input, identify incomplete records, and apply a consistent record-selection rule before flattening related fields. Independently removing blanks from each field is unsafe when their missing positions differ.
Reconstruct Apple’s named example
Create VendorColumn, with ten columns A:J and fourteen rows, using no header rows. Enter this exact grid in A1:D3:
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Aga | Danny | Fleur | Andre |
| 2 | Elisha | Leave empty | Po-Chun | Nisha |
| 3 | Orkun | Jasmine | Liz | Leave empty |
Put positions 1 through 12 in F1:F12. G1 =INDEX(TOCOL($A$1:$D$3),$F1,1) filled through G12 gives Aga, Danny, Fleur, Andre, Elisha, 0, Po-Chun, Nisha, Orkun, Jasmine, Liz, 0.
H1 =INDEX(TOCOL($A$1:$D$3,1),$F1,1) filled through H10 gives the same order without the two zero placeholders. I1 =INDEX(TOCOL($A$1:$D$3,1,TRUE),$F1,1) filled through I10 gives Aga, Elisha, Orkun, Danny, Jasmine, Fleur, Po-Chun, Liz, Andre, Nisha. Leave H11:I12 empty.
Apple documents an error for an array that is too large, without specifying a numeric size limit in this entry. Modes 2 and 3 are documented error-skipping options; these examples do not test particular error values or every mixed-type input.
Array layout and practical limits
These worksheets use INDEX to extract individual array elements into specified cells. Apple documents INDEX as accepting an array returned by another function. This makes each expected position explicit; it is not an observed automatic-spill demonstration.
The function references also permit the spilled-range operator #. Exact native spill placement, maximum array size, imported-workbook behavior and error tokens have not been measured here. Keep input cells separate from result cells, and do not extend an INDEX formula beyond the expected result dimensions.
English formulas use commas between arguments and decimal points. Apple’s functions overview specifies semicolon argument separators when the decimal separator is a comma. Check the target document’s conventions before copying a formula.
For a horizontal result with the same scan choices, use TOROW. EXPAND adds rows or columns instead of compacting values.