How to create multi select dropdown in excel

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 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. But fear not, as there is a solution. By using VBA, you can create drop-down lists with multiple selections.

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. Click Data Validation under the Data tab. Select the Settings tab. Choose the List option under Allow and enable the In-cell dropdown checkbox.

You would need to change this line in the code: If Target. The code works without errors there. Hi, thanks for the great code.

Instead of limiting an Excel data validation drop down list to a single selection, you can use this macro to allow multiple selections. Video and written instructions, and get the free workbook to see how it works. This is the main page for the data validation multiple selection technique. I have also written several blog articles, and you can join in the discussion in the comments there:. To see how this technique works, and a few multiple selection examples, you can watch this short video.

Drop-down lists are very useful data entry tools we see just about everywhere, and you can add custom drop-down lists to your own Excel worksheets. A drop-down list in Excel is exactly like any other drop-down list you're familiar with — just click the arrow then select one of a few pre-determined values. Drop-down lists make it easier and more efficient to enter data into your spreadsheets. You can add drop-down lists to cells in Excel containing options such as "Yes" and "No", "Male" and "Female," or any other custom list of options, including numbers or special characters. Drop-down lists are created using the Data Validation feature in Excel — not particularly intuitive. We created three labeled columns : Name, Age, and Sex, and filled in two example names.

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. But fear not, as there is a solution.

Evkur şirinevler şubesi

I have followed all of the steps that you have provided and i am getting an error. Can i choose the columns to which this code should be applicable? I also added the column destination line as well as changing the comma delimiter with the drop line vbCtLf. The code works only with the dropdown list. I have the same question Hi there — I tried to use this code but it is not allowing me to add multiple values in 3 of my columns. Hi Amanda! The code below solves the problem of duplicates in a multi-selection data validation drop down list. That can make it difficult to filter for a specific item, such as "Two". Please be aware that including your real password in the code could lead to a security risk. Also, this code is set up for a list in column 3. Hi, Thanks for this helpful guide. It will work fine then.

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.

Hi, this worked perfectly, thank you. I even changed the Target. Thanks for the quick reply! If I change the address to If Target. The code worked great and I was so happy with finally being able to add my products within the same cell. Conditional Formatting. Is there a way I can correct the process above to allow me to select multiple names and not trigger a data verification error. Hi, If the sheet is protected, multiple drop down list is not working? Do you have the VBA code know where in the above listed one I can edit to make that happen? I am not sure I fully understand what you mean.