You must have heard or even took tuition of basic computers in which you are taught MS – Office and a few other things. But, considering MS-Office programs as basic is completely wrong because they are very useful, and can do bigger jobs. Especially, MS Excel is one of the most powerful software programs in the world and is highly being used for managing data from smaller to bigger organizations. It is just that you need to know the capacity of this software program and know how you can make most out of it.

There is much more to write about Excel, but today I am going to focus on one Topic, “How to create Drop Down List in Excel”. In the versions, 2010, 2013, and 2016, creating drop down is similar. So, you can consider this guide for all these versions.

What is a Drop-Down List?

Drop Down list is a kind of list that is not visible at first but when you click on the Drop down icon, you can see the items of the list. You can make a selection from that. You must have seen this while registering on various websites. Even, when you click open RUN on windows and click on the icon, you see the list. That is exactly what we say a drop-down list.

Drop Down list in excel are similar as well, but you must know how to create such drop-down list in MS Excel to use it. Let’s check out the steps to do.

Steps to Create a Drop-down List in Excel 2010

1. First of all, we need to put some data into. For example, I have kept a few students names, and I want to show a drop down that will have the color names. So that, the favorite color of those students can be selected. So, for the dummy data, in Column A, there is a list of student, and in the B, I kept the heading as “favorite color”, and rows below this are empty.

 

2. Now, anywhere in that sheet, make a list of colors which we can use as a source.
   
3. We are done with putting up the required data in the excel sheet. Now, the real work starts. Now, put the selector in B2 cell, and go to Data >> Click Data Validation.
   
   
4. This will open a dialogue box titled as “Data Validation”. In that click “Settings” where you will see a drop –down, from which select List.
5. As you click List, you will see another option appears “Source” which you have to use to select the source data. In our example, this will be the list of colors we kept in the sheet.
6. So, click the selector from the Source option, and select the range of the list of colors, and hit enter. You will see this:

In this, you can see, the source has the reference of the list of colors. Now, click “Ok”. And, you are done.

You will see a Drop-down icon on the cell “B2”

Now, you can paste the same in B3, B4, B5, so that same drop down will be created there too.

So, now when you click on these cells, you will see the drop down option. Just click to select the desired color.

That is it.

But, now there is one question arises can we select multiple options in one cell?

Unfortunately, the answer is No.

But, we can make it multiple selection drop-down using VB code which is a bit technical but not too technical.

How to Create drop-down list with multiple selections in Excel

So, to make multiple selections drop-down list in Excel will just take a few more seconds and efforts. Let’s check out the steps:

1. On the same Excel sheet, right click on the sheet1 tab, and click View Code (Shortcut: Alt + F11 or Fn +Alt+ F11 )
2. This will launch the VBA project that shows the code where you can code to get anything done in excel programmatically.

You will see the interface as:

Make sure Sheet 1 is selected as we are working in Sheet 1 and have to apply the code on that only. If you are using any other sheet, select accordingly.

3. Now, it needs to code but considering you are not a programmer; you can find the code n the internet. For making things easier for you, I got the code from one of the good sites.

Private Sub Worksheet_Change (ByVal Target As Range)

Dim Oldvalue As String

Dim Newvalue As String

Application.EnableEvents = True

On Error GoTo Exitsub

If Target.Address = “$B$2” Then

  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

  Else: If Target.Value = “” Then GoTo Exitsub Else

    Application.EnableEvents = False

    Newvalue = Target.Value

    Application.Undo

    Oldvalue = Target.Value

      If Oldvalue = “” Then

        Target.Value = Newvalue

      Else

        If InStr(1, Oldvalue, Newvalue) = 0 Then

            Target.Value = Oldvalue & “, ” & Newvalue

      Else:

        Target.Value = Oldvalue

      End If

    End If

  End If

End If

Application.EnableEvents = True

Exitsub:

Application.EnableEvents = True

End Sub

Code snippet is copied from TrumpExcel with some changes.

Now, save the VBA project just by pressing ctrl + S or save button in the toolbar.

4. Now, return back to the sheet, and try selecting multiple colors. You will be amazed, that you are now able to do that but you cannot select repeated option. I mean you cannot select tow colors. Moreover, with some tweaks in the code, you can do that too.

Apart from these, you can customize your drop-down in excel by various techniques. For example, you can show some input message for drop-down to give instruction to the user.

How to Display Input Message for Drop-Down in Excel?

For this, keep the cursor on the cell in which you applied drop-down. Click Data => Data Validation. Then click “Input Message” tab.

Add the Title, and Input message, and click OK. This will result into this:

Similarly, you can go to “Error Alert” tab to set error message that shows when wrong input is entered.

How to create Excel Drop Down List With Color?

coloring is good to differentiate in the cells. Let’s take our existing sheet, and implement colors on that.

Select B2 cell, Go to Home tab in the Menu, and there click Conditional Formatting >> New Rule

You will see a dialogue box there. In that, select “Format only cells that contain”. Then in the rule description, choose “specific text”, and then write there “red”.

Now, click Format, and select the Red color under the Fill tab, and click Ok to save the settings.

That’s it for the red color. Now, since we have four different color options in our sheet. Repeat this process for blue, pink, and green as well.

Now, copy the rules in all the cells with drop-down so that it gets applied on all of that. It can be done just by copying and pasting B2 cell in rest of the cells.

Now, when you select the color, the cell will turn to that color as you can see in the below screenshot

Hope you have enjoyed this MS Excel guide, and if you like this, please write in the comments, so that I would bring more such stuff for you.

If you have any questions to ask, please ask in the comments.

Thanks for reading! 🙂