Function Problems

How to Fix Excel Data Validation Lists Not Expanding on Mac

Bushra ParveenBushra Parveen Oct 21, 2026 869 views

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.

How to Fix Excel Data Validation Lists Not Expanding on Mac
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.
Before you start

Verify your current version of Excel for Mac, as newer features like searchable data validation lists depend on having the latest software updates installed.

Solution 1Recommended

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.

1
Open Excel Preferences

Click on 'Excel' in the top Mac menu bar, then select 'Preferences' from the dropdown menu.

2
Access Table Settings

In the Preferences window, locate the AutoCorrect section or authoring features and click on 'Table' or 'Tables & Filters'.

3
Enable Table Expansion

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.

Enable 'Automatically Expand Tables' in Excel Preferences
Feature Activation: Once enabled, any data entered immediately below your source table will automatically be integrated, instantly updating your dropdown menus.
Seamless Spreadsheet Management

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. 1. Open Your Spreadsheet: Launch WPS Office and open the workbook where you want to add or modify data validation.
  2. 2. Select Target Cells: Highlight the specific cell or range of cells where you want the dropdown list to appear.
  3. 3. Apply Data Validation: Navigate to the 'Data' tab on the ribbon, click 'Validation', and choose 'List' from the 'Allow' dropdown menu.
  4. 4. Select the Source Data: Click inside the 'Source' box, highlight your reference table or dynamic range, and click 'OK' to finalize.
Fully compatible with Microsoft Excel (.xlsx) file formats and dynamic range functions.Intuitive interface makes setting up data validation and expanding tables straightforward.Lightweight application that runs smoothly and efficiently on Mac, Windows, and Linux.
microsoft office alternative - wps office

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.