Optimising Your Range Names in Excel

Introduction

Range names are a powerful tool for referencing cell ranges in Excel and are an important technical aspect of our best practice financial modelling methodology - Alpha Spreadsheet Engineering. Range names, when combined with formula construction rules, enable longer, multi-layered, powerful formula which reduces the number of lines in a worksheet and maintains transparency. This post introduces the essentials of how we name ranges to enable more sophisticated, easier to read and powerful formulae.

If you would like to learn more about Range Names and structuring powerful formula you will enjoy our two day, 100% hands-on, case study based course, Mastering Financial Modelling delivered by our Principals. 

Download PDF    Training Courses

A critical aspect of a great financial models is the clear presentation of inputs, calculations, outputs and tests. The clear presentation of calculations encompasses; the structure of worksheets within the workbook, the grouping of calculations within worksheets and, as we focus on here, the structure of the individual formula.

Range names are an essential tool in making formulae, the building blocks of the model, readable – this is important because this enables their understanding, and maintenance when the model is used, audited or updated.

Range names, when used properly, are useful because they make:

• formula easier to read by adding immediate context
• formula easier to write thanks to Excels intellisense as you type
• the link to VBA more robust
• links between workbooks more robust

But like many aspects of Excel we need some rules otherwise they often become disparate and hard to manage and then easy to stop using consistently.

Alpha / range name protocol

Alpha range names, work in a similar way to sending a letter to a person.

Country.City.Street.Number.Name

Thinking of it as

A.B.C. …. .Z

Where A through to C are Family (or Group) categories and sub-categories with Z being the particular property. For example:

• Constants.Time.DaysPerYear
• Contstants.Time.QuartersPerYear
• Customers.Country1.Existing

In this example, "Constants" represents the family of model inputs which are not intended to change but need to be readily and clearly referenced. "Constants.Time" is the sub-set of constants which relate to time based values and QuartersPerYear is the value we want to identify and use.

On first impressions, these precursors may appear  to be overkill but the structure becomes obviously useful when you are typing a formula because you don’t have to remember the full syntax of the name, just the "Constant" part and then intellisense provides suggestions for the sub-categories, in this case “Time” and then all you need to do is choose the value you need. This approach is a useful discipline for categorising cell ranges and helps add structure to formula construction.

When thinking through what the categories and sub-categories are think of a tree diagram with branches being minimised and unique.

Family 1: Useful constants

 

Family 2: Company A

 

Family 3: System 

 

Note the capitalisation of the new word, we do not use a space or “_”.

Taking a recent model in the image below you can see how just for Expenses they were split between People and Non-People and that some of the range names have the property XbyY, in this example ByPersonAndYear. Expense.People.Salary.ByPersonAndYear

This makes formula which rely upon ranges, such as Index(Match,Match) or LOOKUP() even easier to read and understand.

Using range names in formula

Let’s take a look at how range names make formula more readable and thereby enable more sophisticated formula to be created whilst maintaining transparency.

Using range names also enable longer formula to be more readable, in this case it saved hundreds of individual lines which would have been sub-workings for each way of slicing up expenses by the many different staff positions, with different salary packages.

Or more advanced use in an array for an ultra-flexible output summary for a multi-national equipment leasing firm.

Or more advanced use in an array for an ultra-flexible output summary for a multi-national equipment leasing firm.

Tips for using range names

• Adopt an easy to use, well considered, approach, like Alpha - and use it consistently
• Keep up with the house-keeping (add the Name Manager to your Quick Access Toolbar).
• Widen the range name box (top left of your workbook, just above A1).
• Don’t go overboard, Zero and One do not need their own names…..yup it happens.

Summary

There is no right or wrong way to manage range names, we have taken ours from the discipline of .NET application development and have found them easy to use consistently, practical and easy for clients to understand. Give it a try.

Happy and smooth modelling!