How to Create a Drop-down List in Excel - Compute Expert

How to Create a Drop-down List in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> How to Create a Drop-down List in Excel





In this tutorial, you will learn how to create a drop-down list in excel in the right way.

When working in excel, we sometimes need to restrict the inputs in some cells so they don’t go out of hand. We might need that so our data processing will be much easier later or so there won’t be any crazy input.

One of the most popular choices to restrict input in excel is to use the drop-down feature. We can create various kinds of drop-down, from the simple one to the more complicated one. It purely depends on our needs which kind of drop-down we should use.

Want to master the creation of a drop-down list in excel so you can create the best one for yourself? Learn from this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







How to Create a Drop-down List in Excel Manually

Let’s start the tutorial by discussing about how to create the most basic form of drop-down list in excel. It is a drop-down list with choices that we type manually into the drop-down.

The process to create this kind of drop-down list in excel isn’t too hard. Here is the step-by-step to do it.

  1. Select the cell where you want to create a drop-down list

    How to Create a Drop-down List in Excel - Screenshot of Step 1

  2. Go to the Data tab and click the Data Validation button there

    How to Create a Drop-down List in Excel - Screenshot of Step 2

  3. In the dialog box that shows up, make sure you are in the Settings tab

    How to Create a Drop-down List in Excel - Screenshot of Step 3

  4. In the Settings tab, click the Allow dropdown and select List

    How to Create a Drop-down List in Excel - Screenshot of Step 4

  5. In the source text box that shows up, type the choices you want for your drop-down list. Separate each of the choices you type with a comma sign ( , )

    How to Create a Drop-down List in Excel - Screenshot of Step 5

  6. After you have typed all the choices you want for your drop-down list, click OK

    How to Create a Drop-down List in Excel - Screenshot of Step 6

  7. Done! Now, your cell should show a drop-down button when you select it. When you click the drop-down button, you can choose an input for the cell from a drop-down list

    How to Create a Drop-down List in Excel - Screenshot of Step 7-1

    How to Create a Drop-down List in Excel - Screenshot of Step 7-2

    How to Create a Drop-down List in Excel - Screenshot of Step 7-3




How to Create a Drop-down List in Excel with Choices from a Cell Range

Already have your drop-down choices in a cell range and want to input from there instead of typing them manually? You can do that easily in the drop-down source text box in the data validation dialog box.

On the right side of the source text box, you might notice that there is a button there you can click. Just click on it if you want to input a cell range as the source of your drop-down choices.

How to Create a Drop-down List in Excel - Screenshot of the Source Text Box Button Location 1

In the dialog box that shows up after you click the button, input your cell range by dragging on it. Excel will automatically input the cell range coordinate you drag.

How to Create a Drop-down List in Excel - Screenshot of the Cell Range Input Result Example by Dragging in the Source Text Box

Click the button in the text box of the dialog box after you finish inputting the cell range.

How to Create a Drop-down List in Excel - Screenshot of the Source Text Box Button Location 2

Then, click OK. Now, you have created a drop-down with the source of the choices from the cell range you set earlier!

How to Create a Drop-down List in Excel - Screenshot of the Example of a Drop-down with Choices from a Cell Range



How to Add/Remove Choices from a Drop-down List in Excel (Edit Drop-down List)

Need to edit the choices in the drop-down you have made? First, check whether you input them by typing them manually or by inputting a cell range.



If you typed the drop-down choices manually, then you need to go back to your cell’s data validation dialog box. In the source text box, you can edit your drop-down choices directly by deleting, re-typing, or typing new ones.

How to Create a Drop-down List in Excel - Screenshot of the Source Text Box Location to Edit Drop-down Choices Which We Type Manually

If you input them from a cell range, then you can go to the cell range and edit the choices there. Re-type the choices which you want to edit.

If you want to delete one/some of them, highlight the choice cells and then right-click on them. Choose Delete… to show a Delete dialog box.

How to Create a Drop-down List in Excel - Screenshot of the Delete... Choice Location in the Cell Right-Click Menu

Choose Shift Cells Up in the dialog box if you want to move up the cells below after you delete. Choose Shift Cells Left if you want to move the cells on the right to the left after you delete.

How to Create a Drop-down List in Excel - Screenshot of the Shift Cells Up and Shift Cells Left Choices Locations in the Delete Dialog Box

You have removed the choices you highlight from your dropdown!

