Function Problems

How to Create an Excel Dropdown Based on Stock Quantity

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to create a data validation dropdown list that dynamically hides items with zero inventory and only displays items with a stock quantity greater than zero.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Managing inventory or selecting food items where out-of-stock items should not be selectable.
Observed behavior: The goal is to show items like 'Steak' with positive stock while hiding items like 'Chops' with zero stock in the dropdown.

Before you start

Ensure your version of Excel or WPS Office supports dynamic array functions like FILTER, and organize your stock data into distinct columns for item names and quantities.

Solution 1Recommended

Use FILTER Function and Data Validation

Extract in-stock items using the FILTER function into a helper column, then reference the spilled range in Data Validation.

This method leverages dynamic arrays. By filtering out items with zero stock to a separate helper column, you can use the spilled range operator (#) to automatically update your dropdown list whenever stock quantities change.

Prepare the helper cell

Select a helper cell (e.g., D2 on your Stock sheet) where you want the filtered list to appear.

Enter the FILTER formula

Type =FILTER(Stock!A2:A100, Stock!B2:B100>0, "") and press Enter. This extracts items from column A where the corresponding quantity in column B is greater than zero.

Open Data Validation

Select the cell where you want the dropdown to appear. Go to the 'Data' tab and click on 'Data Validation'.

Set list source with spilled range

In the Data Validation dialog, choose 'List' under the Allow dropdown. In the Source field, enter =Stock!$D$2# and click OK. The '#' symbol ensures the dropdown includes all dynamically spilled results.

Dynamic Updates: Your dropdown list will now automatically update to show or hide items as their stock quantities change in column B.

Efficient Spreadsheet Data Management

Create Dynamic Dropdowns Easily in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like FILTER and advanced data validation, allowing you to build dynamic stock selection dropdowns with ease.

1. Open your inventory sheet: Launch WPS Spreadsheet and open your stock or inventory workbook.
2. Use the FILTER function: In a helper column, input the =FILTER() formula to extract items with stock > 0.
3. Apply Data Validation: Navigate to Data > Data Validation, choose List, and reference your helper cell using the '#' operator.

Full compatibility with Microsoft Excel formulas and functions like FILTER.Advanced Data Validation for creating precise, dynamic dropdown lists.Free and lightweight alternative for complex data management tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my dropdown showing a #SPILL! error?

A #SPILL! error occurs if there is existing data blocking the FILTER function from outputting its full list of results. Clear the cells below your helper formula to resolve this.

Can I hide zero-stock items without using a helper column?

Currently, Excel and WPS Spreadsheet Data Validation Source fields do not accept dynamic array formulas like FILTER directly. You must use a helper column to generate the spilled range first before referencing it.

What does the '#' symbol mean in the validation source?

The '#' symbol is the spilled range operator. It tells the software to reference not just the specific cell, but the entire dynamic array of results generated by the formula in that cell.

Excel & Data Problems