Formula Errors

How to Remove Zeros from VSTACK, SORT, and FILTER Results in Excel

Olivia Miller Sep 27, 2026 871 views

Question details

Combine multiple tables dynamically while filtering out blank rows, sorting the results, and ensuring blank cells do not appear as zeros.

How to Remove Zeros from VSTACK, SORT, and FILTER Results in Excel

Product: Excel
Device & OS: not provided
Scenario: Combining and sorting multiple tables using dynamic array formulas.
Observed behavior: Blank cells in the source data display as unwanted zeros in the stacked formula results.

Before you start

Ensure you are using a modern version of Excel or WPS Office that supports dynamic array functions such as VSTACK, FILTER, SORT, and LET.

Solution 1Recommended

Use the LET Function to Evaluate and Replace Zeros

Wrap your VSTACK, FILTER, and SORT functions inside a LET function to evaluate the array and replace any zero-value empty cells with blank text.

When Excel references a blank cell in a dynamic array, it automatically evaluates it as a 0. By assigning your complex VSTACK and FILTER formula to a variable using the LET function, you can use a simple IF statement to replace those zeros with empty text.

Combine and filter the tables

Use VSTACK to combine your tables (e.g., tblaco, tblbco, tblcco) and nest it inside a FILTER function to exclude rows where Column 1 is empty.

Assign the array to a variable

Start your formula with the LET function and name your array variable 'v'. Example: =LET(v, FILTER(VSTACK(tblaco, tblbco), VSTACK(tblaco[Column1], tblbco[Column1])<>"").

Apply the IF and SORT conditions

Add the IF condition to check if 'v' evaluates to an empty string. If it does, return an empty string (""); otherwise, return 'v'. Wrap this in the SORT function: SORT(IF(v="","",v),1,1).

Execute the full formula

Input the complete formula into your target cell: =LET(v,FILTER(VSTACK(tblaco,tblbco,tblcco,tbldco,tblhhc),VSTACK(tblaco[Column1],tblbco[Column1],tblcco[Column1],tbldco[Column1],tblhhc[Column1])<>""),SORT(IF(v="","",v),1,1)) and press Enter.

Use the LET Function to Evaluate and Replace Zeros

Performance Optimization: Using the LET function significantly improves formula performance because the spreadsheet calculates the complex VSTACK and FILTER arrays only once, rather than evaluating them twice for the IF condition.

Solution 2

Hide Zeros Using Custom Number Formatting

If you prefer not to modify your complex formula, you can visually hide zeros in the spilled result range using custom cell formatting.

Efficient Spreadsheet Management

Master Dynamic Arrays with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like VSTACK, FILTER, SORT, and LET, allowing you to combine, clean, and analyze data flawlessly without unwanted zero values.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple data tables.
2. Input the LET formula: Select the destination cell and type your optimized =LET(...) formula to combine the tables.
3. Generate the results: Press Enter to automatically spill the filtered, sorted, and zero-free results across your worksheet.

Seamless compatibility with Microsoft Excel formats (.xlsx)Full native support for advanced dynamic array formulasLightweight application with high processing performanceIntuitive interface for complex data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why do blank cells appear as zeros in VSTACK results?

When Excel or WPS Spreadsheet references an empty cell in a formula or an array manipulation like VSTACK, it evaluates the lack of data as a numerical 0. Using an IF function helps intercept this behavior and display empty text instead.

What is the purpose of the LET function in this formula?

The LET function allows you to assign a name (such as 'v') to a specific calculation result within the formula. This prevents the spreadsheet from having to calculate the resource-heavy VSTACK and FILTER functions multiple times when checking for blank cells.

Are VSTACK and FILTER available in older versions of Excel?

No, dynamic array functions like VSTACK, FILTER, and LET are only available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you open a workbook containing these functions in an older version, it will return a #NAME? error.

Excel & Data Problems