Excel Data Validation Drop Down Select Multiple Items (2023)

View the Code for Select Multiple Items

The sample workbook uses Microsoft Excel VBA code (Visual Basic for Applications), which is set up to run automatically, when you make a change on the worksheet.

To see the code for any sheet in the sample file, you can right-click the sheet tab, and click on View Code.

Excel Data Validation Drop Down Select Multiple Items (1)

Change the Column Number

In the sample code shown below, column 3 is the only one where the change will occur. In the screen shot, the line is marked with a red dot:

If Target.Column = 3 Then

In your workbook, you could change the 3 to a different number.

If Target.Column = 6 Then

Or, add more columns in the code. For example:

If Target.Column = 3 _Or Target.Column = 5 _Or Target.Column = 6 Then

Or, use a starting and ending column. For example:

 If Target.Column >= 3 And _Target.Column <= 10 Then

Or, include a starting row as well. For example:

If Target.Column = 3 _And Target.Row >= 4 Then

Or, if you don't want to limit the code to a specific column, you could delete the two rows that are marked with a red dot. When those lines are removed, the code will run when any cell with a drop down list is selected.

Excel Data Validation Drop Down Select Multiple Items (2)

Change the Sheet and Range Names

In some code samples, the sheet names and named ranges are included. You will have to change these to match your sheet names and range names.

For example, shown below is a portion of the code from the LineBreakAddSort sheet. The sheet and range names are highlighted in yellow.

Also, this code is set up for a list in column 3.

i=ws.Cells(Rows.Count,3).End(xlUp).Row + 1

You would need to change that number, if your list is in a different column.

Excel Data Validation Drop Down Select Multiple Items (3)


Can you select multiple items in a Data Validation list Excel? ›

On an Excel worksheet, you can only select one item from a data validation drop down list of items. Later, if you choose a different item from the drop down, the new item replaces the previous selection in the cell.

How do I create a drop down list in Excel with multiple selections or values? ›

Create a drop-down list
  1. Select the cells that you want to contain the lists.
  2. On the ribbon, click DATA > Data Validation.
  3. In the dialog, set Allow to List.
  4. Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.

How do I select multiple items in a dropdown? ›

For windows: Hold down the control (ctrl) button to select multiple options. For Mac: Hold down the command button to select multiple options.

Can a dropdown have multiple values? ›

Multi select dropdown list is used when a user wants to store multiple values for the same record, whereas dropdown list is used to store a single value for a record. You can create custom categories of either dropdown list or multi select dropdown list and define items in each category.

Can you do Data Validation with multiple selections? ›

Instead of limiting the drop down list to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections. With a few adjustments to the VBA code, you can display all the selected items across a row, or down a column, or keep them in a single cell.

How do I make multiple selections in a drop-down list in Word? ›

Under Insert controls, click Multiple-Selection List Box. If you cleared the Automatically create data source check box in step 3, select a repeating field in the Multiple-Selection List Box Binding dialog box to which you want to bind the multiple-selection list box.

How do I create a dynamic drop down list in Excel? ›

How to make dynamic drop down list in Excel
  1. Get items for the main drop down list. For starters, we shall extract all different fruit names from column A. ...
  2. Create the main drop down. ...
  3. Get items for the dependent drop down list. ...
  4. Make the dependent drop down.
Apr 5, 2023

How do I create a dependent drop down list in multiple rows? ›

Copy drop down lists across multiple rows

To copy the dependent drop down list, let's do a regular copy/pasting: Select the cell with the secondary drop-down (C3) and press Ctrl + C to copy it. Select all other cells where you want the dependent list to appear (C4:C12) and press Ctrl + V to paste the copied contents.

How to select multiple options from dropdown without select class? ›

Different Methods to handle Dropdown in Selenium without using Select Class
  1. Method 1: By storing all the options in List and iterating through it.
  2. Method 2: By creating Custom Locator and without iterating the List.
  3. Method 3: By using JavaScriptExecutor class.
  4. Method 4: By using sendKeys method.
Jan 18, 2023

How do I get the selected value in multi select dropdown? ›

