Excel TOCOL function – To arrange Excel data from a table into a single column, you would typically use common Excel copy-and-paste keyboard shortcuts. However, the TOCOL function lets you easily arrange Excel data in a single column.
What Is the Excel TOCOL Function?
When using Excel at work, you may need to convert multiple values into a single column. In that case, we usually use Excel keyboard shortcuts. For that reason, you may not be familiar with the TOCOL function.
Let’s look at the function for converting multiple Excel columns into a single column.
Syntax
The Excel TOCOL function syntax is as follows.
=TOCOL(ARRAY,[ignore],[scan_by_column])
- ARRAY: Enter the data range.
- Ignore: When arranging data in a single column, choose whether to include zeros or blank cells.
- Scan_by_column: Choose whether to scan the data by row or by column.
Excel TOCOL Function Examples
Let’s look at several examples of using the TOCOL function to arrange data in a single column.
First, there is table-formatted data in columns B through E, as shown below.
TOCOL Function Example (Column Direction)
Use absolute references to lock the range of data you want.
Use the following formula to arrange the data in the specified range into a single column.
G3 cell =TOCOL($B$3:$E$5,0,1)
TOCOL Function Example (Row Direction)
Use the following formula to retrieve the Excel data in row order.
G3 cell =TOCOL($B$3:$E$5,0,0)
TOCOL Function Example (Blank Cells or Zero Values)
If the data you want to arrange in a single column contains blank cells or zero values, use the following formula to arrange only the values you want.
G3 cell =TOCOL($B$3:$E$5,1,1)
Conclusion
Converting Excel data is the first step in obtaining the values you need. There are many ways to arrange Excel data, but you should be able to choose the appropriate method based on the amount of data.