How to Find and Expand a Hidden Drop-Down List Source in Excel
Question details
The user needs to locate the source data of an existing Excel drop-down list, which may be on a hidden worksheet, and expand the range to include new options.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating an existing spreadsheet that has data validation drop-down lists relying on hidden or restricted source data references.
- Observed behavior
- The source data formula (e.g., =Lookups!$A$3:$A$17) points to a sheet that is not visible, preventing the user from adding new options to the drop-down menu.
Ensure your workbook is not protected, as workbook structure protection will prevent you from unhiding sheets or modifying data validation rules.
Unhide the Source Sheet and Update the Validation Range
Locate the hidden source sheet to access the drop-down data and manually expand the cell range in the Data Validation settings.
By default, Excel allows you to hide worksheets to keep the interface clean. Drop-down lists often draw data from these hidden reference sheets. You must first reveal the sheet before making edits.
Select the cell containing the drop-down menu, go to the Data tab, click Data Validation, and look at the Source field to find the sheet name (e.g., =Lookups!$A$3:$A$17).
Go to the Home tab, click Format in the Cells group, hover over Hide & Unhide, and select Unhide Sheet. Alternatively, right-click any visible sheet tab at the bottom and choose Unhide.
Choose the hidden sheet (e.g., 'Lookups') from the pop-up dialog box and click OK to make it visible.
Type your new options below the existing list on the newly unhidden sheet. Finally, return to your original drop-down cell, open Data Validation again, and modify the Source range to include your new items (e.g., =Lookups!$A$3:$A$30).
Convert the Source Range to an Excel Table for Auto-Expansion
Change your drop-down source data into an official Excel Table so that new items are automatically included without manual range updates.
How to Edit Drop-Down Lists in WPS Spreadsheet
WPS Office provides an intuitive Data Validation tool, fully compatible with Excel formats, allowing you to seamlessly unhide sheets, update drop-down lists, and manage large datasets.
- 1. Inspect the Drop-Down: Open your spreadsheet in WPS Office, select the cell with the drop-down menu, navigate to the Data tab, and click Validation to view the source formula.
- 2. Unhide Source Data: Right-click any sheet tab at the bottom of your workspace and select 'Unhide' to reveal the hidden source worksheet.
- 3. Expand the List: Add your new list items to the bottom of the data column.
- 4. Apply the New Range: Return to the Data Validation dialog box and adjust the cell range in the Source field to encompass your newly added items.
Frequently Asked Questions
Why is the Unhide option grayed out in Excel?
The Unhide option may be grayed out if the workbook structure is protected. Go to Review > Protect Workbook and enter the password if prompted to unlock it. Once unlocked, you can unhide sheets.
Can I use a drop-down list from another workbook?
Yes, but both workbooks must remain open for the drop-down to function properly without warnings, or you must use a defined Named Range that correctly references the external workbook link in your data validation source.
How do I remove a drop-down list completely?
Select the cell containing the drop-down, navigate to Data > Data Validation, click 'Clear All' in the bottom left corner of the dialog box, and then click OK.
Why isn't my drop-down list updating when I add new data?
If your source data is not formatted as an Excel Table or a dynamic Named Range, the data validation rule won't automatically detect new rows. You must either manually update the range in the Data Validation menu or format your list as a Table.