If you want to add the choices in the cell range, then do the opposite. Highlight the number of cells in the cell range which corresponds to the number of choices you want to add. Then, right-click on them and choose Insert… to show an Insert dialog box.

How to Create a Drop-down List in Excel - Screenshot of the Insert... Choice Location in the Cell Right-Click Menu

Choose Shift Cells Down if you want to move down the cells you highlight to insert cells for your new choices. Choose Shift Cells Right if you want to move them to the right. The one you should choose should depend on the cell range form you have.

How to Create a Drop-down List in Excel - Screenshot of the Shift Cells Down and Shift Cells Right Choices Locations in the Insert Dialog Box

Insert the cells and type your drop-down additional choices on the empty cells you have just inserted. After that, your drop-down should contain those choices in its list!



How to Allow Other Entries in the Cell with a Drop-down List

By default, you cannot input anything other than the dropdown choices if your cell has a drop-down. If you need to allow other inputs, however, then you need to set the allowance in the cell’s data validation settings.

To allow that, highlight the cell with the drop-down, go to the Data tab, and click the Data Validation button. In the Data Validation dialog box, go to the Error Alert tab. Then, remove the checkmark in the “Show error alert after invalid data is entered” check box by clicking on it.

How to Create a Drop-down List in Excel - Screenshot of the Error Alert Tab and Show error alert after invalid data is entered Checkbox Locations

Click OK after that. Now, your cell will allow inputs other than the ones in its dropdown choices!



How to Create a Yes No Drop-down List in Excel

To create a yes/no drop-down list in your cell, input yes and no as your drop-down choices. You can type them directly in your drop-down list source text box or input them via a cell range.

How to Create a Drop-down List in Excel - Screenshot of the Yes and No Input in the Source Text Box as the Drop-down Choices

Do that and you should have a drop-down list in your cell that contains yes and no choices.

How to Create a Drop-down List in Excel - Screenshot of the Yes No Drop-down Example



How to Create a Conditional/Dependent Drop-down List in Excel

From this tutorial part, we will begin the discussion to create a more complex drop-down list in excel. The first one to discuss is creating a conditional/dependent drop-down list in your cell.

You may sometimes need to create a drop-down list which choices depends on another cell value. A simple example of this is city drop-down choices that might depend on the country cell value. If you choose a different country, then the city drop-down choices should change.



How to create something like that in excel? To create this kind of drop-down list, we should use the combination of INDIRECT, SUBSTITUTE, and cell range names.

We name each cell range which contains the scenarios and the drop-down choices we have for each scenario (country names and city names for each country for the case of countries and cities). Then, we use the scenario name as the source in the cell which value our other drop-down choices depend on. Finally, we combine INDIRECT and SUBSTITUTE to convert the cell value to the named cell range for the drop-down choices.

Got confused with the explanation? Follow the discussion of the example below to understand the concept better!

Let’s say we have two drop-downs we want to create in our cells below. The drop-down choices for the “What Food?” depend on the food category value above it.

How to Create a Drop-down List in Excel - Screenshot of the Cells for the Conditional/Dependent Drop-down Creation Example

In a different sheet, we have the references for the food categories and their food choices.

How to Create a Drop-down List in Excel - Screenshot of the References for the Conditional/Dependent Drop-down Creation Example

What should we do to create two drop-downs? Well, first we name the cell ranges that we need for our drop-downs in the reference sheet. In this example, we should name the cell ranges for the food category and food choices in each category. The name of the food choices cell ranges should be the same as the name of their food category.

We have four names after we name all the cell ranges we need in the example.

How to Create a Drop-down List in Excel - Screenshot of the Cell Range Names for the Conditional/Dependent Drop-down Creation Example

After we finish the names, we go back to the sheet where we want to create our drop-downs. In the food category cell, we create a drop-down with our “food_category” name as its data validation source. We do that by typing “=food_category” in the data validation source text box.

How to Create a Drop-down List in Excel - Screenshot of the Cell Range Name Input as a Drop-down Choices Source Example

Do that and the cell drop-down choices will become the same as the values in our food_category cell range.

How to Create a Drop-down List in Excel - Screenshot of a Drop-down Example with a Cell Range Name as Its Choices Source

Next, we create the dropdown for our second cell. For this, we write the combination of INDIRECT and SUBSTITUTE in its data validation source text box.

How to Create a Drop-down List in Excel - Screenshot of the INDIRECT and SUBSTITUTE Combination Example for a Conditional/Dependent Drop-down Choices Source

