How to Create a Dropdown List in Excel

Updated on: January 5, 2023

Excel has a lot of valuable tools that help people to work more efficiently in the worksheets, one of them is the dropdown selections which allows you to categorize some items and enter data into the spreadsheets. Even though this tool sounds easy to add, it is not intuitive, but don’t worry: here is how to insert a dropdown in Excel.

Create the list of items

Before starting, you should know that the Data Validation feature is used to make Dropdown lists, among other purposes. This technique restricts user input in a worksheet so the information can be neat and easier to use.

So, let’s get started! You must first create the list of items, phrases, or different conditionals you want to find when rolling out the dropdown. To do that, open an empty worksheet, choose a column and enter the features you want to establish, one below the other. 

Once you have it, select the range of cells containing the dropdown list items. Afterward, enter a name for it into the “Name Box” located above the grid, just down the principal panel and its group tools (it is a white little white rectangle before and next to the formula bar).

Inserting the dropdown

Afterward, you have the raw material for the dropdown; all that remains to do is find it interesting. To do that, you need to do the following:

  1. First, select the cell you want to add to the dropdown list.
  2. Next, switch to the “Data” tab.
  3. Search the Data Tools group.
  4. Next, click on the “Data Validation” button.
  5. Now, in the dialog box of “Data Validation,” go to the “Settings” tab.
  6. From the “Allow” dropdown list, select the “List” option.
  7. Set the range of items:

Notice that at this point, the dialog box will show you some extra functions: Next to the “List” selection you made, you will find two checkboxes, choose the one that says “In-cell dropdown,” and in the “Source” blank type the equal sign followed the name of the range you did:

=NAME. So if you named your range “Status,” the syntaxis will be =Status.

Now, the other check box called “Ignore blank” is checked by default. This means you can pick or unpick an item if you want. However, if you expect the user to choose an obligatory option from the dropdown, you ought to uncheck the “Ignore blank” checkbox.

  1. Press “OK.”

Note: The dropdown arrow button will be visible only when the cell is selected.

Add a message in the dropdown

You can also personalize the dropdown with a message when selecting the cell dropdown list. For that, you need to re-open the dialog box of “Data Validation,” switch to the “Input message” tab, and verify if the checkbox “Show input message when cells are selected” is marked.

After that, enter the message's title and input a message. When you think you’re done, click the “OK” button at the bottom of the pop-up dialog box.

Removing a dropdown

On the contrary, when you want to erase the dropdown list from a cell, you must go again to the Data Validation dialog box and click on the “Clear all” option at the bottom left corner, which is available in any tab you are in. This will reset the settings to their defaults and restore the cell’s original format.

Okay, now that you know how to add and remove a dropdown list in your worksheet, don’t forget to try it yourself to improve the organization of your data!

Lorena M. Rodas leverages her experience across film, writing, and production to make complex tech concepts accessible through storytelling. With a background spanning sci-fi, AI, and emerging tech, Lorena translates her depth of knowledge into engaging, educational content. She is an expert at decoding high-level topics to reach broad audiences.
Access 2,701+ video lessons. Instantly.
Start Learning Now →
crossmenu linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram