The article Five ways to take advantage of Excel list features showed five basic list features built right into Excel. You don’t have to do much to take advantage of them, but sometimes you’ll need something more sophisticated. In this article, I’ll show you two advanced list features using a validation list and a lookup function to generate a dynamic list. Neither technique is superior—your needs will dictate your choice.
I’m using Excel 2016 (desktop), but both techniques will work in earlier versions. For your convenience, you can download the demonstration .xlsx. The VLOOKUP() function is available in earlier menu versions, but both techniques rely on the Table object to be dynamic, and the menu versions don’t support the Table object. The browser edition supports validation lists and Table objects, but inserting new data into a Table is awkward—that edition ignores the Tab wrap to the new record behavior when entering a new record.