Excel - Create dynamic drop down lists Tutorial

In this video, you will learn how to create dynamic drop-down lists using Microsoft 365. The video covers the process of extracting a list of sellers from an order board and creating a dropdown list that includes each seller only once.
It demonstrates the use of Excel functions such as UNIQUE and SORT to organize the list alphabetically.
The video also explains how to set up the dropdown list using the built-in validation feature in Excel.
By using dynamic matrix functions, the dropdown list can accommodate new sellers added to the order sheet.
This tutorial will help you create efficient and flexible drop-down lists for your data management needs.

524 views

00:00:00

In this tutorial we will see how

00:00:02

to create dynamic dropdown lists.

00:00:05

I want to create a dropdown list

00:00:07

that will offer me a list of sellers

00:00:09

in order to extract the number of

00:00:11

associated orders we have available

00:00:13

here in column C, a table of orders

00:00:16

which list our customers

00:00:18

associated with our sellers.

00:00:20

Then the same seller can appear several

00:00:22

times in the column since several

00:00:24

orders are associated with them.

00:00:26

To be able to create the dynamically

00:00:29

sized dropdown list that will

00:00:30

only include each seller once,

00:00:32

I will use the power of Excel

00:00:34

by the =UNIQUE function.

00:00:36

The first step will be to extract the

00:00:38

list of all sellers regardless of how

00:00:41

many times they appear on my order board.

00:00:44

To do this I have created a

00:00:46

sheet that I called list.

00:00:53

The order in which all employees

00:00:55

appear on the list is defined

00:00:57

by the order in which all

00:01:00

employees appear in column C.

00:01:01

To improve the ergonomics

00:01:03

and presentation of it

00:01:05

I will integrate another matrix function.

00:01:08

called =SORT, allowing me to

00:01:12

order each seller of this result

00:01:14

table in alphabetical order.

00:01:19

Now let's move on to creating the

00:01:21

dropdown list through the builtin

00:01:22

validation feature within Excel.

00:01:30

The problem with this validation

00:01:32

option will be to define a list

00:01:35

that will be based on a source.

00:01:37

And this source will be

00:01:38

extracted from the seller sheet.

00:01:42

The problem also will be the length of

00:01:44

the drop down list that will potentially

00:01:46

be able to accommodate new sellers

00:01:48

overtime so I could select the area.

00:01:53

But at that time my drop down list

00:01:55

would be limited to the sale and to 9

00:01:57

maximum and any seller added in the

00:01:59

order sheet that would take his place

00:02:02

would not be integrated.

00:02:05

We can however and thanks to

00:02:07

dynamic matrix functions,

00:02:08

refer to matrix results,

00:02:10

which will be the case here.

00:02:12

And it will be enough to specify

00:02:15

the starting cell by clicking on

00:02:17

it and adding the pound sign.

00:02:19

When I validate with OK,

00:02:21

we will have a dropdown list offering

00:02:23

alphabetically the list of wholesalers

00:02:25

available to be selected from this list,

00:02:28

for which I could set up as

00:02:29

it is the case here,

00:02:31

a fairly simple continuing function.

00:02:34

To a BC function allowing to count how

00:02:37

many times the same name of employees

00:02:40

presented in cell three of this

00:02:43

table will be presented in the column used.

00:02:46

If I add any additional data to my table.

00:02:50

Let's say a fake order customer

00:02:53

adds with seller Laurent Balardy.

00:03:02

We can see on the seller sheet

00:03:05

that Laurent Balardy has taken its place,

00:03:08

so it is now available in the generated

00:03:11

dropdown list here at the very top

00:03:14

with a comment associated with it.

RELATED COURSES This tutorial is part of the following courses

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

Your trainer for this course
Cours E-learning

164 vues

ALL COURSES