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.
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:
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…
(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:
(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:
(4) You’ll now notice that the Data Validation box will appear on your screen:
(5) Select the ‘List’ option from the drop-down menu:
(6) Press the “Up” arrow in order to define the location where the values for the drop-down list can be taken:
(7) You’ll now need to switch to Sheet2 (as your values for the drop-down list are located in that sheet):
(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:
(9) Finally, press OK:
(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:
(11) Here are the values that you can choose from your drop list:
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:
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:
For the last task, let’s select the value of ‘Pending’ as can be seen below:
Here is the complete set of values chosen from the drop-down list for each of our 5 tasks:
You may also want to check the following source for additional tutorials on MS Excel.