Create Dynamic Drop Down lists in Excel

Quickly create drop down lists in Excel with automatic sorting

The Drop Down list in Excel is a great automation tool. You can turn any data list into a drop down list which makes it easier to place items in cells. Drop down lists show the data in the same order in which they appear in the original list.

This may be a problem if you want your drop down list to be in alphabetical order while your original data should not be sorted. If the drop down list is long, it will take forever to find items if they are not sorted. Also, many data sets are dynamic (new records are added all the time). In this case, you need to keep altering the list range in your Data Validation which manages the list.

In this tutorial, you will learn how to create drop down lists which expand dynamically and sort alphabetically by default.

A normal drop down list looks like this:

List 1

To make the data list and the drop down dynamic as well as making the drop down alphabetical, follow these steps:

Step 1. Select the original list and give it a dynamic range name (which automatically includes new entries). See below.

DNR

Example of formula in ‘Refers to’ field:

=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A$1:$A$1000))

When you type the Offset formula into the ‘Refers to’ field, make sure the cell references reflect the position and size of your list, e.g. $A$1:$A$2000 if you have 1000+ records. You can make this any number of rows.

Step 2. The next step is to open a new or existing empty sheet where you can enter a formula which will create a copy of the original list, but sorted alphabetically. You must enter the formula manually and then press Ctrl + Shift + Enter to make it work. if you only press Enter, it will give an error. Here is an example of the formula:

=IF(COUNTA(List)>=ROWS($A$1:A1), INDEX(List, MATCH(SMALL(COUNTIF(List, “<“&List), ROW(A1)), COUNTIF(List, “<“&List), 0)), “”)

‘List’ in the formula represents the range name you gave your original list earlier.

Once you have typed the formula and pressed Ctrl + Shift + Enter, copy this formula down until it shows an empty cell. This means all the records of the original list are in this list.

LONG

After copying this formula down, your alphabetised list will show. Next, highlight this list and give it a range name.

Newname

The last step is to create the drop down list using the range name as the source.

Dynamic Drop Down lists

You have just created a drop down list which will dynamically update with the original list, and which will always be in alphabetical order!

final

 

Additional Resources

Create a drop-down list in Excel

Dynamic (Cascading) Dropdowns that reset on change

Create a drop-down list in Excel

Save time by avoiding repetition and errors

Excel is great for lists; sales figures, staff rota’s, stock control, to name a few. But an easy trap to fall into is repetitive error prone data-entry that leads to inaccurate business reporting and lost time in troubleshooting.

If you create a drop-down list in Excel, you can avoid all of this. Imagine the time saved short and long term especially if multiple people are using the same spreadsheet.

How to create a drop-down list in Excel

excel_drop_list
Here’s one we made earlier

Step 1 Assign the values for your drop-down list. In a new worksheet, just start your list and order it if you wish (better now than later!)

an_excel_list
advanced Excel courses

Step 2 Now select the data and right click, select Define Name.

Step 3 In the New Name dialogue box you need to give your data name (this is a named range), making sure not to have any spaces in the name. Example, Commute

Name the range

Step 4 Now go to the worksheet where you wish create a drop-down list in Excel, and click a cell. Go to the Data tab and select Data Validation

select data validation

Step 5 In Settings tab we need to do the following:

Select List from the Allow box.
Ensure In-cell dropdown is ticked. If you are okay for blank entries to be made just leave the Ignore blank ticked.
In the Source box we need to type in the name of our list making sure to start with an =. In this case, =Commute

data validation options

Now click OK, your drop-down list is ready to go. You may have noticed two other tabs within the Data Validation box. The Input Message and Error Alert give you even more options to control how data is entered and also what messages appear to users when they have not entered data correctly.

To create a drop-down list in Excel is pretty straight forward giving us some major advantages in saving time from data entry as well as data error. Data validation in it’s own right can really help businesses adopt more consistent and efficient use of Excel spreadsheets.

More related information:

Excel data validation in business

A real world example of assigning values to a drop-down list in Excel

A further look at Input Message & Error Alert