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:
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.
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:
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
Range 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
You can also create Range Names directly in the Name Manager by clicking on Define Name:
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.
(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:
2. Go to the Formulas menu and select Use in Formula.
3. A list of all the workbook’s Range Names will be displayed for picking:
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.
The list will be automatically generated:
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.