You are currently viewing Excel TOCOL and TOROW Function

Excel TOCOL and TOROW Function

Data comes in different shapes and Excel provides us with amazing functions to transform these different shapes into the most suitable structure for our analysis. In this article, we will explore two amazing shaping functions TOCOL and TOROW.

TOCOL

The TOCOL function is only available to Office 365 users. It transforms an array into a single column. 

TOCOL Function Syntax

array – The array or reference to return as a column.

ignore – [Optional] Use the values listed below to specify whether to ignore certain values or errors. By default, no values are ignored.

  • 0: Keep all values (default)
  • 1: Ignore blanks
  • 2: Ignore errors
  • 3: Ignore blanks and errors

scan_by_column – [Optional] Scan the array by column. By default, the array is scanned from left to right (by row).

TRUE=column – Scans values from top to bottom.

FALSE=row – Scans values in an array from left to right

Let’s see how the function works.

All you need to do is type out =TOCOL and then select the range you want to transform into a column.

Scan the array horizontally or vertically

By default, the TOCOL function reads values in an array “by row” that’s from left to right. To read values by column instead, we’ll have to set the scan_by_column argument to TRUE or 1. 

Notice that, in both cases, the returned arrays are the same size, but the values are arranged in a different order.

Remove Duplicate values from a range

Excel’s UNIQUE function is perfect for removing duplicate values from a column However, note that it cannot remove duplicates from more multi-column or row arrays. To achieve this, we have to use both functions together like so. 

Also, you can sort the values by using the SORT function. 


What if we have multiple ranges?

If we have multiple ranges that are scattered around and we need to combine the values into a single column, we will have to bring in the VSTACK function combined with the TOCOL function to achieve this. The VSTACK function allows you to append arrays seamlessly.

Is the TOCOL function not working?

If you ever encounter an error while using the TOCOL function, it may be a result of any of these:

TOCOL is not supported in your Excel: Typiclly, the  #NAME? Error is thrown when the correct spelling of the function’s name is incorrect. However, If the name is correct but the error persists, the function is not available in your version of Excel.

The array is too large: A #NUM error indicates that the array cannot fit into a column. A typical case is when you refer to entire columns or rows.

There are not enough empty cells: When a #SPILL error occurs, check that the column where the formula is entered has enough empty cells for the results. 

The TOROW Function

The syntax of the TOROW function has 3 arguments just like TOCOL. It is the reverse of the TOCOL function as it allows users to transform an array into a single row. 

The syntax works just like the TOCOL function to return a single row from an array as seen below.

You can also incorporate other functions like the UNIQUE function to remove duplicates and the VSTACK function when you have more than one table.

In this article, we explored two of Excel’s array shaping functions the TOCOL and TOROW functions, which allow us to shape data values either horizontally (single row) or vertically (single column). 

Leave a Reply