How can we add a dropdown list in Excel? What if we want the dropdown list to change depending on another cell value? In this article, we will go through how to add a dropdown list in Excel. We will also look at how to have a dropdown list that users can overwrite. In addition we will go through how to add an alert or warning message when users wish to enter free text into a cell with dropdown list. And before the end, we will go through how to remove a dropdown list and also discuss the benefits of using a dropdown list.

How To Add A Dropdown List

We will first go through how to add a basic dropdown list:

1. Select the cell or cells we would like to create a dropdown list for
2. Go to Data tab at the top
3. Click on Data Validation (see image below):
   
   
   
4. Allow: List
5. For Source, there is a few ways we can decide what should be in the dropdown list:
   1. Type out the list and separate each option with a comma (e.g. blue, red, yellow, green)
   2. Select the cells as a reference if the list is in the file. A benefit of this is that if the values of those cells are updated, the dropdown list will update automatically (see image below):
      
      
      
   3. Reference by using Name Managers is also an option. Remember to put an equal sign before the name manager otherwise the name manager itself will become the dropdown list:
      
      

We definitely prefer using Name Managers or cell references. Whichever way you would like to create your dropdown list, here’s the result:

A Dropdown List That Can Be Overwritten

With a dropdown list, you may or may not want the free text option to type in whatever you want into the cell. This means you can have either:

1. A dropdown list and users must only select from the list
2. A dropdown list but also give users the option to enter free text

There are definitely benefits with not allowing users to enter free text but there are times when it might be helpful. For example, it could be useful to have the most common selections available in the dropdown list but also allow users to type in their responses, should the value they want to enter is not in the dropdown list. By default, an error message will appear if we try to enter a value that is not listed as one of the options in the dropdown list:

Luckily, we can change this. First, we go back into Data Validation. And in the Error Alert tab, make sure the “Show error alert after invalid data is entered” is not selected:

Click OK to save.

Once that is done, we can now enter anything we want into the cell:

Alert or Warning Message

In the section above, we went through how we can allow free text from users when there is a dropdown list. In this section we will show how we can allow users to enter free text into a cell that has a dropdown list but have a warning or alert message pop up whenever free text is entered.

To do this, we go back into Data Validation > Error Alert tab:

This time we leave the “Show error alert after invalid data is entered” box ticked and underneath that, we have:

• Style: Warning
• Error message: this is a free text field. You can have whatever error message pop up whenever invalid data is entered

Now let’s test this out again. When we enter “Brown” which is not one of the options in the dropdown list, we will see the following message:

The benefit of this is that it would encourage users to check what they’ve put in. But it is not so restrictive that entering a different value becomes impossible.

How to Remove a Dropdown List

Removing a dropdown list is very simple. Simply go back into Data Validation and for Allow:

• Instead of List, select Any value:

This will remove the dropdown list and we will be allowed to enter any value we want into the cell.

Benefits of Using Dropdown List

The biggest benefit with using dropdown list is it limits what users can enter into the cell. It gives more predictable results and makes data easier to collate and analyze later on. Imagine you have a spreadsheet which will be used by several different users, even for simple data where you expect people to give a rating out of 10, you could end up getting data such as “1” or “one”, “unsure”, “low”, “19”. Data like that will take a long term to clean up.

This is it so far with dropdown list. We hope you find this article useful. If we have missed anything or if you have any further questions on creating a dropdown list, please leave a comment below!