How to create drop down list default value

Answer

What is a dropdown list default value?

A dropdown list default value is a pre-selected option that appears in a cell before the user makes a selection. Excel data validation dropdown lists display blank cells by default until a user chooses an option. Setting a default value provides an initial selection that guides users or pre-populates expected entries.

How to set a default value by typing directly

To set a default value by typing directly, enter the desired value into the cell either before or after applying data validation. Both sequences work without affecting the dropdown functionality.

Entering the default value

1. Select the cell where the dropdown list exists or will be created.
2. Type the default value exactly as it appears in your source list (e.g., "Yes", "Pending", "USD").
3. Press Enter to confirm.

The cell displays the typed value while retaining full dropdown functionality. Clicking the dropdown arrow reveals all available options from the source list.

Applying data validation after entry

1. Select the cell containing the typed default value.
2. Navigate to Data > Data Validation.
3. Select "List" from the Allow dropdown.
4. Enter the source range or named range in the Source field.
5. Click OK.

The default value remains in the cell, and the dropdown list becomes active for future selections.

How to set a default value using a formula

To set a default value using a formula, use a simple cell reference that points to the desired default value in your source list.

Using a cell reference formula

Enter this formula in the dropdown cell before applying data validation:

=Z1

Replace Z1 with the cell containing your desired default value from the source list. This pre-populates the cell with the referenced value.

Applying data validation after the formula

1. Select the cell containing the formula.
2. Navigate to Data > Data Validation.
3. Select "List" from the Allow dropdown.
4. Enter the source range in the Source field (e.g., =$Z$1:$Z$10 or =SourceList).
5. Click OK.

The cell retains the formula-driven default value while offering the full dropdown list for alternative selections.

How to set a default value using VBA

To set a default value using VBA, use the Worksheet_SelectionChange event to detect when a dropdown cell is activated and programmatically assign a value to blank cells.

VBA code for first item as default

Right-click the sheet tab and select View Code. Paste this code in the worksheet module to default to the first item in the source list:

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 assigns the first item from the validation source range to any blank dropdown cell upon selection.

VBA code for custom default value

Paste this modified code to specify a custom default value instead of the first item:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim defaultValue As String
    defaultValue = "Pending"
    
    If Target.Count = 1 Then
        On Error Resume Next
        If Target.Validation.Type = xlValidateList Then
            If Target.Value = "" Then
                Target.Value = defaultValue
            End If
        End If
        On Error GoTo 0
    End If
End Sub

Replace "Pending" with your desired default value. The value must exist in the dropdown source list for data validation to accept it.

Auto-populating defaults on workbook open

Add this code to the ThisWorkbook module or worksheet module to set default values when the workbook opens:

Private Sub Workbook_Open()
    Dim cell As Range
    Dim defaultValue As String
    defaultValue = "Pending"
    
    For Each cell In Range("A1:A100")
        On Error Resume Next
        If cell.Validation.Type = xlValidateList And cell.Value = "" Then
            cell.Value = defaultValue
        End If
        On Error GoTo 0
    Next cell
End Sub

Adjust the range A1:A100 to match your dropdown cell locations. Change the defaultValue variable to your preferred option.

What are the differences between default value methods?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report