How to show the first item in the drop down list

What is the default behavior of an Excel data validation dropdown list?

The default behavior of an Excel data validation dropdown list is that the cell appears blank when no value has been selected. The dropdown arrow appears when a user clicks into the cell, but the cell displays nothing until an option is chosen from the list. Excel dropdowns via Data Validation do not automatically show any text or the first list item as a placeholder.

How to show the first item in the dropdown list using a formula

To show the first item in the dropdown list using a formula, use the INDEX function in a helper cell adjacent to your dropdown cell. This method pulls the first item from the dropdown's source list as a default display.

Using the INDEX formula

Enter this formula in a helper cell (e.g., B1) next to your dropdown cell (A1):

=IF(A1="",INDEX(SourceList,1),"")

Replace "SourceList" with your named range or direct reference such as $Z$1:$Z$10. This formula displays the first source item when A1 is blank and hides when a selection is made.

Setting up the source list location

Store the source list as a named range by navigating to Formulas > Name Manager > New. Create a name such as "SourceList" pointing to a reference like Sheet2!A$1: A$10. Place this list on a dedicated sheet (e.g., "Lists") to keep it hidden from main data areas. Reference the named range in Data Validation by entering =SourceList in the Source field.

Using a simple cell reference

Enter a direct cell reference formula in your dropdown cell before applying data validation:

=Z1

Z1 represents the first item in your source list. This pre-populates the cell with the first value. Apply data validation afterward, and the cell retains the default value while offering the full dropdown list.

How to show the first item in the dropdown list using VBA

To show the first item in the dropdown list using VBA, use the Worksheet_SelectionChange event to detect when a cell with data validation is activated, then programmatically set its value to the first item from the validation list source.

Adding the Worksheet_SelectionChange code

Right-click the sheet tab and select View Code. Paste this code in the worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
        Dim rngSource As Range
        On Error Resume Next
        Set rngSource = Range(Target.Validation.Formula1)
        On Error GoTo 0
        
        If Not rngSource Is Nothing And Target.Validation.Type = xlValidateList Then
            If Target.Value = ""

Then
                Target.Value = rngSource.Cells(1, 1).Value
            End If
        End If
    End If
End Sub

This code runs when a user selects a cell. It checks whether the selected cell has list validation, extracts the source range, and assigns the first item's value to blank cells only.

Auto-populating on workbook open

Add this code to the ThisWorkbook module or the worksheet module to auto-fill dropdown cells when the workbook opens:

Private Sub Workbook_Open()
    Dim cell As Range
    For Each cell In Range("A1:A100")
        If cell.Validation.Type = xlValidateList And cell.Value = "" Then
            Dim src As Range
            Set src = Range(cell.Validation.Formula1)
            cell.Value = src.Cells(1, 1).Value
        End If
    Next cell
End Sub

Adjust the range A1:A100 to match your dropdown cell locations.

What are the differences between formula and VBA methods?