How to Create Dropdown Excel


Home >> How to Use Excel Tutorial >> Excel Tips and Trick >> How to Create Dropdown Excel



In this tutorial, you will learn about how to create dropdown excel. The understanding of how to create dropdown excel becomes important if you need to limit the input choice in a cell on this spreadsheet tool with the pre-determined options.


Why We Need to Learn How to Create Dropdown Excel?


In creating a form or program in this spreadsheet software, often we need to limit input which can be given by the program users later so the results after the filling process are exactly like we want. The input restriction is also useful so data grouping can be more accurate when we do data processing from the inputs.

For an example of the input control importance, imagine if we want to make a standard form for our company. If we do not limit inputs for some of its parts which need it in the form, then the content quite probably will not be up to standard because, in the filling process, the form of input given can be quite limitless.

Besides that example, imagine also if we want to survey to collect data by distributing some forms. If the form input to fill the survey is not controlled, then it will be very difficult for us to process data to conclude from the survey results because of many kinds of input variants will be given in it. It will take a lot of time to group our data. Besides that, it can be that the inputs from some of the respondents are not what we mean on the survey questions. That can happen because there is no input control.

In creating input restriction, if we want to use spreadsheets to create the document or form, one of the good options to be used is practicing how to create dropdown excel. By understanding how to create dropdown excel and use it, then we can control the input with the options that we have determined before in the form/document. Understanding how to create dropdown excel will make it much easier for the input collection and the data grouping after the filling process.


What is Excel Dropdown?


Excel dropdown is one of the functions in this spreadsheet software with the usage objective to control the input in a cell. How to create dropdown excel becomes one important thing to learn because of this, especially if we often create documents or forms that need inputs from people other than us.


How to Create Dropdown Excel?


The next part will explain in detail how to create dropdown excel. In this how to create dropdown excel guide, it will be explained how you can formulate the input options statically and dynamically (if you want the input choice to be added automatically in the dropdown when you add it in a cell).


How to Create Dropdown Excel with Static Choices

  1. Place cursor in the cell where you want to put the dropdown

    How to Create Dropdown Excel - Screenshot of Step 1-1

  2. Click Data Tab and choose Data Validation

    How to Create Dropdown Excel - Screenshot of Step 1-2

  3. In the Settings tab from the dialog box shown, choose List from below Allow: header

    How to Create Dropdown Excel - Screenshot of Step 1-3

  4. Place your cursor in the text box below Source: header and type all of your input options in there separated with comma sign between them or drag your cursor on all cells where the input options have been prepared before. If you place them on cells, though, all of the input options must be in one row or column

    How to Create Dropdown Excel - Screenshot of Step 1-4

  5. Optional: You can change your dropdown setting from the default option by changing it in the dialog box. There are three tabs with their own settings with the explanation as follows:
    • On Settings Tab:
      • On “Ignore blank” checkbox, check if you want the user to have the ability to delete cell content after picking choice until it is empty using backspace and do not check if you want the user to not have the ability to do it

        How to Create Dropdown Excel - Screenshot of Step 1-5-1-1

      • “In-cell dropdown” checkbox must be checked if you want the dropdown to show up on your cell. If it is not checked, then then user can only input by typing manually with the input restriction as you set it

        How to Create Dropdown Excel - Screenshot of Step 1-5-1-2

    • On Input Message Tab:
      On “Show input message when cell is selected” checkbox, check if you want a little note shown up when user place the cursors on the cell with the dropdown. Usually, this note can be used to give directions or explanations about the input expected from the user. Fill the text box with Title: header for your note title and and text box with Message: header for the content of your note if you decide to show the note

      How to Create Dropdown Excel - Screenshot of Step 1-5-2

    • On Error Alert Tab:
      On “Show alert after invalid data is entered” checkbox, check if you want message customization on the dialog box which shows up when the user directly input on the cell with dropdown and the input is not inline with your input restriction requirement. Fill text box with Title: header for your message title and a text box with Message: header for your message content if you decide to check the checkbox. For the one below Style header, there are three choices which are: Stop if you want your message to be absolute and the user must change input that is not in line with the input restriction requirement, Warning if you want the message to be a question and user can choose Yes to keep the input which is not in line with the input restriction requirement, and No to be forced to be inline and change the input, and Information if you want the message to be an information and user can still give input which is not in line with the input restriction requirement

      How to Create Dropdown Excel - Screenshot of Step 1-5-3

  6. Click OK

    How to Create Dropdown Excel - Screenshot of Step 1-6

  7. The how to create dropdown excel process is done!

    How to Create Dropdown Excel - Screenshot of Step 1-7




