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: Read more of this post