How to Find and Edit the Source of an Excel Drop-Down List in WPS Office
Question details
The user needs to locate the source range or named range behind an Excel data validation drop-down list, edit the available items, and change the fill color shown for selected values.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user opens an existing workbook with a data validation drop-down list, wants to update the list entries, and needs to adjust the selected value color.
- Observed behavior
- The drop-down list works, but the source range may be hidden or driven by a named range, and the selected value color is controlled separately by conditional formatting.
Make sure the workbook is open in WPS Office and identify the cell that contains the drop-down list. If the list source may be hidden, right-click any sheet tab and choose Unhide to check hidden sheets.
Locate and edit the data validation source range of a drop-down list
Use this solution when you need to see or update the list items behind a drop-down, including when the source is on a hidden worksheet.
The Data Validation dialog shows the current source as either a worksheet range or a named range. If the source is on a hidden sheet, unhide the sheet before editing the list cells, or edit the source reference directly in the dialog.
Click the cell that contains the Excel drop-down list so WPS Office knows which validation rule to display.
Go to the Data tab, find the Data Tools group, and click Data Validation.
On the Settings tab, read the Source field to see the range or named range. Type a new range or named range to change the list source.
If the source range is on a hidden sheet, right-click any sheet tab, click Unhide, select the hidden sheet, and edit the list values directly.
Change the fill color displayed for a selected drop-down value
Use this solution when the color is applied by conditional formatting and you need to inspect or change the highlighting.
Find and Edit an Excel Drop-Down List Source in WPS Office
You can complete this task directly in WPS Office Spreadsheets: open the Data Validation dialog to locate and edit the list source, unhide a sheet if needed, and adjust conditional formatting to change the selected value color.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel workbook that contains the drop-down list, and select the relevant worksheet.
- 2. Open Data Validation: Click the cell containing the drop-down list, go to the Data tab, and click Data Validation in the Data Tools group.
- 3. Edit the list source: In the Settings tab, review the Source box. Type a new range or named range, or edit the source cells directly after unhiding a hidden sheet.
- 4. Change selected value color: Go to the Home tab, click Conditional Formatting, select Manage Rules, choose the applicable rule, and use Format > Fill to change the color.
- 5. Save in Excel format: Click the Menu button or press Ctrl+S to save the workbook. WPS Office preserves compatibility with the .xlsx format.
Frequently Asked Questions
Where can I find the source range of an Excel drop-down list in WPS Office?
Select the cell with the drop-down, click Data > Data Validation, and check the Source box on the Settings tab. It shows either a worksheet range or a named range.
Can a drop-down list source be on a hidden worksheet?
Yes. If the Source box refers to a hidden sheet, right-click any visible sheet tab, choose Unhide, select the hidden sheet, and then inspect or edit the list range.
How do I change the fill color when a drop-down value is selected?
Select the cell, go to Home > Conditional Formatting > Manage Rules, choose the relevant rule, click Edit Rule, and then click Format > Fill to pick a new background color.
Why does changing the normal cell fill color not change the selected value color?
The selected value color is often controlled by a conditional formatting rule, which overrides the normal cell fill. Edit or delete the matching rule under Home > Conditional Formatting > Manage Rules.
Can I use a named range as the source for a drop-down list?
Yes. In the Data Validation Source box, enter the named range preceded by an equals sign, such as =ProductList, or create a named range under the Formulas tab.
