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!

How to add a DatePicker (Calendar) to Excel Cells

This tutorial walks you though adding a DatePicker Calendar view to a cell range within a Microsoft Excel spreadsheet.

Step 1. Enable Developer Mode in Excel

  • File
  • Options
  • Customize Ribbon
  • Ensure Developer checkbox is enabled
Enable Developer Mode

Step 2. Setup DateTime Control box

  • Microsoft Date and Time Picker Control
  • Click on random cell to place control box
  • Edit or remember the name box of the DateTime Picker, in this case, we will leave it as DatePicker1
Name Date Picker
  • Right click on DateTime Control box
  • DTPicker Object
  • Properties
DTPicker Object > Properties
  • Enable CheckBox
Enable Checkbox

Step 3. Copy the Visual Basic code

To assign column A as a DatePicker, copy the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  With Sheet1.DTPicker1
    .Height = 20         
    .Width = 20         
    If Not Intersect(Target, Range("A:A")) Is Nothing Then             
      .Visible = True             
      .Top = Target.Top             
      .Left = Target.Offset(0, 1).Left             
      .LinkedCell = Target.Address         
    Else             
      .Visible = False         
    End If     
  End With               
      
End Sub
  • Right click the DateTime Control box
  • View Code
View Code
  • Replace all of the existing code with the code from above
  • Close the Visual Basic editor
Paste Code
  • Deselect Design Mode
Deselect Design Mode
  • Click on any cell to remove DateTime Control box
  • Click on any cell in Column A to add a Date
Pick Date

Congratulations!

Important: Make sure that when you save the spreadsheet, you “Save As: Macro enabled workbook”

How to have multiple columns with DatePickers

The above steps only work for single columns, or columns that are right next to each other. If you wanted the DatePicker column to be in column B instead, then you would change the line

If Not Intersect(Target, Range("A:A")) Is Nothing Then

to

If Not Intersect(Target, Range("B:B")) Is Nothing Then

Or if you wanted it from Column E to Column G, then you would change that line to

If Not Intersect(Target, Range("E:G")) Is Nothing Then

However, if you need a DatePicker in two or more non-adjacent columns, then you will need a separate DatePicker (each with a separate name in the Name Box) for each non-adjacenet group. For example, let’s say we need column A, B, E, F, and H to all be DatePickers. We would need to perform the above steps 3 separate times for 3 separate DatePickers.

  • 1 for columns A, B
  • 1 for columns E, F
  • 1 for column H

The steps will be very similar, but the code will be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    

  With Sheet1.DTPicker1    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("A:B")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With

  With Sheet1.DTPicker2    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("E:F")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With

  With Sheet1.DTPicker3    
    .Height = 20             
    .Width = 20             
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
      .Visible = True
      .Top = Target.Top
      .Left = Target.Offset(0, 1).Left
      .LinkedCell = Target.Address
    Else
      .Visible = False
    End If
  End With
  
End Sub