Excel’s TOCOL function solves a common data reshaping challenge. It converts data from multiple rows and columns into a single, organized column. This function, available in Excel 365 and Excel 2025, helps you prepare data for analysis without complex formulas or manual work.
How to Convert Multiple Rows and Columns into a Single Column
Let’s start with the basics. The TOCOL function takes scattered data and arranges it vertically in one column. Here’s how it works.
Basic TOCOL Formula Steps
- Open Your Spreadsheet Select the cell where you want your results to appear. Make sure you have enough empty cells below for the output.
- Enter the Formula Type the basic TOCOL syntax:
Copy
=TOCOL(range)
Where ‘range’ is your source data area. - Select Data Range Click and drag to select your source data, or type the cell references directly.
Example:
Original Data | ||
---|---|---|
A1 |
B1 |
C1 |
A2 |
B2 |
C2 |
Formula: =TOCOL(A1:C2)
Result:
Output |
---|
A1 |
B1 |
C1 |
A2 |
B2 |
C2 |

Scanning Data by Column
TOCOL reads data horizontally by default. But what if you want to read vertically first? Here’s how:
- Add the Scan Parameter Modify your formula:
Copy
=TOCOL(range,,TRUE) - Watch the Order Change The data now reads down each column before moving to the next.
Example with vertical scanning:
Original | |
---|---|
1 |
4 |
2 |
5 |
3 |
6 |
Formula: =TOCOL(A1:B3,,TRUE)
Result:
Output |
---|
1 |
2 |
3 |
4 |
5 ![]()
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule. Get Started![]() |
6 |

Working with TOCOL’s Ignore Parameter
The ignore parameter helps you handle unwanted data in your range. You can skip empty cells, errors, or specific values.
Filtering Out Blank Cells and Errors
- Skip Empty Cells
Copy
=TOCOL(range, “”) - Ignore Errors
Copy
=TOCOL(range, “#N/A”)
Example with ignored values:
Data | |
---|---|
A | |
B |
C |
#N/A |
D |
Formula: =TOCOL(A1:B3, “”)
Result:
Clean Output |
---|
A |
B |
C |
D |
Real-World TOCOL Applications
TOCOL shines in practical scenarios. Here are some common uses:
- Sales Data Organization Convert regional sales tables into a single list for analysis.
- Customer Information Transform contact details from multiple sheets into one column.
- Product Catalogs Consolidate product information from various categories.
Combining TOCOL with Other Excel Functions
Mix TOCOL with other functions to enhance your data handling:
- Remove Duplicates
Copy
=UNIQUE(TOCOL(range)) - Sort Results
Copy
=SORT(TOCOL(range)) - Filter and Transform
Copy
=TOCOL(FILTER(range, criteria))
Next Steps
Start simple. Try TOCOL with small data sets first. Once comfortable, tackle larger projects. Practice with different parameters to understand their effects.
Need to sync your transformed data with other business systems? Try Coefficient. It connects your spreadsheets directly to your data sources, keeping everything current and accurate. Get started with Coefficient today and take your data management to the next level.