Skip to main content

Tricks for creating dropdown lists that let you select multiple items

Our guest blogger Debra Dalgleish is a consultant specializing in Microsoft Office programming and development. She offers her Excel wisdom every week on the Contextures blog and has received the Excel MVP award every year since 2001.

A shadow of a magician. At the Mystical Magic Factory*, one of the perks is that office employees get to disappear at noon on most Fridays. The office stays open though, so at least two people are on duty to answer the phones and greet visitors.

 

 

Microsoft Excel

Turn data into insights.

Get Excel

Before the magic

To keep track of who will stay in the office each week, Dee, the office manager, created an Excel file. This file contains a list of the employee names and a list of weeks. Dee created a dropdown list of employee names for each week.

Creating a drop-down list.

 

The dropdown list works well for selecting one name, but if Dee selects a second name in a cell, it overwrites the first name.

Creating another drop-down menu.

 

Add the magic

To let Dee select multiple names in each cell, we’ll add a bit of magic to her worksheet with a few lines of VBA code. The code keeps the old name in the cell, and adds a comma, followed by the new name.

Let’s take a peek at the code and see how you can use this trick in your worksheets.

Behind the curtain

To see the code for a worksheet, right-click on its sheet tab and click View Code. That will open the Visual Basic Explorer (VBE).

The Visual Basic Explorer.

 

In the project list at the left, the FridayStaffName workbook is listed, and the Friday_Staff sheet is highlighted. At the right, we can see the Worksheet_Change event code for that worksheet. That code runs automatically if Dee makes a change on the sheet.

Using the Visual Basic Explorer.

 

Copy the code to your file

Even if you don’t know anything about programming, you could copy this code and use it in your workbook. There is one line of code that you can change, if your dropdown list is not in column B.

In the code, Target refers to the cell that you changed. In the screen shot below, the code checks to see which column that Target cell was in. Then, if it was column 2 (B), the rest of the code will run.

Code-checking within the Visual Basic Explorer.

 

Instead of using column 2, you could change to a different number, or use multiple columns, such as: Case 2, 5, 6

Let the magic work for you

With this little bit of magic added to her worksheet, Dee can quickly and easily select the office staff for the Friday afternoon shifts. The phones will be answered while everyone else is off relaxing.

You can download the sample file, to see how it works, and add this amazing trick to your workbooks too: Download Friday Staff Names Sample File

*Mystical Magic Factory is a fictional company, but this blog topic is based on a real issue of a Contextures’ client.

Additional info on Excel dropdown lists:

How to Make a Cell Drop-down List in Excel 2010

Related posts

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…

Image for: Surface Pro X and Surface Arc Mouse inside on desk with Excel and search on screen.

Connect to your own data with more new data types in Excel

The evolution of Excel Excel is the ultimate decision-making tool. We’re amazed every day by the ways in which you, our customers, use Excel to make better decisions, leveraging the flexibility of the 2D grid and formulas to capture, analyze and collaborate on data. Up to this point, Excel has only had a couple base…