Office Settings & Configuration

Fix Empty Excel Data Validation Dropdown Lists on Mac and Android

Phi Hung Vo Sep 27, 2026 869 views

Question details

Dropdown data-validation lists are appearing empty in newer versions of Excel on Mac and Android, despite functioning correctly in older versions.

How to Fix Empty Data Validation Dropdown Lists in Excel

Product: Microsoft Excel
Device & OS: Mac and Android
Scenario: Accessing and using workbooks containing existing dropdown data validation rules that are linked to hidden attribute lists.
Observed behavior: The dropdown menus render as completely empty when clicked, due to failing attribute mapping or broken references to hidden worksheets.

Before you start

Enable the Developer tab in your Excel ribbon, as you will need to access the Visual Basic for Applications (VBA) editor to modify the visibility properties of the affected worksheets.

Solution 1Recommended

Make Very Hidden Worksheets Visible and Recreate Data Validation

Use the VBA editor to unhide the source worksheets, inspect the data, and reselect the attributes for your data validation rules.

In some Excel versions, data validation mapping fails if the source data is located on a 'Very Hidden' worksheet. Modifying the sheet's visibility allows you to repair the broken references.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in desktop Excel.

Change Worksheet Visibility

In the VBA Project Explorer pane, select the relevant hidden worksheet. In the Properties window below, find the 'Visible' property and change it from '2 - xlSheetVeryHidden' to '-1 - xlSheetVisible'.

Inspect Source References

Return to your Excel workbook and open the newly visible worksheet. Check the lists for any unusual or incorrect data formatting that might be causing the dropdown to fail.

Recreate the Validation Source

Select the cell with the broken dropdown. Go to the Data tab on the ribbon, click Data Validation, and reselect the source range from the visible worksheet to fix the mapping.

Make Very Hidden Worksheets Visible and Recreate Data Validation

Validation Restored: Once the attributes are successfully re-mapped and the dropdown functions, you can safely hide the worksheet again.

Solution 2

Test Worksheet Name References

Check if the affected workbook is failing to recognize the specific worksheet names used in the validation formulas.

Free Microsoft Office alternative

Use WPS Office to Seamlessly Handle Complex Excel Data Validations

If software updates frequently disrupt your workflow by breaking dropdown lists on Mac or Android, consider switching to WPS Office. It provides a highly compatible spreadsheet tool that accurately renders existing data validation rules and hidden sheet references without requiring manual VBA fixes.

1. Download WPS Office: Visit the official WPS Office website and download the correct installer for your operating system (Mac, Android, or Windows).
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your .xlsx file containing the data validation rules.
3. Test the Dropdown Lists: Click the cells with validation rules to verify that dropdown options display properly based on the hidden lists.

Fully compatible with Microsoft Excel (.xlsx) formats and complex data validation rulesLightweight and reliable performance across macOS, Windows, and Android devicesFamiliar user interface requiring zero learning curve for seasoned Excel usersFree to download and use for everyday spreadsheet and data management tasks

Frequently Asked Questions

Why do my Excel dropdown lists suddenly show as empty?

Software updates across different platforms (like upgrading to Excel 2501 on Mac and Android) can sometimes alter how the application processes references to hidden sheets or external data arrays, causing previously working validation sources to disconnect.

What does xlSheetVeryHidden mean in Excel?

It is a specialized worksheet visibility state. Unlike a standard hidden sheet, a 'Very Hidden' sheet removes the tab completely so it cannot be unhidden via the standard Excel right-click menu, requiring the VBA editor to make it visible.

Will unhiding the source sheet affect my workbook formulas?

No, unhiding the sheet only alters its visibility state in the user interface. It allows you to safely repair the broken references and data validation lists without changing how underlying formulas calculate.

Office App Problems