How to Select Multiple Data Validation in Excel

Excel's data validation is a fantastic way to control what users enter into a cell, especially with dropdown lists. However, its one limitation - letting you pick only one item at a time - can be a real roadblock. In this tutorial, you'll learn a powerful workaround using a snippet of VBA code to create a dropdown list in Excel that allows you to select and display multiple items in a single cell.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

What's Wrong with Standard Data Validation?

First, let's quickly cover how a standard data validation list works and where it falls short. A normal dropdown list is great for single-choice scenarios, like selecting a status ("To Do," "In Progress," "Done") or assigning a single person to a task. You set up a source list of options, point the data validation in a cell to that list, and you're good to go.

The problem arises when you need to assign multiple attributes. Imagine you're managing marketing campaigns and want to tag each one with several categories in a single cell, like:

• Campaign: Summer Sale 2024
• Tags: "Email, Social Media, Q3"

Or perhaps you need to assign multiple team members to a specific project:

• Project: Website Redesign
• Assigned To: "Sarah, Mike, Jessica"

With a default data validation dropdown, you can only pick "Email," "Sarah," or "Q3." The next time you click the dropdown and select a new item, it overwrites the previous one. This is where a little bit of macro magic comes to the rescue.

The Solution: A VBA-Powered Multi-Select Dropdown

To break free from the one-item limit, we can use Visual Basic for Applications (VBA), Excel’s built-in programming language. Don't worry if you've never written a line of code before. You'll be able to copy and paste the code directly, with just a couple of minor tweaks to aim it at the right cell.

By adding this code to your worksheet, you can transform a standard dropdown menu into a smart multi-select list. Each time you select an item from the list, the code will check if it's already in the cell. If not, it adds it. If it’s already there, it removes it. The result is a clean, comma-separated list of your selections - all within a single cell.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Before You Start: Save as a Macro-Enabled Workbook

Because this solution uses VBA, you must save your Excel file as a macro-enabled workbook. Otherwise, your code will be stripped from the file the next time you save it.

Go to File > Save As, and from the "Save as type" dropdown menu, choose Excel Macro-Enabled Workbook (*.xlsm).

With that out of the way, let's build it.

Step-by-Step: How to Create the Multi-Select List

Follow these steps carefully to set up your interactive, multi-select dropdown list from scratch.

Step 1: Create Your List of Options

First, you need the master list of items that will appear in your dropdown. For better organization, it's best practice to put this list on a different worksheet. Let's create a new sheet called "Lists."

In the "Lists" sheet, type your options into a single column. For this example, let's create a list of project categories in cells A1 through A5:

• Social Media
• Email Marketing
• SEO
• Paid Ads
• Content

Step 2: Create a Regular Data Validation Dropdown

Now, go back to the sheet where you want the dropdown list to appear (e.g., "Sheet1").

1. Select the cell where you want your multi-select dropdown. For this guide, we'll use cell B2.
2. Go to the Data tab on the Ribbon and click Data Validation.
3. In the Data Validation dialog box, under the "Settings" tab:
4. Click OK.

At this point, you'll have a standard dropdown list in cell B2. If you try to use it, you'll see it still only allows one selection at a time. That’s perfectly fine - the VBA code we add next will give it its new multi-select powers.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 3: Open the VBA Editor and Add the Code

This is where the magic happens. We need to add the VBA code directly into the code module of the worksheet containing your dropdown list.

1. Right-click on the tab of the sheet where you created the dropdown (e.g., "Sheet1").
2. From the context menu, select View Code. This will open the VBA Editor directly to that worksheet's module.
3. Copy the code below and paste it into the white code window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
'== CODE BY GRAPHED TUTORIALS ==

' --- Start of customizable section ---
' Define the cell that has our multi-select dropdown
Const MultiSelectCell As String = "B2"

' Define the separator between items
Const Separator As String = ", "
' --- End of customizable section ---

Dim OldValue As String
Dim NewValue As String

If Target.Address = Me.Range(MultiSelectCell).Address Then
    ' Do nothing if the validation was removed or is not a list
    If Target.Validation.Type <> 3 Then Exit Sub

    ' Get the new value from the dropdown selection
    NewValue = Target.Value

    ' Immediately turn off events to prevent this code from triggering itself
    Application.EnableEvents = False
    
    ' Undo the change Excel just made so we can control it ourselves
    Application.Undo

    ' Get the value that was in the cell *before* the change
    OldValue = Target.Value

    '--- Logic to add or remove the item ---
    If OldValue = "" Then
        ' If the cell was empty, just put the new value in
        Target.Value = NewValue
    Else
        ' If the new item is NOT already in the list, then ADD it
        If InStr(1, OldValue, NewValue) = 0 Then
            Target.Value = OldValue & Separator & NewValue
        ' If the new item IS already in the list, then REMOVE it
        Else
            ' Handle removing the first item in the list
            If InStr(1, OldValue, NewValue & Separator) > 0 Then
                Target.Value = Replace(OldValue, NewValue & Separator, "")
            ' Handle removing other items from the list
            Else
                Target.Value = Replace(OldValue, Separator & NewValue, "")
            End If
        End If
    End If
End If

' Re-enable events so Excel works normally again
Application.EnableEvents = True
End Sub

Step 4: Customize the Code (Just Two Lines!)

The code is designed to be easy to modify. You only need to look at two lines in the "customizable section" at the top.

• Const MultiSelectCell As String = "B2" Change "B2" to the cell address where your dropdown list is located — for example, "C5" or "D10".
• Const Separator As String = ", " This controls how the selections are separated. By default, it's a comma followed by a space. You could change this to a semicolon ", " or a pipe " | " if you prefer.

Once you've made your changes, close the VBA editor by clicking the "X" in the top-right corner. The code saves automatically in that sheet's module.

Step 5: Test Your New Multi-Select List

Head back to your Excel sheet. Remember to save the file as an .xlsm workbook first!

Now, test it out:

• Select "Email Marketing" from the dropdown in cell B2. The cell should display "Email Marketing."
• Select "SEO" from the dropdown. Cell B2 should now display "Email Marketing, SEO."
• Select "Email Marketing" again. The macro recognizes it's already there and removes it, leaving just "SEO."

You now have a fully functional multi-select data validation list!

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

A Non-VBA Alternative: Using Form Control Checkboxes

If you work in an environment where macros are a no-go, or you simply prefer to avoid VBA, there's another "low-code" way to achieve a similar result using checkboxes and a formula. This method is more manual but gets the job done.

Step 1: Add the Developer Tab to Your Ribbon

If you don't have it already, you'll need the "Developer" tab.

1. Go to File > Options > Customize Ribbon.
2. In the right-hand list under "Main Tabs," check the box for Developer. Click OK.

Step 2: Insert and Link Your Checkboxes

For each item on your list, you'll insert a checkbox and then link it to a helper cell.

1. On the Developer tab, click Insert, and under "Form Controls," select the Check Box icon.
2. Click on your sheet to place the first checkbox. Right-click the checkbox to edit the text and type the name of your first option (e.g., "Social Media").
3. Right-click the checkbox again and select Format Control.
4. In the "Control" tab, click in the Cell link box and select an empty cell nearby (e.g., C2). Click OK. Now, when you check the box, cell C2 will display TRUE, when unchecked, it will display FALSE.
5. Repeat this process for all your options, linking each one to a unique cell (C3, C4, etc.).

Step 3: Combine Selections with a Formula

Now, use a formula to gather all the selected options into a single cell. Let's say your checkbox text labels are in E2:E6, and your linked cells showing TRUE/FALSE are in C2:C6.

In the cell where you want the combined output, enter a formula. For Excel 2019 and newer, TEXTJOIN is perfect for this:

=TEXTJOIN(", ", TRUE, IF(C2:C6, E2:E6, ""))

This formula looks at the TRUE/FALSE values in C2:C6. For every TRUE value, it grabs the corresponding text from E2:E6 and joins them together with a comma and a space, ignoring any that aren't selected (FALSE).

Final Thoughts

While Excel's standard data validation only handles single selections, this tutorial shows two different ways to overcome that limit. The VBA method creates a seamless and intuitive multi-select dropdown that feels like a native feature, and the checkbox method offers a reliable macro-free alternative for simpler use cases.

Relying on helper columns, formulas, and VBA code to get your reports in order is a common part of an Analyst's job, but it can quickly become cumbersome. We built Graphed to simplify this entire workflow. Instead of manually wrangling data in spreadsheets, you can just connect your data sources (like Google Analytics, Shopify, Databases, and others) to our platform and ask for the charts and insights you need in plain English. There's no more exporting CSVs or fighting with macros - just clear, real-time dashboards delivered in seconds.