🚀 Think you’ve got what it takes for a career in Data? Find out in just one minute!

Dropdown Excel: How does it work?

-
3
 m de lecture
-
Drop-down list in Excel: How does it work?

An essential function in Excel, the Dropdown Excel list facilitates data entry on spreadsheets, minimizing errors. But how do you create a drop-down list in Excel? Find out the answers.

What is a Dropdown Excel List?

The Dropdown Excel feature that creates a list of predefined choices in a cell. In this way, the user doesn’t need to enter data for each cell. Simply select an option from the drop-down menu.

The Dropdown Excel list is an invaluable tool for data analysts who have to deal with several hundred rows of data. And with good reason, this Excel function enables you to:

  • Simplify data entry: thanks to the Dropdown Excel list, you no longer have to enter the same data over and over again. You can simply select the data you need from a list of predefined choices.
  • Harmonize data: with manual data entry, you sometimes unintentionally add an accent or an extra space, or make typing errors. The result: multiple entries for a single category. This is no longer the case with the Excel drop-down list, since each entry is made only once, when the list is created.
  • Reduce the risk of calculation errors: as the data is uniform, there is less risk of calculation errors.

How do I create a Dropdown Excel list?

There are several ways to create Dropdown Excel list. It all depends on your needs. Here are the main methods.

Simple drop-down list

To create a classic Dropdown Excel list, proceed as follows:

  • Select the cells for which you wish to implement the drop-down list;
  • Click on “Data”, then “Data validation”;
  • In the “Options” tab and the “Allow” zone, select “List”;
  • In the “Source” zone, select the relevant cell range;
    Click on “OK”.

 

💡 Related articles:

Pie Chart Excel: Mastering Data Visualization
Creating Stunning Excel Charts: A Step-by-Step Guide
Efficient Excel Data Formatting Based on Cell Values
How do I insert columns and rows in Excel?
Excel to Power BI: how to transform a pivot table in Excel into a dataset that can be used by Power BI?
Excel vs. Power BI: What are the differences?

Drop-down list with fixed values

This type of list is particularly useful if the list values are limited. For example, True/False.

Here’s how it works:

  • Select the cells for which you want to implement the drop-down list;
  • Click on “Data”, then “Data validation”;
  • In the “Options” tab and “Authorize” zone, select “List”;
  • In the “Source” zone, select the data to be entered in the list (e.g. TRUE / FALSE);
  • Click on “OK”.

Please note! With Excel, typography is crucial, especially when it comes to capitalization. For example, if the user types “true” without capital letters, an error message will appear.

Dropdown Excel list from a named cell range

This type of Dropdown Excel list is particularly useful if your data is going to be modified. With this model, the list will automatically adapt as you add or delete items.

To use this method, you must first name a range of cells. Here’s how to do it:

  • Select the cells and click on the “Name box” (above column A);
  • Type the name you wish to give to your list (the name must begin with a letter or the “_” sign and contain no spaces).

Then simply create your Dropdown Excel list:

  • Select the cells for which you want to implement the drop-down list;
  • Click on “Data”, then “Data validation”;
  • In the “Options” tab and the “Authorize” zone, select “List”;
  • In the “Source” field, type the “=” sign, then the name assigned to your list;
  • Click on “OK”.

Dynamic Dropdown Excel list from a table

This is a dynamic list, since the list adapts to any modification of the cell range. In this case, an Excel table.

To do this, you need to name your table. Here’s how to do it:

  • Select the entire table or a single cell;
    Click on “Table” (in the “Insert” tab);
  • If you have selected a single cell, Excel will suggest all the cells it considers to be part of the table.
  • Check that there are no errors;
  • Check the “My table has headers” box;
  • Click on “OK”;
  • Select the table and click on the “Name zone” (above column A);
  • Type the name you wish to give your table.

Then simply repeat the steps for creating a drop-down list in Excel. Namely:

  • Select the cells for which you are implementing the drop-down list;
  • Click on “Data”, then “Data validation”;
  • In the “Options” tab and the “Allow” zone, select “List”;
  • In the “Source” field, type “=INDIRECT(“TableName[column header]”) ;
  • Click on “OK”.

The Dropdown Excel list, an indispensable tool for data analysts

By facilitating data entry and harmonizing data, the Excel drop-down list simplifies the work of data analysts. This feature lets you enter all types of values (whether fixed cells, tables, etc.). And best of all, with the dynamic drop-down list, any modification to the cell range is automatically integrated.

If you want to master the Excel functions that are useful for data analysis, we invite you to discover our dedicated training course.

Facebook
Twitter
LinkedIn

DataScientest News

Sign up for our Newsletter to receive our guides, tutorials, events, and the latest news directly in your inbox.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox