Function Problems

How to Select Multiple Values from an Excel Drop-Down List Without VBA

Huda Qurayshi Oct 8, 2026 869 views

Question details

The user needs to select multiple items from a data validation drop-down list and combine them into a single cell, separated by semicolons, without using VBA macros.

How to Select Multiple Values from an Excel Drop-Down List Without VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Compiling multiple categorized selections into one cell for reporting or data entry purposes.
Observed behavior: Native Excel data-validation drop-down lists restrict users to a single selection per cell.

Before you start

Ensure you have a pre-defined list of items ready in your spreadsheet to serve as the source data for your validation drop-down menus.

Solution 1Recommended

Use Helper Columns and the TEXTJOIN Function

Since native multi-select isn't possible without VBA, this practical workaround uses adjacent helper columns with single drop-downs, combining their results into one main cell.

This method relies on creating multiple individual drop-down lists and then stitching their selections together using a formula.

Once configured, you can hide the helper columns to keep your interface clean and presentable.

Create helper drop-down columns

Set up standard data validation drop-down lists in several adjacent helper columns (e.g., Columns B, C, and D) using your source data.

Apply the TEXTJOIN formula

In your target result cell (e.g., cell A2), enter the formula =TEXTJOIN(";", TRUE, B2:D2). This will concatenate the selected values and ignore empty cells.

Select your multiple values

Use the drop-downs in the helper columns to pick your desired multiple items. They will instantly combine in the target cell separated by semicolons.

Hide the helper columns

Highlight the headers of your helper columns, right-click, and select 'Hide'. Your spreadsheet will now display only the single cell containing the multiple selections.

Use Helper Columns and the TEXTJOIN Function

Function Compatibility: If you are using an older version of Excel that does not support TEXTJOIN, you can use a formula like =B2&";"&C2&";"&D2 instead.

Advanced Spreadsheet Solution

Easily Manage Drop-Down Lists and Formulas with WPS Office

WPS Spreadsheets provides comprehensive support for data validation and advanced string manipulation functions like TEXTJOIN, allowing you to easily build multi-select workarounds.

1. Set up validation in WPS: Open your file in WPS Spreadsheets, highlight your helper cells, and navigate to Data > Validation to create your drop-down lists.
2. Combine the text: Enter the =TEXTJOIN(";", TRUE, [range]) formula in your main display cell to merge the selections.
3. Hide supporting columns: Right-click the helper column letters at the top of the sheet and click 'Hide' to maintain a clean workspace.

Complete compatibility with Microsoft Excel (.xlsx) formatsFull support for data validation and complex formulasIntuitive interface for managing and hiding helper columnsFree and lightweight alternative for spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Can I make an Excel drop-down select multiple values without any helper columns or VBA?

No, Excel natively restricts data validation drop-downs to a single selection per cell. To achieve a multi-select result in a single cell, you must use either a VBA macro or the helper column workaround.

How do I separate the combined values with a comma instead of a semicolon?

In your TEXTJOIN function, simply change the delimiter in the first argument from a semicolon to a comma and space. For example: =TEXTJOIN(", ", TRUE, B2:D2).

Will the hidden helper columns appear when I print the document?

No. Columns that are hidden in your spreadsheet layout are automatically excluded and will not appear when you print the document.

Excel & Data Problems