I am in two minds about named ranges.

Named ranges allow a formula to capture the logic of the calculation in an easy to understand way – illustrating the developers thought process and assumptions.

=Cost_Price*(1+MarkUp)

While this helps logic reviews, it does make it more difficult to review accuracy. When using named ranges, reviewers need to both (i) identify which cells are being referred to by the named ranges, and (ii) ensure they contain the appropriate value. This introduces an additional step in the review. Further complications can arise when ranges have similar names, ranges appearing in multiple worksheets.

The biggest difficulty with named ranges, however, is the vast number of redundant ranges which seem to accumulate. Incrementing versions of the model over time or the course of development, copying between workbooks, all seem to cause this problem. The impact of this is slower performance, increased file size, and decreased stability of the model.

EconomicsLAB’s First Aid Kit is an Excel add-in which addresses this issue, and other common Excel problems. If you would like further information or would like us to tailor a solution to your needs, please contact adam@economicslab.com.au.