Excel Drop Down List Creation Guide

[nerdslife_team]

Drop-down lists in Excel are super handy for keeping data consistent. They give users a set of choices, which makes entering info quicker and helps avoid mistakes.

Method 1: Using a formula for a dynamic list

This is the most flexible method, especially if your list might change in the future.

1. First, make your list of options somewhere in your workbook, either on the same sheet or on another sheet. For example, let’s say you have years listed in cells B1 through B5 on Sheet2.

   

2. Now, go back to the sheet where you want the dropdown, and select the cell, such as cell D1.

   

3. Open the “Data Validation” window by clicking “Data” tab and then “Data Validation”

   

4. In the “Source” box, use the OFFSET formula to create a dynamic range. For instance:

   =OFFSET(Sheet2!$B$1,0,0,5)
   

   This formula tells Excel to start at cell B1 on Sheet2 and include the next five rows. You should change “5” to match how many items are in your list.

   

5. Click “OK,” and the drop-down list will appear in the cell. It will display the items from the range on Sheet2.

   

Method 2: Using a list of cells

This is a straightforward method when your list of items is in your spreadsheet and unlikely to change much.

1. Say you want a drop-down to track trip statuses, and you have statuses like “Finished” and “Pending”. List these statuses in a column, either on the same sheet or another one. In our case, let’s use Sheet2.

   

2. Go back to the main sheet (e.g., Sheet1) and select the cell where you want the dropdown (for example, cell B2).

   

3. Go to the “Data” tab and click on “Data Validation.” Select “Data Validation” from the dropdown menu.

   

4. In the Data Validation window, go to the “Settings” tab and choose “List” from the “Allow” dropdown.

   

5. Click in the “Source” box, then navigate to the sheet with your list (Sheet2) and select the cells containing your options (for instance, A1:A5).

   

6. The selected range will appear in the “Source” field. If you don’t want users to be able to leave the cell blank, uncheck “Ignore blank.” Then, click “OK” to create the dropdown.

   

7. Now, you’ll have a drop-down menu in the cell.

   

8. To copy the drop-down to more cells, click the fill handle at the bottom-right corner of the cell with the dropdown and drag it down to the range you want, like from B2 to B6.

   

9. Now the dropdown list will be copied to all the cells you selected.

   

Method 3: Entering data manually

This works best for short lists that won’t change.

1. Select the cell for your drop-down list, such as C2 under the “Season” column.

   

2. Go to the “Data” tab and click on “Data Validation.”

   

3. In the Data Validation box, choose “List” from the “Allow” dropdown. In the “Source” box, type your list items, separated by commas (without spaces), such as “Spring,Summer,Fall,Winter”, then click “OK”.

   

4. Now you’ll see the list in a drop-down in your selected cell. You can copy this drop-down to other cells just as described before.

   

Removing a drop-down list

1. To remove a drop-down list, select the cell. Then go to the “Data” tab, click on “Data Validation,” and click “Clear All” in the dialog box.

   

2. To remove dropdown lists from multiple cells, select all the cells and then open the Data Validation dialog, click “Clear All”. Or, you can check “Apply these changes to all other cells with the same settings” before clicking “Clear All,” then click “OK.”