Named ranges is one of the most useful features that excel has. Before pivot tables and charts, this is one of its core functions that helped me develop and understand excel macros.

A particular scenario I encountered extensive use for named ranges is in a timelogger we use at work.

Particularly, to get a list of deliverables or tasks for a particular development phase like detailed design creation. The excel file we use has a named range "DD" and we use this named range in conjunction with excel's list data validation to be used as a dropdown when inputting a time log. :)

A very helpful topic covered by the @theexcelclub.