There are pages and pages and blogs and blogs worth of sometimes very heated debates dedicated to the simple topic of whether or not it is considered “best practice” to use a Named Range in building a spreadsheet model.

Simply put, I do on a very limited basis and usually only in two cases.

There are pages and pages and blogs and blogs worth of sometimes very heated debates dedicated to the simple topic of whether or not it is considered “best practice” to use a Named Range in building a spreadsheet model.

Simply put, I do on a very limited basis and usually only in two cases:

  • When referring to a constant (i.e. cell “A10” on my “InputSheet” is the Tax Rate Assumption of 35%).  It is much more intuitive to see a formula multiplied by “TaxRate” in the Workbook rather than, for example, “InputSheet!A10”
  • When referring to a data range of information source as a data dump of general ledger information.  For example, if one sheet in the workbook is the source data of general ledger information and this source extends from cells A1:E4096 and throughout the workbook I continually refer to this source through VLOOKUPS, I think it is much more intuitive to see a VLOOKUP formula written, for example, as:
    • =VLOOKUP(lookupreference,columnnumber,”SOURCEDATA”,FALSE) rather than
    • =VLOOKUP(lookupreference,columnnumber,A1:E4096,FALSE) particularly if the source data is on a different tab).