Using our inbuild value property, we can get the selected value in the multiselect component Please refer to the block of code below.
  1. // Instance created on multiselect component.
  2. var multiselectObj = document.getElementById('ddlFilterEmployee').ej2_instances[0];
  3. // Get the selected value through Value property.
Oct 24, 2019

How do you select multiple items at once? ›

Windows method one
  1. Click on one of the files or folders you want to select.
  2. Hold down the control key (Ctrl).
  3. Click on the other files or folders that you want to select while holding the control key.
  4. Continue to hold down the control key until you select all the files you want.
Feb 3, 2023

What is the difference between dropdown and multiple choice? ›

Multiple Choice Fields: A question with a group of answers choices that lets the user select one choice only. Dropdown Menus: A question with an answer fields that expands into a list of options that lets the user select one choice.

Can you have 2 drop-down lists in Excel? ›

Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form. You can create multiple drop-down lists in Excel using the same source data.

How many items can be in a drop down list in Excel? ›

There are limits to the number of items that will show in a data validation drop down list: The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

How do you add multiple criteria in Data Validation? ›

Try it!
  1. Select the cell(s) you want to create a rule for.
  2. Select Data >Data Validation .
  3. On the Settings tab, under Allow , select an option: ...
  4. Under Data , select a condition.
  5. Set the other required values based on what you chose for Allow and Data .

How do I use multiple criteria in Data Validation in Excel? ›

Create a custom Data Validation rule for multiple criteria

From the “Allow” drop-down menu, select “Custom.” In the Formula field box, type an “ = “ and use Ctrl+V to paste your nested formula in the field. Click OK. NOTE: Only the Ctrl+V keyboard shortcut will work for this.

How to do multiple validation in Excel? ›

Re: Excel multiple data validation

Here's how you can do it: Select the cells you want to apply data validation to. Go to the Data tab on the ribbon and click on Data Validation. In the Data Validation dialog box, select Custom from the Allow drop-down list.

How do I add multiple items to a drop down list in sheets? ›

More often than not, you'll want to use your dropdown list for an entire row or column of cells. To do this, you can either drag the bottom-right corner of the cell with your dropdown menu to the cells you want to populate with the same list, or copy and paste the contents of the cell into other cells.

How to select multiple options in Data Validation list sheets? ›

Below are the steps to so this:
  1. Select the cell where you want the drop-down list.
  2. Navigate to Data >Data validation.
  3. In Criteria, select Dropdown (from a range) and then select the range that has the items that you want to show in the drop-down.
May 2, 2023

Which method is used to select multiple items from ListBox? ›

Multiple items are selected by holding down Shift and choosing them with the mouse, or by holding down Shift and pressing an arrow key to extend the selection from the previously selected item to the current item. You can also select items by dragging with the mouse.

Which property allows to select multiple items in ListBox? ›

The SelectionMode property enables you to determine how many items in the ListBox a user can select at one time and how the user can make multiple-selections. When the SelectionMode property is set to SelectionMode.

What is dynamic drop-down list? ›

Drop-down Menu: Dynamic Select. The drop-down menu is a clear method of showing a list of data, allowing users to pick their choice from the list. This field can be categorized into two types based on the choice of selection types offered.

What is cascading dropdown? ›

A cascading drop-down list is a series of dependent DropDownList controls in which one DropDownList control depends on the parent or previous DropDownList controls. The items in the DropDownList control are populated based on an item that is selected by the user from another DropDownList control.

How do you create a dynamic validation? ›

How to Create Dynamic Data Validation Dropdown List in Excel
  1. We can use OFFSET function to make dynamic data validation list.
  2. Press ALT + D + L.
  3. From Settings tab; click on Allow.
  4. In Source box, enter the following formula.
  5. =OFFSET($A$2,,,COUNTA($A:$A)-1)

How can you select multiple options from the items displayed in a list? ›

2) Selecting Multiple Items

To select multiple items in a list, hold down the Ctrl (PC) or Command (Mac) key.

How do you select multiple things not in order? ›

Press and hold CTRL. Select the next item that you want.

