Get Experts' Help with Excel Formulas, Dynamic Report & Dashboards, Process Automation, & VBA Programming

  • The ExcelHelp Expert

The 2021 Pro Guide to Create a Drop Down List in Excel

Drop down list in Excel can be created using data validation. Let us have a more in-depth look into how to create a drop-down list in excel spreadsheet.


An excel drop-down list helps in the organization of data systematically. It frees up space and improves the content layout and design, providing a professional look.


If you want to become an Excel Pro, than this guide is the must



Make a drop down list in Excel


A drop down list in excel is a graphical element of control. It is like a list box that a lows users to choose one value from a given list. Let's start the steps on how to create a drop down list in Excel


  1. Create item-wise lists under a said category (here, fruits and vegetables, respectively). Refer Image 1.

  2. Select the data and convert it into a table (shortcut- CTRL+T). Refer Image 2.

  3. Go to the cells where you want to place the drop list.

  4. Select the cells.

  5. Under the Data tab, select the data validation option. Refer Image 3.

  6. Fill in the data validation window. Select type “LIST” in the Allow option, and under Source, select the table made in step 1. Refer Image 4.

  7. Press OK.

  8. Now go back to the selected cells. You will see an arrow corresponding to the cell. When you click the arrow, you receive the drop-down list. Refer Image 5.

  9. Repeat the steps for the vegetable list. Refer Image 6.



An Example for creating a drop down list in Excel


Before creating a drop down list in excel, you should be aware of items to be included in that list.

Assume you are a supplier of fruits and vegetables. There is a limited range of items that you supply regularly. In this example, we look from customers' perspective and how they get to select from the available options. Here is the step-by-step guide to building a drop-down list in excel.


Create item-wise lists under a said category (here, fruits and vegetables, respectively).



Image 1: Step 1: How to create a drop-down list in Excel

how to create a drop-down list in excel
Step 1: How to create a drop-down list in Excel



Image 2: Step 2: How to create a drop-down list in Excel



how to make a drop-down list in excel
Step 2: How to create a drop-down list in Excel

Image 3: Step 3: How to create a drop-down list in Excel



how to add drop-down list in excel
Step 3: How to create a drop-down list in Excel

Image 4: Step 4: How to create a drop-down list in Excel



how to insert drop-down list in excel
Step 4: How to create a drop-down list in Excel


Image 5: Step 5: How to create a drop-down list in Excel



how do i create a drop-down list in excel
Step 5: How to create a drop-down list in Excel

Image 6: Step 6: How to create a drop-down list in Excel



how to create a drop-down list in excel
Step 6: How to create a drop-down list in Excel



In the above example, we see how purchasing became easier wherein the customers had to choose from a given list. Similarly, while recording data, few data points remain obvious among a given set of variables. That is where a drop-down list can be used effectively.

Important tips:

  1. Ensure that you put the items on the list in a table format (use CTRL+T shortcut). A table format is essential, as any changes in the table or original list are edited automatically under the drop-down list.

  2. While making many drop-down lists, it is advisable to name the tables instead of randomly creating them. By doing so, one can mention the table name under Source in the data validation window.

Where can a drop-down list be used


A drop-down list, also called a drop-down menu, drop menu, pull-down list, or a picklist, is usually used when a predetermined set of items or data set needs to be put in place.


For example, let us suppose you are booking a flight ticket and while selecting a country in Asia, you have limited options. Therefore, one country needs to be chosen among a few countries, and this is where a drop-down list is used. Similarly, while shopping for clothes according to seasons, a drop-down list can be used to decide before purchasing as there is a pre-defined number of seasons. Look out for someone who can help you with Excel online help :)


Looking to hire an Excel Expert to help you with Excel Spreadsheet? Connect Now.

116 views0 comments