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’.


About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

One Response to Data Validation: Part 2a of 3

  1. Pingback: Data Validation: Part 3 of 3 «

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: