Excel TOCOL Function: Transform Multiple Rows to Single Column (2025 Guide)

Published: January 21, 2025 - 3 min read

Vijay Srinivas

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

  1. Open Your Spreadsheet Select the cell where you want your results to appear. Make sure you have enough empty cells below for the output.
  2. Enter the Formula Type the basic TOCOL syntax:
    Copy
    =TOCOL(range)
    Where ‘range’ is your source data area.
  3. 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:

  1. Add the Scan Parameter Modify your formula:
    Copy
    =TOCOL(range,,TRUE)
  2. 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

Coefficient Excel Google Sheets Connectors
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

  1. Skip Empty Cells
    Copy
    =TOCOL(range, “”)
  2. 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:

  1. Sales Data Organization Convert regional sales tables into a single list for analysis.
  2. Customer Information Transform contact details from multiple sheets into one column.
  3. Product Catalogs Consolidate product information from various categories.

Combining TOCOL with Other Excel Functions

Mix TOCOL with other functions to enhance your data handling:

  1. Remove Duplicates
    Copy
    =UNIQUE(TOCOL(range))
  2. Sort Results
    Copy
    =SORT(TOCOL(range))
  3. 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.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 500,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Vijay Srinivas GTM @ Coefficient
Vijay Srinivas is an engineer turned marketer who loves to dabble in data and has 6 years of experience in GTM for Startups and SaaS orgs. Building his skills currently to be a PLG & spreadsheet expert.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies