We will cover 3 ways within data validation that can be used to display a list of names, or a list of anything.

Let’s say you want a list (drop-down) with names

  • Roy
  • John
  • Amy
  • Sheryl

Refer this post to know how to create a drop-down list http://www.excelrush.com/drop-down-list

  1. Create a comma separated list
    • Select the cells where you want the list to appear, say A1 to A5
    • Go to Data tab and click on Data Validation
    • In Allow, select List
    • In Source, type the 4 name with a comma in between each
    • Press Ok
    • There will be a drop-down list in cells A1 to A5 with the 4 names, and you can choose any 1 of them

Comma separated list

Drop-down list

To change any of the names, you will have to go back to Data Validation and change it manually in the source box. But this can be avoided with the next 2 methods.

  1. Create a list by picking up names directly from other cells
    • For this, first you need to write the 4 names in some other cells, say J1 to J4
    • Select the cells where you want the list to appear
    • Go to Data tab and click on Data Validation
    • In Allow, select List
    • In Source, type an = and then with the mouse, select J1 to J4
    • Press Ok
    • There will be a drop-down list in cells A1 to A5 with the 4 names, and you can choose any 1 of them

To change any of the names, simply change the name in J1 to J4 and will get automatically updated in the drop-down list.

From other cells directly

 

  1. Create a list by picking up names using Name Box

Let’s say there are too many drop-downs that you need to create in your file, and rather than remembering which values are kept in which cells (J1 to J4), you want to simply use names.

 

Refer to this post to know how to use the Name Box and create a Named Range http://www.excelrush.com/create-a-named-range-using-name-box

    • For this too, first you need to write the 4 names in some other cells, say J1 to J4
    • Create a named range, name given let’s say ‘people’
    • Go to Data tab and click on Data Validation
    • In Allow, select List
    • In Source, type an = and then the name of the named range ‘people’. So it would be =people
    • Press Ok
    • There will be a drop-down list in cells A1 to A5 with the 4 names, and you can choose any 1 of them

To change any of the names, simply change the name in J1 to J4 and will get automatically updated in the drop-down list.

With Named Range