How to create dropdown in MS Excel

How to create dropdown in MS Excel

Create a Dropdown List :

To create a drop down List in MS Excel you have to execute the following steps:

  1. Type the Item on the second sheet that you want to appear in the drop down list.
Excel Create a Dropdown List
Excel Create a Dropdown List

Note: if you don’t want users to access the items on the second sheet you can hide the second sheet. To achieve this right-click on the second sheet and click on hide.

How to create dropdown in MS Excel
How to create dropdown in MS Excel
  1. Select cell B1 on the first sheet.
How to create dropdown in MS Excel
How to create dropdown in MS Excel
  1. On the Data tab click the Data Validation in the Data Tools Group.
conditional drop down list excel
conditional drop down list excel

The ‘Data Validation’ dialog box appears just like above given.

  1. Click List in the Allow box.
  2. Click in the Source box and select the range A1: A3 on the second Sheet.
how to create drop-down list in excel 2020
how to create drop-down list in excel 2020
  1. Click ok button.

Result:

excel drop down list multiple selection
excel drop down list multiple selection

Note: To copy and paste a drop-down list, select the cell with the drop-down list and press CTRL+c, and another cell press CTRL+v.

  1.  You can also type the items directly into the source box, instead of using a range reference.
excel formula based on drop down list
excel formula based on drop down list

  Note: this makes drop down list case sensitive.

Allow other Entries

You can also create a drop down list in excel that allows some other entries.

  1. When you type a value that is not in the list, Excel shows an errors alert.
How to Create Dynamic Drop Down List in Excel
How to Create Dynamic Drop Down List in Excel

To allow other entries, execute the following steps:

  1. On the Data tab, click the Data Validation in the Data Tools group.
Creating a drop-down list in Excel
Creating a drop-down list in Excel

The ‘Data Validation’ dialog box appears just like above given.

  1. On the Alert tab, uncheck ‘Show error alert after invalid data is entered’.
excel drop down list
  1. Click ok button.
  2. Now you can enter a value that is not in the list.
How to create a drop-down menu in Excel
How to create a drop-down menu in Excel

Add/Remove items

You can add or remove items from a drop-down list in MS Excel without opening the ‘Data Validation’ dialog box and change the range reference.  By this you can save your time.

  1. To add an item, go to the items on second Sheet and select the item.
How to create a drop-down list in Excel
How to create a drop-down list in Excel
  1. Right click on that item and click insert.
How to Create Drop Down Lists in Excel
How to Create Drop Down Lists in Excel
  1. Select the “Shift cells Down” and then Click Ok.
How to Create a Drop Down List
How to Create a Drop Down List

Result:

Create a Drop Down List in Excel
Create a Drop Down List in Excel
  1. Type a new item.
How to Create Drop Down Lists in Cells
How to Create Drop Down Lists in Cells
  1. If you want to remove an item from a drop down list, click Delete, select “Shift cells up: and then click OK.

How to create dropdown in MS Excel

Leave a Reply