Forum Discussion
Drop-down list
The built-in Data Validation drop-down list has a fixed maximum number of rows it will display. In your version of Excel (Office 365), that limit is 12 rows. Because your list contains 15 items, Excel will automatically add a scroll bar to the list. Unfortunately, adjusting the cell height, row height, or font size does not change this behavior; the pop-up window size is controlled internally by Excel.
The Working Solution: Combo Box (Form Control)
To display all 15 rows at once without scrolling, the best and most reliable method is to use a Combo Box Form Control. This is a small object you place on the worksheet that gives you full control over how many rows are shown.Here are the simple steps to set it up:
1. Enable the Developer Tab: Right-click anywhere on the gray Ribbon at the top of Excel and select Customize the Ribbon. In the right-hand panel, check the box next to Developer and click OK.
2. Insert the Combo Box: Click the Developer tab at the top, then click Insert. Under the section labeled Form Controls, click the Combo Box icon (it looks like a small dropdown box).
3. Draw the Box: Click and drag on your worksheet to draw the box. You can resize it at any time by dragging the small circles on its border.
4. Configure the Box:
- Right-click the Combo Box and select Format Control.
- Go to the Control tab.
- Input range: Highlight the cells containing your 15-item list (e.g., $A$1:$A$15).
- Drop down lines: Type 15 in this field. This is the key setting that forces the list to show all 15 items without a scrollbar.
- Cell link: Click the box and select any blank cell (e.g., D1). This cell will store the numeric position of the selected item (1–15).
- Click OK.
5. Display the Selected Value (Critical Step):
- The "Cell link" (e.g., D1) now contains a number (1–15), but you need the actual value from the list.
- In a separate cell (e.g., E1), enter the formula:
=INDEX($A$1:$A$15, D1)
This converts the numeric code in D1 to the corresponding item from your list (e.g., if D1=3, the formula returns the 3rd item in A1:A15).
When you click the arrow on this Combo Box, you will see all 15 items displayed neatly without any scrolling required.
I hope this provides a clear and workable path forward!
Good afternoon! Thank you very much for the instructions. I was unable to replace the numbers with values. In my case, the formula looks like this: INDEX(R[3]C[2]:R[39]C[2];R[3]C[-115]) is a list of 37 items. There are no letter values in the table. I am using a laptop, Excel 2016, and Microsoft Excel (.xlsx)
In the Format Control dialog of the Combo Box, you set a Cell link (e.g., D1). Under R1C1 notation, that cell would be shown as something like R3C4 (Row 3, Column 4).
Write down or remember exactly which cell you used as the Cell link.
Your formula:
INDEX(R[3]C[2]:R[39]C[2]; R[3]C[-115])
The problem is that you're mixing relative references (with brackets [ ]) when you likely need absolute references. Here's the difference:
- R[3]C[2] — means 3 rows DOWN and 2 columns RIGHT from the formula cell (relative)
- R3C2 — means Row 3, Column 2 (absolute)
You almost certainly want absolute references for both the list range and the cell link.
Corrected Formula:
=INDEX(R3C3:R39C3; R3C4)
Where:
- R3C3:R39C3 = Your list of 37 items (Rows 3 to 39, Column 3)
- R3C4 = The Cell link cell (Row 3, Column 4)
Temporarily Switch to A1 Style (Easier Method)
If this is too confusing, you can switch Excel back to regular letters and numbers:
- Go to File → Options → Formulas.
- Under Working with formulas, uncheck "R1C1 reference style".
- Click OK.
Your formula will now look like the original example I gave:
=INDEX($C$3:$C$39; $D$3)
You can switch back to R1C1 style afterward if needed, and the formula will convert automatically.
My answers are voluntary and without guarantee!
Hope this will help you.