Source lists in a separate tab: Whenever possible, set up all your source lists in a separate tab.This will also enable you to connect data validation list to table columns thru structural references. Use named ranges: Instead of hard-coding cell addresses, use named ranges for setting up validation lists.That said, keep these ideas in mind when implementing them. They are user friendly and easy to set up. Best Practice for Drop-downsĭrop-downs are very useful for data analysis, charting and reporting work. Watch it below or visit my YouTube Channel. Check it out if you want to understand how Dynamic Array method and Pivot Table method can be setup. You can see a cameo from Nishanth (my son) in the video.
#How to make a drop down list in excel with autocomplete how to#
I made a video explaining how to make dropdowns without duplicate values. Drop-down list without duplicates – Video Your drop-down list without duplicates will be ready. Note: Change $A$21 to a cell address further down if you will have more options.įinally, use myOptions as the list source for data validation. This will make a dynamic named range with how many ever education options are there in that pivot table. Now, create a name with myOptions and use the formula =OFFSET($A$2,0,0, COUNTA($A$2:$A$21), 1) Your browser can’t show this frame.Pivot table with list of education values To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video. 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. When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. There’s only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed. 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. This combo box can also be linked to a worksheet cell, and the value is entered in that cell, when you select an item from the list.Īlso, you can type in the combo box, and the text autocompletes as you type, finding the first matching item in the list. 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. You can set the properties, such as font, font size, number of rows, and even include multiple columns. This is similar to the Forms Control combo box, but has more properties that you can adjust.Īfter you insert a combo box, right-click the combo box, and click Properties. 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).Īnother drop down list option is to use an ActiveX combo box. To show the item name, instead of the item number, you can use an INDEX function in another cell. In the screen shot above, February was selected, and a 2 is entered in cell D5. That item’s index number (its position in the list) is entered in the linked cell. The font size can’t be changed though, so the list would be hard to read on a zoomed worksheet.Īlso, you can’t type in the box, so click the arrow, then select an item from the list. 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?” At a client’s office last week, I was selecting a pricing option from a data validation drop down list.
0 kommentar(er)
