Dynamic Drop-down List In Excel That Automatically Expands

How to create Dynamic Drop-down List In Excel That Automatically Expands, data validation help to make drop down list in excel,Dropdwon list in excel

 

The Excel drop-down list in the feature is helpful if a user has the option to select from a pre-input data source creation. If anyone type something repeated daily or regularly this feature will save time and avoid typing mistake. In Excel, easily can create a drop-down list using the Data Validation function. 

To automatically expand data dynamically from the data source using just the offset function In the data validation source. Usually, the list of valid items is stored in the same workbook, but it’s possible to set up a drop-down from a list in another workbook. 

A master data source list will be created in one workbook, and drop-down lists in a different workbook will be based on that list. Dynamic Excel drop-down list gives users extra benefits to input data from sources. Here are given full steps on how to do this.

Benefits from Dynamic Drop-Down List in Excel

• Drop-down lists allow users to type inputs faster
• Drop-down lists provide users with an easy interface to understand what input is expected from them
• Drop-down lists prevent spelling mistakes with notification
• Data entry is quicker and more accurate
• Allow people to pick an item from a list that previously creates.

Steps by Steps to Create a Dynamic Drop-Down List in Excel

First, create your data source in the same worksheet or another worksheet, Here are the (Data Source) supposed B Column Country Name column types some data it will update your drop-down list automatically when you add an item to the end of the list by using Offset function.

Now here select any cell-like C3 and type =offset given reference B as a first data source and freeze $B$ Cell by dollar sign then for row 0, for column 0, and for hight selection given Counta and select value any selective range or full C column, Here select full column and freeze  $B:$B, close bracket then coma then given value 1. 
=OFFSET($B$3,0,0,COUNTA($B:$B),1)

Now copy the full formula to create a named range in the formula name manager.

Click on the formula than in the name manager popup click New

In Name, Box Type your desired name without space like Source_list, and in Refers to box paste formulas that are copied -=OFFSET($B$3,0,0, COUNTA($B:$B),1) then OK and close.

Now go to your typing cell where want to input the data from the source, let's select the column C 3: C12  Or C3, Now go to Data then click Data validation, In the data validation window in Allow box select List and ensure Ignore blank and In-cell drop-down are check mark.

Now in the source box refer to source data, go to the formula tab then from the name manager select the source list and ok.

And finally, dropdown lists are created, Now you can input data without repeated data entry from a list.

Now in the data source sheet, you can add more data below which are automatically updated on the drop-down list.

Download Practice File

Frequently Asked Questions—FAQs

How many ways create a drop-down list in excel by data validation feature?

Different Dropdown List Using Data Validation Tool create -

• Simple Dropdown List-Just creates the data source than in the data validation source select the data source.

• Dynamic Dropdown List -Create data source then in data validation source select data source  with Offset function

• Dependent Drop-Down List- the list of items changes, depending on the value in another cell data source

Final Words :

A drop-down list created by using the data validation feature is really helpful to excel users when many repeated or similar data need to be input regularly into any workbook. You can also be setting an error message and a message type for any error anyone on the data validation feature from the input message and error alert tab.