Data Validation: Part 2a of 3

Convert your named Range into a Table for more flexibility

In part 2 of this Data Validation Series, I showed how using a named range when adding data validation to your fields allows for a cleaner worksheet. Listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window, I showed you, is more efficient and cleaner than listing them in the Data Validation window itself or separately in a list, but on the same sheet.

But we can enhance this method even further. Using the named range, you might have discovered, makes adding an item to the list difficult. You either have to change the referenced range area each time you add an item or you have to make the original range large enough to include further inclusions. Unfortunately, creating a range with blank rows leaves spaces in your data validation list.  That’s obviously not ideal or neat.

A great workaround for this is to convert the range of allowable values into a table as follows:

1. Making sure to first include a header row, select the entire range (column) of values.
2. On the Insert Ribbon in the Tables Group, Select ‘Table’

Instead of referring to the named range (“animals” in part 2), we can now use the name of the table. Its default name is “Table1”, but you can easily change it by doing the following:

1. On the Formulas Ribbon in the “Defined Names” Group, Select ‘Name Manager’
2. Select ‘Table1’ and ‘Edit’
3. Change the name to whatever you prefer, such as ‘animalList’

You can now use the same method explained in Part 2, but instead of referring to the named range, you can now refer to the Table, ‘animalList’.