How To Make A Drop-Down List In Excel

5 min read|Last Updated: March 22nd, 2024|Categories: excel|
table of content

Making a drop-down list in Excel lets you regularize your data and avoids making mistakes. You can add options such as YES or NO, FEMALE or MALE, or any other option to a cell too. Also, you are able to limit the value of other cells according to a cell containing a drop-down list. For example, when we select the Canada state, the cities of Toronto, Vancouver, and Montreal are displayed in another cell.

In this tutorial, we will learn about how to add a drop-down list in Excel. Also, we talk about:

  • Making lists of names
  • Creating a table
  • Naming a range
  • Adding a dependent list by indirect formula
  • Add a third dependent list

Add a Drop Down List 

If you already have a list and only need to create a drop-down list, follow these steps:

  1. Create a table.
  2. Select the cell you want to enter the drop-down list.
  3. Go to the Data tab,
  4. Click on the Data Validation from the Data Tools section.
  5. Go to the Setting tab from the Data Validation dialogue box.
  6. Open the Allow menu, and pick the List.
  7. In the Source box, enter the listed range you already have.
  8. Press OK.

But if you need to make a list, the following tutorial will guide you through it. Also, we’re going to learn how to make a Professional drop-down list:

Making lists of names

First, you need a workbook containing two worksheets. We named the worksheets “Data” and “Lists.” So open Excel, on the bottom of the worksheet press on  icon, and make a new sheet. By right-clicking on the Sheet1 and selecting Rename (or double click on the tab), you can change the name of sheet1 to Data (for this example), then press the Enter key on your keyboard.

How to add a new sheet to an Excel workbook?
Picture 1- Add a new sheet to an excel workbook

 

In the Data sheet, we’ll enter lists of names. These lists provide the items needed for affiliate drop down lists. The lists are:

  1. State: Massachusetts, California, and New York.
  2. City: Boston, Cambridge, Worcester, LA, SF, NY, NF.
  3. Branch: A, B, C, D, E, F, G, H, I, and J. 
Making a list of name for creating a drop-down list.
Picture 2- Lists of names

 

Creating a table

Follow the steps below to create a table with a name for your list. This will make your list dynamic, so new items that are added will be automatically added to your drop down list.

  1. Select one of your list cells.
  2. Go to the Home tab.
  3. From the Style section, click on the Format as Table.
  4. Choose one of the styles from the menu.
  5. Check the My Table Has Headers checkbox.
  6. Press OK.
How to make a table in Excel (part 1)?
Picture 3- Create a table in Excel (part 1)

 

How to make a table in Excel (part 2)?
Picture 4- Create a table in Excel (part 2)

 

Why should you put your information on the table?

Once your data is in a table, when adding or deleting items in the list,

each slide is automatically updated base