Excel TOCOL function – convert range to single column
Excel has long allowed you to switch data between rows and columns. But turning a whole range of cells into a single column used to be difficult. That has now changed.
Microsoft has introduced a new function called TOCOL. It lets you quickly turn an array or range into a single column — in just one step.
Below are some common tasks that the TOCOL function can now handle with ease.
Excel TOCOL function
The TOCOL function in Excel lets you change an array or range of cells into a single column. It has three settings, but you usually only need the first one.
TOCOL(array, [ignore], [scan_by_column])
- Array (required) – This is the range or array you want to turn into a column.
- Ignore (optional) – This option lets you decide if you want to ignore certain values. You can choose:
- 0 or omitted (default) – Keep all values
- 1 – Ignore blank cells
- 2 – Ignore error values
- 3 – Ignore both blanks and errors
- Scan_by_column (optional) – This controls how Excel scans the array:
- FALSE or omitted (default) – Scan by row, from left to right.
- TRUE – Scan by column, from top to bottom.
TOCOL availability
TOCOL is a new function, which is supported in Excel for Microsoft 365 (for Windows and Mac) and Excel for the web.
Basic TOCOL formula to transform range into column
You can use the TOCOL function with just one thing — the array of cells you want to change.
For example, if you have a block of cells with 3 columns and 4 rows, you can turn it into one column like this:
Just type this formula in one cell (like E2), and Excel will automatically fill the cells below with the result. This is called a spill range — Excel “spills” the results into the cells below without you having to drag the formula.
How this formula works:
Technically, the range A2:C5 is first converted into a two-Dimensional array. Please notice the semicolon-separated rows and comma-delimited columns:
{“Green”,”Blue”,”yellow”;a,0,B;4,#N/A,6;1,8,8}
The TOCOL function scans the array from left to right and transforms it into a one-dimensional Vertical array:
{“Green”;”Blue”;”yellow”;A;0;B;4;#N/A;6;1;8;8}
The result is placed in cell E2, from which it spills into the below cells.
How to use TOCOL function in Excel - formula examples
To better understand what the TOCOL function can do and how it can be useful, let’s look at a few example formulas.
Transform array to column ignoring blanks and errors
As you saw in the earlier example, the basic TOCOL formula includes all the values from the original range — even blank cells and error messages.
In the result, blank cells are shown as zeros, which can be confusing if your data already contains real zeros.
To avoid this, you can use the second argument in the TOCOL function to tell Excel to skip blank cells, errors, or both
Here’s how:
Scan array horizontally or vertically
By default, the TOCOL function reads the array row by row, from left to right. This happens when the scan_by_column setting is left out or set to FALSE.
If you want Excel to read down each column instead — from top to bottom — set this part to TRUE (or 1).
Here’s an example:
In both cases, the result will have the same number of values, but the order of those values will be different depending on how the array was scanned.
Combine multiple ranges into one column
If you’re working with multiple ranges that aren’t next to each other, you can first combine them into one using the VSTACK function. Then, use TOCOL to turn the combined data into a single column.
For example, if your two ranges are A2:C4 and A8:C9, the formula looks like this:
This formula uses the default setting, so it reads the data row by row (left to right), and stacks everything into one column.
If you want to read the values column by column (top to bottom), use TRUE for the 3rd argument:
Keep in mind: in this case, Excel reads the entire stacked array—not each original range separately. So it will first take all values from column A, then column B, and so on.
If you need to stack your ranges side by side (horizontally) instead of one below the other, use the HSTACK function instead of VSTACK.
- To join the arrays side by side and read them left to right (row by row), use this formula:
- To join the arrays side by side and read them top to bottom (column by column), use this one:
In both cases, the arrays are combined across, and TOCOL turns them into a single column, based on how you want to read the data.
Extract unique values from a multi-column range
The UNIQUE function in Excel is great for finding unique values in a single row, column, or even entire rows.
But if you have a range with multiple columns and just want the individual unique values — not full rows — it won’t work by itself.
To solve this, you can combine UNIQUE with TOCOL.
For example, to get all the different values from the range A2:C7, use this formula:
If you want to sort the results alphabetically, wrap the formula inside the SORT function like this:
That’s how you can use the TOCOL function in Excel 365 and alternative solutions in earlier versions to transform a 2-dimensional array into a single column. I thank you for reading and hope to see you on our blog next week!
Download Practice Files
You can also practice this through our practice files. Click on the below link to download the practice file.