We use SUBSTITUTE inside our INDIRECT here since we want to anticipate spaces in our cell value. Since we cannot name our cell range with spaces, we should substitute them with underscores ( _ ). SUBSTITUTE will handle the substitution process for us in the data validation source.

The INDIRECT here is important because it can convert text into a formula reference. Without using INDIRECT, excel won’t recognize our cell value as a cell range name.

We do all that and we will get a conditional/dependent drop-down in our cell! Now, whenever we change the cell value that becomes our drop-down reference, our drop-down choices change too.

How to Create a Drop-down List in Excel - Screenshot of the Conditional/Dependent Drop-down List Example 1

How to Create a Drop-down List in Excel - Screenshot of the Conditional/Dependent Drop-down List Example 2



How to Create a Drop-down List with a Default Value in Excel

Need to have a default value for your drop-down before a person selects a choice from it? You need to create your drop-down first for this. After your drop-down is ready, you can follow these steps.

  1. Highlight the cell where your drop-down is

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 1

  2. Go to the data validation dialog box by going to the Data tab and click the Data Validation button there

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 2

  3. Allow other entries in your cell by unchecking the check box in the Error Alert tab (click on it if it has a checkmark). Then, click OK

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 3

  4. Type the default value you want for the drop-down

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 4

  5. Go back to the data validation dialog box to check the checkbox in the Error Alert tab again (click on the checkbox). Click OK

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 5

  6. Done! Now, the cell with the drop-down shows up a default value before we select a drop-down choice there

    How to Create a Drop-down List in Excel - Screenshot of the Creation of a Drop-down List with a Default Value, Step 6




How to Create a Dynamic Drop-down List in Excel

When we refer to a cell range as the source of our drop-down choices, we may need a dynamic reference. This means when we input new data to the cell range, we want the dropdown choices to automatically include it.

We can do that but we cannot just input the cell range as the dropdown choices source. We should use the combination of OFFSET and COUNTA when we input the source of the dropdown choices.



Here is the general writing form of the OFFSET and COUNTA combination for the dynamic drop-down choices source.

= OFFSET ( first_cell_of_the_cell_range , 0 , 0 , COUNTA ( expanded_cell_range ) , 1 )


This formula writing assumes you have a column cell range for your drop-down list. If you have a row cell range, you can swap the position of COUNTA and 1 there. The 1 input here means that OFFSET won’t expand the cell range horizontally, only vertically (for a column cell range).

OFFSET can expand the cell we input depending on the other inputs we give to it. We write COUNTA in the OFFSET input part which asks how many cells it should expand our cell vertically. In the other inputs, we input something that makes the cell in OFFSET not move (0 and 0 for the inputs of how far we want the cell to move).

As COUNTA counts all data in its cell range, OFFSET will expand our cell depending on the data it has! Therefore, we should input our cell range to COUNTA by including the cells where we will input our new data too.

Here is an implementation example of the dynamic drop-down list concept by using the OFFSET and COUNTA combination. Let’s say we have this drop-down below.

How to Create a Drop-down List in Excel - Screenshot of the Drop-down for the Dynamic Drop-down Creation Example

The drop-down choices source comes from the cell range in another sheet.

How to Create a Drop-down List in Excel - Screenshot of the Drop-down Choices Source for the Dynamic Drop-down Creation Example

To make the drop-down includes new data we give to the cell range later, we write this as its source.

How to Create a Drop-down List in Excel - Screenshot of the OFFSET and COUNTA Combination Example in the Dynamic Drop-down Choices Source Input

In the OFFSET, we input the cell coordinate where our first drop-down choice is as the first input. In other OFFSET inputs besides COUNTA, we input 0, 0, and 1 as we discussed previously.

In the COUNTA, we input the cell range that we expand to include all cells where we will input new data. Thus, we input the cell range from the first cell in our cell range until the last row in the worksheet!

As a result of the OFFSET and COUNTA combination, we can update our drop-down choices easily. Just enter new data in its cell range source and we are good to go!

How to Create a Drop-down List in Excel - Screenshot of the New Data Example in the Dynamic Drop-down Cell Range Source

How to Create a Drop-down List in Excel - Screenshot of the Dynamic Drop-down Choices Example When We Add New Data in Its Cell Range Source



How to Create a Drop-down List with Color in Excel

Want to make your cell shows a specific color when you pick a specific choice from your drop-down list? We can create something like that by using the Conditional Formatting feature in excel.

