Welcome to this Excel tutorial. In this blog, we are going to look at the old school and the new school techniques of create auto-expand drop-down list in Excel. Lets get started.
Tutorial Video
Kindly watch the video below and subscribe to the YouTube channel
Sample Data
We have a simple Covid-19 dataset in the caption below. The Data is already formatted as an Excel Tables in both sheets
Old School Technique
To create the auto-expand drop-down list in the old ways, the dataset must be formatted as an Excel Tables by pressing CTRL + T inside the data and click on OK in theCreate Tableintermediate dialogue box.
Next, to create thenamed range:
- Select the data from cell A1 to A20
- Deploy CTRL + SHIFT + F3
- In theCreate Names from Selection,Top Rowis fine.
To create the drop-down list:
- In cell F2, In the Data Tools group of the Data tab, click on Data Validation
- Select List from theAllowdrop-down
- Click inside theSourceand deploy F3 (which opens the Paste Name)
- SelectCountries
- Click OK twice. The auto-expand drop-down list is created and the last country isUSAin row 20
When we addedNigeriain row 21 and we checked the drop-down, we can see that Nigeria is included in the List. See the caption below
New School Method
The new technique is much easier and simpler to perform and this technique is currently available in Excel for M365 Beta & Current Channel.
We still have the same dataset formatted as an Excel Tables in the New Method sheet tab
All that we needed to do is proceed to create dropdown list and the table will auto-expand when new data is added
- In cell F2, In the Data Tools group of the Data tab, click on Data Validation
- Select List from theAllowdrop-down
- Click inside theSourceand select all the countries (cells A2:A20)
When we added a new country in row 21 (Ghana), we can see that the drop-down auto-expand without named range! See the caption below