Function Problems

How to Create an Excel Drop-Down List from Multiple Worksheets

Rana Garcia Sep 28, 2026 873 views

Question details

The user wants to combine data from different worksheets into a single vertical range to use as the source for a data validation drop-down list.

How to Create an Excel Drop-Down List from Multiple Worksheets

Product: Excel
Device & OS: not provided
Scenario: Creating a comprehensive data validation drop-down menu that seamlessly pulls and displays options from multiple separate tabs.
Observed behavior: The user requires a dynamic formula approach to stack data from different sheets into a single referenceable list for data validation.

Before you start

Ensure that your spreadsheet software supports dynamic arrays and the VSTACK function, as this method relies on spilled ranges to populate the drop-down list.

Solution 1Recommended

Use the VSTACK Function with Spilled Range References

This method efficiently stacks multiple worksheet ranges vertically into a single helper column, allowing you to use a dynamic spilled array for your drop-down list.

The VSTACK function allows you to append arrays vertically. By placing this formula in a helper cell, it generates a spilled array that automatically adjusts in size. You can then reference this array in your Data Validation settings using the '#' symbol.

Create a helper cell

Select an empty cell (e.g., C1) on a worksheet that you want to use as your helper column.

Enter the VSTACK formula

Type the formula =VSTACK(Sheet2!A1:A20,Sheet3!A1:A20) and press Enter. This will vertically stack the data from Sheet2 and Sheet3.

Select the target cell

Click on the cell where you want your new drop-down list to appear.

Open Data Validation

Navigate to the Data tab on the top ribbon and click on 'Data Validation'.

Configure the drop-down list

In the Settings tab, select 'List' from the Allow drop-down menu. In the Source box, enter the reference to your helper cell followed by a hashtag, like =$C$1#, and click OK.

Use the VSTACK Function with Spilled Range References

Drop-down List Capacity Limit: Excel drop-down lists are limited to approximately 32,767 items. A list containing more than this limit cannot be fully displayed in a single data validation drop-down.

WPS Spreadsheet Solution

Create Dynamic Drop-Down Lists with WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for dynamic formulas and data validation, making it incredibly simple to consolidate data across multiple worksheets and create robust drop-down menus.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing the multiple worksheets you want to consolidate.
2. Set up a helper column: Pick a cell and use array formulas or consolidation methods to combine your data from the secondary sheets.
3. Access Data Validation: Go to the 'Data' tab on the main ribbon and select 'Validation'.
4. Apply List Criteria: Choose 'List' under the Allow criteria and select your newly consolidated helper range as the Source.
5. Confirm and use: Click 'OK' to save the settings and instantly use your combined drop-down list.

Highly compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Supports advanced data validation settings for professional data entry forms.Completely free, lightweight, and features an intuitive tabbed interface for seamless task management.

microsoft office alternative - wps office

Frequently Asked Questions

Can I hide the helper column used for the VSTACK formula?

Yes. Once you have set up your Data Validation drop-down list using the spilled array reference (like =$C$1#), you can safely hide the helper column or place it on a separate hidden worksheet. The drop-down list will continue to function normally.

Why does the Data Validation source use a hashtag (#)?

The hashtag symbol is a spilled range operator. When appended to a cell reference (e.g., =$C$1#), it tells the software to reference the entire dynamic array that spills from that starting cell, rather than just the single cell itself.

What happens if my combined VSTACK range has more than 32,767 items?

Spreadsheet drop-down lists have a maximum limit of approximately 32,767 items. If your combined ranges exceed this limit, the list will truncate and will not display all entries.

Excel & Data Problems