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.

3:26
2718 views

Available in Microsoft365

Objectifs :

This tutorial aims to guide users in creating dynamic dropdown lists in Excel that display unique sellers from an order table, allowing for efficient data extraction and management.

Chapitres :

Introduction to Dynamic Dropdown Lists
In this tutorial, we will explore how to create dynamic dropdown lists in Excel. The focus will be on generating a list of unique sellers from an order table, which will help in extracting the number of associated orders available in the dataset.
Understanding the Order Table
The order table contains a list of customers associated with various sellers. It is important to note that the same seller may appear multiple times in the column due to several orders being linked to them. Our goal is to create a dropdown list that includes each seller only once.
Using the UNIQUE Function
To achieve a dynamically sized dropdown list, we will utilize Excel's UNIQUE function. This function allows us to extract a list of all sellers, regardless of how many times they appear in the order table. We will create a separate sheet named 'List' to store this unique list.
Sorting the Seller List
To enhance the presentation and usability of the seller list, we will incorporate another matrix function called SORT. This function will arrange the sellers in alphabetical order, making it easier to navigate the dropdown list.
Creating the Dropdown List
Next, we will create the dropdown list using Excel's built-in data validation feature. The challenge here is to define a source for the dropdown list that dynamically updates as new sellers are added. Instead of limiting the dropdown to a fixed range, we will use dynamic array functions to reference the results from our seller list.
Implementing Dynamic Ranges
To set up the dynamic dropdown list, we will specify the starting cell and add a pound sign (#) to indicate that we want to include all subsequent cells in the range. This allows the dropdown list to automatically adjust as new sellers are added to the order sheet.
Counting Associated Orders
Once the dropdown list is created, we can implement a COUNTIF function to count how many times a selected seller appears in the order column. This will provide valuable insights into the number of orders associated with each seller.
Testing the Dynamic Dropdown
To demonstrate the functionality, we can add a new order with a seller, such as 'Laurent Balardy.' After adding this data, we will see that Laurent Balardy appears at the top of the dropdown list, confirming that our dynamic list is working correctly.
Conclusion
In this tutorial, we successfully created a dynamic dropdown list in Excel that displays unique sellers from an order table. By utilizing the UNIQUE and SORT functions, along with dynamic ranges, we ensured that the dropdown list remains up-to-date and user-friendly. This approach enhances data management and allows for efficient order tracking.

FAQ :

How do I create a dynamic dropdown list in Excel?

To create a dynamic dropdown list in Excel, use the UNIQUE function to extract unique values from your data source. Then, apply Data Validation to create the dropdown list, referencing the range that includes the UNIQUE function output.

What is the purpose of the UNIQUE function?

The UNIQUE function is used to filter out duplicate entries from a list, providing a clean list of unique items that can be used in dropdowns or other analyses.

Can I sort the items in my dropdown list?

Yes, you can sort the items in your dropdown list by using the SORT function in conjunction with the UNIQUE function to ensure that the dropdown items are displayed in alphabetical order.

What happens if I add new sellers to my order sheet?

If you add new sellers to your order sheet, the dynamic dropdown list will automatically update to include the new sellers, provided you have set it up correctly using dynamic array functions.

How can I count the number of orders associated with a seller?

You can count the number of orders associated with a seller by using the COUNTIF function, which allows you to specify a condition (the seller's name) and count how many times it appears in your order data.

Quelques cas d'usages :

Sales Order Management

In a sales department, a dynamic dropdown list can be used to select sellers when entering new orders. This ensures that only valid sellers are chosen, reducing errors and improving data integrity.

Customer Relationship Management (CRM)

In a CRM system, using dynamic dropdowns for selecting sales representatives can streamline the process of assigning orders to sellers, making it easier to track performance and manage customer interactions.

Inventory Tracking

When managing inventory, a dynamic dropdown list can help in associating products with specific sellers, allowing for better tracking of stock levels and sales performance.

Reporting and Analytics

In reporting scenarios, using dynamic dropdowns to filter data by seller can enhance the analysis process, allowing users to generate reports based on specific sellers and their associated orders.

Training and Onboarding

During training sessions, demonstrating how to create and use dynamic dropdown lists can help new employees understand data management in Excel, improving their efficiency in handling order data.

Glossaire :

Dynamic Dropdown List

A dropdown list in Excel that updates automatically based on the data source, allowing users to select from a list that reflects current entries.

UNIQUE function

An Excel function that extracts unique values from a range, ensuring that each item appears only once in the result.

SORT function

An Excel function that arranges the values in a specified order, such as alphabetical, making it easier to read and navigate through data.

Data Validation

A feature in Excel that allows users to control the type of data entered into a cell, including the creation of dropdown lists.

Matrix Functions

Functions in Excel that perform calculations on arrays of data, allowing for more complex data manipulation and analysis.

Cell Reference

A way to refer to a specific cell in Excel, which can be used in formulas to perform calculations based on the data in that cell.

Count Function

An Excel function that counts the number of cells that contain numbers or meet a specified condition.

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.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2677 Views

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 Po...

ALL COURSES