Intelligent Drop Lists and Evolution of Data Validation

New Intelligent Drop List in Excel

Table of Contents

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

Download Exercise File and Follow Along 19

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.

A screenshot of a computer Description automatically generated

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

The Old Method

A blue car with two people in the back Description automatically generated

  • 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)

A screenshot of a computer Description automatically generated

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

A screenshot of a computer Description automatically generated

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.

A screenshot of a computer Description automatically generated

Now you would have created the Drop List in Cell H1

A screenshot of a computer Description automatically generated

The Recent Method

A car parked in front of a black background Description automatically generated

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

A screenshot of a computer Description automatically generated

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.

A screenshot of a computer Description automatically generated

And the Drop List is Created.

The Wow Method

A red sports car with a black background Description automatically generated

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

A screenshot of a computer Description automatically generated

The New Amazing Drop List is created:

  1. All Duplicates are automatically removed

A screenshot of a computer Description automatically generated

  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.

A screenshot of a computer Description automatically generated

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

A screenshot of a computer Description automatically generated

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)

A screenshot of a computer Description automatically generated

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

A screenshot of a computer Description automatically generated

Enjoy your “Intelligent” Drop-Down List

A black background with a black square Description automatically generated with medium confidenceA logo for a company Description automatically generated

Download Exercise File and Follow Along

A red sign with white text Description automatically generated

Watch me doing it on my YouTube Channel- Click Below

A cartoon character on a blue background Description automatically generated

 

Share This Post
Have your say!
0 0

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.