Using Named Ranges in Excel: Some Comments

An earlier blog on Best Practice Principles in .Excel Modelling generated quite some interest, as well as demand for more details on some of the points made, especially those concerning the use of named ranges risk assessment models in Microsoft Excel. In the earlier posting, I had simply stated that (in my opinion): “Named ranges should be used highly selectively but not excessively”. Here I will expand a little more; the topic itself can be a subject of quite animated discussion within the risk analysis modelling community, with a wide set of opinions expressed. The points I make below are therefore simply my view of the topic.

In my view, named ranges are indispensable in some types of modelling situations. The most frequent of these in my experience are:

• When writing VBA code (macros) that refer to ranges in the workbook (as such code almost always would do at some point), the use if names provides a much more robust way of creating flexible code, rather than referring to the range using cell references.
• For general Excel modelling, it can be useful to name a small set of key ranges, so that the F5 key or the name box can be used to rapidly navigate around the model.
• Where the model process is not required as a process to experiment with or modify a model, but is purely required to implement a known situation which will never be changed. However, much Excel modelling involves the process of experimenting with different approaches, and the use of named ranges in such cases can create extra complexity.

Some disadvantages of using named ranges include: that their use too early on in the risk analysis modelling process can create cumbersome structures, that it can be easy to create models with far too many names that then become poorly labelled, and the possibility to inadvertently create links between models. The management of names (such as their deletion and their scope) has traditionally been cumbersome in Excel. It is important to note that Excel’s 2007 Name Manager has radically reduced some of these disadvantages (this change being one of the most important improvements made to Excel when moving from Excel 2003 to 2007, in my opinion).

This set of points is by no means complete; a deeper discussion of modelling in Excel, including robust and readily understandable risk analysis models or option valuation and price forecasting.

An earlier blog on Best Practice Principles in .Excel Modelling generated quite some interest, as well as demand for more details on some of the points made, especially those concerning the use of named ranges risk assessment models in Microsoft Excel. In the earlier posting, I had simply stated that (in my opinion): “Named ranges should be used highly selectively but not excessively”. Here I will expand a little more; the topic itself can be a subject of quite animated discussion within the risk analysis modelling community, with a wide set of opinions expressed. The points I make below are therefore simply my view of the topic.

In my view, named ranges are indispensable in some types of modelling situations. The most frequent of these in my experience are:

• When writing VBA code (macros) that refer to ranges in the workbook (as such code almost always would do at some point), the use if names provides a much more robust way of creating flexible code, rather than referring to the range using cell references.
• For general Excel modelling, it can be useful to name a small set of key ranges, so that the F5 key or the name box can be used to rapidly navigate around the model.
• Where the model process is not required as a process to experiment with or modify a model, but is purely required to implement a known situation which will never be changed. However, much Excel modelling involves the process of experimenting with different approaches, and the use of named ranges in such cases can create extra complexity.

Some disadvantages of using named ranges include: that their use too early on in the risk analysis modelling process can create cumbersome structures, that it can be easy to create models with far too many names that then become poorly labelled, and the possibility to inadvertently create links between models. The management of names (such as their deletion and their scope) has traditionally been cumbersome in Excel. It is important to note that Excel’s 2007 Name Manager has radically reduced some of these disadvantages (this change being one of the most important improvements made to Excel when moving from Excel 2003 to 2007, in my opinion).

This set of points is by no means complete; a deeper discussion of modelling in Excel, including robust and readily understandable risk analysis models or option valuation and price forecasting.