Note a couple of things about this step. First, we highlighted the three entries in the data portion of this list (K2:K4), then we created a new named range (Formulas, Name Manager, New). Note that in the Refers to: box it shows “=Region[Region]”, not K2:K4. Because we had highlighted all of the data portion of the table, Excel correctly assumes that we want to identify not just the three particular cells highlighted but the entire data section of this one-column table. Hence it uses the complete name to reference it (namely, Table[Column]) and shows “Region[Region].” This will be important when we add data to this table.
Second, note that the name we gave to this named range is Regionn, with a second n to distinguish it from the table Region. You can use whatever naming convention you choose to keep it straight in your mind whether you are referring to the table or to the data portion of the table. This is necessary as you can’t have both a table and a range with the same name in a workbook.
Now we are ready to use this in setting up a data validation list. Highlight the cells in the Region column of the table that you want to apply the Data Validation to, click Data, Data Validation, choose List in the Allow box, then in the Source box type “=Regionn” (or whatever you have named the range you wish to use).