Table of Contents
The TOCOL function is a useful tool in Google Sheets that allows users to convert a reference to a specific column into its corresponding column number. This function can be used to simplify data manipulation and analysis, as it eliminates the need for manually counting and inputting column numbers. To use the TOCOL function, simply enter the reference of the desired column as the input and the function will return the corresponding column number. This can greatly enhance the efficiency and accuracy of data management in Google Sheets.
TOCOL function
This function transforms an array or range of cells into a single column. TOCOL can scan values:
- By column, top to bottom
- By row, left to right
The scan_by_column argument is a boolean value that controls how TOCOL reads values from the source array.
Sample Usage
TOCOL(A1:C3): A TOCOL function that keeps all values and scans by row.
TOCOL(A1:C3, 1, TRUE): A TOCOL function that ignores blanks and scans by column.
Syntax
TOCOL(array_or_range, [ignore], [scan_by_column])
array_or_range: The array or range of cells to return as a column.[ignore]: By default, no values are ignored. Specify one of these values:- 0: Keep all values
- 1: Ignore blanks
- 2: Ignore errors
- 3: Ignore blanks and errors
[scan_by_column]: The boolean value ofscan_by_columndetermines how the array is scanned. By default, theTOCOLfunction scans the array by row.- True: Scans the array by column
- False: Scans the array by row
Examples
Use simple data transformation operation with TOCOL
Example data:
A | B | C | |
|---|---|---|---|
1 | Ben | Peter | Mary |
2 | John | Hillary | Jenny |
3 | Agnes | Harry | Felicity |
Example: Input this formula in E1: =TOCOL(A1:C3)
Result:
E | |
|---|---|
1 | Ben |
2 | Peter |
3 | Mary |
4 | John |
5 | Hillary |
6 | Jenny |
7 | Agnes |
| 8 | Harry |
| 9 | Felicity |
Make a Copy
Ignore blanks with TOCOL
Example data:
A | B | C | |
|---|---|---|---|
1 | Ben | Peter | Mary |
2 | John |
| Jenny |
3 | Agnes | Harry | Felicity |
Example:Input this formula in E1: =TOCOL(A1:C3, 1)
Result:
E | |
|---|---|
1 | Ben |
2 | Peter |
3 | Mary |
4 | John |
5 | Jenny |
6 | Agnes |
| 7 | Harry |
| 8 | Felicity |
Make a Copy
Scan by column with TOCOL
Example data:
A | B | C | |
|---|---|---|---|
1 | Ben | Peter | Mary |
2 | John | Hillary | Jenny |
3 | Agnes | Harry | Felicity |
Example: Input this formula in E1: =TOCOL(A1:C3, 0, TRUE)
Result:
E | |
|---|---|
1 | Ben |
2 | John |
3 | Agnes |
4 | Peter |
5 | Hillary |
6 | Harry |
7 | Mary |
| 8 | Jenny |
| 9 | Felicity |
Make a Copy
Related functions
- TOROW function: This function transforms an array or range of cells into a single row.
Cite this article
stats writer (2024). How can I use the TOCOL function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-tocol-function-in-google-sheets/
stats writer. "How can I use the TOCOL function in Google Sheets?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-tocol-function-in-google-sheets/.
stats writer. "How can I use the TOCOL function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-tocol-function-in-google-sheets/.
stats writer (2024) 'How can I use the TOCOL function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-tocol-function-in-google-sheets/.
[1] stats writer, "How can I use the TOCOL function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the TOCOL function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.