How to show the first item in the drop down list instead of blank?
Drop-down lists in Excel worksheets are a practical feature for streamlining and standardizing data entry—users can simply select from predefined options rather than typing values individually. However, sometimes you may encounter a situation where, upon clicking a drop-down cell, the initial selection appears as a blank instead of the first actual data item. This issue often arises if the source data list was edited and blank rows remain at the end, or if items near the end were deleted, leading data validation to include unintended empty slots at the top of the list. Especially with lengthy lists, constantly having to scroll back past blank entries to the first valid item can be inefficient and frustrating.
Addressing this not only improves convenience for users but also helps prevent accidental selection of blank values, which could impact subsequent data processing or reporting tasks. In this article, you'll learn practical methods to ensure that the first entry in your drop-down list always appears at the top, eliminating these unnecessary blanks.
Show the first item in drop down list instead of blank with Data Validation function
Automatically show the first item in drop down list instead of blank with VBA code
Using Excel Table as Data Source
Show the first item in drop down list instead of blank with Data Validation function
One effective way to avoid blank entries at the top of your drop-down list is to set up your Data Validation using a formula that dynamically determines the correct range. This approach ensures that only the populated cells from your source list are included, regardless of any empty rows caused by deleting data at the end. This solution is especially suitable for users who frequently modify the source list or who want a straightforward, formula-based adjustment without needing to use macros.
1. Select the cells in which you wish to create the drop-down list. Then, go to the Excel ribbon and click Data > Data Validation > Data Validation. The Data Validation dialog box will open, as shown below:
2. Under the Settings tab in the Data Validation dialog, set Allow to List. In the Source box, enter this formula to dynamically reference only the range containing actual data:
=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A)-1,1)
Note: In this formula, Sheet3 refers to the sheet where your source data resides, and A1 is the starting cell of your list. Adjust these as necessary for your specific worksheet layout. The use of COUNTA ensures that only non-blank cells are included, starting from A1. If your source list contains deliberate blank rows within it (not just at the end), this method may not fully exclude those, so keep your source list contiguous for best results.
3. Click OK to apply the settings. Now, when you click any of the drop-down list cells you configured, the list will display with the first actual data item at the top. This remains true even if the source data changes, as long as the range covers all the items in column A and you have no blank cells within the main block of data. See the result below:
Tip: If you later need to expand or contract your source list, you do not need to update your data validation settings. The formula will automatically adjust, provided there are no blank cells at the start of your range. However, be aware that if a blank is present within the list (not just at the end), it will be skipped in the calculation count but may create unintended gaps in the drop-down.
Potential issue: If your data source might contain deliberate gaps, or if you have merged cells or non-contiguous data, consider using an Excel Table as your source range, or review the VBA method below for more flexible handling.
Automatically show the first item in drop down list instead of blank with VBA code
In some scenarios, adjusting the data validation source alone is not sufficient—for example, if your data changes frequently, or if there’s a risk of blanks appearing for other structural reasons in your source range. With a simple VBA code, you can ensure that whenever a cell with data validation is activated, the drop-down always selects and displays the first available item automatically. This can also improve data entry speed, as it minimizes user clicks.
1. After you have inserted your drop-down list, right-click the sheet tab containing the drop-down, and choose View Code from the context menu. The Microsoft Visual Basic for Applications editor will appear. In the window, paste the following code into the relevant worksheet module (not a standard module). This code will work in the background and reset the drop-down each time you select a validation cell:
VBA code: Automatically show the first data item in drop down list:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
Dim xFormula As String
On Error GoTo Out:
xFormula = Target.Cells(1).Validation.Formula1
If Left(xFormula, 1) = "=" Then
Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
End If
Out:
End Sub
2. After pasting the code, save your workbook (preferably as a macro-enabled file with .xlsm extension), and close the VBA editor window. Now, return to your sheet and try clicking any cell with the drop-down list—when you activate the cell, the first entry in your drop-down will be automatically displayed.
Tips and considerations: This VBA approach is ideal when you want a seamless experience for users, especially with dynamic or long source lists, or lists that may contain unavoidable blank entries. Remember to enable macros for this to function, and inform other users of the workbook since some environments restrict macro usage for security reasons.
Troubleshooting: If the code does not seem to work, double-check that it is placed in the correct worksheet code window in the VBA editor. Also make sure that the drop-down uses a standard Data Validation list.
Limitation: The VBA solution will only trigger if the user selects the drop-down cell; it does not work if the cell is populated by other means (such as formula results or via paste). If you remove the drop-down from the cell, or move the cell to another sheet without the VBA code, you’ll lose the automatic selection behavior.
Using Excel Table as Data Source
If your drop-down source list is dynamic and you want better maintainability, consider converting your source list into an Excel Table. Tables automatically adjust their size as data is added or removed, so your list remains up to date. However, note that an Excel Table does not automatically exclude blank cells—any blank entries in the table will still appear in the drop-down list unless you filter them out explicitly (e.g., using the FILTER function available in Excel 365 and Excel 2021).
1. Select your source data and press Ctrl + T to convert it into a Table. Make sure there are no blanks at the top. Assign a meaningful name to the Table, such as MyList (using the Table Design tab).
2. When setting up data validation, use the structured reference to your table column. In Data Validation's Source, type:
=INDIRECT("MyList[Column1]")Replace Column1 with your actual column name (the column header). This method dynamically includes all items in the table column that are filled, maintaining list integrity as you update data.
This approach is especially suitable for environments where the source data is regularly updated and multiple users need to manage the validated list efficiently.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in