{"id":768,"date":"2022-11-18T16:11:31","date_gmt":"2022-11-18T16:11:31","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=768"},"modified":"2024-05-08T01:55:26","modified_gmt":"2024-05-08T01:55:26","slug":"how-to-create-dependent-drop-down-list-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-create-dependent-drop-down-list-in-excel\/","title":{"rendered":"How to create a Dependent Drop Down List in Excel? Dependent drop-down list."},"content":{"rendered":"

Dependent drop down list in Excel is a dynamic tool that significantly enhances data entry efficiency and accuracy by linking lists and ensuring that the choices in one drop down are relevant to the selection in another. Perfect for cascading selections like country and city or product category and items, this feature streamlines workflows and minimizes errors. In this guide, we explore the steps to create dependent drop down list in Excel, enabling you to construct interactive, user-friendly spreadsheets that respond intelligently to user input, ensuring data consistency and precision in your reports and forms.<\/p>\n

The dependent drop down displays values in a drop-down list based on the value selected in another drop-down. This makes it simple for users to enter the data that is required. Drop down lists are used to validate data in a unique way.<\/p>\n

First, take a sample list of Continents and some of its Counties as per below list:<\/p>\n

\"Dependent<\/p>\n

Steps of creating a Dependent Drop-Down List in Excel:<\/strong><\/h2>\n

Step-1:<\/strong> Select the cell where you want the first (main drop) Excel drop down list to appear.<\/p>\n

\"Dependent<\/p>\n

Step-2:<\/strong> Navigate to Data. Then tap on the Data Validation. The data validation dialog box will appear.<\/p>\n

\"Dependent<\/p>\n

Step-3:<\/strong> Select List on the data validation dialog box’s settings tab (data validation list).<\/p>\n

\"Dependent<\/p>\n

Step-4:<\/strong> In the Source area, enter the range that contains the items to be displayed in the first drop down list.<\/p>\n

\"Dependent<\/p>\n

Step-5:<\/strong> Select OK. This will result in the creation of the Drop Down 1. The main drop down list is created.<\/p>\n

\"Dependent<\/p>\n

Step-6:<\/strong> You must build named ranges for dependent drop-down lists. Select the entire data set. Navigate to Formulas -> Defined Names -> Create from selection. Here we make a dependent list.<\/p>\n

\"Dependent<\/p>\n

Step-7:<\/strong> A pop-up will appear. Select “Top Row” and then click OK.<\/p>\n

\"Dependent<\/p>\n

Step-8:<\/strong> Choose the cell where you want the Dependent\/Conditional Drop-Down (second drop-down) list to appear.<\/p>\n

\"Dependent<\/p>\n

Step-9:<\/strong> Navigate to Data. Then tap on the Data Validation. The data validation dialog box will appear.<\/p>\n

\"Dependent<\/p>\n

Step-10:<\/strong> Select List on the data validation dialog box’s settings tab.<\/p>\n

\"Dependent<\/p>\n

Step-11:<\/strong> Enter the following formula in “Source” and click OK.<\/p>\n

=INDIRECT(A2)<\/strong><\/p>\n

(A2 is the cell that holds the main drop-down menu.)<\/strong><\/p>\n

\"Dependent<\/p>\n

Result outlined below:<\/p>\n

\"Dependent<\/p>\n

Choose Europe from the Continent list, in the Countries drop you see the relevant countries.<\/p>\n

\"Dependent<\/p>\n

Create multiple dependent drop down list in Excel<\/h2>\n

Creating multiple dependent drop-down lists in Excel involves setting up lists where the options in one drop-down list depend on the selection made in another. Here are key points to guide you through creating these dependent lists:<\/p>\n

    \n
  1. Define Your Data<\/strong>:\n