Creating a drop down list in Excel

1. The Drop down list in Excel

The Drop down list in Excel is like the select element in the HTML/WEB World but in Excel you can add drop down list much easier with few steps.
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.

2. How to create drop down list in Excel

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:
   

   Select Cell

2. From the menu <Data> click the button “Data Validation”. A dialog box will appears:
   

   Data Validation menu

   

   Data Validation dialog box

3. Choose “List” in the Allow option:
   

   Allow box list

4. 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 Rrange Vvalue

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

   Drop down list created

3. 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.
• 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

4. 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 this 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.