Creating a drop down list in Excel

*Click images to enlarge

Here we are and welcome to another quick tutorial with some Excel Basics. In this tutorial we gonna see how to create drop down list in Excel.

Before we start you need to know some terms in the Excel Application like menu bar, workbook, worksheets… you can find more details here A brief introduction to Excel.

The Drop down list in Excel

The Drop down list in Excel is like the select element in the HTML/WEB World but you can add drop down list in Excel much easier with few steps.

Its simply transform multiple selected cells into a drop down . We can say its a drop down list or a drop down options in Excel.

For example if you had a list of data entry in a sheet : list of country or list of menu food and you want the user to select one value instead of typing it the solution is to use the Drop Down list.*

NB : this tutorial works for all version of Excel.

How to create drop down list in Excel

In Excel you have books and sheets to work with :

• Excel workbook is a file that contains one or more spreadsheets that you can use to store data. You can use workbooks to track data, create charts and graphs, and perform calculations.
• Excel spreadsheets are one of the most popular ways to store and organize data. They are easy to use and allow you to create complex mathematical formulas to analyze your data.

Here we gonna use the same workbook but in 2 different way :

1. Both data and the drop down created are in the same worksheet or the active sheet :
   The active sheet is the sheet that is currently being viewed or edited. When you first open Excel, the active sheet is typically Sheet1. You can change the active sheet by clicking on a different sheet tab at the bottom of the Excel window.
   The active sheet is important because it is the sheet that will be affected by any changes that you make to the workbook, such as adding or deleting data.
2. The  data and the drop down created are in different worksheet

Create a Drop Down list in Excel in the same active sheet

To create a Drop Down list in Excel in the same active sheet flow those steps:

1. Select Cell were you want to add the drop down list:
2. From the menu <Data> click the button “Data Validation”.
   Data validation in Excel is a feature that allows you to control what data can be entered into a cell. This is useful for ensuring that data is entered in the correct format, such as a date or a number or a list.
   Data validation in Excel can also be used to create drop-down lists, which can be helpful for restricting data entry to a limited number of options.
   

   Data Validation menu

3. A dialog box will appears, its the Data Validation dialog where you can choose setting for your drop down list:
   

   Data Validation dialog box

4. Choose “List” in the Allow option:
   

   Allow box list

5. In the Source field type or select the range or the list of value from the active sheet to add to Drop Down list.
   If you use to select the range click “Enter” button after you finish selecting cells to return to the Data Validation dialogue box.
   

   Select Range Value

6. Finally click “OK” and a new drop down list will be created in the selected cell from step 1:
   

   Drop down list created

Make drop down list in Excel in different worksheet

Here the data and the drop down created will not be in the same sheet:

1. Select the sheet where to place the drop down
2. Click the Data Validation button
3. From the Data Validation’s dialogue box select the range value here you need to move to the sheet where the data is
4. Select the data and Click ok
5. A drop down list will be created in the first sheet where you select the cell

Advanced use of a drop down list in Excel

Here are some tips help you understanding more the drop down list in Excel:

• Copy/Paste the Drop down list to any cell you want in the active sheet.
• In the Data Validation dialogue box you can also type directly the list of value instead of using a range reference.
• In the Data Validation dialogue box you can also write a title and  message in the second tab “Input Message”.
• Typing a value directly in the drop down. If the value doesn’t exist Excel will display an error.
  

  Error value Excel Dropdown list

  For fixing that under the Data Validation dialogue box select the Error Alert tab uncheck ‘Show error alert after invalid data is entered’ and click o
  OK.

  

  Error Alert tab

Add/Remove item to drop down list in Excel

1. Add item(s)
   Insert a new Cell in the middle of the list and enter a new value. Automatically the drop down list will be updated with the new value. If you want to dynamically update the drop down list even if you entered a new value in the end or the top of the list you can use the Offset formula.

   =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
   

   Replace Sheet1 with the active sheet where the list of value exit and $A for the column and $1 for the first row that contain the first value.

2. Remove item(s)
   To remove a drop-down list in Excel, execute the following steps:
   • Select the drop down list.
   • Open the Data validation dialogue box.
   • Click the button “Clear All” under the tab Setting.
   • Click OK to save.

Conclusion

The drop down list in Excel is a good element to organize you data and give the user options to select. But we can say it has some cons like dealing with error value and when moving out of the drop down the right arrow will be invisible and you need to reselect it so you can choose another option.

For that there is another approach to create a drop down list in Excel on using the Form Controls or even using macros in Excel.