There’s a popular sample file on my website, that lets you select multiple items from a data validation drop down list. Since the original article, I’ve posted updates:
- how to set up multiple selection
- edit multiple selection cells
- prevent duplicates in multiple selection column
And here is the latest update.
Remove Previous Selections From Cell
In the comments on the original post, Dan asked for a way to remove items if they been previously selected. For example, the cell contains four items — “One, Two, Three, Four”.
When you select Three again, that number is removed from the cell, instead of being added at the end of the cell.
Download the Sample File
To experiment with this technique, you can download the sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page. The file is in Excel 2003 format and zipped. There are macros in the file so enable them to test the features.
The new sample is on the SameCellAddRemove worksheet.
Please let me know in the comments if there are other features you’d like to see in this workbook. Thanks!
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 sample 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.
Watch the Excel Tutorial Video
This video shows how the multiple select and remove features work. You can also see the Excel VBA code, and the steps that it goes through, when you make a selection in the drop down list.