What a multi-select drop-down list does

A drop-down list in Excel normally lets you pick one item from a list you set up ahead of time. A multi-select drop-down list lets you pick more than one item from that same list and see all your choices in a single cell. This is useful when you are tracking things like project tasks (where one task might have multiple people assigned), dietary restrictions (where someone might need to avoid multiple ingredients), or equipment features (where a device might have several capabilities).

Excel does not have a built-in multi-select feature in the standard data validation menu. You have to add it using a helper column and a small piece of code called a macro, or use a workaround with formulas. The macro method is more reliable and easier to use once it is set up.

Key Takeaways

  • Excel's standard data validation only allows one selection per cell, so you need a macro (a small automated script) to enable multiple selections.
  • The macro method requires you to enable macros in your workbook and paste a specific code into the Visual Basic editor, which takes about five minutes.
  • Once the macro is in place, you set up your drop-down list normally through data validation, and the macro handles multiple selections automatically.
  • You can also use a formula-based workaround with a helper column if you cannot or do not want to use macros, though it is more cumbersome to use.

Setting up the macro method (the faster way)

Open your Excel file and press Alt + F11 on Windows (or Option + F11 on Mac) to open the Visual Basic editor. On the left side, you will see a list of sheets. Right-click on the sheet where you want the multi-select drop-down to work and choose Insert, then Module. A blank white box will appear on the right side.

Copy and paste this code into that blank box:

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 1 Then If Target.Value <> "" Then Target.Value = Target.Value & ", " & process.InputBox("Add another item or press Cancel to finish") End If End If End Sub

This code watches column A (the first column). Every time you pick something from the drop-down, it asks if you want to add another item. If you click Cancel or press Escape, it stops and saves what you picked. Close the Visual Basic editor by pressing Ctrl + W or clicking the X button.

Now save your file. Excel will ask if you want to save it as a macro-enabled workbook. Click Yes and choose the format .xlsm (not .xlsx). If you do not save it as .xlsm, the macro will not work the next time you open the file.

Creating the actual drop-down list

Select the cells where you want the drop-down to appear. Click on the Data tab at the top, then click Data Validation (in some versions it says Validity). A dialog box opens.

In the Allow dropdown, choose List. In the Source field, type your options separated by commas (like: Red, Blue, Green, Yellow) or point to a range of cells that contain your list. Click OK.

Now when you click on one of those cells, a small arrow appears. Click the arrow to see your list. Pick an item. The macro will when ready ask if you want to add another. Type the next item name or click Cancel to stop. Your cell will show all the items you picked, separated by commas.

The formula workaround if you cannot use macros

Some workplaces block macros for security reasons. If that is your situation, you can use a formula-based method instead, though it requires more steps each time you use it.

Set up your drop-down list the normal way (Data Validation, Allow = List). In a helper column next to it, use a formula like =CONCATENATE(A1, ", ", B1, ", ", C1) to combine multiple cells into one. This only works if each person picks one item per cell, so you would need one column per possible selection. It is clunky but it works when macros are not available.

A better formula workaround uses a helper column with a formula that checks if an item is already in the list and adds it only if it is not there. This prevents duplicates. The formula is complex enough that you may want to search for "Excel multi-select without macro" and follow a step-by-step guide for your specific Excel version, since the exact formula changes between Windows and Mac versions.

Troubleshooting common problems

If the macro does not run when you pick from the drop-down, check that you saved the file as .xlsm. Also check that macros are enabled. Go to File > Options (or Preferences on Mac) > Trust Center > Trust Center Settings > Macro Settings and choose Enable All Macros (or the second option if you want to be asked each time).

If the macro asks for an item name every time you click the cell, even when you are not trying to add anything, you may have pasted the code into the wrong place. Delete it and make sure you are pasting into a Module, not directly into the sheet code. Right-click the sheet tab, choose View Code, and delete anything you see there. Then follow the steps above again, making sure to insert a Module first.

If your list is very long and you want to pick from it without typing, use the formula workaround instead. The macro method works best with lists of 10 to 20 items.

When to use this instead of other approaches

Multi-select drop-downs are useful for tracking attributes or assignments, but they are not the best tool for everything. If you are building a database where you need to search or filter by individual selections (like finding all rows where "Red" was picked, even if it was picked alongside other colors), a proper database or a pivot table will serve you better than a single cell with comma-separated values.

If you are sharing the file with people who do not know how to enable macros, or if your workplace blocks them, the formula workaround or a straightforward note asking people to type items separated by commas may be more practical than fighting with macro settings.

Frequently Asked Questions

Can I use this in Google Sheets?

Google Sheets does not support the same macro language as Excel. You can create a multi-select drop-down in Google Sheets using a script, but it requires more technical knowledge. For most people, Google Sheets' standard data validation (one selection per cell) is simpler to use.

What happens if someone types something that is not on the list?

The macro does not stop them. If you want to enforce that only list items can be entered, you need to add extra code to the macro that checks each entry against your approved list. This is more advanced and usually not necessary unless you are building a strict data entry form.

Can I delete one item from the list without deleting the whole cell?

Not easily with the macro method. You would have to clear the cell and start over. If you need to edit individual selections often, the formula workaround or a database tool would be more practical.

Does the macro work on Mac?

Yes, the code above works on both Windows and Mac Excel. The keyboard shortcut to open Visual Basic is Option + F11 on Mac instead of Alt + F11. Everything else is the same.

What if I share the file and the other person does not have macros enabled?

The drop-down will still work, but the macro will not run. They will only be able to pick one item. When they open the file, Excel will usually ask if they want to enable macros. If they click Yes, the multi-select feature will work for them too.