How do you select a dropdown value and what are the different methods are there? ›

The following are the most commonly used methods to deal with a drop-down list:
  1. selectByVisibleText: selectByVisibleText(String arg0): void. ...
  2. selectByIndex: selectByIndex(int arg0) : void. ...
  3. selectByValue: selectByValue(String arg0) : void. ...
  4. getOptions: getOptions( ) : List<WebElement> ...
  5. deselectAll()
Mar 16, 2023

How to multiselect a dynamically populated dropdown based on another dropdown value selection? ›

It's simple: just add an onClick to call a function every time the options are checked (or unchecked), and then, loop the checkboxes looking for the cheked options, concatenating their texts and showing it at the main label of the select. Same thing is possible if you want to show the number of items selected.

How do I get selected items from a dropdown? ›

If we want to get the selected option text, then we should use the selectedIndex property of the selectbox . The selectedIndex property denotes the index of the selected option.

How do you select multiple objects quickly in Excel? ›

To select one object, click the object. To select multiple shapes in a group, press and hold Shift or Ctrl while you click the shapes.

Which control allows you to select multiple items? ›

Hold the CTRL key and click the items in a list to choose them. Click all the items you want to select. They don't have to be next to each other.

What action must be taken to select multiple objects at the same time? ›

Select multiple objects

To select all the objects in a rectangular area, use the Selection tool to drag a marquee over the objects you want to select. To select nonadjacent objects, use the Selection tool to select an object and then press Shift as you click additional objects. Clicking selected objects deselects them.

What is the difference between a drop-down list and a combobox in Excel? ›

  • a combo box is a combination of an input text field and a list of options. You can type in stuff in the input field and the list should update to highlight a possible match.
  • a dropdown is just a list of options that drops down when you click on the arrow button. You can only choose from a list of predefined options.
Feb 1, 2010

What is difference between dropdown and Dropdownlist? ›

The following terms are important to understand as you read this article: A standard list box is a box containing a list of multiple items, with multiple items visible. A drop-down list is a list in which the selected item is always visible, and the others are visible on demand by clicking a drop-down button.

What is the advantage of a drop-down list compared to a multiple choice questions? ›

Easy-to-use: A dropdown menu is very easy to use. All you need is to drag and drop to add question type and edit the answer options. Easy-to-answer: Respondents have to just click on an option from the list of answers. Brevity: Dropdown menu packs a number of options into a simple list of options.

Can a drop-down list have multiple selections? ›

Multi select dropdown list is used when a user wants to store multiple values for the same record, whereas dropdown list is used to store a single value for a record. You can create custom categories of either dropdown list or multi select dropdown list and define items in each category.

Can we select multiple values in dropdown? ›

With the multiple attribute, you can allow the user to select multiple items from the dropdown. This makes 4 items visible by default. To select multiple items, the user has to hold down the shift or ctrl key, then select with the mouse.

How do I create a drop-down list in Excel with multiple cells? ›

On the ribbon, click the Data tab > Data Validation. In the Data Validation dialog window, select List from the Allow drop-down menu. Place the cursor in the Source box and select the range of cells containing the items, or click the Collapse Dialog icon and then select the range. When done, click OK.

How to select multiple options in Data Validation list Google sheet? ›

On the Data validation rules panel, under "Criteria," select an option:
  1. Dropdown from a range: Choose the cells to include in the list.
  2. Dropdown: Enter the dropdown value. Click Add another item to add additional dropdown values.


Top Articles
Latest Posts
Article information

Author: Manual Maggio

Last Updated: 31/12/2023

Views: 6334

Rating: 4.9 / 5 (49 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Manual Maggio

Birthday: 1998-01-20

Address: 359 Kelvin Stream, Lake Eldonview, MT 33517-1242

Phone: +577037762465

Job: Product Hospitality Supervisor

Hobby: Gardening, Web surfing, Video gaming, Amateur radio, Flag Football, Reading, Table tennis

Introduction: My name is Manual Maggio, I am a thankful, tender, adventurous, delightful, fantastic, proud, graceful person who loves writing and wants to share my knowledge and understanding with you.