[Latest update: July 27, 2016] With a bit of Excel VBA programming, you can change an Excel data validation drop down list, so it allows multiple selections. There is a sample file and instructions, in a blog post that I wrote a couple of years ago – Select Multiple Items from Excel Data Validation List.
There is a video in that post, that shows how the multiple selection options work, and a peek at the code that makes the multiple selections possible.
Setting up the Workbook
Even though the post is a couple of years old, it still gets comments and questions. This week, Rick asked for details on setting up a new workbook, and copying the sample code into the new file.
To help, I’ve recorded another video, showing the setup steps.
More Articles on Select Multiple Items
[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I’ve added more articles on the same topic. Please read these updates, for more details on working with the VBA code:
Changing the Macro Code
Download the Sample Data Validation File
To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page.
Buy the Premium Version
There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell.
It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.
The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists — you don’t want multiple items selected in the main columns).
The kit has 3 Excel files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.