How to Create Dropdown Excel - Dynamic Choices Without Empty Cells

For how to create dropdown excel with dynamic choices, you must place all of your current dropdown choices and inputs for the new ones later in one column/row. Input for dropdown options using this how to create dropdown excel method must not leave empty cells among the first until last option

  1. Place cursor in the cell where you want to put the dropdown

    How to Create Dropdown Excel - Screenshot of Step 2-1

  2. Click Data Tab and choose Data Validation

    How to Create Dropdown Excel - Screenshot of Step 2-2

  3. In the Settings tab from the dialog box shown, choose List from below Allow: header

    How to Create Dropdown Excel - Screenshot of Step 2-3

  4. Place your cursor in the text box below Source: header and:
    1. Type equal sign ( = )

      How to Create Dropdown Excel - Screenshot of Step 2-4-1

    2. Type OFFSET (can be with large and small letters) and open bracket sign after =

      How to Create Dropdown Excel - Screenshot of Step 2-4-2

    3. Type the first cell coordinate in the column / row of your dropdown options. Ignore dollar sign that shows up when you input the coordinate by clicking the cell. Then type comma sign ( , )

      How to Create Dropdown Excel - Screenshot of Step 2-4-3

    4. Type 0 then comma sign then 0 then comma sign

      How to Create Dropdown Excel - Screenshot of Step 2-4-4

    5. Type COUNTA (can be with large and small letters) and open bracket sign

      How to Create Dropdown Excel - Screenshot of Step 2-4-5

    6. Drag cursor to all columns or rows where you place your dropdown options and where new options that will be inputted later. Ignore dollar sign which shows up when you finish the drag. Then type close bracket sign and comma sign

      How to Create Dropdown Excel - Screenshot of Step 2-4-6

    7. Type 1

      How to Create Dropdown Excel - Screenshot of Step 2-4-7

    8. Type close bracket sign

      How to Create Dropdown Excel - Screenshot of Step 2-4-8

  5. Optional: You can change your dropdown setting from the default option by changing it in the dialog box. There are three tabs with their own settings with the explanation as follows:
    • On Settings Tab:
      • On “Ignore blank” checkbox, check if you want the user to have the ability to delete cell content after picking choice until it is empty using backspace and do not check if you want the user to not have the ability to do it

        How to Create Dropdown Excel - Screenshot of Step 2-5-1-1

      • “In-cell dropdown” checkbox must be checked if you want the dropdown to show up on your cell. If it is not checked, then then user can only input by typing manually with the input restriction as you set it

        How to Create Dropdown Excel - Screenshot of Step 2-5-1-2

    • On Input Message Tab:
      On “Show input message when cell is selected” checkbox, check if you want a little note shown up when user place the cursors on the cell with the dropdown. Usually, this note can be used to give directions or explanations about the input expected from the user. Fill the text box with Title: header for your note title and and text box with Message: header for the content of your note if you decide to show the note

      How to Create Dropdown Excel - Screenshot of Step 2-5-2

    • On Error Alert Tab:
      On “Show alert after invalid data is entered” checkbox, check if you want message customization on the dialog box which shows up when the user directly input on the cell with dropdown and the input is not inline with your input restriction requirement. Fill text box with Title: header for your message title and a text box with Message: header for your message content if you decide to check the checkbox. For the one below Style header, there are three choices which are: Stop if you want your message to be absolute and the user must change input that is not in line with the input restriction requirement, Warning if you want the message to be a question and user can choose Yes to keep the input which is not in line with the input restriction requirement, and No to be forced to be inline and change the input, and Information if you want the message to be an information and user can still give input which is not in line with the input restriction requirement

      How to Create Dropdown Excel - Screenshot of Step 2-5-3

  6. Click OK

    How to Create Dropdown Excel - Screenshot of Step 2-6

  7. The how to create dropdown excel process is done!

    How to Create Dropdown Excel - Screenshot of Step 2-7-1



    How to Create Dropdown Excel - Screenshot of Step 2-7-2



    How to Create Dropdown Excel - Screenshot of Step 2-7-3



    How to Create Dropdown Excel - Screenshot of Step 2-7-4




