Ravindra BagaleCourses & study guides मराठी Track your progress

9. Dynamic Arrays

9.13 TOCOL and TOROW: Turn a Grid into One List

By Last updated:

Example first. Salman keeps a fictional rider roster for the Pune dark store as a grid: shifts down the side, days across the top, a rider name in each cell, and a few empty cells. He needs one simple list of every rider name, without the gaps, to count shifts and to build a drop-down. TOCOL flattens the grid into one column in a single step.

TOCOL and TOROW — in 30 seconds

What: TOCOL takes a block of cells (rows and columns) and returns all its values in one column. TOROW does the same into one row. You can skip blanks and errors.

Why: Many tools need a single list: UNIQUE, COUNTIF, charts, drop-downs, statistics. Copy-paste from a grid to a list is slow and breaks when the grid changes.

Tiny example: =TOCOL(B2:D4,1) turns the 3 by 3 roster into one list of 7 names (the 2 empty cells are skipped).

First look at the data (before → after):

TOCOL and TOROW: before and after tables, fictional Blinkit Maharashtra data

Expected result — F2 spills one column

F: Rider
Shahrukh
Amir
Shahrukh
Zoya
Zoya
Raja
Rani

Expected result — H2: =UNIQUE(TOCOL(B2:D4,1))

H: Unique rider I: Shifts (=COUNTIF(B2:D4,H2#))
Shahrukh 2
Amir 1
Zoya 2
Raja 1
Rani 1
Before table and formula as text

Before — Roster in A1:D4 (rider per shift and day)

A: Shift B: Mon C: Tue D: Wed
Morning Shahrukh Amir Shahrukh
Afternoon Zoya Zoya —
Evening — Raja Rani

Formula / click path

F2: =TOCOL(B2:D4,1)
ignore = 1 skips blanks; Excel reads row by row (Mon, Tue, Wed of Morning first)

Do it step by step:

  1. Type the roster in A1:D4: shifts in column A, days in B1:D1, names inside, two cells left empty.
  2. In F2 type =TOCOL(B2:D4) and press Enter. Notice the two empty cells come back as 0.
  3. Edit it to =TOCOL(B2:D4,1). The zeros disappear; you get 7 names.
  4. In H2 type =UNIQUE(TOCOL(B2:D4,1)) to get 5 different riders.
  5. In I2 type =COUNTIF(B2:D4,H2#) to count shifts per rider.
  6. Try =TOCOL(B2:D4,1,TRUE) to read the grid column by column (all of Monday first).

Version note

TOCOL and TOROW are listed for Excel for Microsoft 365 (Windows and Mac) and Excel 2024 (Windows and Mac). Excel 2021 and older show #NAME?.

Syntax

TOCOL(array, [ignore], [scan_by_column]) and TOROW(array, [ignore], [scan_by_column])

Argument Values
ignore 0 = keep everything (default), 1 = skip blanks, 2 = skip errors, 3 = skip blanks and errors
scan_by_column FALSE or left out = read row by row (default), TRUE = read column by column

Row by row vs column by column on our roster

Formula Result (top to bottom)
=TOCOL(B2:D4) Shahrukh, Amir, Shahrukh, Zoya, Zoya, 0, 0, Raja, Rani
=TOCOL(B2:D4,1) Shahrukh, Amir, Shahrukh, Zoya, Zoya, Raja, Rani
=TOCOL(B2:D4,1,TRUE) Shahrukh, Zoya, Amir, Zoya, Raja, Shahrukh, Rani
=TOROW(B2:D4,1) the same 7 names as the second row, but spilled across one row

Example 2: statistics on a weekly grid. Weekly sales (fictional, ₹ thousand) for three cities sit in B2:E4 (weeks W1 to W4 across). Functions like MEDIAN or LARGE already accept a grid, but UNIQUE, SORT on a single list, and charts need one column:

City W1 W2 W3 W4
Pune 520 610 580 700
Nashik 300 280 350 330
Nagpur 410 390 450 470
Goal Formula Result
All 12 numbers in one sorted column =SORT(TOCOL(B2:E4)) 280, 300, 330, 350, 390, 410, 450, 470, 520, 580, 610, 700
Top 3 weeks of any city =TAKE(SORT(TOCOL(B2:E4),,-1),3) 700, 610, 580
How many weeks were above 450? =SUM(--(TOCOL(B2:E4)>450)) 5

Example 3: skip errors. If a lookup grid has a few #N/A cells, =TOCOL(B2:D10,2) returns only the good values, and =TOCOL(B2:D10,3) drops blanks and errors together.

Example 4: one header row from two. =TOROW(A1:C2,1) turns a two-row header into one row. Useful before VSTACK (lesson 9.8).

Common problems

What you see Why Fix
Zeros in the list Empty cells are returned as 0 when ignore is 0. Use ignore = 1 (or 3).
The order looks mixed up TOCOL reads row by row by default. Add TRUE as the 3rd argument to read column by column.
#SPILL! Cells below the formula are not empty. Clear the cells or move the formula.

How these results were checked: this function is not available in LibreOffice, so every expected value on this page was computed in Python and cross-checked in LibreOffice with classic formulas that give the same numbers. The layout of the spill follows Microsoft's documentation. Microsoft reference: TOCOL function, TOROW function.

Practice task (try it before you open the answer):

  1. Using the weekly grid above, list all 12 numbers in one row with TOROW.
  2. Find the average of all 12 numbers with one formula that uses TOCOL.
  3. Return one column that lists W1 for all three cities, then W2 for all three cities, and so on.
Answer
  1. =TOROW(B2:E4) gives 520, 610, 580, 700, 300, 280, 350, 330, 410, 390, 450, 470.
  2. =AVERAGE(TOCOL(B2:E4)) gives 449.17 (AVERAGE(B2:E4) gives the same; TOCOL matters when you need the list itself).
  3. =TOCOL(B2:E4,,TRUE) gives 520, 300, 410, 610, 280, 390, 580, 350, 450, 700, 330, 470.

Ravindra Bagale's Tip

Whenever a grid must feed UNIQUE, SORT or a chart, put TOCOL in front of it. Keep the grid for people to read, and let TOCOL make the list for formulas.