Just set the rules in your cell’s conditional formatting that give each choice in your drop-down a certain color. To do that, first, highlight the cell with the drop-down list. Then, go to the Home tab, click the Conditional Formatting button dropdown, and choose Manage Rules….

How to Create a Drop-down List in Excel - Screenshot of the Home Tab, Conditional Formatting Button Dropdown, and Its Manage Rules... Choice Locations

In the dialog box that shows up, click the New Rule… Button.

How to Create a Drop-down List in Excel - Screenshot of the New Rule... Button Location in the Conditional Formatting Dialog Box

Choose “Format only cells that contain” in the Select a Rule Type box.

How to Create a Drop-down List in Excel - Screenshot of the Format only cells that contain Choice Location in the Select a Rule Type Box

In the part below the Select a Rule Type box, choose “Cell Value” and “equal to” in the dropdowns. Then, input the first choice of your drop-down either by typing it directly or by using a cell coordinate.

How to Create a Drop-down List in Excel - Screenshot of the First Drop-down Choice Input Example in the Conditional Formatting Dialog Box

Next, click the Format… button below.

How to Create a Drop-down List in Excel - Screenshot of the Format... Button Location in the Conditional Formatting Dialog Box

There will be a dialog box where you can format your cell if it contains the drop-down first choice. As you want to color it, go to the Fill tab and choose the color you want there.

How to Create a Drop-down List in Excel - Screenshot of the Fill Tab Location in the Conditional Formatting Dialog Box

Click OK and then OK again. You have added the color rule for your first drop-down choice!

How to Create a Drop-down List in Excel - Screenshot of the Color Rule Example for the First Drop-down Choice

Click the New Rule… button again. Now, repeat the steps above until you have given colors to all your drop-down choices.

How to Create a Drop-down List in Excel - Screenshot of the Color Rule Example for All Drop-down Choices

Click OK after you have done that. Now, your cell will change its color according to the drop-down choice you choose!

How to Create a Drop-down List in Excel - Screenshot of the Drop-down List with Color Example 1

How to Create a Drop-down List in Excel - Screenshot of the Drop-down List with Color Example 2



How to Manage a Drop-down List Font Size in Excel

The font size problem in a drop-down list usually happens if you use a Windows computer. You may sometimes find that the font size of your drop-down choices is too small to read.



How to change the drop-down choices font size? There are two things you can adjust when you need to change your drop-down choices font size.
  • The font size in the text in the same worksheet as the drop-down. Try to make it smaller so your drop-down font size can stand out more
  • The zoom level of your worksheet. Try to zoom in so you can read the font in your drop-down choices

Unfortunately, you cannot change the font size in your drop-down list directly. Therefore, try to play with the two variables we mentioned so you can get the right font size for yourself!



Drop-down List Shortcut in Excel

Need to select a choice from the drop-down in your cell without using a mouse?

You can press Alt + down (Option + down in Mac) to open the drop-down list in your cell. After that, just press the up or down button to toggle between the drop-down choices.

After you highlight the drop-down choice you want, press enter. You have inputted your cell from the drop-down list without using a mouse!



How to Remove a Drop-down List in Excel

Removing a drop-down list from your cell in excel is quite easy.

Just highlight your cell and open the Data Validation dialog box (go to the Data tab and click the Data Validation button there).

In the dialog box, click the Allow dropdown and choose Any Value.

How to Create a Drop-down List in Excel - Screenshot of the Any Value Choice Location in the Allow Dropdown

Then, click OK. You have removed the drop-down list in your cell!

How to Create a Drop-down List in Excel - Screenshot of the Drop-down Removal Result Example



Exercise

After you have learned how to create a drop-down list in excel, now it is time to do some exercise. This is so you can understand what you have just learned more practically.

Download the exercise file from the following link and do the instructions below. Download the answer key file if you have done the exercise and want to check your answers!

Link to the exercise file:
Download here

Instructions

Do each instruction in the appropriate gray-colored cell!
  1. Create a drop-down from the choices in column 1!
  2. Create a drop-down from the choices in column 2 using the OFFSET and COUNTA combination method! Add F, G, and H to column 2. Do they also automatically appear as your cell’s dropdown choices?
  3. Create a drop-down from the choices in column 3! Set the cell so it will color itself to your choice color when you pick a choice from its drop-down!

Link to the answer key file:
Download here



Additional Note

INDIRECT and conditional formatting aren’t case-sensitive when running their functions in excel.



Related tutorials you should learn:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert