Intro to Financial Modelling part 11: Clarity and Named Ranges

This topic is covered on page 13 of the Financial Modelling Code; “Use clear range names where appropriate”.

In Excel, cells and ranges are referred to by a grid style reference utilising column letters and row numbers, e.g. ‘A1’ or ‘H5:D10’. However, Excel also has the functionality to assign an alphanumeric name to a cell or range, called a ‘named range’, which can then be referenced in formulae in place of direct column and row cell references. 

Sounds straightforward, but I have to say my heart sank a little when I drew this topic. In years past, the use (or not) of Names and named ranges in Excel was a source of great contention in the modelling community – almost sectarian in nature – with very robust opposing views on both sides of the debate.

The debate continues but it has become more nuanced over time. The process to develop the Financial Modelling Code, building consensus across the major firms and boutiques, has identified common principles of good practice and I think most modellers would agree that Names have a useful role to play.  We explore those principles further below.

One commonly stated reason for using named ranges is to aid model transparency by making formulae more intuitive to read and interpret. In the same way you define a variable in VBA as if you were reading from a set of instructions, you can define named ranges in a model such that a formula can read like a natural(ish) language sentence. As a simple example, having defined the inputs, you can define Sales as ‘Volume’ * ‘Unit_Price’, as shown in the graphic below.

Correctly done, this can embed the purpose and nature of the formula within the cell, making it easy for a reader to understand.

Note from the example above that we have not abbreviated ‘Unit_Price’ to a shorter term using fewer characters and taking less space. This is intentional, the reason for this is to prevent the named ranges from becoming too cryptic and difficult to interpret to model users that are not otherwise familiar with the model. However, there is a balance to be struck between comprehension and usability. If names are presented ‘longhand’ the formula can quickly become overly long and unwieldy. Therefore, we would usually advocate some degree of abbreviation or ‘shorthand’ but this needs to be carefully considered at inception as the model is scoped and designed. It is important that the naming convention adopted is:

1. as intuitive and unambiguous as possible (e.g. prefer SenDebtInt1 over SDI1 or SDInt1);
2. internally consistent (i.e. repeat common syntax throughout); and
3. capable of expansion without compromising the first two objectives.

The above example also demonstrates that using named ranges can bring clarity to a function; if you were to explain to someone how we have calculated sales, it would be as simple as saying that we have ‘multiplied volume by unit price’. The flip side of this is that if you are tasked with reviewing a model that uses named ranges, a formula’s precedents become less intuitive to navigate as the named range separates the formula from its physical orientation within the spreadsheet, and the simplicity of a sheet / cell reference is obscured. Contemporary model audit software can go some way to assist with this, but many modellers would cite this as a significant drawback to using extensive named ranges.

In past versions of Excel this obscuring of explicit cell referencing was sometimes hard to follow: formulae could make use of the fact that when a reference was made to a named array which treated it like a single cell then the formula would pick up only the value from that array which was physically in-line with the cell containing the formula. A common example of this being where the model needed to multiple price and volume assumptions on a month by month basis. Every cell in the results range would reference the entire source range and each cell in the results range would use the source values from the corresponding positions. The overall result was that the position of a cell making the reference would control which individual referenced cell within a named range was called upon, but there was little control or clarity on what was happening and it was reliant on the model author being diligent about keeping source and destination ranges in line. This issue has been somewhat addressed by the latest version of Excel and its support for dynamic arrays and the ‘spill’ behaviour which is covered in more detail below.

Since the Code was published, Microsoft has introduced Dynamic Arrays within Excel. These have seemingly made it quicker and easier to create models which contain consistent formulae. 

In previous versions, referring to a named range that contained multiple cells would only produce a single result, in ‘Dynamic Excel’ referring to a named range will now automatically generate formulae for the full array across multiple cells. Applying this to our example above, we can enter one formula in the first cell then Excel will now automatically populate the length of the array across all columns of the source ranges.

(Note, if this is impossible to do because the required cells are already populated then Excel will produce a #SPILL! Error, and if the source ranges were different sizes then any mismatched cells produce a #N/A error.)

Again though this raises the counter-argument that whilst reading the formulae makes sense, trying to understand the formulae’s precedents due to the lack of cell references can make this formulae less intuitive – i.e. the formula in cell D6 simply says “Volumes * Unit Prices” it does not explicitly reference cells D3 and D4 so the model reviewer needs to understand the context in order to understand which values are being used in this particular cell. 

A further argument against named ranges is that its use can lead to bloated file sizes, excessive recalculation time and eventually an unusable model. This issue arises when importing sections or worksheets from other files which contained named ranges. As part of the copy/paste process the references to named ranges can be brought across, even if they were not used within the values or formulas being copied. Repeating this process over and over leads to an excessive amount of duplication of names which have now been automatically defined in your financial model (including any erroneous ones). By default these imported named ranges will now be defined to the worksheet, and further issues can occur when an imported named range already exists within the workbook... (This is one of the main reasons to advocate for using ‘paste special’ when copying values from external files – it reduces the chance of bringing more than you intended to!)

Many modellers reading this article will have seen a model or spreadsheet suffering from this in the past and understand how frustrating it can be to solve the problem, especially when attempting to copy and paste a worksheet only to receive a seemingly endless prompt from Excel stating that the name already exists, as shown in the graphic below. (Thank goodness there is now a ‘yes to all’ button!)

Resolving the presence of excess names can be a laborious and time-consuming process unless a VBA script is able to be employed to help automate the process. 

Accordingly, we recommend you ensure regular maintenance of the Name Manager, periodically reviewing and removing erroneous or unused named ranges.

One clear benefit of using named ranges is where VBA code is used in the model. The reason for this is that unlike in Excel worksheets, direct or static cell references within VBA code are not automatically updated if rows/columns are inserted or deleted, or blocks of spreadsheet code are moved in the model and as a result a macro with hardcoded cell references will end up referencing incorrect cells if they have moved since the macro was written. However, if the VBA code references a named range, then it doesn’t matter if the physical location of the named range changes on the sheet, as the macro will always correctly reference the range by name rather than ‘by coordinates’.

Regardless of where you stand on the great names debate – it is clear that named ranges can serve a clear purpose, and in some instances, are a necessary tool in order to overcome certain limitations that exist within Excel. 

If you are using named ranges in formulae or VBA code, then remember:

1. use a clear, understandable, name and avoid excess abbreviating of key terms;
2. ensure regular maintenance of the Name Manager by removing erroneous or unused named ranges; and
3. consider the use of named ranges in your VBA code to avoid ‘static’ direct cell references.