Zoho Sheet TOCOL: Flatten Ranges by Row or Column
Turn a range into one column in Zoho Sheet. Compare scan order, blank and error settings, complete spill layouts and record-alignment pitfalls.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
TOCOL turns a rectangular range into one column. Its optional settings control which cells are omitted and whether the source is read by rows or by columns. That choice matters when the output positions represent months, departments or repeated measurements.
Availability and version history
- Introduced: Added in November 2022, according to Zoho’s release history and its linked seven-function announcement. No numbered cloud release is specified.
- Availability: Listed in the current Zoho Sheet function help; no final supported-version endpoint is established.
- Scope: Current web-service documentation. Individual mobile clients, offline use and imported workbook behavior remain separately unverified.
- Checked: October 14, 2026, against the official TOCOL reference. The worked layouts and calculations were independently derived; no native Zoho Sheet test was performed.
Syntax, defaults and scan order
TOCOL(array; [ignore]; [direction])
The official entry requires a source range. The two optional arguments have these meanings:
| ignore | Documented selection |
|---|---|
| 0 or omitted | Keep all values |
| 1 | Omit blank cells |
| 2 | Omit errors |
| 3 | Omit blank cells and errors |
For direction, FALSE or omitted reads each row from left to right before moving down. TRUE reads each column from top to bottom before moving right. This controls the sequence, not merely the orientation of the final result.
Zoho’s launch announcement distinguishes TOCOL’s single-column output from TOROW and its single row. These are separate functions, rather than alternate spellings.
Flatten six readings in two different orders
Create Readings, reserving A1:K12. Put headings above the three input columns and enter only these numbers in A2:C3:
| Row | A | B | C |
|---|---|---|---|
| 2 | 12 | 14 | 16 |
| 3 | 21 | 23 | 25 |
Keep E2:E7, G2:G7 and I2:I7 empty. Put =TOCOL(A2:C3) in E2, =TOCOL(A2:C3;0;TRUE) in G2 and =TOCOL(A2:C3;0;FALSE) in I2.
Zoho’s dynamic-array instructions say to enter one anchor formula and press Enter. Its results occupy adjacent cells automatically. Leave the complete output area empty; an obstruction produces #SPILL!. Do not fill duplicate formulas across the intended result.
The independently derived results are:
| Output position | E: default | G: by column | I: explicit by row |
|---|---|---|---|
| 1 | 12 | 12 | 12 |
| 2 | 14 | 21 | 14 |
| 3 | 16 | 14 | 16 |
| 4 | 21 | 23 | 21 |
| 5 | 23 | 16 | 23 |
| 6 | 25 | 25 | 25 |
Enter =SUM(E2:E7) in K2 and =SUM(G2:G7) in K3. Both give 111. K4 =AVERAGE(E2:E7) gives 18.5, and K5 =COUNT(E2:E7) gives 6. The complete numeric set is unchanged; only its ordering differs. SUM, AVERAGE and COUNT provide different checks.
Copy Readings as ChangedReading, changing only B2 from 14 to 140. E3 and I3 become 140, while the same input appears in G4. Both totals become 237, the mean becomes 39.5, and the count stays six. This identifies the source-to-output mapping without changing the original exercise.
Omit actual blanks and a deliberate error
Create IncompleteReadings, reserving A1:K12. Set A2=12, C2=16, A3=21, C3=25, A4=0 and B4=32. Leave B2 and C4 genuinely empty; do not type spaces or formulas into them. Put =NA() in B3. Zoho’s NA reference documents an unavailable-value error; this is an error cell, not the text “#N/A”.
Leave E2:E7 and G2:G7 empty. Enter =TOCOL(A2:C4;3;FALSE) in E2 and =TOCOL(A2:C4;3;TRUE) in G2.
| Position | E: by row | G: by column |
|---|---|---|
| 1 | 12 | 12 |
| 2 | 16 | 21 |
| 3 | 21 | 0 |
| 4 | 25 | 32 |
| 5 | 0 | 16 |
| 6 | 32 | 25 |
The genuine zero survives. K2 =SUM(E2:E7) gives 106; K3 =COUNT(E2:E7) gives 6. Filtering missing and erroneous entries does not mean filtering zero measurements.
For a separate error-preserving control, leave I2:I8 empty and put =TOCOL(A2:C4;1;FALSE) in I2. Its seven positions follow 12,16,21,error,25,0,32: only the blanks are omitted. Do not apply a total to this control and then quietly interpret the error as zero.
Advanced: avoid breaking record alignment
Create Records, reserving A1:G7. Put IDs L1, L2 and L3 in A2:A4. Put amounts 5 and 9 in B2 and B4, leaving B3 genuinely empty. Enter =TOCOL(A2:A4;1) in D2 and =TOCOL(B2:B4;1) in F2, with both spill areas empty.
D2:D4 contains L1,L2,L3, but F2:F3 contains 5,9. Pairing the second output position would attach 9 to L2, although the original amount belongs to L3. Independent omission changed the lengths differently.
Keep each ID with its original source row until a common record-level inclusion rule has been applied. TOCOL supplies a sequence; it does not preserve a relationship between independently filtered ranges or decide that missing amounts should equal zero.
Errors and boundaries
The function entry lists #NUM! for an array that is too large, but gives no exact maximum in that entry. It also lists invalid-type and invalid-reference errors without defining every invalid ignore or direction value. Use the documented settings rather than extrapolating other numbers.
These exercises avoid predicting how a retained blank is displayed with ignore 0 or 2. Formula-generated empty text, merged cells, error propagation beyond the demonstrated selection rule and imported-array behavior remain unverified. Semicolons match the English reference; use your workbook’s actual formula-entry settings.