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.

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.

2. Select cell B1 on the first sheet.

3. On the Data tab click the Data Validation in the Data Tools Group.

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

4. Click List in the Allow box.
5. Click in the Source box and select the range A1: A3 on the second Sheet.

6. Click ok button.

Result:

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.

7.  You can also type the items directly into the source box, instead of using a range reference.

  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.

To allow other entries, execute the following steps:

2. On the Data tab, click the Data Validation in the Data Tools group.

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

3. On the Alert tab, uncheck ‘Show error alert after invalid data is entered’.

4. Click ok button.
5. Now you can enter a value that is not in the list.

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.

2. Right click on that item and click insert.

3. Select the “Shift cells Down” and then Click Ok.

Result:

4. Type a new item.

5. 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

• Read More:-
  • MS Excel Tricks
  • 3 Cool Notepad Tricks That You Like
  • How to permanently delete Files & Folders in Computer ? | Computer tricks