Javatpoint Logo
Javatpoint Logo

How to create a drop-down list in Excel?

A drop-down list is an outstanding way to give the client an option to choose from a pre-defined list.

It very well may be utilized while getting a client to fill a structure or while making an intuitive excel dashboard.

Drop-down lists are very regular on social sites and apps and are very natural for the client.

Create a Drop-down List

To create a drop-down list in Excel, perform the following steps.

  1. Using Data from Cells.
  2. Entering Data Manually.
  3. Using the OFFSET formula.

Using Data from Cells.

1. We have a list of elements, as demonstrated below:

How to create a drop-down list in Excel

2. Select a cell where we need to make the drop-down list.

3. Click on Data and select Data Tools → Data Validation.

How to create a drop-down list in Excel

4. The 'Data Validation' dialog box shows up.

5. In the Settings icon, choose List as the Validation criteria

6. When we choose List, the source field shows up.

How to create a drop-down list in Excel

7. In the source field, enter= $L$18+'[Book2.xlsx] Sheet 2'!$A$3:$A$8 or basically press in the Source field and choose the cells utilizing the mouse and press OK. This will embed a drop-down list in cell C2.

8. Ensure that the In-cell dropdown alternative is checked (which is checked by default). If this option is unchecked, the cell does not display a drop-down, we can manually enter the values in the list.

How to create a drop-down list in Excel

By Entering Data Manually

In the example, cell references are utilized in the Source field. We can also add elements precisely by entering it manually in the source field.

For example, suppose we need to display two options, yes and No, in the drop-down in a cell. Here is how we can precisely enter in the data validation source field:

Select a cell where we need to generate the drop-down list (cell C2 in this example).

Click on Data and select Data Tools -> Data Validation.

How to create a drop-down list in Excel

In the Data Validation dialogue box, inside the Settings icon, select List as the Validation criteria.

When we choose List, the source field shows up.

How to create a drop-down list in Excel

In the source field, enter Yes, No.

How to create a drop-down list in Excel

Ensure that the In-cell dropdown option is tested.

Press OK.

How to create a drop-down list in Excel

This will make a drop-down list in the selected cell. All the elements recorded in the source field, isolated by a comma, are recorded in various lines in the drop-down menu.

All the things entered in the source field, isolated by a comma, are shown in various lines in the drop-down list.

Dynamic Drop-down List

We can utilize a formula that refreshes our drop-down list naturally when we include an element to the end of the list.

1. In the first sheet, choose cell B1.

How to create a drop-down list in Excel

2. In the Data icon, in the Data Tools group, press Data Validation.

How to create a drop-down list in Excel

The 'Data Validation' dialog box shows up.

3. In the Allow box, press List.

4. Press on the Source box and enter the formula: =OFFSET (Sheet2! $A$1, 0, 0, COUNTA (Sheet2! $A: $A), 1)

How to create a drop-down list in Excel

5. Press OK.

6. On the second sheet, add a new element to the end of the list.

How to create a drop-down list in Excel

Remove a Drop-down List

To remove a drop-down list in Excel, perform the following steps.

1. Select the cell with the drop-down menu.

How to create a drop-down list in Excel

2. In the Data icon, in the Data Tools group, press Data Validation.

How to create a drop-down list in Excel

The 'Data Validation' dialog box shows up.

3. Press Clear All.

How to create a drop-down list in Excel

4. Press OK.

Dependent Drop-down Lists

Sometimes, we may have more than one drop-down list, and we need the elements shown in the second drop-down to be dependent on what the client chooses in the first drop-down.

These are known as dependent or conditional drop-down lists.

Here are the steps to make a dependent and conditional drop-down record in Excel:

  • Select the cell where we need the first (primary) drop-down list.
  • On the Data tab and Selects Data Validation. This will open the data validation dialog box.
How to create a drop-down list in Excel

On the data validation dialog box, inside the settings icon, choose List.

How to create a drop-down list in Excel

In the Source field, indicate the range that includes the elements that are to be displayed in the first drop down list.

How to create a drop-down list in Excel

Press OK. This will make the Drop-Down 1.

How to create a drop-down list in Excel

Choose the entire data set (A1: C8 in this example).

How to create a drop-down list in Excel

Click on Formulas and select Defined Names, then Create from Selection (or we can utilize the keyboard shortcut Control + Shift + F3).

How to create a drop-down list in Excel

On the 'Create Named from Selection' dialog box, check the Top row alternative and uncheck all the others. Doing this makes two names field ('Employees Name' and 'Gender'). Employees named field defines all the employee names in the file, and the Gender named field represents the genders in the file.

How to create a drop-down list in Excel

Press OK.

Choose the cell where we need the Dependent and Conditional Drop Down list (E3 in this example).

Click on Data and select Data Validation.

How to create a drop-down list in Excel

On the Data Validation dialog box, inside the setting icon, make sure the List is selected.

How to create a drop-down list in Excel

In the Source data, enter the formula =INDIRECT (D3). Here, D3 is the cell that includes the main drop-down.

How to create a drop-down list in Excel

Press OK.






Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA