Excel - Display multiple columns in a validation list

December 2016




We'll need to trick Excel to accept to create a validation list with multiple columns

Example to show the range A1 to C20 (20 rows and 3 columns).:
Start by naming the range A1 to A20 as "List"
  • Highlight A1 to A20

Excel 2003 and earlier version

  • Insert / Name / Define
  • in the Name text box in the workbook: name the range as list
  • create a list of validations in E3 (Data / Validation, in Allow: select "List" in Source: type =List)
  • Open the Name manager Insert/Name/Define, select the name of the range (List)
  • In "Refers to": Change the values from $A$1:$A$20 to $A$1:$C$20

Excel 2007 and above

  • Data tab / Data Validation module
  • in the Name textbox : name the range as List
  • Create a list of validations in E3 (Data/Validation, in Allow: select "List" in Source: type =List)
  • Open the Name manager: Formula tab/set name/Name Manager, select the name of the range (List)
  • In "Refers to": Change the values from $A$1:$A$20 to $A$1:$C$20



Related :

This document entitled « Excel - Display multiple columns in a validation list » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.