How to Fix Excel Data Validation Lists Not Expanding on Mac
Question details
The user needs to fix an issue where data validation dropdown lists do not automatically include newly added items from a source table or range.

- Product
- Microsoft Excel
- Device & OS
- macOS
- Scenario
- Adding new items to a source table or data range that is linked to a data validation dropdown menu.
- Observed behavior
- The data validation dropdown list fails to expand, preventing the newly added source items from being displayed as selectable options.
Verify your current version of Excel for Mac, as newer features like searchable data validation lists depend on having the latest software updates installed.
Enable 'Automatically Expand Tables' in Excel Preferences
Configure your Excel application settings to automatically include new rows and columns when data is typed adjacent to existing tables.
If your data validation source is formatted as an Excel Table, the application must be permitted to expand tables automatically. Without this setting enabled, new items typed at the bottom of the table will not become part of the table structure, and therefore will not appear in the validation list.
Click on 'Excel' in the top Mac menu bar, then select 'Preferences' from the dropdown menu.
In the Preferences window, locate the AutoCorrect section or authoring features and click on 'Table' or 'Tables & Filters'.
Check the box next to 'Automatically expand tables'. Close the Preferences window and add a new item to your source table to verify the dropdown updates.

Insert New Cells Within the Existing Range
Force a standard data validation range to expand by inserting new cells directly inside the current reference boundaries.
Create Dynamic Data Validation Lists Easily with WPS Office
WPS Spreadsheet provides a highly compatible and intuitive environment for creating, managing, and updating dynamic data validation lists without the hassle of restrictive settings.
- 1. Open Your Spreadsheet: Launch WPS Office and open the workbook where you want to add or modify data validation.
- 2. Select Target Cells: Highlight the specific cell or range of cells where you want the dropdown list to appear.
- 3. Apply Data Validation: Navigate to the 'Data' tab on the ribbon, click 'Validation', and choose 'List' from the 'Allow' dropdown menu.
- 4. Select the Source Data: Click inside the 'Source' box, highlight your reference table or dynamic range, and click 'OK' to finalize.

Frequently Asked Questions
Why isn't my Excel data validation list searchable on Mac?
Searchable data validation lists are a relatively recent addition to Excel. If you cannot search within your dropdown menus, you are likely using an older version of Excel for Mac. Check for updates via Help > Check for Updates to gain this feature.
Can I use formulas to make my data validation list expand automatically?
Yes. You can create a dynamic named range using the OFFSET and COUNTA functions. By defining your validation source with this named range, the list will automatically accommodate any new items added to the column without needing to format it as a Table.
Will my Excel data validation settings carry over if I open the file in WPS Office?
Absolutely. WPS Spreadsheet is fully compatible with Microsoft Excel formats. Any data validation lists, dynamic named ranges, or formatted tables you created in Excel will function exactly the same way when opened in WPS Office.




