Flatten an Excel Range into One Column
The TOCOL function converts a multi-column Excel range into a single vertical column. For example, =TOCOL(A1:C4) stacks the values in row order, while optional arguments let you ignore blanks or scan by column. If your Excel version does not support TOCOL, the VBA alternative below provides another approach.
Use =TOCOL(A1:C4,1) to omit empty cells or =TOCOL(A1:C4,3,TRUE) to ignore blanks and errors while scanning by column. Results spill into adjacent cells, so clear the output area before entering the formula. See the VBA alternative if your Excel version lacks TOCOL.
What is the TOCOL Function in Excel?
TOCOL is an Excel function introduced in Microsoft 365 and Excel 2024 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.
3 – Ignore blanks and errors.
- scan_by_column(Optional) – How the function reads the data:
- FALSE (Default) – Reads row by row.
- TRUE – Reads column by column.
Example Usage:
For values in A1:C3, the following formula stacks rows into one column and skips blank cells:
=TOCOL(A1:C3,1,FALSE)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 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 i = 1 To rowCount ' TOCOL default: scan rows first
For j = 1 To colCount
tempArray(index, 1) = rng.Cells(i, j).Value
index = index + 1
Next j
Next i
' Return the result as a vertical array
TOCOL_VBA = tempArray
End FunctionHow 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)
- In dynamic-array versions of desktop Excel this may spill results; in legacy Excel, select an output range with one cell per input cell and confirm as an array formula with Ctrl+Shift+Enter. Save the workbook as
.xlsmand enable macros only from trusted sources. This simple VBA function does not implement TOCOL’s ignore options, and is limited to a single contiguous range. It returns the same row-first ordering as TOCOL, stacking the values into a single column.
Real-World Applications of TOCOL
- Cleaning Data – Use the
ignoreargument to omit blank cells and/or error values; TOCOL does not trim whitespace within text. - 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 in Excel for Microsoft 365 and Excel 2024, including supported desktop and web editions. It is not available in perpetual Excel 2021, 2019, 2016, or earlier.
Can I use TOCOL in Google Sheets?
Google Sheets also supports TOCOL. Its older FLATTEN function is another option, but does not automatically ignore blanks; check the ordering and blank-handling you need.
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?
Yes. TOCOL accepts an array returned by another dynamic-array formula, such as =TOCOL(FILTER(A1:C10,A1:A10<>"")). Here FILTER selects rows based on column A before TOCOL stacks the resulting cells; it does not automatically remove blanks in other columns. Add 1 as TOCOL’s second argument if you also want to ignore blank cells.
Is there a way to get TOCOL in older versions without VBA?
In older Excel versions, consider Power Query (where available), manual formulas, or VBA. You cannot install the modern TOCOL worksheet function as a native feature in Excel 2021 or earlier.
Conclusion
Use TOCOL for a dynamic, automatically updating result in supported Excel versions. In older desktop editions, the sample VBA function offers row-first flattening, but unlike TOCOL it does not ignore blanks or errors and requires macro support. Validate the resulting order and output length before using it for imports or reports.
For reusable reports, keep the original source range intact and place the flattened result in a separate area so it can expand without overwriting data.
Accuracy and version notes
For a 2×2 range A1:B2 containing 1, 2 in the first row and 3, 4 in the second, =TOCOL(A1:B2) returns 1, 2, 3, 4, whereas =TOCOL(A1:B2,,TRUE) returns 1, 3, 2, 4. Blank cells may appear as zero unless ignored. The VBA function shown above scans rows first to match TOCOL’s default. It does not implement native TOCOL’s ignore or scan-by-column options. VBA user-defined functions require desktop Excel with macros enabled; Excel for the web cannot execute them.
Worked comparison: row order, column order, and blank cells
Enter A1=Red, B1=Blue, A2=Green, B2=Yellow. =TOCOL(A1:B2) returns Red, Blue, Green, Yellow; =TOCOL(A1:B2,0,TRUE) returns Red, Green, Blue, Yellow. If B1 is truly empty, =TOCOL(A1:B2,1) omits it; by contrast the default result may show zero for an empty source cell. A formula returning "" is not necessarily treated like a truly empty source cell, so check its behavior in your workbook before promising blank removal.
Data quality and compatibility checklist
- Compare the first and last five output values with the original grid before importing the flattened data elsewhere.
- Do not select
ignore=2orignore=3simply to hide source errors: investigate whether the errors represent missing or invalid records. - Use a bounded source range and leave enough empty spill cells; the result expands when source values change.
- For a VBA user-defined function, keep the workbook in a trusted location, save as
.xlsm, and note that Excel for the web cannot run VBA. - Test with empty cells, numeric zero, text, error values and a two-by-two grid before relying on the result.
For an independent function reference, see Microsoft’s TOCOL documentation. For related cleanup, read counting nonblank Excel cells.
Troubleshooting TOCOL and the VBA alternative
#SPILL!: Clear obstructing cells below the formula and avoid entering a spilling formula inside an Excel Table. #NAME?: Check that your Excel edition supports TOCOL and that the function name is spelled correctly. #CALC! or other errors: Inspect the source values and choose the appropriate ignore option rather than silently discarding important errors.
VBA limitations: The supplied function returns every source cell in row-first order, including blanks and errors, and it does not support the native function’s optional arguments. It must be stored in a macro-enabled workbook and used in desktop Excel. Test on a small range before applying it to a production report.

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.
