The drop down arrow is always visible, so the Form Control combo box is easy for users to find on the worksheet. You can also link the combo box to a cell, so the selected item number appears on the worksheet. 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. It gives you a bit more control over the appearance of the drop down list. Instead of using data validation, you could use a Form Control Combo Box. You can’t change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users. only the active cell shows a drop down arrow.We’ll compare these types of Excel drop down lists:ĭata validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. There are other ways to create a drop down list though, and one of those options might work better in your worksheet. Unfortunately, there’s no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list. Sometimes the font in a data validation drop down is so small that you can barely read the list. It had some limitations, so here are 3 types of Excel drop down lists compared.Īt my client’s office, 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?” The INDIRECT function returns the contents of a cell by using its reference, For example, if cell B5 contains cell of the text value B8 (Or Name Range) and cell B8 contains the value 100, the formula =INDIRECT(B5) returns the value 100.At a client’s office last week, I was selecting a pricing option from a data validation drop down list. Select Mobile (E3 to E9) and Press Ctrl+T.Check “ Top Box” only (Create three list names).Go to Formulas tab> Create from Selection (Ctrl+Shift+F3).Enter =INDIRECT(B5), click OK, Ignore Error.Select Data>Data Validation>Settings>Source (Shortcut: Alt+DL).Select a cell C5 to create a Drop-Down list.Go to worksheet ‘ Dependent’ of Excel working file (Image instructions below).The second drop-down list is dependent upon the choice of the First drop-down list, the following are key steps to follow:

2nd Drop-down list is contnigent upon the choice of 1st drop-down list. So, If the user selects a product name from parent drop-down list simultaneously the brand names of the subordinate drop-down list must also change.

Insert drop down menu in excel 2016