# New Intelligent Drop List in Excel

Introduction 3

The Work Situation 3

The Old Method 4

Creating The Drop List 7

The Recent Method 10

The UNIQUE function 10

Create the Drop List 12

The Wow Method 13

Still Missing 15

Drop List without Blanks 16

Watch me doing it on my YouTube Channel 19

# Introduction

Data Validation is one of the most important tools in Excel to create dynamic Reports and Dashboards. Until recently, we had to prepare for creating Drop Lists, by extracting a List of unique Values. Not anymore.

In this article, I show you what we have been doing for decades, then what we recently did with Dynamic arrays and Finally what we currently do!

So, let’s have a look

# The Work Situation

In our example I have a list that shows: Payment, Product, Region and Sales.

I want to create a Drop List for the different Payment methods in cell H1.

For decades, we had to prepare for creating our drop list by extracting a list of unique values.

# The Old Method

• Select all the Payment methods in Column A (A2:A64)
• Click Copy on the Home Tab
• Select Cell F1
• Click Paste
• On the Data Tab ► Select Remove Duplicates (The Shortcut is ALT + A + M)

You get a message box saying that duplicates are removed ► Hit OK.

# Creating The Drop List

Now we can create our Data Validation Drop List in Cell H1.

• Select Cell H1
• Click on Data Validation on the Data Tab (Alternatively you may use the shortcut ALT + D + L)
• Under Allow ► Select “List”
• In the Source box ► Select the range of Unique Values that we previously extracted I Column F
• Hit OK.

Now you would have created the Drop List in Cell H1

# The Recent Method

With the introduction of Dynamic Array functions, we could now extract a list of unique values in column F without having to copy the payment methods.

## The UNIQUE function

• In cell F1 create a UNIQUE Dynamic Array Function.
• The function is created in cell F1 and it spills to the adjacent cells

=UNIQUE(A2:A64) ►Hit Enter

And the list of unique values is created.

## Create the Drop List

To create the Drop List in Cell H1

• Select Cell H1
• Click on Data Validation on the Data Tab (Alternatively you may use the shortcut ALT + D + L)
• Under Allow ► Select “List”
• In the Source box ► Select the first cell F1 (where you created the UNIQUE function) and type the Spilled Array symbol (Shift + 3)
• Hit OK.

And the Drop List is Created.

# The Wow Method

In Excel Online and in Office 365 You Do Not Need Anymore To Create A List Of Unique Values.

• We directly select F1 (where I want to create my Drop List) and click on Data Validation on the Data Tab
• Under Allow ► Select List
• In the Source box ► Select all the payment Methods in column A ► hit OK

The New Amazing Drop List is created:

1. All Duplicates are automatically removed

1. On top of that, it’s a searchable Drop-Down List:

Type a character and the options in the drop list shrink to match that character.

# Still Missing

The only disadvantage that we still have is the appearance of a Blank if the source data has blanks.

This problem is already resolved in Excel Online and I am sure it will soon be available on the Desktop version.

In the Region Column we have some blanks. If I use the previous method to create a Drop List, it will show a blank along with a list of Regions

Until the option becomes available in the desktop version, we can create a solution by using a combination of Dynamic Array Functions.

# Drop List without Blanks

• In Cell F1 create a UNIQUE function for the Region Column:

=UNIQUE(C2:C64) ► We get a unique list of regions along with a Blank

• Wrap the UNIQUE function in a SORT function. We type -1 to SORT Descending. This moves the Blank to the end of the List. The middle argument is skipped because we have a single column.
• Finally wrap the previous functions in a DROP function that will drop the last item:

=DROP(SORT(UNIQUE(C2:C64),,-1),-1)

Now we can create the drop list in Cell H1 using the spilled array we created in the previous step, to get a drop list having No blanks