How to Create a Drop Down List in Excel

Published Date : Mar 16, 2020
Category : How To
Create a Drop Down List in Excel

Playing with data is easy if you are adequately skilled in excel. In this blog, we will educate you on how to create a drop-down list in excel. Drop-down lists help users pick one value from a list of items on a spreadsheet easily. Largely used in the design of graphical user interfaces, drop-down lists play an important role in web designing too. 

In excel, drop-down lists can be easily created with the help of the Data Validation function. Let us see in detail how this function can be put to use for creating a drop-down list in excel manually.

How to Add a Drop-Down List in Excel:

Applying Data Validation Function

  • In Excel, you can open a new spreadsheet and enter the items in a cell range that you want the drop-down list to display. 
  • You can also choose to convert your list to a table by choosing a cell range and then pressing Ctrl + T together.
  • Next, you can select the cell where the drop-down list has to come into effect.
  • Then, you can go to the Data tab on the Ribbon (situated on the top portion of the spreadsheet).
  • Once the Data Validation window opens; under the Settings tab, you can select the option of ‘List’ in the Allow box.
  • Once you have chosen the option of List, further, you will need to select the ‘Source,’ which will be ideally the cell range from which the list will display values.
  • You can then click on ‘OK’ for bringing the function into effect.
  • You can check the boxes of ‘Ignore blank’ and ‘In-cell dropdown’ too.

Read More: How To Convert PDF To Excel (Online & Offline)

Adding an Input Message

  • To add an input message, you can go to the Input Message tab and enter a ‘Title’ along with an input message that you wish it to display.
  • Remember to check the box which says “Show input message when the cell is selected.”
  • Additionally, you can also choose an error message to display when a user tries to input anything in the cell other than the drop-down options.

Adding an Error Message

  • To do this, go to the Error Alert tab, and input a Title and Error Message description accordingly. 
  • Also, ensure that the box is checked which says “Show error alert after invalid data is entered.”
  • You can also select an error style.


In the example, we have entered the drop-down list options in column E (E2: E6).

Example of Creating a Drop Down List in Excel

Then Column B should have the drop-down in excel. So, we applied the Data Validation function as described in the above steps, including a display message.

Example of Creating a Drop Down List in Excel

We also incorporated an error message which threw an error when we tried entering a different word other than the items on the list.

Example of Creating a Drop Down List in Excel

Summing It Up

With excel drop-down list, it can be ensured that data entry is more accurate and quicker than manual entry. This can be especially useful when a spreadsheet is handled by multiple members to avoid manual errors.

Snigdha is a technical content writer with expertise in the field of science & technology. Though she has a very diverse academic and professional profile yet she finds writing to be a creative form that she was unable to explore so far. When not writing, she can be found indulging in other forms of creativity like painting or travelling.

Leave a Response

Related Posts