Use Excel TOCOL Function Without Zeros from Blank Rows
Question details
The user wants to convert a 2D data range into a single vertical column using the TOCOL function, while ensuring that blank rows or empty cells do not result in unwanted zero values in the output.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing spreadsheet data templates or 2D ranges into a single column and needing to exclude empty cells or formula-generated zeros.
- Observed behavior
- By default, referencing blank rows or empty cells in data ranges results in unwanted zeros appearing in the final TOCOL array output.
Ensure you are using a modern version of your spreadsheet software (such as Microsoft 365 or WPS Office) that supports Dynamic Array functions like TOCOL and FILTER.
Ignore Blank Cells Using the TOCOL Ignore Argument
Excel's TOCOL function has a built-in argument specifically designed to skip truly blank cells automatically, preventing them from turning into zeros.
The TOCOL function takes multiple arguments. The second argument dictates what the function should ignore. By setting this argument to 1, you instruct the formula to skip blank cells entirely.
Click on the empty cell where you want the new single column of data to begin.
Type the formula =TOCOL(Data!Q2:AZ202, 1), replacing 'Data!Q2:AZ202' with your actual data range.
Press Enter to generate the array. The '1' in the formula tells the function to ignore any truly blank cells, keeping zeros out of your results.
Filter Out Zeros Generated by Formulas
If your 'blank' cells actually contain formulas returning zero or empty strings, the basic TOCOL function might still output zeros. You must combine it with the FILTER function.
Reorganize Data Effortlessly with WPS Office
WPS Spreadsheet provides full support for advanced dynamic array functions like TOCOL and FILTER, allowing you to manipulate and reorganize your data ranges quickly without unwanted blank cells or zeros.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your 2D data range.
- 2. Select your target cell: Click on an empty cell where the vertical column should start.
- 3. Apply the TOCOL formula: Enter =TOCOL(A2:F10, 1) to automatically transform the range and ignore empty cells.
- 4. Populate the data: Press Enter to instantly populate your dynamic data column without zeros.

Frequently Asked Questions
Why does TOCOL return zeros for blank cells by default?
When a spreadsheet function references an empty cell within a defined data range, it evaluates that empty value as a zero. Setting the second argument of TOCOL to 1 overrides this default behavior and forces it to skip these empty cells entirely.
What is the second argument in the TOCOL function?
The second argument is the 'ignore' parameter. Using 0 (or omitting it) keeps all values, 1 ignores blank cells, 2 ignores errors, and 3 ignores both blanks and errors.
Can I use TOCOL to ignore both blanks and errors simultaneously?
Yes, you can set the second argument of the TOCOL function to 3 (e.g., =TOCOL(A2:C10, 3)), which will safely remove both completely blank cells and any cell containing an error from your final output array.
Why didn't TOCOL(range, 1) remove the zeros in my output?
If your cells are not truly blank (for instance, if they contain a formula that evaluates to zero or an empty string), TOCOL will not consider them blank. In this case, wrapping the function in a FILTER function, such as =FILTER(TOCOL(range), TOCOL(range)<>0), is required to exclude those zeros.




