How to create a dynamic list in Excel

Advanced drop-down lists in Excel allow you to create lists whose options change based on selections made in another list. This technique is a smart way to facilitate data entry, saving time and reducing errors.

A dependent drop-down list in Excel is a type of drop-down list whose options change based on selections made in another list. For example, if in a form, phone brand If selected, the second list will display only the models of that brand. This feature allows you to create professional forms in Excel. This type of drop-down list is also known as a dynamic drop-down list in Excel.

To make an advanced drop-down list in Excel, you usually need to Named Ranges and function INDIRECT be used This function references a range of cells that changes based on the selections made in the first list, thus updating the selections in the second list.

In this example, we create a dynamic list in Excel and first select the brand of the phone, and then the models of the same brand are displayed in the second list.

1- Data preparation

• Enter the titles as follows: Phone brand (A1) and Model (B1).
• Then, in another part of the page (for example, columns F and G), enter the section headings: Apple (F1) and Xiaomi (G1).
• The model of the phones Enter below each section.

For example:

• Under the Apple column: iPhone 14, iPhone 15, iPhone 16
• Under the Xiaomi column: Redmi Note 13 4G, Xiaomi 14T, Xiaomi 14T Pro

2- Creating named ranges (Named Ranges)

Now we need to create named ranges for the sections:

3- Creating an independent drop-down list

• The first drop-down list in the column Apple (A2 onwards) is created and is independent; In the sense that it is not dependent on others for its options.
• Under the Apple heading In column A, select the desired range of cells to insert the drop-down list (do not select the title).
• Click the Data tab in the top menu and click Data Validation Click in the Data Tools group.
• On the Settings tab, drop-down menu Allow in the mode List put Then, inside the text box below Source Click and select Apple and Xiaomi titles (F1:G1). Roy Ok Click in the dialog box to create an independent dropdown in the worksheet.

• In this drop-down list, you can choose between the options Apple and Xiaomi Choose one.

4- Creating a dependent drop-down list in Excel

• The desired cell under the title Model Select (B2 down).
• Select the Data tab in the top menu and click Data Validation Click in the Data Tools group.

in the episode SourceEnter the following formula:

To turn it into a dynamic Excel dropdown list, we need the INDIRECT function to reference the independent dropdown list we created in the previous section. Click inside the parentheses () of the formula and the first drawer under the title Model Select (B2). The formula should be as follows:

• Remove the second dollar sign to make the formula look like this:

• Now, Roy Ok Click to close the dialog box and place the dependent drawer in the worksheet.

• Now when a phone brand you select in column A, you should see the appropriate set of names in the dropdown in column B.

To edit the drop-down list in Excel or change the data range:

• Select the desired cell.
• to fever Data And then Data Validation go and Source change the

To remove the drop-down list in Excel:

• Select the desired cell and in the Data Validation section Clear All and finally on Ok click

Shortcut key for the drop-down list in Excel (down arrow)Alt + Down Arrowis a useful tool that allows users to work with dropdown lists more quickly. With the help of this shortcut, you can see the drop-down menu and its options without the need to open the mouse.

Using this technique, you can create a smart drop-down list in Excel that helps improve productivity not only in small projects but also in complex and large projects. Learning this skill is a big step to becoming a professional Excel user.

If you are looking for Excel training and you want to do your projects in the least amount of time, the Excel tricks article will be useful for you.