How to create a drop down list in Excel

It is unfortunately all too common for many people performing data entry work to suffer from Fat Finger Syndrome. Imagine creating a detailed sales report for your company, handing it off to your executives, only to find out that Michael only had half of his usual sales for the month, because 50% of his entries were spelt Micheal instead of Michael. Poor Michael.

Ensuring that salesmen named Michael get their full month’s commission isn’t the only reason for creating a drop-down list though. Some spreadsheets are built to distribute to different people to log data with, whom of which may not be aware of what an example answer may be for any given column. Or you want to restrict the values in a column to a certain list of available options. Whatever the reason is, the solution is easy with only a few trivial steps!

Before we actually create a list, we need a table to enter data in. For this example, let’s assume we’re keeping track of a sales record table and want to ensure that some of the columns provide a drop-down list to choose a value from. (More than likely you already have a table you need to add your drop down list to, so just jump to the next part)

Basic Excel Table for Sales Report

Create lists in new separate tab

In order to create a drop down list, we will need to create the list range. For this, I like to keep a separate tab for all my drop down lists.

  • Create new Tab
  • Make your list(s)
    • Pro Tip: if you have many lists, it makes it easier to add a header label to each list. Just make sure the header is not included in the data range, explained later
Excel Table with new tab to hold drop down lists

Add list range to Data Validation

Now, back in your main Excel tab, click on the column header for the column you wish to add a drop down list for. In this example, we will be adding 2 drop down lists, one for Column B, and one for Column C.

  • Click on the B Header Cell
Excel Table with Column B Highlighted
  • Click “Data” (top Navbar Menu)
Arrow pointing to the Data menu option
  • Click “Data Validation” in Icon Sub-Menu
Arrow pointing to the Data Validation menu option
  • In the “Allow” drop down menu, select “List”
Data Validation popbux box with "List" option highlight

When you select “List” a new input field will display called Source. Click inside the Source input field, then find your list you want to use as the “Source” for this drop-down list.

In this case, we want to use our Employees list (A2 : A6) as the drop-down list for our Employees in Column B. So we will click inside the Source input field, then click back into our Lists tab, and click and drag from cell A2 to A6.

  • Click inside Source input field
  • Go to Lists tab
  • Highlight cells A2, A3, A4, A5, and A6
  • Press Enter
Data Validation with A2 : A6 cells selected as drop down list range

In this example, we will repeat the same steps for our “Item Sold” column, using the “Items” list as our drop-down options.

  • (In Sales tab) Click on Column C Header
  • Click on Data Validation in the top menu Data option
  • Click on List in the Allow drop down
  • Click inside the Source input field
  • Click on the Lists tab
  • Highlight cells B2, B3, B4, and B5
  • Press Enter
Data Validation with B2 : B5 cells selected as drop down list range

That’s it!

Now you have 2 columns that have 2 separate drop down lists.

What if you only need one cell and not the entire column?

The steps above show you how to add a drop-down list to all cells within the entire column. But if you need to add a drop-down list to only a single cell, the steps are nearly identical!

Instead of clicking on the column header cell, you will only click on the cell you need the drop-down on. For instance, we added the Employees list to the entire Employees column, so we highlighted Cell B Header Cell, then opened up the Data Validation window.

However, if we only needed the first cell to contain the drop-down, then instead of highlighting the entire column by selecting the B Header Cell, we would’ve just selected cell B1, then opened up the Data Validation window and follow the rest of the steps accordingly.

Pretty simple! Comment below if you’re having any difficulties or want help with something more complex!

Adam Allard

Hi, I'm Adam Allard. I'm a Full Stack Software Engineer for Northrop Grumman creating web applications for the DoD. At this time I'm primarily working with Java and Angular based applications, although I have some years of experience in various languages and frameworks from previous jobs where I dabbled with Python & Django, C# & Xamarin, PHP, and Bootstrap. My hobbies include time with my family, wondering when the Green Bay Packers will win their next Super Bowl, drinking over-priced beer, and of course learning and teaching.

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Posts