Tech

How to create a drop down list in Excel?

Excel is full of very practical features, including drop-down lists. For those who don’t know how to go about it, here are the steps to follow to create one.

The drop down lists are one of the most commonly used features in Excel. They make it possible to impose a set of acceptable values ​​in a cell. It is therefore a list of choices with predetermined data, unlike cells in free entry.

This feature can be useful, for example, when you have to produce a document to be completed by other users. This allows you to limit certain cells to specific choices.

Excel: how to create a drop-down list?

Creating a drop-down list in Excel is done in one or two steps depending on the complexity of the data to be displayed. The simplest is to populate the list items directly, without going through a data source.

  • Select the cell in which you want to create the drop-down list.
  • Open the tab Data in the Excel menu. In the section Data toolsselect Data Validation.
Drop-Down List Data Validation
  • In the small window that appears, stay in the tab Options then select Listing at line level To allow.
  • In the field Sourceenter the items from the drop-down list separated by a semicolon (;) then validate.
Manual drop down list in Excel

  • You have just created a drop-down list.
Drop-down list: cities

This is the easiest way to create a drop down list in Excel. But sometimes the data to be displayed already exists in a range of cells or in a table located in the same document. In this case, these cells should be used as the data source. This method is more convenient for long lists. Even more so if they must be scalable.

Create drop down list in Excel using data source

With the first method, we have learned how to directly input drop-down list items at the target cell in Excel. In the second method, which this time takes place in two steps, First, you need to create a data source.. This is a range of cells that contains the items to display in the drop-down list.

The second step is to fetch the items from the source range and display them in the drop down list. The source can be on the current worksheet or in another sheet of the same Excel document.

Step 1: Create the data source

The source functions as a database containing the items in the drop-down list. These elements must be completed line by line in the same column. Let’s go back to our example of a list of cities. You must enter them one after the other as in the screenshot below.

Data source: cities

Note that it is quite possible to have the data source on the same sheet as the one that should host the drop-down list. But for practical reasons, we advise you to create the source in another sheet. This allows for more clarity.

Read also: how to put a password on an Excel file?

Step 2: creating the drop-down list

This process is practically the same as the one described in the first section of this tutorial. The steps change slightly from point 4.

  1. Select the cell in which you want to create the drop-down list.
  2. Go to the tab Data from the Excel menu. In the section Data toolsselect Data Validation. A small window appears.
  3. In the tab Options from said window, select Listing at line level To allow.
  4. In the field Source, click on the arrow at the right end then select the range containing the data to display in the list. Then press Walk in then on OK.
Drop-down list: retrieve data from source

Note that in our example, the source is in the range of cells G4 to G8 on another sheet named Data source. We could have named this sheet differently. For example “Source cities”, “reference cities”, etc.

You now know how to create a drop down list in Excel. The simplest method is to enter the list items directly at the data validation level. But for more modularity and to have a more flexible list, it is better to create a separate column with the different elements to display.

Related Articles

Leave a Reply

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