Names are Good

Post date: Sep 19, 2016 2:03:31 PM

Using names in XL is an XL modeling best practice. There are many strong reasons to use names and no rational reasons against them, And I mean none at all. Which is odd because some modelers are adamantly against names. For years I have sought their reasoning. What's to hate? Here is how one standard explains it:

"In the majority of cases, the ingredient-rich construct of a () calculation block makes naming not only unnecessary (as the dynamic row label provides better information than a Name would) but also something to avoid (given the likelihood that a discrepancy between the dynamic row label and the static name will creep in over time)."

"ingredient-rich construct" - I don't know what that means. But I do know it says labels that would apply to named ranges are dynamic. That would be bad. Once a model is placed into production such labels must not change. If the name matches the value's label when audited then no discrepancies should creep in over time if the model is properly protected. If the model is not properly protected then all auditing efforts are wasted and the model cannot be considered validated.

Names and labels must not change after being placed into production. During development is a different story. During development evolving ideas common in the development cycle may encourage us to change labels and names. Fortunately that is no problem when using Excel's Tables and Structured References introduced in XL2007 (nearly a decade prior to this writing). Structured References are a type of automatic, dynamic name. When we use them in formulas we can readily identify which column a Structured Reference comes from. And if we change the column heading all dependent formulas automatically adopt the new name. Discrepancies between column headings and Structured References simply cannot exist.

Names changed in Name Manager also propagate throughout dependent formulas but when changed in this manor can introduce discrepancies between the name and the named range's label. Changes to named range labels can also introduce discrepancies. To address this BXL provides an add-in that manages named ranges. It creates them and updates them to force unity between the name and the named range label. It also propagates name changes to dependent formulas. For a copy of this add-in visit: BXL Modeling Helper. The add-in is only to be used during development. It should never be used on models in production.

I want to emphasize, once a model has been audited and placed into production, names, formulas, and labels must not change. We keep hearing nightmare stories about errors in XL models and how 80% or more of XL models contain errors. If we look at those reports we see one common thread, those errors were discovered by someone other than the modeler who looked. If a model is important, it MUST be tested and audited and once validated the calculations MUST NOT CHANGE! Change management is a SOX requirement and should be a financial modeling community demand.

Change management is not a name problem. It is a modelling problem. Complaints against names are never about names but more deeply routed problems. Names won't solve those problems directly, but they can help indirectly. Here are some examples:

The HUMAN Factor:

We are humans, not machines. We understand and remember names better than addresses. This is why computer languages replaced memory addresses (similar to cell addresses) with variable names (similar to XL's names) over a half century ago. Names make things easier for people to understand and easier to identify when things are out of place.

Where? Why care?

Names make it possible to move our values anywhere we want. As we develop our model we often see opportunities to improve our model's readability by placing values somewhere else. Names allow us to do that without breaking dependent formulas.

Mistype Mistakes? No More

My keyboard skills are not the best. I fat finger keys all the time. I can mistype cell references and Excel will be blissfully unaware of my mistake. If I mistype a name Excel complains bitterly.

Breaking Limitations

Names can grow. I love tables. They are a type of dynamic named range. We can expand tables and not worry about breaking dependent formulas. Prior to tables we had to create complicated and fragile dynamic range formulas or watch our formulas anchored by cell addresses break.

Transparency

I measure formula transparency as the number of steps required to find a formula parameter’s value and label. If the label is missing, the parameter is opaque. To find a formula’s parameter value and label I click in the formula bar which highlights and color codes parameter cells. One step is all it takes. This is the same for cell references and names but finding labels differs.

To find a cell reference’s label we must look immediately left or above the parameter's value. Sometimes they are easy to find. Sometimes they are impossible to find because the modeler neglected to add them. It is impossible to neglect labeling a named reference’s value because the name IS its label. There are no steps required to find a named reference’s label. Names are ALWAYS more transparent than cell references and can NEVER be opaque.

Self Documenting Formulas

Because names are more transparent, formulas using them are more transparent. In particular, Structured References not only provide a reference's label but also its context making reading formulas that use them almost like reading a sentence. Those unfamiliar with structured references may not know exactly what this says, but they would have a good idea:

=[@Revenue] - [@Expenses]

Ironically, modeling standards prohibiting names also promote the idea that anyone of any skill level should be able to read their models. With cell references that simply isn't possible. A cell reference is a technical construct that must be learned before it can be understood. And they are not simple to understand as demonstrated by the number of experienced XL users still fumbling with absolute, relative, or mixed cell addresses.

I abandoned cell references years ago. My models do not use them at all. They are unnecessary, cumbersome, restrictive and fragile. Names are more flexible, more robust and more transparent. When used properly they make our models better.