melentha
27 Jun 2019
Recommended

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. 

The multi column data validation list gives you the ability to look up data using more than one criteria. By creating this, you don't need to go and select Product code in one column, White in the next column and Medium in another column to see that you have 10 in stock for example. Using the multiple data validation method you can combine the product with the colour and size as one lookup to see how much stock is available - saving you a lot of time.

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
Parents Comment Children
  • over 1 year ago in reply to Carmen.M

    Agreed - the product Code would be more useful. If the ProductList ComboBox property 'Bound Column' is changed to '2' then the Product Code is stored in the 'ProductName' cell that was initially defined. If the color and/or size are needed in other cells, vlookup functions can be used to find the value in the ProductTable based on the unique code stored in 'ProductName'. It would make more sense to me if the 'ProductName' cell was actually named 'ProductCode' and referred to as such in the ProductList ComboBox 'Linked Cell' property, but that's just for clarity, not functionality. Hope this helps!