Flattening Excel Table/Ranges into one Column (+ VBA for Older Versions!)

Table of Contents

Introduction

Transform Your Data Instantly: Meet the TOCOL Function in Excel!

Have you ever struggled with rearranging your data from a multi-column format into a single column? Manually copying and pasting is tedious, and complex formulas can be frustrating. Fortunately, Excel’s TOCOL function solves this problem effortlessly.

Whether you’re dealing with reports, datasets, or tables, TOCOL converts any range into a vertical list with just one formula. However, since it’s a new function exclusive to Excel 365, users of older versions need a workaround—VBA to the rescue!

Let’s dive into how TOCOL works, its real-world applications, and how to achieve the same result using VBA if you’re using an older version of Excel.

What is the TOCOL Function in Excel?

TOCOL is an Excel function introduced in Excel 365 and Excel 2021 that converts a range of data into a single column. It eliminates blank cells (if needed) and keeps data structured efficiently.

Syntax:

TOCOL(array, [ignore], [scan_by_column])

Arguments:

array (Required) – The range or array to convert into a column.
ignore (Optional) – Determines which values to exclude:
- 0 (Default) – Keep everything.
- 1 – Ignore blanks.
- 2 – Ignore errors.
scan_by_column(Optional) – How the function reads the data:
- FALSE (Default) – Reads row by row.
- TRUE – Reads column by column.

Example Usage:

If you have data in A1:C3 and want to convert it into a column

=TOCOL(A1:C3,,TRUE)

VBA Alternative for Older Excel Versions

Since TOCOL is not available in Excel 2021, 2019, 2016, or earlier, you can achieve the same result using VBA (Visual Basic for Applications).

VBA Code to Convert a Range into a Column:

Function TOCOL_VBA(rng As Range) As Variant
    Dim cell As Range
    Dim tempArray() As Variant
    Dim rowCount As Long, colCount As Long
    Dim i As Long, j As Long, index As Long
    
    ' Get number of rows and columns in the input range
    rowCount = rng.Rows.Count
    colCount = rng.Columns.Count
    
    ' Initialize the output array with the total number of elements
    ReDim tempArray(1 To rowCount * colCount, 1 To 1)
    
    ' Convert the range into a single column array
    index = 1
    For j = 1 To colCount  ' Iterate over columns first (like TOCOL)
        For i = 1 To rowCount
            tempArray(index, 1) = rng.Cells(i, j).Value
            index = index + 1
        Next i
    Next j
    
    ' Return the result as a vertical array
    TOCOL_VBA = tempArray
End Function

How to Use

Open Excel and press ALT + F11 to open the VBA Editor.
Click Insert > Module and paste the above code.
Use the function in Excel, simply type = TOCOL_VBA(A1:C3)
This will return the same result as TOCOL, stacking the values into a single column.

Real-World Applications of TOCOL

Cleaning Data – Remove blank spaces and errors efficiently.
Preparing Reports – Convert tables into column format for easy analysis.
Database Imports – Some software requires columnar data input.
Inventory Lists – Flatten multi-row/column product lists into a single list.

FAQ: TOCOL Function in Excel

What Excel versions support TOCOL?

TOCOL is available only in Excel 365. It’s not available in Excel 2021, 2019, 2016, or earlier.

Can I use TOCOL in Google Sheets?

No, TOCOL is an Excel-exclusive function. However, Google Sheets offers an alternative using FLATTEN().

How do I remove blanks when using TOCOL?

Use 1 as the second argument. For example,

=TOCOL(A1:C3, 1)

This will remove blank cells from the output.

Can I use TOCOL with arrays generated by other formulas?

Absolutely! TOCOL is very versatile in that it can ingest an array produced by other Excel formulas.

Is there a way to get TOCOL in older versions without VBA?

No, TOCOL is a built-in function exclusive to Excel 365. The only workaround is VBA or manual formulas

Conclusion

The TOCOL function is a game-changer for transforming data in Excel 365. If you’re using an older version, VBA provides an effective alternative. Now you can streamline your workflow and organize data effortlessly!

If you found this guide helpful, share it with fellow Excel users! 🚀

C P Gupta

C P Gupta is a YouTuber and Blogger. He is expert in Microsoft Word, Excel and PowerPoint. His YouTube channel @pickupbrain is very popular and has crossed 9.9 Million Views.