TOCOL Function
With every update, new functions are added to Excel. One of the lesser-known but incredibly useful functions introduced in recent versions is TOCOL.
If you’ve ever struggled with reshaping data, maybe converting a wide table into a single column for easier analysis, then TOCOL is your best bet. Instead of relying on complex formulas, manual copy-pasting, or Power Query, TOCOL gives you a simple way to flatten arrays or ranges into a single column.
The TOCOL function was introduced in Excel 2024 and is available across the Microsoft Office apps. This makes it part of the new wave of dynamic array functions designed to simplify reshaping and restructuring data.
In this article, we’ll explore what TOCOL does, why it’s useful, and some practical examples.
What is TOCOL?
The TOCOL function in Excel is a dynamic array function that allows you to convert a range or array into a single column. Think of it as a “data flattener” that takes values spread across multiple rows and columns and stacks them neatly into one vertical list, for easier analysis. TOCOL literally means To Column, and yes, its counterpart TOROW exists.
Syntax
=TOCOL(array, [ignore], [scan_by_column])
The TOCOL function syntax has the following arguments:
Array: The array or reference to return as a column.
Ignore: Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following:
- 0 Keep all values (default)
- 1 Ignore blanks
- 2 Ignore errors
- 3 Ignore blanks and errors
Scan_by_column: If omitted or is FALSE, which is the default, the array is scanned by row; If TRUE, the array is scanned by column.
Practical Example
Here is an array of some household appliances, with blanks and errors.
Using the syntax:
Get Odinaka Kingsley Afam’s stories in your inbox
Join Medium for free to get updates from this writer.
=TOCOL(C3:G8, 0, FALSE), returns the column below:
If we omit the last two arguments, which are optional, we will still get the same result.
Now, let’s take it a step further by changing the last argument to TRUE.
=TOCOL(C3:G8, 0, TRUE)
For this, the array is scanned by column. Here is the result:
In the two results, zeros were added to our result, whereas we have no zeros in our array. To get rid of the zeros, we change the 0 in the Ignore argument to 1.
=TOCOL(C3:G8, 1). The result becomes:
To remove the error messages from our array, we simply change 0 to 2, in our function argument.
=TOCOL(C3:G8, 2)
To get rid of both the blanks and errors, we simply input the number 3, in place of 0.
Bonus Tip
To avoid the occurrence of repetition, we wrap our TOCOL function within a UNIQUE function. Our new combo function becomes:
=UNIQUE(TOCOL(C3:G8, 3))
Here’s the result:
Conclusion
TOCOL makes working with data in Excel much easier. Instead of spending time copying, pasting, or using complicated formulas, you can quickly turn any range into a clean column. Whether you want to remove blanks, ignore errors, or combine it with functions like UNIQUE or SORT, TOCOL helps you save time and keep your data organised. It’s a small function with a big impact, and once you start using it, you’ll wonder how you managed without it.
The journey continues, until the next time.✌️
