Show Multiple Columns in Excel Drop Down List

If you tried to make a comment on a blog post last week, you might wonder why it didn’t appear. I’m really sorry about that! I reported the problem on Thursday morning, and the support staff at the web hosting company couldn’t figure out how to fix it. Several days and phone calls later, I was lucky, and talked to the right person – she figured it out in 5 minutes, and we’re back in business.

I’ve extended the deadline for the giveaway for Jon Peltier’s awesome charting utility. Now you have until August 5th, at 12 noon Eastern time, so go to the giveaway blog post, read the rules, and add your comment. Good luck!

And now, back to our regular business…

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,

  • can’t change the font size
  • single column only
  • no autocomplete

comboboxaddress03

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.

comboboxaddress01

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.

comboboxaddress02

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.

datavalcombosheet04

Download the Address Combo Box File

To see how the customer location combo box works, you can 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.

___________________________

You may also like...

4 Responses

  1. Maxim Manuel says:

    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?

  2. OmarF says:

    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.

  3. faizul says:

    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.

  4. Jen says:

    Yeah – but HOW to use a combo box, which is what I was hoping for…

Leave a Reply to Maxim Manuel Cancel reply

Your email address will not be published. Required fields are marked *