New Auto-expand Drop-down List Technique in Excel

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. Let’s 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 the Create Table intermediate dialogue box.

Next, to create the named range:

  • Select the data from cell A1 to A20
  • Deploy CTRL + SHIFT + F3
  • In the Create Names from SelectionTop Row is fine.

  • Click OK

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 the Allow drop-down
  • Click inside the Source and deploy F3 (which opens the Paste Name)
  • Select Countries 
  • Click OK twice. The auto-expand drop-down list is created and the last country is USA in row 20

When we added Nigeria in 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 the Allow drop-down
  • Click inside the Source and select all the countries (cells A2:A20)

  • Click OK. That’s all!

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

 

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *