Note: The other languages of the website are Google-translated. Back to English

How to set a default value in data validation drop-down list in Excel?

Set a default value in data validation drop-down list helps others to identify the drop-down list cells easily at a glance in a worksheet. This article provides a VBA method to set a default value in data validation drop-down list in details.

Set a default value in data validation drop-down list with VBA code

Set a default value in data validation drop-down list with VBA code

As below screenshot, supposing all cells in range B2:C7 contain drop-down list, and you want to set a default value (says - Choose from the list -) in these drop-down list before choosing values from them. You can try the following VBA code.

1. Right click the sheet tab with the drop-down lists you need to set a default value, then click View Code from the menu.

2. In the Microsoft Visual Basic for Applications window, copy and paste the following VBA code into the Code window.

VBA code: set a default value in data validation drop-down list

Sub DropDownListToDefault()
    Dim xCell As Range
    Dim xRg As Range
    Dim xAcCell As Range
    Dim xScreen As Boolean
    On Error Resume Next
    Set xAcCell = Application.ActiveCell
    Set xRg = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
    If xRg Is Nothing Then
        MsgBox "No data validation drop-down lists in current worksheet", vbInformation, "Kutools for Excel"
        Exit Sub
    End If
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For Each xCell In xRg
        If xCell.Validation.Type = 3 Then
            If xCell.Value = "" Then xCell.Value = "'- Choose from the list -"
        End If
    Application.ScreenUpdating = xScreen
End Sub

Note: you can change the default value in the VBA code to the one you need.

6. Press the F5 key to run the code. You can see the default values are added to all drop-down list cells as below screenshot shown.


1. The default value will disappear after selecting value in the drop-down list.
2. If there has no data validation drop-down lists in current worksheet, you will get the below prompt box.

Related articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Love the code about 'VBA code: set a default value in data validation drop-down list', how do I use the same code for just one column with one dropdown?
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations

Follow Us

Copyright © 2009 - | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL