[Excel] Display multiple columns in a validation list

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

See also

Knowledge communities.

• Forum
• Excel forum
• Office software forum

This document entitled « Excel - Display multiple columns in a validation list » from Kioskea (en.kioskea.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.