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:

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

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

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.

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.

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.

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.

In the source field, enter Yes, No.

Ensure that the In-cell dropdown option is tested.

Press OK.

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.

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

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)

5. Press OK.

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

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.

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

The ‘Data Validation’ dialog box shows up.

3. Press Clear All.

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.

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

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

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

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

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

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.

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.

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

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

Press OK.