How to create multi select dropdown in excel

The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates.

One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel. When you create a drop-down list, you can only make one selection. If you select another item, the first one is replaced with the new selection. He wanted to make multiple selections from the same drop down in such a way that the selections get added to the already present value in the cell. The only way is to use a VBA code, which runs whenever you make a selection and adds the selected value to the existing value.

How to create multi select dropdown in excel

Normally, a dropdown list in Excel only lets the user select one of its options at a time. However, sometimes we might need to select and display multiple options from the list. For instance, a dropdown list could contain values like table tennis, chess, basketball, football, etc. Now, if the user wants to take part in multiple sports, the dropdown needs to accept more than one value. His articles mainly involve helping new users to quickly familiarize themselves with the Excel interface and explaining various essential features. While he got introduced to Excel in his early school days, he developed a keen interest in it after working on a college project. He was impressed at how quickly one could accomplish several tasks with built-in functions like the filter function and user-friendly tools like the power query. Keeping beginner audiences in mind, he loves to explain even the most fundamental Excel concepts in detail and break down complex topics with a step-by-step approach. As an avid Excel user, he believes every task can be done a lot quicker if you know the right tools and techniques. Facebook Twitter Instagram. Step 1: Make a Dropdown List Select the cell s where you want to have the dropdown list.

You need to keep the cells with drop down unlocked.

Excel drop-down lists are a fantastic tool for ensuring data consistency and ease of entry. However, by default, they restrict you to selecting just one item. But what if you need to select multiple items from the same drop-down list? This comprehensive guide will explore methods to enable multiple selections in Excel drop-down lists, manage duplicates, set custom separators, and define the scope of these lists. This section provides two methods to help you enable multiple selections in drop-down list in Excel. The script can modify the behavior of a drop-down list to make it a multiple-choice list. Please do as follows.

If you work with Excel regularly, you probably know how tedious it can be to enter repetitive data manually. They allow you to input data quickly and consistently, reducing the chance of errors. A drop-down list in Excel is essentially a form control that allows users to select a value from a pre-defined list. It is commonly used for data entry and validation purposes, as it ensures the data is accurate and consistent. The drop-down list appears in a cell, and when the user clicks on it, a list of options is displayed. The user can then choose a value by clicking on it, and the selected value is entered into the cell. Drop-down lists can also be used to create dependent lists, where the options in one list change based on the selection made in another list. This is useful for organizing and categorizing data, and can save time by reducing the need for manual data entry. Additionally, drop-down lists can be customized with different fonts, colors, and sizes to match the overall design of the Excel spreadsheet.

How to create multi select dropdown in excel

The article shows how to create an Excel drop down that allows users to select multiple items with or without duplicates. Excel has come a long way since its inception and introduces more and more useful features with each new release. In Excel , they've added the ability to search within data validation lists, which is a huge time-saver when working with large sets of data. However, even with this new option, out-of-the-box Excel still only allows selecting one item from a predefined list of options.

Toni camille naked

For example, if you drop down is in cell D2, then change it to D2. How to get the count of selected items instead of those values? Search Box - Contains In Excel and later, type in the filter Search box, just above the list of filter options. This is the code from your website I am using now: Option Explicit. Thank you, this has been very helpful. Can that be done. But I can't look in your workbook and your Excel to find the cause of the problem. Try installing the code in a new workbook or downloading the sample file from the link at the end of the article. You are wonderful, and this material is so helpful. I am allowing customization within my dropdown list selection. I assume it has to do with the way I am tying is into the sheet 2.

One of my colleagues asked me if it is possible to make multiple selections in a drop-down list in Excel. When you create a drop-down list, you can only make one selection.

For this, find this line of code:. We do allow the user to enter a new value if needed. Thank you so much for this. Hi Sumit, thanks for the codes. Both happens the same… the code is gone after I reopen my file. Is this possible at all? Is there a way to have the backspace key actually remove the value you want deleted? However, I want to display only the count. I don't really understand what the filter has to do with the theme of this article. How do I overcome this? Please be kind to help Best Regards. Free Download