Select Multiple Items From Long List in Excel

It's hard enough to select one item from a long drop down list – how can we make it easier to select multiple items for a cell?

In the screen shot below, the worksheet has code that lets you select multiple item from the drop down list. Each new item is added to the cell, instead of replacing the previous selection.


Use a ListBox

To see more of the list, and select multiple items at once, you can use a ListBox, embedded in a UserForm. When you click OK, all of the selected items are entered in the cell, separated by commas.

This technique works for drop down lists that are based on a named range, such as MonthList, or AllProducts.


Really Long List

However, if your list has thousands of items, it can take a while to scroll through a ListBox too. So, to make it easier to check items in the list, I've created a new sample file, based on the previous ListBox file.

  • It has a combo box at the top of the UserForm, and you can start typing an item name there. When the correct item shows up, click Add, or press the Enter key, to add it to the list.
  • You can also use the ListBox, to check or uncheck items.
  • When you've finished selecting items, click OK, to add all the items to the cell.


In the sample file, there are two versions of the technique –

  • one opens the ListBox UserForm when you click on a cell with a drop down list
  • one opens the ListBox UserForm when you double-click on a cell with a drop down list

Download the Sample File

To see how this technique works, you can download the sample file from my Contextures website. On the Sample Files page, go to the Data Validation section, and look for DV0067 - Select Multiple Items in ComboBox or ListBox

The zipped file is in xlsm format, and contains macros. Enable the macros, when you open the file, if you want to test the code.

To see the code, right-click on the DataEntry_Click or DataEntry_DoubleClick sheet tab then click View Code.


You may also like...

2 Responses

  1. Gabe Hurney says:

    Code works great, thank you! I would just like to change one thing. Instead of adding a comma between selections made in the list I would like to have each selection start on a new line. Much like how you can manually press alt+enter to start writing in a new line. How could I change the code to make this work?

    • In the the Visual Basic Editor, select the UserForm, right-click the OK button, and click View Code
      Change the strSep line, so it adds a line break, instead of a comma and space:
      strSep = vbCrLf

Leave a Reply

Your email address will not be published. Required fields are marked *