How to create a multi column data validation list in Excel

When using a data validation list, only one column of data is displayed, but sometimes there is a requirement for additional columns to be displayed simultaneously.

For example, sometimes you may have a list of products that have the same description but different colours and sizes. In this tip, we will take you through the steps of how to create a multi column data validation list.

Download the workbook to practice. 

  1. Select an empty cell on your worksheet, e.g. H1 and name the cell ProductName. To name the cell, select Formulas > Defined Name

 

  1. Select your data and following the links above, name the range ProductTable

 

 

  1. Under the “Developer” Tab, select “Insert” from the “Controls” group and insert a Combo Box from the ActiveX Controls section.

 

  1. Right click on the inserted combo box and select Properties

 

  1. In the Properties dialogue box, set the following properties
  • Name : ProductList
  • BorderColor : Menu bar
  • ColumnCount : 4
  • ColumnWidths : 100 pt;80 pt;80 pt;100 pt
  • DropButtonStyle : 1 or 2
  • LinkedCell : ProductName
  • ListFillRange : ProductTable
  • ListWidth : 390 pt

  1. On the Developer Tab, ensure that the Design Mode is no longer active. If it is, click on the section to deselect it.

 

Your multi column drop down list is all setup and ready for use.

Anonymous