WPS Spreadsheet: Copy Zero-Quantity Inventory Rows to Another Sheet
Question details
Display every inventory row whose quantity is zero on a separate worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The inventory list contains a quantity column, and the user wants a separate out-of-stock view that updates from the source data.
- Observed behavior
- The matching zero-quantity rows need to appear automatically on another worksheet instead of being copied manually.
Confirm which column contains the quantity values and identify the source range or table name. On the destination sheet, select an empty area with enough space for the filtered rows and columns to spill.
Use FILTER with an inventory table to show zero-quantity rows
Use this method when the inventory is formatted as a table named Stock and you want the results to update as the table changes.
FILTER returns every matching row and spills the results into adjacent cells on the destination worksheet. The formula below returns the Description-through-Volume columns for records where Qty equals zero.
In WPS Spreadsheet, click the worksheet where the out-of-stock list should appear, then select the top-left output cell.
Type =FILTER(Stock[[Description]:[Volume]],Stock[Qty]=0,"No out-of-stock items") and press Enter.
Allow the formula to populate the cells below and to the right of the formula cell. The output updates when values in the Stock table change.
Use FILTER with a normal cell range
Use this method when the inventory is not formatted as a table and the source data occupies a fixed range.
Create a live zero-quantity inventory list in WPS Spreadsheet
WPS Spreadsheet lets you use the FILTER function to pull zero-quantity inventory rows into another worksheet. The results spill automatically and can be saved and shared in commonly used Excel-compatible formats.
- 1. Open the inventory workbook: Launch WPS Office, open the workbook in WPS Spreadsheet, and select the worksheet containing the stock data.
- 2. Prepare the destination sheet: Add or open the worksheet for out-of-stock items and click an empty top-left output cell.
- 3. Insert the FILTER formula: Enter a table-based formula such as =FILTER(Stock[[Description]:[Volume]],Stock[Qty]=0,"No out-of-stock items") or use a matching cell-range formula.
- 4. Check and save the results: Confirm that the zero-quantity rows spill into the destination sheet, then save the workbook in .xlsx format if it will be opened in Microsoft Excel.
Frequently Asked Questions
Can FILTER return complete inventory rows instead of only the quantity cells?
Yes. Set the first FILTER argument to the full row range, such as A1:C13, or to the table columns you want returned, such as Stock[[Description]:[Volume]].
Where should I enter the formula to copy results to another sheet?
Open the destination worksheet, click the cell where the list should begin, and enter the FILTER formula there. WPS Spreadsheet spills the matching rows into the surrounding empty cells.
What if there are no inventory items with a quantity of zero?
Add a third argument to FILTER, such as "No out-of-stock items". WPS Spreadsheet displays that message when no rows meet the zero-quantity condition.
Why are some filtered rows missing from the result?
Check that the quantity range and return range contain the same number of rows. Also verify that zero values are stored as numeric 0 rather than text, and expand a fixed range if new records were added outside it.
