5
Thanks

A few words of thanks would be greatly appreciated.

Display Multiple Columns in a Validation List in Excel

In this article we will show you how to display multiple columns in a validation list in Excel.

Create a Validation List With Multiple Columns

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" then highlight A1 to A20 and following the procedures outlined below:

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 text box : 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


Image © Dzmitry Kliapitski -123rf.com

5
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
Related
This document, titled « Display Multiple Columns in a Validation List in Excel », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!