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.

Example:

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

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.

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

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.