What is TOCOL in Google Sheets?
The TOCOL function in Google Sheets is used to convert a range or array into a single column by stacking its values vertically. In simple terms, it flattens your data — taking all values from a horizontal or tabular layout and placing them into one long column. This function is especially useful when you want to combine data from multiple columns into a single list. It can also be used to prepare data for lookup, filtering, or charting. The function transforms wide tables into a single column format without manually copying and pasting.
Unlike traditional formulas that only process one cell or row at a time, TOCOL can handle entire arrays dynamically. For instance, suppose you have the following dataset in A1:C3. If you want to turn this entire range into a single column, you can use:
=TOCOL(A1:C3)
This output shows how TOCOL stacks the range column by column into one continuous list.
Key Takeaways
- TOCOL in Google Sheets converts a multi-row and multi-column range into a single column of data.
- The syntax of the function is: =TOCOL(array_or_range, [ignore], [scan_by_column])
where ignore removes blanks or errors and scan_by_column defines the stacking direction.
- You can easily combine TOCOL with functions like UNIQUE, SORT, FILTER, and ARRAYFORMULA to clean, sort, or summarize large datasets.
- TOCOL is particularly useful for data consolidation, reporting, and dashboard creation, helping you manage and analyze scattered data efficiently in one place.
Syntax
The syntax for the TOCOL function in Google Sheets is:
=TOCOL(array_or_range, [ignore], [scan_by_column])
Parameters are as follows
- array_or_range → The input data range or array you want to convert into a single column.
- ignore (optional) → Determines which values to exclude.
- 0 → Include all values (default).
- 1 → Ignore blanks.
- 2 → Ignore errors.
- 3 → Ignore blanks and errors.
- scan_by_column (optional) → Defines the direction of stacking.
- TRUE → Stack by column (default).
- FALSE → Stack by row.
How To Use TOCOL Function in Google Sheets
To see how and where the TOCOL function can be applied, we can apply it in an example. it’s commonly used in tasks like converting sales data, combining lists from multiple departments, or preparing a dataset for pivot tables or charts.
Let’s go step by step to understand how to use it effectively.
Entering TOCOL Manually
Step 1: Prepare the data and enter it in a Google Sheet. This table represents a small set of values spread across three columns.
Step 2: Click on an empty cell where you want the flattened list to appear. Here, we choose E1. Type the TOCOL formula as follows:
=TOCOL(A1:C4)
Here’s what each part means:
A1:C4 → the data range you’re converting.
Step 4: Press Enter. Google Sheets will instantly display the combined column list.
Additionally, if your range includes blanks or errors, you can modify the formula to skip them as follows:
=TOCOL(A1:C4, 1)
Examples
Below are three real-world TOCOL examples. Each example gives us insights into the usefulness of TOCOL in Google Sheets.
Example #1
In this example, a business records weekly sales for three different products across multiple weeks. They want to compile all sales values into a single column for analysis, reporting, or filtering purposes. Instead of manually copying and pasting data from multiple columns, the TOCOL function can automate this process with a single formula.
Step 1: Input the data as shown below in a new Google Sheet. Each column represents a product, and each row represents weekly sales.
Step 2: Use the TOCOL formula to combine all these sales values into a single vertical list.
Enter the following formula in an empty cell.
=TOCOL(B2:D5)
B2:D5 is the range of sales data you want to flatten.
The TOCOL function reads the range column by column and stacks all the values vertically into one single list.
Step 3: Press Enter. Google Sheets will instantly generate a single column list containing all sales values from columns B, C, and D, stacked one after another. This result can be easily used to create charts, filter data, or apply summary functions like AVERAGE to the entire dataset.
The TOCOL function helps you quickly transform multi-column data into a single column format, saving time and reducing errors. It’s especially useful for combining datasets, preparing for pivot tables, or consolidating sales values for company-wide analysis.
Example #2 – Removing Blank Cells from a Dataset
In this example, consider a company that records customer feedback IDs from different regions. Each region maintains its own list of IDs in separate columns. However, since not all customers provide feedback, some cells are blank. The goal is to create one continuous list of all feedback IDs, excluding the blanks. The TOCOL function can help automate this process quickly.
Step 1: Enter the data in your Google Sheet as shown below.
Step 2: Use the TOCOL in Google Sheets formula to flatten the entire range into one column and remove blanks. Click on an empty cell and type the following formula:
=TOCOL(A2:C5, 1)
Explanation:
- A2:C5 is the range containing all customer feedback IDs.
- The second argument 1 tells the TOCOL function to ignore blank cells while stacking the data.
- This ensures the final column only contains valid feedback IDs, skipping all empty rows automatically.
Step 3: Press Enter. Google Sheets will instantly display a single vertical list of all feedback IDs from the three regions, without any blank spaces. You now have a consolidated list that’s easy to analyze or export.
Using the TOCOL function with the ignore parameter is a simple and effective way to clean data.
Example #3 – Combining TOCOL with UNIQUE Function
In this example, a company maintains a list of product categories sold in different regions. Each region keeps its own record, and there are several overlaps among categories. The management team now wants to generate a single, unique list of all product categories sold across all regions, with duplicates removed.
The TOCOL function can flatten the data into one column, and when combined with UNIQUE, it filters out repeated entries instantly.
Step 1: Enter the data in your Google Sheet as shown below.
Step 2: Use the TOCOL function to flatten the data range and then combine it with the UNIQUE function to remove duplicates.
Click on an empty cell (for example, E2) and type the following formula:
=UNIQUE(TOCOL(A2:C5, 1))
Explanation:
- A2:C5 is the entire dataset containing categories from all regions.
- The TOCOL function converts all category names into a single column, stacking them one below another.
- The argument 1 ensures that any blank cells are ignored during flattening.
The UNIQUE function then filters this single column, keeping only one instance of each category and removing duplicates automatically.
Step 3: Press Enter. Google Sheets will display a single vertical list of all unique product categories across the three regions. This list updates automatically whenever you add new categories or modify existing ones in the original table.
Combining TOCOL with UNIQUE is a powerful way to consolidate and clean up data across multiple columns.
Important Things to Note
- If you provide an invalid range or incorrect arguments in the TOCOL formula, Google Sheets will return a #VALUE! error.
- The output of the TOCOL function is a vertical array containing all values from the specified range, stacked into a single column. It automatically spills down across cells below the formula cell.
- When you set the optional argument ignore to 1 or 3, TOCOL will skip blank or erroneous cells, ensuring a clean, compact list.
- The scan_by_column argument defines whether the function stacks by column (default) or by row. Setting it to FALSE will stack data row by row instead.
- TOCOL is extremely useful for preparing data for summaries, pivot tables, or filters, as it eliminates the need to manually copy and paste columns into one list.
Frequently Asked Questions (FAQs)
Both TOCOL and TOROW are functions that transform ranges into a single line of data. The difference lies in their output orientation:
1. TOCOL converts data into a single column, stacking values vertically.
2. TOROW converts data into a single row, stacking values horizontally.
For example, =TOCOL(A1:C3) will return a vertical list, while =TOROW(A1:C3) will produce a horizontal sequence of values. Use TOCOL when you need a column-based list (for filtering or sorting) and TOROW when you need a row-based layout (for presentation or formulas like JOIN).
TOCOL works seamlessly with functions like UNIQUE, SORT, and FILTER.
When we add SORT to UNIQUE and TOCOL, we can organize that list alphabetically.
=SORT(UNIQUE(TOCOL(A1:C10, 1)))
This combination is especially useful for creating dropdown lists, summaries, or unique category lists across multiple columns.
These optional arguments help one control how the data is processed. The ignore argument removes unwanted blanks or errors, keeping the list clean.
The scan_by_column argument controls the direction of stacking:
• TRUE (default) – stacks values column by column.
• FALSE – stacks values row by row.
For example, =TOCOL(A1:C3, 0, FALSE) will flatten the range row by row instead of column by column.
Download Template
This article must help understand the TOCOL Function in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What Is TOCOL Function In Google Sheets. We explain how to use the TOCOL Function In Google Sheets with examples and points to remember. You can learn more from the following articles. –
Leave a Reply