Function Problems

Use Excel TOCOL Function Without Zeros from Blank Rows

Adam DavisAdam Davis Oct 12, 2026 872 views

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.

How to Use Excel TOCOL Without Zeros from Blank Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Select destination cell

Click on the empty cell where you want the new single column of data to begin.

2
Enter the TOCOL formula

Type the formula =TOCOL(Data!Q2:AZ202, 1), replacing 'Data!Q2:AZ202' with your actual data range.

3
Apply the function

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.

Quick Tip: If you also want to ignore cells with errors, you can change the second argument from 1 to 3.
Advanced Spreadsheet Functions

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. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your 2D data range.
  2. 2. Select your target cell: Click on an empty cell where the vertical column should start.
  3. 3. Apply the TOCOL formula: Enter =TOCOL(A2:F10, 1) to automatically transform the range and ignore empty cells.
  4. 4. Populate the data: Press Enter to instantly populate your dynamic data column without zeros.
Fully compatible with Microsoft Excel formulas and dynamic arrays.Clean, intuitive interface for managing large datasets easily.Free, lightweight alternative with seamless file format support for .xlsx.Includes a rich library of pre-built templates and advanced data tools.
microsoft office alternative - wps office

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.