How to Use TOROW Function in Excel

This is the ultimate guide on how to use the TOROW function in Excel.

The rules for using the TOROW function in Excel are the following:

  • When the scan_by_column is left blank or FALSE, the TOROW function will scan the selected array by row.
  • When the scan_by_column is TRUE, the TOROW function will scan the selected array by column.  
  • The TOROW function will return a #VALUE! error when the selected array constant contains one or more numbers that are not whole numbers.
  • If the selected array is too large, the TOROW function returns a #NUM error. 

Excel contains many tools and functions we can use to organize our data. And one of these functions is the TOROW function. So the TOROW function is used to return a selected array into a single row. 

Essentially, we can easily transform the arrangement of our data set into a single row whenever needed. If we are working with a large data set, it will take too much time to manually input the values of an array into a single row. 

So the TOROW function quickly performs this task for us and returns a new arrangement of our data set in a single row. 

Let’s take a sample scenario wherein we will use the TOROW function in Excel.

Suppose you are given a large data set wherein the values are arranged in an array. But, you need the values to be in a single row to input more information about them below. 

Since it would be inefficient and time-consuming to arrange the values in a row individually, you quickly finished your task by using the TOROW function. 

Great! Before we move on to a real example of using the TOROW function, let’s first learn how to write the TOROW function in Excel.

 

The Anatomy of the TOROW Function

The syntax or the way we write the TOROW function is as follows:

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

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • TOROW() is our TOROW function. And this function is used to return an array as one single row. 
  • array is the only required argument for this function. So this refers to the array or cell reference we want to return as a single row. 
  • ignore is an optional argument. And this refers to what kind of values the function will optionally ignore. So there are four values we can input for this argument to control what values to ignore or keep. 
  • scan_by_column is another optional argument. And this is a boolean value that controls how the function will read the values from the selected array. By default, the function reads the array by row from left to right. Otherwise, the function will read the selected array by column.

Amazing! Now let’s dive into a real example of using the TOROW function in Excel.

 

 

A Real Example of Using the TOROW Function in Excel

Let’s say we have a data set containing an array of values. And we need to re-arrange the values into a single row. So our initial data set would look like this:

Initial data set

Essentially, the TOROW function has three arguments that we can manipulate to customize how we want the function to return our array. Firstly, the ignore argument controls how the function will deal with the values. And there are four options we can input for this argument. 

By default, the ignore argument is set to 0, meaning the function will keep and return all the values in the array. Then, we can input the value 1, which will ignore the blank cells. 

If we want to ignore the errors in the array, we can input the value 2. Lastly, we can input the value 3 to instruct the function to ignore both the blanks and errors in the array. 

Here is a table to easily remember the options we have for the ignore argument. 

Value Description
0 (default) Keep all the values
1 Ignore the blanks in the array
2 Ignore the errors in the array
3 Ignore both the blanks and errors in the array

Secondly, we can also decide how we want the TOROW function to read the values in the selected array with the use of the scan_by_column argument. So the scan_by_column needs a boolean value to be inputted. 

By default or FALSE, the function will read the values from the selected array by row from left to right. When it reaches the end of the row, the function will drop down and read the values of the next row from left to right. 

Otherwise, we can input TRUE or 1 for the function to read the values by column. So the function will read the values in the first column from top to bottom. Then, it will move to the next column to the right and read the values the same way. 

After using the TOROW function, our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Finally, let’s start learning the process of how to use the TOROW function in Excel.

 

 

How to Use TOROW Function in Excel

In this section, we will explain the step-by-step process of how to use the TOROW function in Excel. Furthermore, each step contains detailed instructions and pictures to guide you along the way.

1. Firstly, let’s simply select an array and transform it into a single row. So we will start by typing an equal sign and the function name in a new cell where we want to return the row. Then, input the formula “=TOROW(A2:C4)”. Lastly, press the Enter key to return the result.

TOROW function in Excel

 

2. And tada! We have successfully used the TOROW function in Excel.

Final results

 

3. Secondly, let’s say our data set contains some blank cells. So we can use the ignore argument to ignore blanks in the array. In this case, we will type in the formula “=TOROW(A6:C8,1)”. Then, press the Enter key to return the row.

TOROW function in Excel

 

4. And tada! We ignored the blanks in the array and returned the array into a single row.

Final output

 

5. Lastly, we can try scanning the array by column. In this case, we will input a TRUE value at the end of the formula. So our entire formula would be “=TOROW(A2:C4,, TRUE)”. Then, press the Enter key to return the result.

TOROW function in Excel

 

6. And tada! We have successfully scanned the selected array by column.

TOROW function in Excel

 

And that’s pretty much it! So we have discussed thoroughly how to use the TOROW function in Excel. Furthermore, we explained how we could manipulate the function and customize how we want the function to act and return the values. Now you can apply this and use the TOROW function in your work. 

Are you interested in learning more about what Excel can do? You can now use the TOROW function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.



		
0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like