will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
3 Types of Excel Drop Down Lists Compared
At a client’s office last week, I was selecting a pricing option from a data validation drop down list. The worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, “How can you even read that?”
Good question. Sometimes the font in a data validation drop down is so small that you can barely read the list.
Unfortunately, there’s no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. There are other ways to create a drop down list though, and one of those options might work better in your worksheet.
We’ll compare these types of Excel drop down lists:
- Data Validation List
- Form Control Combo Box
- ActiveX Control Combo Box
Data Validation List
Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, the font size can’t be changed, only 8 rows are visible at a time, and only the active cell shows a drop down arrow.
You can’t change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users.
Form Control Combo Box
Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.
You can adjust the number of drop down lines in the Form Control Combo box, so you can show all 12 months, without a scroll bar.
The drop down arrow is always visible, to the Form Control combo box is easy for users to find on the worksheet. The font size can’t be changed though, so the list would be hard to read on a zoomed worksheet.
If you select an item from the Form Control drop down list, the item number is entered in the linked cell. In the screen shot below, February was selected, and a 2 is entered in cell D5.
To show the item name, instead of the item number, you can use an INDEX function in another cell. In this example, the INDEX function returns a month name from the MonthList named range, from the row number (2) for the selected month (February).
ActiveX Combo Box
Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.
After you insert a combo box, right-click the combo box, and click Properties.
You can set the properties, such as font, font size, number of rows, and even include multiple columns.
If you know that a worksheet will be zoomed most of the time, you can use a larger font, to make the list easier to read.
Combine the Drop Down Lists
To make the most of the data validation features, while allowing font changes, autocomplete and more rows, I sometimes combine data validation with a combo box. There’s only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed.
When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. If users don’t want to use the combo box, they can simply click on the arrow in the cell, and use the data validation list.
Watch the Drop Down Lists Video
To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video.
____________
February 3rd, 2010 at 4:12 am
You forgot there is one more type of dropdown list
Just type a list starting from A2 to say A10
Hide the rows.
Select A1 and type Alt+Down arrow OR rightclick button +k
February 3rd, 2010 at 5:10 am
Hi Debra,
As it happens, I have published an article about worksheet controls quite recently:
http://www.jkp-ads.com/articles/controls.asp
February 3rd, 2010 at 1:41 pm
I don’t have a comment. I’m just looking for: “Notify me of followup comments via e-mail”. Won’t let me leave a blank comment, so now I’m explaining. Let’s see what works.
Thanks
Jeff
February 3rd, 2010 at 3:23 pm
While I wouldn’t recommend it, one option for Validation is to magnify the worksheet when the user clicks on the cell containing the validation. Something like:
Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(False, False) "A1" Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 200
End If
End Sub
February 3rd, 2010 at 4:13 pm
LOL I’ve got a post scheduled about the various ActiveX controls. I’ll send a trackback when it’s published.
February 3rd, 2010 at 5:53 pm
@sam, thanks for the reminder about the Pick From List feature.
@Jan Karel and @JP — obviously, great minds think alike!
@Jason, thanks for the code sample. That’s similar to the example that I have on the Contextures site, and isn’t a perfect solution, but can help in some situations:
Make the Dropdown List Appear Larger
February 5th, 2010 at 3:56 am
Hi Debra,
I feel that many a times the width of dropdown box is similar to that of the column which contains the validated cell, but many a times it is much wider than that. Is there any specific reason for that ?
Thanks
February 5th, 2010 at 12:22 pm
Hi Kanwaljit, I haven’t found a reliable fix for the problem, but there are a couple of suggestions on my Contextures website:
Data Validation List Too Wide
February 6th, 2010 at 10:59 pm
Hello,
Does anyone have an idea of using dependent drop down list from another workbook?
I could only get a list but not dependent list.
Note: some people may say that why don’t you use dependent list in one excel sheet… As per the company policy, changing data means we are going to have long time to get it approved and though we have to change our document revision number everytime that data gets increased. that’s why we need it in different workbook.
Thanks.
February 7th, 2010 at 8:30 pm
Redha, instead of a dependent list based on another workbook, you could use Microsoft Query to connect to the list in the master workbook. Set the connection to automatically update when the file is opened, and users will always have the latest version of the list from the master file.
February 7th, 2010 at 11:22 pm
Thanks Debra.
I’ve used VLookup and it works fine and no need to open both of the workbooks… but I’ll also try microsoft query as well although never used it before on excel sheets!
March 5th, 2010 at 9:06 am
Hello all,
maybe you can help me, I have a question related to this discussion. If I use form control, is it possible to create dependent list?
I know how to create dependent list with Data Validation (using INDIRECT reference), but it doesn’t work when I use a form control. I want to use a form control, because I also need to get the value of my selection in a linked cell.
For example, I have a dropdown list with all car brands, depending on the brand that I selected, I can choose the correspondent models to that brand in a second dropdown list. And if for example, I select the third model in the list I want to see “3″ in a cell.
thank you very much in advance!!