Even better data validation

Data validation is great, I have written about it here and here.

And now it can be even better. It has always required a bit of mucking around to get the data validation list to grow automatically if you add more items to the list.

I now have a new favourite way of doing it. Thanks to Excel off the grid for this excellent video.

The problem has always been that data validation can’t take in a table column. I hope that one day Excel will be upgraded to enable this, but until then, here’s how I am going to do it from now on.

1. Create the table with the list you want for the data validation.
2. Click on the Formulas menu and choose Define Name (it won’t be greyed out). (Shortcut keys Alt, M, M, D)
3. Give your Named Range a name, in the name box.
4. In the Refers to box, select the whole column of the table you created in step 1. Click Ok.
5. Go to where you want to add your data validation.
6. Go to Data menu and choose Data validation (Shortcut keys Alt, A, V, V)
7. Choose List, and in the Source box put the named range you made in step 4. You can push F3 and a list of the name ranges pop up, and you can choose from the list! Click Ok, and Ok again.

Now you are done. Whatever is in the table will show on the drop down list. If you add new items to the table, they will show on the drop down list.

I think this is by far the neatness and most robust way of doing this. It is a little bit of initial set up but well worth it for a fantastic result.