How to create drop-down list in Excel for multiple cells

How to Make Multiple Selections in a Drop Down List in Excel

• -- By Sumit Bansal

FREE EXCEL TIPS EBOOK - Click here to get your copy

One of my colleagues asked me if it is possible to make multiple selections in adrop-down list in Excel.

When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection.

He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell.

Something as shown below in the pic:

If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.

Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.

Working with your drop-down list

After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list.

To delete a drop-down list, see Remove a drop-down list.

Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.

Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data. If the list is not large, you can easily refer to it and type the entries directly into the data validation tool.

1. Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells.

2. Select the cells that you want to restrict data entry in.

3. On the Data tab, under Tools, click Data Validation or Validate.

   

   Note:If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected. For more information about workbook protection, see Protect a workbook.

4. Click the Settings tab, and then in the Allow pop-up menu, click List.

5. Click in the Source box, and then on your sheet, select your list of valid entries.

   The dialog box minimizes to make the sheet easier to see.

6. Press RETURN or click the Expand

   
   button to restore the dialog box, and then click OK.

   Tips:

   • You can also type values directly into the Source box, separated by a comma.

   • To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.

   • You can specify your own error message to respond to invalid data inputs. On the Data tab, click Data Validation or Validate, and then click the Error Alert tab.

1. In a new worksheet, type the entries you want to appear in your drop-down list. Ideally, you’ll have your list items in an Excel table.

   Notes:

   • Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don't need to do anything else.

   • Now is a good time to Sort your data in the order you want it to appear in your drop-down list.

2. Select the cell in the worksheet where you want the drop-down list.

3. Go to the Data tab on the Ribbon, then click Data Validation.

4. On the Settings tab, in the Allow box, click List.

5. If you already made a table with the drop-down entries, click in the Source box, and then click and drag the cells that contain those entries. However, do not include the header cell. Just include the cells that should appear in the drop-down. You can also just type a list of entries in the Source box, separated by a comma like this:

   Fruit,Vegetables,Grains,Dairy,Snacks

6. If it’s OK for people to leave the cell empty, check the Ignore blank box.

7. Check the In-cell dropdown box.

8. Click the Input Message tab.

   • If you want a message to pop up when the cell is clicked, check the Show message checkbox, and type a title and message in the boxes (up to 225 characters). If you don’t want a message to show up, clear the check box.

9. Click the Error Alert tab.

   • If you want a message to pop up when someone enters something that's not in your list, check the Show Alert checkbox, pick an option in Type, and type a title and message. If you don’t want a message to show up, clear the check box.

10. Click OK.

After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries. If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list.

Download Practice Workbook

How to Create Drop Down List in Excel with Multiple Selections.xlsm

---

Steps to Create Drop-Down List in Excel with Multiple Selections

Here we’ve got a data set with the Names of some books in a bookshop called Martin Bookstore.

Our objective today is to create a drop-down list based on this data set that takes multiple selections.

We will accomplish this in two steps.

Step 1: Creating a Drop-Down List by Data Validation

Step 2: Enabling that List to Accept Multiple Selections by VBA Code

---

Step 1: Creating a Drop-Down List by Data Validation

• Select the cell where you want to enter the drop-down list. Then go to Data>Data Validation>Data Validation under the Data Tools section.

• Click on Data Validation. You will get the Data validation dialogue box.

• From the Allow option, select List. Then in the Source option, enter the range of the cells that you want to enter as the drop-down list ($B$4:$B$23 in this example).

• Then click OK. You will find a drop-down list created in your selected cell.

---

Similar Readings:

• How to Make a Drop Down List in Excel (Independent and Dependent)
• Make Multiple Selection from Drop Down List in Excel (3 Ways)
• How to Create Dependent Drop Down List in Excel
• Create Drop Down List in Multiple Columns in Excel (3 Ways)

---

Step 2: Enabling the Drop-Down List to Accept Multiple Selection by VBA Code

Now we’ve created a drop-down list. But up till now, it accepts a single selection.

We want it to accept multiple selections.

• To enable it to accept multiple selections, press Alt + F11 on your keyboard. It will open the Visual Basic Application (VBA) window.
• In the left panel of the window, you will get a folder called VBA Project. Under this, there is another folder called Microsoft Excel Objects.

• Double-click on the name of your worksheet (Sheet1 in this example). And it will open the VBA code window of the worksheet.

• Now enter either of the two codes in this window, depending on whether you want to allow repetition in your multiple selections or not.

---

Case 1: VBA Code for Multiple Selection with Repetition

If you want multiple selections with repetition, enter the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String On Error GoTo Exitsub If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & ", " & Newvalue End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

Note: Here in the 5th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then

---

Case 2: VBA Code for Multiple Selection without Repetition

And if you don’t want repetition, use this code instead.

Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim Oldvalue As String Dim Newvalue As String Application.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$D$4" Then If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then GoTo Exitsub Else: If Target.Value = "" Then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else If InStr(1, Oldvalue, Newvalue) = 0 Then Target.Value = Oldvalue & ", " & Newvalue Else: Target.Value = Oldvalue End If End If End If End If Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

Note: Here in the 6th line of the code (If Target.Address = "$D$4" Then), “$D$4” is the cell where I am entering the list.

You use your one.

If you want to enter the list in multiple cells, then repeat the line for multiple cells using an or operator.

For example, if you want to enter the list in cells D4, F4, and H4, use:

If Target.Address = "$D$4" or Target.Address = "$F$4" or Target.Address = "$H$4" Then

After entering any of the above codes, save the file as an Excel Macro-Enabled Workbook (*.xlsm).

Now come back to your worksheet and go to the drop-down list you created earlier.

You will be able to make multiple selections from the list now, separated by commas (,).

---