This is the ultimate guide on how to use the TOCOL function in Excel.
The rules for using the TOCOL function in Excel are the following:
- If we leave the ignore argument blank, the function will use the default option of keeping all values.
- If we leave the san_by_column argument blank, the function will use false.
- If the data set we selected contains any blank cells, the function will input 0 in them.
- The function will return a #VALUE! error when the array constant contains any numbers that are not whole numbers.
- The function will return a #NUM error when the array is too large.
Excel is an excellent tool for inputting data. Because it is quite easy and simple to input data and creates tables in Excel, many people use it for those purposes. Another reason could be the many functions in Excel that make it easy to input, organize, and analyze data.
And one example of this is the TOCOL function in Excel. So the TOCOL function returns the selected array in a single column. Basically, it will transform an array into a single column. So we can rearrange our inputted values in a single column if needed.
Let’s take a sample scenario wherein we can use the TOCOL function in Excel.
Suppose you have finished inputting your values in a table format with rows and columns. But, you received an urgent message from your team leader to arrange the data in a single column since more data will be inputted later.
And manually inputting the data again would take too long and be inefficient. So you make use of the TOCOL function to transform the data set into a single column really quickly and simply.
Great! Before we discuss a real example of how to use the TOCOL function in Excel, let’s first learn its syntax.
The Anatomy of the TOCOL Function
The syntax or the way we write the TOCOL function is as follows:
=TOCOL(array,[ignore],[scan_by_column])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- TOCOL() this is our
TOCOLfunction. And this function will return a specific array into a single column. - array is the only required argument for the
TOCOLfunction. And this refers to the array or cell reference that we want to return as a column. - ignore is an optional argument. And this refers to which values in the array we want to ignore. So 0 or the default is keeping all the values. Then, we can input 1 to ignore blanks, 2 to ignore errors, and 3 to ignore both blanks and errors.
- scan_by_column is another optional argument. And this refers to a logical value that will indicate how the array will be scanned. For instance, we can input false to scan by row which is the default. And we can input true to scan by column.
Awesome! Now let’s discuss a real example of how to use the TOCOL function in Excel.
A Real Example of Using the TOCOL Function in Excel
Let’s say we have inputted our values in a table format containing multiple rows and columns. And we want to rearrange the values to form a single column instead.
Performing this task manually, especially with a large data set, would be inefficient and take too much time and effort. So we can utilize the TOCOL function to perform this task quickly.
So our initial data set in a table format would look like this:
After, we make use of the TOCOL function to rearrange the data set into a single column. Then, our final data set would look like this:
Finally, we have successfully used rearranged our values to a single column using the TOCOL function. You can make your own copy of the spreadsheet above using the link attached below.
Then, let’s move on and learn the steps on how to use the TOCOL function in Excel.
How to Use the TOCOL Function in Excel
In this section, we will explain the step-by-step process of how to use the TOCOL function in Excel. Each step will contain detailed instructions and pictures to guide us along the way.
1. Firstly, we need to select the array we want to transform into a single column. In this case, our values are in A2:D5 . To begin, type an equal sign and the TOCOL function. So our entire formula would be “=TOCOL(A2:D5)”. Lastly, press the Enter key to return the rearranged values.
2. And tada! We have successfully used the TOCOL function in Excel.
3. Furthermore, let’s learn how to use the TOCOL function when our data set contains blank cells. Firstly, we need to select the array containing the values. Then, we will start the function by typing an equal sign and the function.
Since the array contains blank cells, we will use the ignore argument and input a 1 to ignore the blanks. So this time, our entire formula would be “=TOCOL(A2:D5,1)”. Lastly, press the Enter key to return the results.
4. And tada! We learned how to use the TOCOL function when an array contains blank cells.
5. Additionally, let’s say we want the values located in the first column to appear first in the rearranged column. To do this, we need to use the scan_by_column argument.
Firstly, we need to start the TOCOL function and select the array containing the values. Then, input true for the TOCOL function to scan the array by column instead of by rows. So our entire formula would be “=TOCOL(A2:D5,true)”Lastly, press the Enter key to return the results.
6. And tada! We have successfully used the TOCOL function to scan by column instead of by row.
And that’s pretty much it! We have discussed thoroughly how to use the TOCOL function in Excel. Also, we explained how to use the function in different situations, such as having blank cells or scanning by column. Now you can use the TOCOL function when you need to rearrange your data set.
Are you interested in learning more about what Excel can do? You can now use the TOCOL 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.
