How to Use the TOCOL and TOROW Functions in Microsoft Excel

Reshaping a two-dimensional array of data into a single column or row in Microsoft Excel streamlines the layout of your figures and makes for more efficient sorting and filtering. For these reasons, you should get to know how Excel's TOCOL and TOROW functions work.

The TOCOL and TOROW functions are available to those using Excel 2024 or later, Excel for Microsoft 365, Excel for the web , and the Excel mobile and tablet apps.

The TOCOL Function: Turning a Two-Dimensional Array Into a Single Column

As its name suggests, Excel's TOCOL function turns an array of multiple columns and rows into a single, one-dimensional column of data.

The syntax for this function is as follows:

where

• a (required) is the array to return as a column,

• b (optional) defines whether the result should ignore certain types of values, and

• c (optional) defines whether the array should be scanned by column or row.

If you omit argument b , the formula returns all the source array's values in the resultant column. However, 1 tells Excel to ignore blanks, 2 tells Excel to ignore errors, and 3 tells Excel to ignore blanks and errors.

Omitting argument c scans the array by row from left to right, starting in the top row. On the other hand, the Boolean value TRUE tells Excel to scan the array by column from top to bottom, starting in the leftmost column.

Imagine you're the CEO of a retail company, and you have a spreadsheet containing store types in column A, countries in row 1, and a manager's name in each intersecting cell.

Your aim is to turn the array of manager names in cell B2 to cell F17 into a single column, which you will use as the starting point of a new dataset.

Let's first look at TOCOL in its simplest form, using only the required argument. In a blank cell, type:

and then select the cells containing the values you want to list in a single column.

Next, close the parentheses at the end of the formula, and press Enter.

The resultant 1D column of names appears as a dynamic array that spills from the cell where you typed the formula.

Due to the result being dynamic, it will adopt any changes to the original data. However, bear in mind that dynamic arrays are incompatible with Excel tables . This means that if you want to format the 1D list as an Excel table, first, you'll need to select all the cells in the result, press Ctrl+C to copy them, and press Ctrl+Shift+V to paste them as values. However, at this point, the resultant list is no longer linked to the original data, so it will not reflect any subsequent changes made.

Because you omitted argument b (values to ignore), the result returns all values, including blanks (shown as zeros) and errors. Also, because you omitted argument c (scan direction), the result lists the values as they appeared in the source from left to right on the first row (all the supermarket managers), then the second row (all the newsagent managers), and so on.

Any repeated values in the source array will also be repeated in the result. See the final section of this guide to see how to return only unique values.

While leaving zeros and errors in the result might be useful in some contexts—like if you want to analyze their prevalence in the source data—most of the time, the data will be cleaner if you force Excel to hide them. To do this, use the same formula, but type 3for argument b :

and press Enter.

Finally, rather than scanning the original array by row, let's say you want to scan it by column. That is to say, you want the result to list the values as they appeared in the source from top to bottom in the first column (all the U.S.-based managers), then the second column (all the U.K.-based managers), and so on.

To do this, you need to type TRUEfor argument c :

The TOROW Function: Turning a Two-Dimensional Array Into a Single Row

Microsoft Excel's TOROW function turns an array of multiple columns and rows into a single, one-dimensional row of data.

The syntax works in the same way as the TOCOL function:

where

• a (required) is the array to return as a row,

• b (optional) defines whether the result should ignore certain types of values, and

• c (optional) defines whether the array should be scanned by column or row.

Omitting argument b returns all the source array's values, including blanks and errors, in the resultant row. However, 1 tells Excel to ignore blanks, 2 tells Excel to ignore errors, and 3 tells Excel to ignore blanks and errors.

If you omit the optional argument c , the function scans the array by row from left to right, starting in the first row. If, instead, you want it to scan the array by column from top to bottom, starting in the first column, you need to add the Boolean value TRUE .

Using the same dataset as in the previous example, this time, you want to list all the managers' names in a single row as column headers, beneath which you'll type their employees' names. To do this, in a blank cell, type:

and select the cells in the array.

Then, close the parentheses and press Enter:

The result displays all the names in a single row, with blanks showing as zeros and errors included.

To force the function to ignore blanks and errors, type 3for argument b . Also, to scan the original data by column rather than by row, type TRUEfor argument c:

To turn the result from dynamic values to static ones, select all the cells, press Ctrl+C to copy them, and press Ctrl+Shift+V to paste them as values.

Merging Multiple Arrays Into a Single Column or Row

Used in isolation, TOCOL and TOROW can only turn a single array into a column or row of data. However, when used alongside VSTACK and HSTACK, they can merge multiple arrays into a column or row of data.

In this section, I'll explain how to merge multiple arrays into a single column using TOCOL. However, you can apply the same steps with TOROW to merge multiple arrays into a single row.

Here, you have two datasets—the first contains the manager of each store by European country, and the second contains the same details for Asia. Your aim is to reproduce all the managers' names from both arrays in a single column.

VSTACK stacks multiple arrays on top of one another. You can see this in action in the screenshot below.

As a result, if you nest VSTACK inside a TOCOL formula, the names of all the European managers will be listed by row first, followed by all the Asian managers by row.

On the other hand, HSTACK stacks multiple arrays next to each other.

So, if you nest HSTACK inside TOCOL, the resulting column first lists all the supermarket managers (Europe, then Asia), then all the newsagent managers (Europe, then Asia), and so on.

Remember, you can also include the third argument of the TOCOL and TOROW functions to further redefine the order of the result.

Turning a Two-Dimensional Array Into a Single Column of Unique and Sorted Values

Excel's TOCOL and TOROW functions can be nested inside a whole host of useful data-organization functions. Two of the most useful companions of TOCOL and TOROW are SORT and UNIQUE .

Let's say you're an athletics manager, and you have an Excel array of gold medal winners in various events over the past five years.

Your aim is to convert all the names into a single column. Using just TOCOL, you would type:

However, some people appear more than once in the list because they've won multiple gold medals. So, to avoid repetition, you need to nest the whole TOCOL formula inside UNIQUE:

Finally, you want to sort these unique values into alphabetical order, which is the default order adopted by the SORT function. So, you can wrap the whole of the UNIQUE-TOCOL formula within SORT:

To sort the result in reverse order, type -1for the third argument of the SORT part of the formula:

The SORT and UNIQUE functions' default arguments work best with column-based arrays (rather than row-based arrays). To use these two functions alongside TOROW, you must type TRUEfor the second argument of the UNIQUE part of the formula, and TRUEfor the fourth argument of the SORT part of the formula:

---

The TOCOL and TOROW functions are just two ways to rearrange data in Microsoft Excel . For example, you can flip the rows and columns using the TRANSPOSE function, transform large datasets using PivotTables , or combine worksheets using Power Query .