How to create drop down lists in Excel

 However, not all users know how to create drop down lists in Excel.

Create an Excel drop down list

The steps to create a drop down list are as follows:

Step 1: Create an optional content list.

How to create drop down lists in Excel Picture 2

Step 2: Select a cell or a range of cells for your drop-down list with the same drop-down list content.

How to create drop down lists in Excel Picture 3

Step 3: On the Data tab (1) , click Excel Data Validation (2) .

How to create drop down lists in Excel Picture 4

Step 4: The Data Validation window appears, on the Settings tab , follow these steps:

1. In the Allow box , select List .
2. In the Source box : enter the location of the area in which you create the drop-down list.
3. Select the checkmark in the In-cell dropdown box.
4. Select Ignore blanks if you want to allow users to leave the box blank. And do not choose if not allowed.

Click OK and you're done!

How to create drop down lists in Excel Picture 5

The result you get is as follows:

How to create drop down lists in Excel Picture 6

Note: in Step 4 , in the Source section,you can enter the options that you want to appear in your list of options separated by commas for similar results.

How to create drop down lists in Excel Picture 7

Copy and paste the drop down list

If you accidentally selected the drop-down list area in step 2, you can copy the drop-down list following the steps below.

Step 1: Select the cell containing the drop-down list and press Ctrl + C to copy.

How to create drop down lists in Excel Picture 8

Step 2: If you want to copy both the drop down list and the format of the original cell, just select the cell to be copied and press Ctrl + V to copy.

How to create drop down lists in Excel Picture 9

Step 3: If you just want to copy the drop down list and do not include the original format. After copying in Step 1 , select the cell to copy the list, right-click and select Paste Special .

How to create drop down lists in Excel Picture 10

Paste Speacial window appears, select Validation and then click the OK button .

How to create drop down lists in Excel Picture 11

How to select cells containing drop-down lists

Select all the cells containing drop down lists

Because the arrow button does not show on the drop-down list unless that cell is selected, it is difficult to know which cell contains the drop-down list. You can apply different formats to the cells in the drop down list for easy distinction. But first need to find all the drop down lists. There is a way to select all cells containing drop down lists. Select the Home tab (1) and click the arrow under Find & Select (2) in the Editing section, then select Data Validation (3) .

How to create drop down lists in Excel Picture 12

Results of the cells containing the drop down list selected:

How to create drop down lists in Excel Picture 13

Select the cells containing the same drop down list

With the above method, you can select entire drop down lists, but to select cells containing the same drop down list, follow these steps:

Step 1: Select a cell containing the drop-down list, select the Home tab (1) and click the arrow under Find & Select (2) in the Editing section, then select Go To Special . (3) .

How to create drop down lists in Excel Picture 14

Step 2: On the Go To Special window, click the check box as below and then press the OK button .

How to create drop down lists in Excel Picture 15

As a result, only the cells containing the drop-down lists like the ones selected in Step 1 are selected:

How to create drop down lists in Excel Picture 16

Delete a dropdown list in one cell

To delete the dropdown list in a cell, select the cell and go to the Data tab (1) and click Data Validation (2) in the Data Tools section .

How to create drop down lists in Excel Picture 17

Validation window appears, click the Clear All button .

How to create drop down lists in Excel Picture 18

The options on the Data Validation dialog box will be reset to the default values, then click OK .

How to create drop down lists in Excel Picture 19

So the selected cell will delete the drop down list:

How to create drop down lists in Excel Picture 20

Above, Software Tips guide you how to create drop down lists in Excel. Good luck!

Kareem Winters

Update 19 May 2020