Contact
12 programs
Explore the site

TOCOL Function in LibreOffice Calc

Array LibreOffice Calc LibreOffice 25.8

Flatten LibreOffice Calc data into one column with TOCOL, controlling row-major or column-major order and whether true blanks are omitted.

Verification status: Eight ordinary INDEX-selected components, dimension or padding-check expressions were recalculated in LibreOfficeDev 26.8.0.0.alpha0 (2c87e51eeaa2b413ff4ae097b2705eea1995d8e5) using headless XLSX import and forced recalculation. Exact identities, typed results and controls were checked. LibreOffice 26.8.1.1 was source-reviewed only; stable 25.8 and GUI array entry were not executed. How formula examples are checked

Quick answer: =INDEX(TOCOL(H2:J3);4;1) returned 4 in the tested development build.

Availability and version history

  • Introduced: LibreOffice 25.8, explicitly stated in function-specific Help.
  • Supported in: 26.8 Help and 26.8.1.1 registration; no retirement is established.
  • Scope: Desktop Calc, English semicolon-separated formulas. Help identifies the COM.MICROSOFT.TOCOL namespace outside OpenFormula 1.3.
  • Checked: October 14, 2026. Eight listed component/dimension examples were recalculated in LibreOfficeDev 26.8.0.0.alpha0 using headless XLSX import. Stable 25.8, 26.8.1.1 and GUI array entry were not executed.

What TOCOL returns

TOCOL reads a rectangular array in a chosen order and returns the selected values as a single column. Reading order and output orientation are separate decisions: the result is always a column, while By_column decides whether to finish each source column or each source row first. Function Help.

Syntax and arguments

TOCOL(Array [; Ignore [; By_column]])

Square brackets mark optional arguments; do not type them. Array is a range or matrix. Ignore defaults to 0: keep all values. Use 1 to ignore blank cells, 2 to ignore errors, or 3 to ignore both. By_column defaults to FALSE, scanning rows left to right; TRUE scans each column from top to bottom. The inspected callback restricts Ignore to the four documented codes.

For a whole-array result, the official Help instructs array-formula entry with Ctrl+Shift+Enter. The examples below instead select components with INDEX or inspect dimensions and padding. They do not establish a GUI spill or array-entry workflow.

Worksheet setup

Enter this source table in H2:J3:

Row H I J
2 1 2 3
3 4 5 6

Also enter the mixed table below. Leave H9 and I10 truly empty; do not type the word “blank” or an empty-string formula.

Row H I
8 red 1
9 (blank) 2
10 blue (blank)

Eight reproducible examples

The native column is from LibreOfficeDev 26.8.0.0.alpha0, build 2c87e51eeaa2b413ff4ae097b2705eea1995d8e5, using headless XLSX import and forced recalculation. Text is shown in quotes.

Formula Independent reference LibreOfficeDev result
=INDEX(TOCOL(H2:J3);4;1) 4 4
=INDEX(TOCOL(H2:J3;0;TRUE());4;1) 5 5
=ROWS(TOCOL(H2:J3)) 6 6
=COLUMNS(TOCOL(H2:J3)) 1 1
=INDEX(TOCOL(H8:I10;1);3;1) 2 2
=INDEX(TOCOL(H8:I10;1;TRUE());2;1) "blue" "blue"
=ROWS(TOCOL(H8:I10;1)) 4 4
=INDEX(TOCOL(H8:I10;0);1;1) "red" "red"

Practical worksheet

With H2:J3 holding rows 1, 2, 3 and 4, 5, 6, =TOCOL(H2:J3) orders values 1, 2, 3, 4, 5, 6. =TOCOL(H2:J3;0;TRUE()) instead orders them 1, 4, 2, 5, 3, 6. Both are six-by-one arrays, but their fourth values differ: 4 versus 5. Choose the scan order before converting a cross-tab into a list, especially when the next calculation assumes groups remain adjacent.

Advanced interpretation and checks

The mixed table H8:I10 contains four real values and two genuinely empty cells. With Ignore=1, row scanning yields “red”, 1, 2, “blue”; column scanning yields “red”, “blue”, 1, 2. The result length is four in either case. Blank filtering changes positions, so a later INDEX refers to the compressed list rather than the original row number. Numeric zero is a value and should not be omitted merely because it looks visually empty. Inspected implementation.

Boundaries and troubleshooting

The source distinguishes a true empty matrix cell from an empty string. A formula returning "" is not automatically covered by the true-blank examples. Error-skipping modes 2 and 3 were source-reviewed but not executed here; deleting errors can hide a problem that should instead be repaired. If filtering leaves no entries, the inspected callback returns #N/A rather than a zero-length list. That all-filtered outcome was not executed.

TOROW, WRAPCOLS, INDEX.

Official evidence and checking scope

All eight saved formula identities and their typed results were reconciled. The stale SUM cache changed to 3 and the uncached SUM returned 7. Imported _xlfn.TOCOL formulas were saved with the documented COM.MICROSOFT.TOCOL identity. These checks establish the listed development-build components only; current-source guards and unexecuted cases remain source-derived.