How to show/display more than 8 lines in data validation list in Excel?
When you're working with data validation drop-down lists in Excel, you may quickly notice a limitation: the default pop-up only displays a maximum of 8 items at once. This means if your list is longer, you'll need to scroll to view all options, which can be inconvenient in cases where quick selection from a large list is required. In many practical business, administrative, or data analysis scenarios, users need to quickly scan and select from lists exceeding 8 options. Excel doesn't provide a setting to directly change the visible item count in standard data validation lists, which can slow down workflow or reduce clarity for the user.
Fortunately, Excel offers alternative methods to overcome this default limitation and display more than 8 lines in your drop-down selection. These approaches vary in application, flexibility, and complexity. This tutorial describes several practical solutions, including using standard Combo Box controls, ActiveX Combo Boxes, custom VBA interfaces, and formula-driven dynamic alternatives. Each method is suitable for different scenarios depending on your requirements for appearance, programmability, and interactive user experience.
Show more than8 lines in data validation list with Combo box
Show more than8 lines with ActiveX Combo Box
VBA: Custom Drop-down Interface with Extended Display
Show more than 8 lines in data validation list with Combo box
Using Excel's standard Combo Box control is a convenient way to display more than 8 lines in a drop-down list directly on your worksheet. This method is straightforward and provides a customizable interface to expand the visible item count, which is particularly useful if your worksheet includes long lists or you want users to quickly glance at a broader range of options.
Here are the practical steps to implement this solution:
1. First, make sure the Developer tab is enabled in your Excel ribbon. Then, click Insert > Combo Box. See screenshot below for reference:
If you do not see the Developer tab, you can follow this guide to enable the Developer tab in Excel.
2. Next, draw the Combo Box on your worksheet to the desired size and position. To access its properties, right-click the Combo Box and select Format Control from the context menu.
3. In the Format Object dialog box, switch to the Control tab and configure the following fields carefully:
Once configured, the Combo Box on your worksheet will display the number of lines you specified, allowing users to see and select from more than 8 options at a time. This greatly enhances selection efficiency and improves clarity, especially in forms or lists with many choices.
Tip: If you want the Combo Box to update dynamically as your source list changes, reference a dynamic named range or use a table for your Input range. Be cautious: Combo Box selections return the index (number) of the selected item, not the actual value, so you may need to use =INDEX(range,cell_link) to retrieve the value for downstream use.
Show more than 8 lines with ActiveX Combo Box
The ActiveX Combo Box is an alternative control available under the Developer tab that provides greater customization than the standard (Form Control) Combo Box. It allows for advanced properties including the number of visible lines, font size, colors, and can be programmed via VBA for interactive responses.
This method is especially recommended when you need more flexibility (such as custom event handling or dynamic list population) or wish to integrate with other workbook features.
To implement with ActiveX Combo Box:
1. Make sure the Developer tab is enabled, then select Insert > ActiveX Controls > Combo Box.
2. Draw the ActiveX Combo Box to the desired location and size. Right-click the Combo Box and choose Properties to customize its settings in-depth.
3. In the Properties window, set the ListFillRange property to the range of your menu items (e.g., Sheet1!A2:A20). Adjust the ListRows property to any number (e.g.,15) to show more than 8 lines in the drop-down.
4. You can further customize appearance, font, and other behaviors in the Properties area. If desired, leverage VBA to dynamically adjust Combo Box items or respond to user actions (e.g., via the Change event).
5. Exit Design Mode on the Developer tab to activate the Combo Box for normal use.
Note: ActiveX controls work only in Windows versions of Excel and may not be supported or may behave differently on other platforms. They provide richer interaction and appearance than Form controls, but can be less robust in protected sheets or web-based environments. Save your work before applying complex settings.
Tip: The value selected in an ActiveX Combo Box can be referenced in VBA or formulas by reading the Combo Box's .Value property. For automation, you can populate the Combo Box using VBA for dynamic lists, such as from database queries or filtered results.
VBA: Custom Drop-down Interface with Extended Display
For situations requiring a highly customizable drop-down interface or the ability to display even more items (with tailored appearance and interactive capabilities), you can create a user form using VBA. This form can hold a Combo Box or List Box that is not limited to 8 items and can display and interact with worksheet data directly. This is ideal for automated applications, forms, or when building professional dashboards.
Follow these steps for a custom drop-down via VBA:
1. Go to the Developer tab, click Visual Basic to open the Microsoft Visual Basic for Applications editor. Choose Insert > UserForm.
2. On your UserForm, add a ComboBox or ListBox control by clicking it in the toolbox and drawing it on the form. In the Properties window, you can set the ListRows (for ComboBox) or adjust the size for ListBox to show as many lines as you want. Then, use the following VBA code to populate and connect the form to your worksheet data:
4. Right click the userform and select View Code from the context menu. Then replace the original code with the following VBA code.
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ComboBox1.List = ws.Range("A2:A" & lastRow).Value
ComboBox1.ListRows = 20 ' Show up to 20 items at once
End Sub
Private Sub ComboBox1_Change()
' On selection, write the value to specific cell
ThisWorkbook.Sheets("Sheet1").Range("B1").Value = ComboBox1.Value
' You can add more actions here as needed
End SubNotes: In the VBA code,
- Replace Sheet1, A2:A and 20 with your actual worksheet name, the data range and the number of items to show.
- Cell B1 is the reference cell; the item selected in the combo box will be displayed here. Adjust as needed.
5. To display the form, click Insert > Module, then add the following VBA code.
Sub ShowDropDownForm()
UserForm1.Show
End Sub6. After entering the code in the Module window, click Run. Your custom form will appear, showing a Combo Box with as many lines as you specified!
Tips: You can further customize the interface, add filtering or search functionality, and even update multiple worksheet cells upon selection. The value transfer between form and worksheet keeps data synchronized for interactive reports or input panels. If running macros for the first time, you may need to enable macros in Excel's Trust Center settings. Always save your workbook before making VBA changes to avoid data loss.
Below are some troubleshooting recommendations and summary suggestions for these methods:
- If your Combo Box or ActiveX controls aren't working as expected, check for worksheet protection, platform compatibility (ActiveX is Windows-only), and cell linking issues.
- When using formulas, ensure your version of Excel supports dynamic arrays and check for spill errors (indicated by a
#SPILL!error in the cell). - Always test drop-down interfaces with sample data and on your intended devices to make sure the desired number of items displays as planned.
- For VBA-based solutions, back up your workbook before running new code, and enable macros; if a form isn't displaying, verify you have inserted a UserForm and named controls correctly.
- Review each solution to choose the best one based on your scenario—Combo Boxes for quick setup, ActiveX Combo Box for deep customization, formulas for maximum flexibility, and VBA UserForms for advanced interfaces.
Related articles:
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
- How to auto populate other cells when selecting values in Excel drop down list?
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