How to Create Dropdown Excel - Dynamic Choices Consist of Empty Cells

For this how to create dropdown excel method, you must prepare first the row/column where you will put dynamic dropdown options before it is inputted in the Data Validation input part

Preparing Dynamic Dropdown Choices

  1. Create 2 tables with 2 columns for the first table and 1 column for the second table. Fill your current dropdown choices on the right column on the first table

    How to Create Dropdown Excel - Screenshot of Step 3-1-1

  2. On the left column in that table:
    1. On the row after the header row, type equal sign ( = )

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-1

    2. Type IF (can be with large and small letters) and open bracket sign after =

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-2

    3. Type cell coordinate in right column which is parallel with the cell where you type IF formula. Give condition evaluation which gives TRUE result if the content of the cell coordinate is empty (e.g. B1=“”) then type comma sign ( , )

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-3

    4. Type two quotes signs (represents that IF result will be empty if the condition evaluation which is inputted in previous part gives TRUE value) then type comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-4

    5. Type MAX then open bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-5

    6. Type header cell coordinate above the cell where you write this IF formula. Makes sure that the header cell content is not just number

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-6

    7. Give absolute reference sign for that cell coordinate (dollar sign ($) in front of the column letter and row number)

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-7

    8. Type two dots sign ( : ) then type header cell coordinate again, right above where you write this IF formula, then type close bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-8

    9. Type plus sign ( + ) then input number 1

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-9

    10. Type close bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-10

    11. Press Enter
    12. Drag to copy your IF formula in that column until the last row which you think will be the maximum quota where you will input the additional options for your dropdown

      How to Create Dropdown Excel - Screenshot of Step 3-1-2-12

  3. On the second table:
    1. In the row after the header row, type equal sign ( = )

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-1

    2. Type IFERROR (can be with large and small letters) and open bracket sign after =

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-2

    3. Type VLOOKUP (can be with large and small letters) and open bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-3

    4. Type ROW (can be with large and small letters) and open bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-4

    5. Type any cell coordinate as long as it is in the first row (e.g. A1) then type close bracket sign and comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-5

    6. Drag cursor on all cells in the two columns of the first table to input their cell range and type comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-6

    7. Type 2 as a symbol that the data which will be taken by this VLOOKUP formula later will be in the second column of the first table (the right column) then type comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-7

    8. Type FALSE because we want to get exact match for the lookup value (the lookup value is the result of previous ROW formula) then input close bracket sign and comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-8

    9. Type two quotes signs as a symbol that if the VLOOKUP formula results in error then the cell will be empty. Then type close bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-9

    10. Press Enter
    11. Drag to copy formula from your writing until the maximum quota row where you will input last additional choice for your dropdown, as you estimated on the first table

      How to Create Dropdown Excel - Screenshot of Step 3-1-3-11

  4. The choices have been prepared!

    How to Create Dropdown Excel - Screenshot of Step 3-1-4



