How to Create Drop Down List in Excel: A Step-by-Step Guide

Creating a drop down list in Excel is a handy skill to have, especially if you’re dealing with a lot of data. It’s pretty simple too. All you need to do is select the cell where you want the list, go to the Data tab, click on Data Validation, and then select List. From there, you just type in the values you want to appear in the list, and voilà! You’ve got yourself a drop down list.

Step by Step Tutorial on How to Create a Drop Down List in Excel

When you create a drop down list in Excel, you’re making it easier for yourself and others to enter data consistently. This can be especially useful if you’re sharing your spreadsheet with others or using it for data entry purposes.

Step 1: Select the cell where you want the drop down list

Click on the cell where you want your list to appear.
This cell will be the home of your new drop down list, so choose wisely.

Step 2: Go to the Data tab

Find the Data tab in the ribbon at the top of Excel.
This tab is where all the magic happens when it comes to data manipulation in Excel.

Step 3: Click on Data Validation

Look for the Data Validation button and give it a click.
This button will open up a new menu where you’ll set up your drop down list.

Step 4: Choose ‘List’ under Validation criteria

In the Data Validation menu, under ‘Allow’, select ‘List’.
This tells Excel that you want to create a list of options for your drop down.

Step 5: Type in your list items

In the ‘Source’ box, type the items you want to appear in your drop down list, separated by commas.
Make sure each item is separated by a comma with no spaces, and that you include all the options you want to offer.

After you’ve completed these steps, you’ll have a functional drop down list in your selected cell. This will make data entry faster, easier, and more accurate.

Tips for Creating Drop Down Lists in Excel

  • Make sure your list items are clear and concise.
  • Consider sorting your list alphabetically or logically to make it easier to use.
  • If you have a long list of items, consider placing them in a separate Excel sheet and referencing them in the Data Validation menu.
  • Use named ranges in Excel to manage your list items more efficiently.
  • Test your drop down list to make sure it works as intended before sharing your spreadsheet.

Frequently Asked Questions

Can I create a drop down list with items from another worksheet?

Yes, you can reference items from another worksheet by using a named range or by directly referencing the cells.

Can I edit the items in my drop down list after it’s been created?

Absolutely, just go back to Data Validation and update the ‘Source’ field with your new items.

Can I change the appearance of the drop down button?

While you can’t change the actual button, you can adjust the size of the cell to make it more noticeable.

How do I delete a drop down list?

To remove a drop down list, go back to Data Validation and select ‘Clear All’.

Can I create a drop down list that allows users to enter their own data?

Yes, you can enable this by checking the ‘Ignore blank’ and ‘In-cell dropdown’ boxes in the Data Validation menu.


  1. Select the cell for the drop down list
  2. Go to the Data tab
  3. Click on Data Validation
  4. Choose ‘List’ under Validation criteria
  5. Type in your list items


In conclusion, creating a drop down list in Excel is a breeze once you get the hang of it. It’s a fantastic way to streamline data entry, maintain data integrity, and provide an overall cleaner look to your spreadsheets. Whether you’re a seasoned pro or a beginner, mastering this skill will undoubtedly make your Excel experience more efficient and enjoyable. So why not give it a try? Create your first drop down list today and watch as your data management becomes a whole lot easier. And if you ever get stuck, remember that Excel has a great help feature and there are tons of resources available online. Happy Excelling!

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy