How to Create a Drop-Down List in Excel 

Need to create a drop-down list in Excel? If so, in this post I’ll show you the steps to create a drop-down list in Excel 2016.

The example

To start, let’s review an example where you have 5 simple tasks that you want to track:

(1) Place a purchase order to buy a computer
(2) Pay for the purchase order
(3) Delivery of order arrived
(4) Install the computer
(5) Test that the computer is operational

You can create a drop-down list to track the status of each of the above tasks.

Here is the list of statuses that you can use to track your tasks:

• Started
• Pending
• Ongoing
• Completed
• Failed

In the next section, we will review how to establish a drop-down list in Excel to track the above tasks…

Steps to create a drop-down list in Excel

Let’s review the steps to create a drop-down list in Excel:

(1) First, you’ll need to copy or type the tasks’ descriptions into ‘Sheet1’

Your Sheet1 should look as below. Notice that we added an additional column (i.e., column B) to track the Task Status. This is where we will place the drop-down list…

 

Data in Excel

 

(2) Now, switch to ‘Sheet2’ where you will need to copy or type the list of statuses that will be available to choose from the drop-down list.

Your Sheet2 should look as below:

 

Cells in Excel

 

(3) Return to Sheet1 and select the cell B2 (this is the cell where you will initially place your drop-down list)

Then, press the Data tab.

Finally, press the Data Validation icon as below:

 

Data Validation in MS Excel

 

(4) You’ll now notice that the Data Validation box will appear on your screen:

 

How to Create a Drop-Down List in Excel

 

(5) Select the ‘List’ option from the drop-down menu:

 

How to Create a Drop-Down List in Excel

 

(6) Press the “Up” arrow in order to define the location where the values for the drop-down list can be taken:

 

Create a Drop-Down List in Excel 

 

(7) You’ll now need to switch to Sheet2 (as your values for the drop-down list are located in that sheet):

 

Excel - Data Validation

 

(8) Once in Sheet2, select the range of cells (i.e., the range A1 to A5) where the values for the drop-down list are located.

Then, press the “Down” arrow:

 

Data Validation - Range of cells in MS Excel

 

(9) Finally, press OK:

 

How to Create a Drop-Down List in Excel 2016

 

(10) Your drop-down list is now available to be used in cell B2 under Sheet1.

Press on the “Down” arrow to populate the drop-down list:

 

Drop-down menu in MS Excel

 

(11) Here are the values that you can choose from your drop list:

 

Drop-down list in Excel

Apply the drop-down list across additional cells

Earlier, we saw how to apply the drop-down list into cell B2.

But what if you would like to apply the drop-down across additional cells?

Well, the good thing about Excel is that you can drag the drop-down list across multiple cells.

To do so, simply hover on the bottom right-hand-side of cell B2, until you see the symbol ‘+’

Then, drag-down the + symbol all the way to cell B6:

 

Apply the drop-down list across additional cells

 

Your drop-down list is now available for each of your 5 tasks. Let’s choose some values from the list…

For the task ‘Place a purchase order to buy a computer’ let’s choose the option of ‘Completed’ from the list under cell B2:

 

Drop-down list across additional cells

 

For the last task, let’s select the value of ‘Pending’ as can be seen below:

 

Status in MS Excel

 

Here is the complete set of values chosen from the drop-down list for each of our 5 tasks:

 

Drop-down list - MS Excel 2016

 

You may also want to check the following source for additional tutorials on MS Excel.