Document Editing Problems

How to Stop Excel Online Data Validation Options from Disappearing

Jason Miller Oct 13, 2026 872 views

Question details

Users need to prevent items in a data validation dropdown list from disappearing after being selected, allowing for repeat selections.

How to Stop Excel Online Data Validation Options from Disappearing

Product: Excel for Microsoft 365 online
Device & OS: not provided
Scenario: Using data validation dropdown lists for data entry in a shared or online spreadsheet.
Observed behavior: Dropdown options are automatically removed from the list immediately after a user selects them, preventing the same option from being chosen again.

Before you start

Ensure you have full editing permissions for the workbook and verify whether the dropdown list is pulling from a fixed range of cells or a dynamic formula.

Solution 1Recommended

Check and Adjust Dynamic Validation Sources

Determine if the dropdown source relies on a dynamic formula, table, or script that intentionally hides selected values to prevent duplicates.

Standard data validation in Excel normally leaves the source list unchanged. If options are disappearing, the list is likely referencing a dynamic array formula (like FILTER), a Power Query output, or is being modified by an Office Script.

Select the affected cell

Click on the cell containing the dropdown list where the options are disappearing.

Open Data Validation settings

Navigate to the 'Data' tab on the ribbon and click on 'Data Validation'.

Inspect the Source field

Look at the 'Source' box under the Settings tab. Check if it points to a complex formula, a dynamic spill range (e.g., =$A$1#), or a named range linked to a macro.

Change to a static range

To stop options from disappearing, replace the dynamic reference with a standard, fixed range of cells (e.g., =$F$1:$F$10) that contains your desired list options.

Check and Adjust Dynamic Validation Sources

Background Automation: If the source appears to be a normal static range but options still disappear, the workbook might be running an Office Script or VBA macro in the background that actively deletes used values.

Solution 2

Test with a Fixed Comma-Separated List

Isolate the issue by testing data validation with a static, manually typed list to rule out workbook corruption or background automation.

Use WPS Spreadsheet

Create Stable Dropdown Lists Easily in WPS Spreadsheet

WPS Spreadsheet provides a highly stable and intuitive environment for creating standard data validation dropdowns. It effortlessly handles fixed lists without the unexpected behavior caused by complex background scripts, ensuring your data entry process remains smooth.

1. Open your file in WPS: Launch WPS Spreadsheet and open your existing workbook.
2. Select the target cells: Highlight the cells where you want to apply the dropdown list.
3. Navigate to Data Validation: Click on the 'Data' tab in the top ribbon and select 'Validation'.
4. Configure the list: Choose 'List' under the Allow settings and input your static cell range or comma-separated values in the Source box.
5. Apply and test: Click 'OK'. Your dropdown will now reliably display all options without removing them after selection.

Fully compatible with Microsoft Excel (.xlsx) data validation formatsClear, easy-to-navigate Data Validation managerFree and lightweight spreadsheet tool for reliable offline and online work

microsoft office alternative - wps office

Frequently Asked Questions

Why do standard data validation options disappear after I select them?

Standard Excel data validation does not remove options by default. If they disappear, the dropdown's source is likely linked to a dynamic array formula (such as FILTER or UNIQUE), a custom Power Query, or an active script intentionally designed to prevent duplicate entries.

Can I create a dropdown list that allows multiple selections of the same item?

Yes. By default, regular data validation lists allow you to select the same item multiple times across different cells. Just ensure your data validation 'Source' points to a static range of cells or a manual comma-separated list rather than a dynamic formula.

How do I find the source of my data validation dropdown in Excel Online?

Click on the cell containing the dropdown, go to the Data tab, and select Data Validation. Look at the 'Source' box to see if it contains a static range reference (like =$A$1:$A$5), a dynamic array reference (like =$A$1#), or a custom formula.

Document & File Issues