Excel TOCOL Function: Turn a Grid Into One Column

The TOCOL function in Excel turns a grid of cells into one column. You give it a range, such as a rota with names spread across several days, and it stacks every value into a single list. That list is much easier to count, sort or de-duplicate. This guide covers the syntax, the options for blanks and errors, and the formulas people use TOCOL for most.

What does the TOCOL function do?#

TOCOL reads every cell in a range and returns the values in one column. The result is a dynamic array: it spills, which means it fills the cells below the formula automatically. The syntax has three parts:

=TOCOL(array, [ignore], [scan_by_column])

TOCOL is available in Microsoft 365, Excel 2024 and Excel for the web. In Excel 2021 or older, the formula shows #NAME?. If spilled results are new to you, read dynamic arrays explained first.

A basic TOCOL example#

The examples below use this shift rota in A1:D4. Cell C3 is empty because nobody is booked for that shift:

Click F2 and type:

=TOCOL(B2:D4)

The result spills down nine cells: Ana, Ben, Ana, Cara, 0, Dev, Eli, Cara, Ben. TOCOL reads across row 2, then row 3, then row 4. The 0 is the empty cell C3. TOCOL keeps blanks by default and shows them as zero.

How to skip blanks and errors#

Use the second argument to leave values out. To drop the empty cell:

=TOCOL(B2:D4,1)

Now the list has eight names with no 0. The other options work the same way:

• 2 skips error values such as #N/A or #DIV/0!. This is handy when the grid holds lookup results and some lookups fail.
• 3 skips both blanks and errors.

A cell that contains a formula returning "" isn't truly empty, so option 1 keeps it as an empty-looking text value. If your list still has gaps, check the source for formulas like =IF(B2="","",B2).

How to read column by column#

TOCOL reads row by row unless you tell it otherwise. Set the third argument to TRUE to read down each column instead:

=TOCOL(B2:D4,1,TRUE)

This returns Ana, Cara, Eli (Monday), Ben, Cara (Tuesday), then Ana, Dev, Ben (Wednesday). Choose the order that matches how the grid is organised. For a rota with days as columns, reading by column keeps each day's names together.

TOCOL has a twin, TOROW, which takes the same arguments and returns one row instead. And if you only want to flip a range sideways while keeping it as a grid, that's a job for TRANSPOSE. The guide to transposing data in Excel explains the difference.

TOCOL with UNIQUE and SORT#

TOCOL is most useful as the first step inside another function. UNIQUE and SORT expect a single list, so TOCOL prepares the grid for them.

To list everyone on the rota once, A to Z:

=SORT(UNIQUE(TOCOL(B2:D4,1)))

The result is Ana, Ben, Cara, Dev, Eli. TOCOL flattens the grid, UNIQUE removes the repeats and SORT puts the names in order. The UNIQUE function guide and the SORT function guide explain every argument, and getting unique values from multiple columns shows more variations, including VSTACK for columns that aren't next to each other.

Other useful combinations:

To use the spilled list in another formula, refer to its first cell with a #. If the TOCOL formula is in F2, =COUNTA(F2#) counts every value it returned. The article on spill range references covers this in detail. The UNIQUE function article has more ways to count and filter the result.

How to flatten a grid in older Excel#

Without TOCOL, use INDEX with INT and MOD to read the grid one position at a time. For a grid 3 columns wide, item number n sits in row INT((n-1)/3)+1 and column MOD(n-1,3)+1. In F2, type:

=INDEX($B$2:$D$4,INT((ROWS($F$2:F2)-1)/3)+1,MOD(ROWS($F$2:F2)-1,3)+1)

Fill it down nine rows (Windows: Ctrl+D; Mac: ⌘D). ROWS($F$2:F2) counts 1, 2, 3 as you go down, so each row returns the next item. Change the 3 to the number of columns in your grid. To know how many rows to fill, use =ROWS(B2:D4)*COLUMNS(B2:D4), which gives 9 here. Unlike TOCOL, this method can't skip blanks, and empty cells return 0.

Common TOCOL problems#

Most TOCOL issues come from the version, the space below the formula, or the source data:

1. #NAME? Your Excel doesn't have TOCOL. Use the INDEX method above.
2. #SPILL! Something is in the way of the result. Clear the cells below the formula.
3. Zeros in the list. The source has empty cells. Add 1 as the second argument.
4. Wrong order. Switch the third argument between FALSE and TRUE.
5. Header text in the list. Start the range below the header row, as in B2:D4, not B1:D4.

Practise it#

Try the exercise on this page: it uses the INDEX method to find any position in a grid, which is exactly the logic TOCOL runs for you. Then practise modern Excel functions in a live spreadsheet to work with spilled results and # references, and practise UNIQUE in its own lesson to turn long lists into clean ones.