Create Dropdown


  1. Place cursor in the cell where you want to put the dropdown

    How to Create Dropdown Excel - Screenshot of Step 3-2-1

  2. Click Data Tab and choose Data Validation

    How to Create Dropdown Excel - Screenshot of Step 3-2-2

  3. In the Settings tab from the dialog box shown, choose List from below Allow: header

    How to Create Dropdown Excel - Screenshot of Step 3-2-3

  4. Place your cursor in the text box below Source: header and:
    1. Type equal sign ( = )

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-1

    2. Type OFFSET (can be with large and small letters) and open bracket sign after =

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-2

    3. Type the first cell coordinate in the column of your dropdown options. Ignore dollar sign that shows up when you input the coordinate by clicking the cell. Then type comma sign ( , )

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-3

    4. Type 0 then comma sign then 0 then comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-4

    5. Type MAX (can be with large and small letters) and open bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-5

    6. Drag cursor on all the cells in the left column where you put your IF formula in the first table. Ignore dollar signs that show up when you have done the drag process. Then type close bracket sign and comma sign

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-6

    7. Type 1

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-7

    8. Type close bracket sign

      How to Create Dropdown Excel - Screenshot of Step 3-2-4-8

  5. Optional: You can change your dropdown setting from the default option by changing it in the dialog box. There are three tabs with their own settings with the explanation as follows:
    • On Settings Tab:
      • On “Ignore blank” checkbox, check if you want the user to have the ability to delete cell content after picking choice until it is empty using backspace and do not check if you want the user to not have the ability to do it

        How to Create Dropdown Excel - Screenshot of Step 3-2-5-1-1

      • “In-cell dropdown” checkbox must be checked if you want the dropdown to show up on your cell. If it is not checked, then then user can only input by typing manually with the input restriction as you set it

        How to Create Dropdown Excel - Screenshot of Step 3-2-5-1-2

    • On Input Message Tab:
      On “Show input message when cell is selected” checkbox, check if you want a little note shown up when user place the cursors on the cell with the dropdown. Usually, this note can be used to give directions or explanations about the input expected from the user. Fill the text box with Title: header for your note title and and text box with Message: header for the content of your note if you decide to show the note

      How to Create Dropdown Excel - Screenshot of Step 3-2-5-2

    • On Error Alert Tab:
      On “Show alert after invalid data is entered” checkbox, check if you want message customization on the dialog box which shows up when the user directly input on the cell with dropdown and the input is not inline with your input restriction requirement. Fill text box with Title: header for your message title and a text box with Message: header for your message content if you decide to check the checkbox. For the one below Style header, there are three choices which are: Stop if you want your message to be absolute and the user must change input that is not in line with the input restriction requirement, Warning if you want the message to be a question and user can choose Yes to keep the input which is not in line with the input restriction requirement, and No to be forced to be inline and change the input, and Information if you want the message to be an information and user can still give input which is not in line with the input restriction requirement

      How to Create Dropdown Excel - Screenshot of Step 3-2-5-3

  6. Click OK

    How to Create Dropdown Excel - Screenshot of Step 3-2-6

  7. The how to create dropdown excel process is done!

    How to Create Dropdown Excel - Screenshot of Step 3-2-7-1



    How to Create Dropdown Excel - Screenshot of Step 3-2-7-2



    How to Create Dropdown Excel - Screenshot of Step 3-2-7-3



    How to Create Dropdown Excel - Screenshot of Step 3-2-7-4



Exercise


After you have learned how to create dropdown excel methods, you can practice your understanding of how to create dropdown excel methods through the exercise below!

Download the file from the following link and answer the questions below. Please download the answer if you have done this how to create dropdown excel exercise and sure about the result!

Link to download file for how to create dropdown excel exercise:
Download here

Questions

  1. Practice how to create dropdown excel with static choices from the options on column D header 1!
  2. Practice how to create dropdown excel with dynamic choices by not allowing empty cells from the options on column F header 2! Add F, G, and H options just below the last filled row! Are the new choices showing up in your dropdown?
  3. Practice how to create dropdown excel with dynamic choices by allowing empty cells from the options on column H header 3! Add F, G, and H options with empty cells between the letters, just below the last filled row! Are the new choices showing up in your dropdown?

Link to download how to create dropdown excel exercise answer file:
Download here


Additional Notes


You can also use Name feature in this spreadsheet software to save data, cell range, or formula to be used for your dropdown choice in using how to create dropdown excel method. This is a practical way if you will often use the data, cell range, or formula for various needs other than one dropdown options input.

The way to use Name feature is by clicking on Formula Tab and click Define Name.

How to Create Dropdown Excel - Screenshot of Additional Notes 1



Then type the name that you want to give for your dropdown choices, input the data, cell range, or formula, and click OK.

How to Create Dropdown Excel - Screenshot of Additional Notes 2



Your dropdown choice has been saved in Name feature. Now to use it during your how to create dropdown excel method, you just need to type equal sign ( = ) and the dropdown choice name which has been defined previously on Source: input in Data Validation List.

How to Create Dropdown Excel - Screenshot of Additional Notes 3-1

How to Create Dropdown Excel - Screenshot of Additional Notes 3-2





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