How to Create a Drop-Down List in MS Excel

Would you like to select items from a list, rather than typing in the same things over and over? You can create a drop-down list in Excel for exactly that purpose. You can create a cell on your spreadsheet, with a little drop-down arrow, which provides a choice of values when someone clicks on it – this is called a drop-down list.

When you use a drop-down to limit the inputs, data entry becomes much quicker and more reliable. You can easily create drop-downs in MS Excel using several methods listed below.

4 Methods of creating a drop-down list in MS Excel:

  • Creating a drop-down manually
  • Creating a drop-down using a range of cells
  • Creating a drop-down using a table
  • Creating a drop-down using the OFFSET formula

Method #1: Creating a Drop-Down Manually

This method allows you to enter the values manually in the source field. It is ideal for a short range of values that won’t change, such as the days of a week or Yes/No.

Step #1: Select the target drop-down cell

Select the cell where you want the drop-down to appear,  by clicking on it. 

You can see the selected cell name in the Name Box in the top-left corner. 

In our example, it’s B1.

Step #2: Select ‘Data Validation’

In the main menu bar, click on the Data tab.

From the ribbon that appears, select the icon ‘Data Validation’.

When the drop-down menu appears, click on ‘Data Validation…’ again.

Step #3: Enter the drop-down values

Once you have select Data Validation, a Data Validation dialog box will open. 

Under the ‘Allow:’ drop-down, select List.

Once you select List, you will see a ‘Source:’ text field. 

Enter your drop-down values in the text field. Separate the different values using commas, with no spaces between them.

Step #4: Click OK

Once you’re done entering the values in the ‘Source:’ text field, click the OK button.

Once you click OK, you will see the drop-down options in the cell you selected in the beginning.


Method #2: Creating a Drop-Down Using a Range of Cells

For this option, you can use the values present in a few cells as your drop-down values. When you change the cell values, the drop-down changes accordingly.

Step #1: Input the values in cells

To start you will have to enter the planned drop-down values in different cells. 

In our example, we have four values in the first four rows of column A.

Step #2: Select the target drop-down cell

Select the cell where you want the drop-down list to show. 

In our example, cell I1 is selected. 

You can see the value of the highlighted cell in the Name Box – top left.

Step #3: Select ‘Data Validation’

In the main menu bar, click on the Data tab.

From the ribbon that appears, select the icon ‘Data Validation’.

When the drop-down menu appears, click on ‘Data Validation…’ again.

Step #4: Select the source of the list

Once the Data Validation dialog box opens, under the ‘Allow:’ drop-down options, select List.

Once you select List, you will see a ‘Source:’ text field. 

Place your cursor in this ‘Source:’ text field.

Click on the arrow to the extreme right if this field.

The dialog box will minimize so you can view your spreadsheet.

Select the cells with the drop-down values by clicking and dragging your cursor over the range of cells. 

Once done, the dialog box will reappear with the Source text field automatically referencing the  range of cells you selected.

Step #5: Click OK

Make sure that the cell reference is right. 

Click on the OK button once you’re done.

You’ve now created a drop-down successfully. 

Note: This is not a dynamic list. If you add another option in the fifth row, it will not reflect in the drop-down. This is because we’ve only selected the first four rows. Only changes in those four rows will be incorporated into the drop-down.


Method #3: Creating a Drop-Down Using a Table

You can create a table and link the values in that table as your drop-down values. Any changes in the table will be reflected in the drop-down values.

Step #1: Create a table

Enter the planned drop-down values in cells. 

In our example, we have a header in the first row and the four drop-down options in the next four rows. 

Once you have entered the values, select the range of cells and the header by dragging your cursor across them.

In the main menu bar, click on the Data tab.

From the ribbon that appears, select the icon ‘From Table’.

You will now see the Create Table dialog box. 

The field will automatically be populated with the highlighted rows. Check if they’re right. 

Click on the OK button.

Step #2: Name your table

A Power Query dialog box will now open. 

Here, you can rename your table to something more convenient. This would help you when you reference it later. 

In our example, we have named the table statusOptions.

Once you’re done, close the dialog box.

Step #3: Select the target drop-down cell

Now select the cell where you want to display the drop-down list.

You can see the value of the highlighted cell in the Name Box – top left.

Step #4: Select ‘Data Validation’

In the main menu bar, click on the Data tab.

From the ribbon that appears, select the icon ‘Data Validation’.

When the drop-down menu appears, click on ‘Data Validation…’ again.

Once the Data Validation dialog box opens, under the ‘Allow:’ drop-down options, select List.

Step #5: Link the table as the source

Once you select List, you will see a ‘Source:’ text field. 

To link the table to the drop-down, you have to enter the following formula in the text field. 

=INDIRECT(“table_name[header_name]”) 

In our example the table name is ‘statusOptions’, and the header of the column is ‘Options’ so our formula would be =INDIRECT(“statusOptions[Options]”) 

Step #6: Click OK

Check your formula once more to see if it is correct.

Click on the OK button.

You can now see the drop-down values imported from the table.


Method #4: Creating a Drop-down Using The OFFSET Formula

With the previous methods, if you add more values to your list or table, these entries won’t display in your drop-down list. This is because the drop-down list only accounts for the range of cells you’ve indicated in the beginning when creating it. 

However, if you frequently want to add values dynamically, this is the method for you.

Step #1: Select the target drop-down cell

Select the cell where you want the drop-down list to show. 

In our example, cell D1 is selected. 

You can see the value of the highlighted cell in the Name Box – top left.

Enter the planned drop-down values in different cells. 

In our example, we have four values in the first four rows of column A.

Step #2: Select ‘Data Validation’

In the main menu bar, click on the Data tab.

From the ribbon that appears, select the icon ‘Data Validation’.

When the drop-down menu appears, click on ‘Data Validation…’ again.

Once the Data Validation dialog box opens, under the ‘Allow:’ drop-down options, select List.

Step #3: Enter the formula

Once you select List, you will see a ‘Source:’ text field. 

Place your cursor in this ‘Source:’ text field.

Enter the formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)

The offset formula has five parameters or arguments:

  1. The first one is the reference. It represents the cell reference of the first value. Here, it’s A1 and relatively written as $A$1.
  2. The second one represents the rows to offset.
  3. The third one represents the columns to offset.
  4. The fourth one counts the values in the specified column. Here, it is column A. This automatically excludes empty cells and counts the cells with values in them. 
  5. The last one represents the width.

Step #4: Click OK

Once you have entered the formula, click the OK button.

You can now see the drop-down under column D.

Step #5: Add values dynamically to the list

This step is only a test to see if the drop-down works dynamically.

Just add another value in the next row – A5. 

Now check your drop-down to see if the change is reflected.


The Bottom Line

A drop-down is a wonderful way to make your spreadsheet more user-friendly, save you time, and build a spreadsheet that many people can use.

If you have a few simple values that are fairly static, method 1 will suit you fine. If you have a slightly longer list, method two is also ideal to use, and gives you a more visual approach to your list, as you can see the values on your spreadsheet.

Method 3 is great if you want to create several drop-down lists. We suggest having one spreadsheet with all the different tables, while your working spreadsheet only displays the drop-down lists.

The best option though for a list that constantly changes, such as team members, would be a dynamic drop-down, as explained in method 4.

Pick a method that suits your needs and use it to create a drop-down in no time.

If you inherited a drop-down list, see our related article – How to Edit a Drop-Down List in MS Excel.

Leave a Comment