How to use Range Names to Simplify Your Business Models

 

Today, let’s look at how Excel’s Range Names feature can make your life more simple. Business models using Excel usually require remembering complex formulas and cell references. No longer, as Range Names let you construct much simpler formulas, while simultaneously providing a way to check for errors and making it easier for other people to understand your spreadsheet. Sounds too good to be true? It’s not! Read on to see how it works.

 

Excel uses a grid structure so that cells are referred to by grid ‘coordinates’ indicating the column and row, such as C3 or AZ14.

 

When constructing formulas, using coordinate references can be obscure, for example:

 

= Z14*SUM(A17:21)/COUNT(A17:21)

 

It would be far easier to construct and understand a formula such as:

 

= Discount * Unit_Prices / Number_of_Units

 

Range names are a way of translating the Excel grid coordinates into meaningful descriptive words.

 

The use of Range Names provides the benefits of:

 

  • Making it quicker to construct formulas

 

  • Making formulas more error resistant

 

  • Providing a check of formulas against logical business processes

 

  • Providing clear documentation of how the formulas are constructed

 

  • Providing a clear audit trail through the workbook

 

Creating Range Names

 

Range names can be used to refer to a single cell or an area of cells. It is also important to remember that Range Names are absolute references to cells – they don’t change reference when they are dragged or copied across the workbook.

 

Excel pic

Excel 2

It is far easier to refer to cells and parts of a worksheet by a name instead of the cell reference, for example “July_Total”    instead of “C8” or “NameList” instead of “B1:B7”.

 

The simplest way to create a range name is by:

 

Selecting a cell (or range of cells)

 

Then typing a range name in the Name Box at the top left of the screen:

 

 

Name Box

A range name cannot have spaces in it so use the “_” character to separate words, or use “CamelCase” i.e. a mixture of upper and lower case letters to identify individual words.

 

Managing Range Names

 

Managing Range NamesRange name are managed from the Name Manager in the Formulas menu, where you can see all the names used in a workbook, their cell references and current values

Range names 2

 

You can also create Range Names directly in the Name Manager by clicking on Define Name:

 

range names 3

 

Using the Name Manager to create Range Names is very useful where the range of cells referred to is an irregular shape, and it also provides the opportunity for comments to be recorded against Range Names.

Deleting Range Names

 

Range Names can only be deleted or edited from the Name Manager (i.e. once they have been created in the Name Box, they cannot be deleted or edited there.

Range names 4

 

(HINT: to see a list of Range Names used, you can use the F5 key shortcut.  This is the GO TO function.)

 

Using Range Names

 

In any Excel formula or selection, the name of a defined range can be used in place of the cell row and column numbers. For example:

 

= (July_total + June_total)  instead of   = (C2 +C3)

 

To pick a Range Name for use in a formula.  For example, if you want to construct a formula using SUM(GreenRange):

 

1. Start to type the formula:

Sum

 

2. Go to the Formulas menu and select Use in Formula.

Use in formula

3. A list of all the workbook’s Range Names will be displayed for picking:

 

use in formula 2

 

Listing Range Names for Reference and Audit

 

As a key part of workbook documentation, Excel provides the facility for listing all Range Names defined, and the cells to which they refer.

 

To generate this list, click on a cell in the worksheet where you would like the list pasted, then Use in Formula, then Paste Names.

 

Listing reference

 

Paste name

 

The list will be automatically generated:

 

Name list

 

These range names allow for much clearer descriptions of what is contained on the worksheet, and provides a valuable tool in checking for spreadsheet errors, and providing audit facilities.

 

This post was written by Harold Graycar, an experienced executive with a broad background in information technology, business development and general management.He has over 25 years’ experience in technical, commercial and general management roles: advising corporate and government clients on project funding, models and strategies for business development, procurement of equipment and services and outsourcing of systems and operations – all based around business analysis and numerate skills.

 

If you want to find out more, check our Business Analysis course here.