A data validation drop down list in Excel only shows one column of items. See how to show multiple columns in Excel drop down list — we’ll use something different from an ordinary in-cell drop down.
Show a Drop Down List
You can make data entry easier by adding drop down lists on a worksheet. You can do this with a data validation list, but it has limitations.
For example, here are a few restrictions on in-cell drop down lists,
- can’t change the font size
- single column only
- no autocomplete
Use a Combo Box Instead
A combo box also lets you select from a drop down list, and you can change the font size, use autocomplete, and show multiple columns.
Add a Single Combo Box
For worksheets where you only need one or two drop downs, just add the combo box on the worksheet, and set its properties.
In the example shown above, the combo box is used to select a customer name, and there can be multiple locations for each customer. The list has 6 columns. The first column, ShipID, is hidden, and it shows the next 5 columns, so you can see the customer’s address information, to make sure that you’re picking the right one.
Then, in the cells below, INDEX formulas pull the related details for the selected customer location.
Use Multiple Combo Boxes
Too many combo boxes can cause problems in an Excel file, so it’s best to limit the number that you add.
If you need lots of drop downs on the worksheet, you can use my sample that has a hidden combo box. It pops up when you click, or double-click, on a data validation cell. This technique uses programming, but there is only one combo box, so it doesn’t add much to the file size.
Download the Address Combo Box File
To see how the customer location combo box works, download the sample file from my website. On the Sample Files page, go to the Functions section, and download the file FN0004 – ComboBox Selection.
The zipped file is in xlsm format, and contains macros that update the ListFillRange.
___________________________
If I need my combo box to show 3 columns as in the example, will I need to select my range or list using 3 column?
I admit that a Combo Box is better but is it not heavier for a worksheet of 8,000 items than dropdown list?
This is timely as I had a drop down with sales person numbers in a report meant to be used by people not familiar with each person’s number. I had told my boss I wanted to do something about that. My work around was to have a reference list showing on the sheet in a non-printing area.
I switched it to a combo box this morning, but spent way too much time trying to get the column widths right. In the end, I added a fourth column that mostly stayed hidden so that all of the first three would show correctly. This is in 2010 with the recent service pack added.
hi debra,
would you like to make a video on youtube about ‘Show Multiple Columns in Excel Drop Down List’?maybe visual-learning is more effective than instructional learning.thank you.
Yeah – but HOW to use a combo box, which is what I was hoping for…
Brilliant site and very useful page. I have combined two methods to get round a tricky problem. Many thanks.
I need to add royalties for authors. You have this set up for customer info … name/street/city/state …
Do you think I “box” like this would work for calculating royalties?
I just need “author name/book title/cover price/Vendor/net money” … In another column I would just type the quantity sold. I think I could stick in a formula that would calculate “net money” x quantity sold.
Something like this would save an inordinate amount of time typing and looking up everything … not to mention, allotting rows for books that did/didn’t sell.
Paypal has a wonderful invoice … type the first few letters, and your item and price pop up automatically … but Paypal wants to send invoices … this doesn’t work for statements or royalties.
I’m not getting suicidal (yet) but the quarterly royalty ordeal takes the joy out of my chosen hobby/profession.
please make a video tutorial it is much needed. please also show how to do it without the vba combo box as well.
thanks