How to insert drop down list in excel

In various web pages and software you might have seen drop down list.This drop down list allows us to select the input without any error. Now let’s see advantages of drop down list and how to insert drop down list in Excel :

  • it will give options to the user to select the input easily and quickly
  • the input given by user will be accurate as per predetermined list.

How to insert drop down list in excel:

  1. Direct entry to Data Validation list
  2. Selection of a range in the sheet
  3. Creating a list by define name option
  4. Using OFFSET formula
  5. Creating Drop down using Combo Box

1. Direct entry to Data Validation list

In case if we have a small list to be shown in drop down list we can follow direct entry method in data validation to do this you can follow below mentioned steps:

  • Select the cell for which you want to add drop down list
  • Go to Data Tab and click on Data validation
  • Select List under Validation criteria
  • Enter list in Source box with comma
How to insert drop down list in excel
Now you can see the drop down like this.

2. Selection of a range in the sheet

In this method, the list is entered in sheet in a range and the range is selected in data validation Source box. The limitation of this method is list should be in same sheet.

  • Select the cell for which you want to add drop down list
  • Go to Data Tab and click on Data validation
  • Select List under Validation criteria
  • Give reference range in Source box
How to insert drop down list in excel

3. Creating a list by define name

In case you have created in a separate sheet of the workbook, then you have to Define the Name for the range and you can use the name in Data Validation Source box to show in list box.

This method is helpful when you have list range in different sheet.

Steps to Define Name for a range

  • Select the range which you cant to show in drop down
  • Go to Formulas and click on Name Manager
  • Click on New
  • Enter the name for range and give reference to the range
  • Click OK 

This will create a name with range as list

Steps to add the Name in Data Validation list

  • Select the cell for which you want to add drop down list
  • Go to Data Tab and click on Data validation
  • Select List under Validation criteria
  • Enter the Name created in Source box starting with “=” sign

4. Using OFFSET formula

Instead of giving reference in Source, you can also use OFFSET function to define the range to be displayed in drop down.

You can read more about how to use OFFSET function Here

5. Creating Drop down using Combo Box

One more different method to create drop down is to use Combo Box instead of Data Validation. Combo boxes are independent of any cell. But the out put of the drop down list should be linked to a cell. 

To add a Combo Box, you have to enable the Developer tab in excel, If you don’t know how to add Developer Tab please read: How to add the developer tab to the ribbon

To add Combo Box

  • Go to Developers Tab and Click on Inserts
  • Click on Combo Box under Form Controls
  • Drag and drop with left mouse click where you want to put Combo Box

After Adding Combo Box, please follow below steps to add drop down list and result cell to that.

  • Right click on Combo box and click on Format Control
  • Select the Control Tab
  • Enter input range to show as drop down
  • Enter Cell link to give the output for selection

When you select a item from drop down list, it will update the serial number of range in the output cell. Whenever the selection is changed, the value is updated automatically in this cell.

That’s all about How to insert drop down list in excel, please comment if you have any question, i will write about dynamic and dependent drop down in my next post.