How do I create a drop down list in Excel 2020?

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

Video liên quan

إرسال تعليق (0)
أحدث أقدم