What is known as range of cells and list their types?

A cell range in Ms Excel is a collection of chosen cells. It can be referred to in a formula. This is defined in a spreadsheet with the reference of the upper-left cell as the minimum value of the range and the reference of the lower-right cell as the maximum value of the range.

If you were to ask modelling professionals about the merits of using range names, you would find that opinion is strongly divided. In spreadsheets, used appropriately and sparingly, range names can provide great value because they can make formulas easier to read. In macros (not discussed here), they are vital. Overuse, on the other hand, can lead to end-user confusion.

Range names are names used to refer to cell references, formula results, or values. They are often used to avoid hard-coded values appearing in formulas and to make formulas clearer in general. They are stored in what is known as the Name Manager in Excel.

Users may create these names with the Name Box (circled in the screenshot above) drop-down menus and/or simply use the keyboard shortcut Ctrl+F3in all versions of Excel, and then click on the New button in the Name Manager dialog box, as shown in the screenshot below.

Clicking on New (shown in the screenshot above) causes the following dialog box to appear:

Scope

Note the second section (Scope). All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognised without qualification.

For example, if you have defined a range name as “Profit” with its scope as Sheet1, rather than Workbook, then it will be recognised as “Profit” only in Sheet1 (ie, without qualification).

To use this local name in another worksheet, you must qualify it by preceding it with the localised worksheet name:

=Sheet1!Profit

If you have defined a name, such as “Cashflow”, and its scope is the workbook, that name is recognised for all worksheets in that workbook (but not for any other workbook). If the scope was to a worksheet (say, Sheet1), then the range name would be “Sheet1!Cashflow” instead. Therefore, workbook scope makes for clearer range names and avoids confusion.

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However, you can use the same name with different scopes. For example, you can define a name, such as “Profit”, that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name “GrossProfit”, for example, is always referencing the same cells at the local worksheet level.

You can even define the same name, such as “Profit”, for the global workbook level, but again this scope is unique. In this case, there may be a name conflict. To resolve this conflict, Excel uses the name that is defined for the worksheet by default. The local worksheet level takes precedence over the global workbook level. This can be circumvented by adding a prefix to the name, eg, rename it “WorkbookFile_Profit” instead.

It is possible to override the local worksheet level for all worksheets in the workbook, except for the first worksheet. This will always use the local name if there is a name conflict and cannot be overridden.

It is strongly recommended that you always try to create range names on a workbook scope level only. Further, where possible, avoid using range names in (sections of) worksheets that will be copied to other worksheets or workbooks. You will confuse Excel, the end user, and most importantly, yourself!

Care with names

The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.). For example, to enter the name “Cash Flow” you should enter “Cash_Flow” or “Cash.Flow”.

You cannot use a name that could otherwise be confused as a cell reference; for example, “Day1”, as this is already a cell reference (many people have tried!).

There appears to be no limit to the number of names you can define, but a name may contain no more than 255 characters. Names can contain uppercase and lowercase letters, but Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the global name “Profit” and then create another global name called “PROFIT” in the same workbook, the second name will be rejected as names need to be unique, irrespective of capitalisation.

It is not a syntax issue, but I strongly recommend thought is given to adding prefixes to range names. Regular readers will note that my list range names always begin with “LU_” where “LU” stands for “Look Up”. Similarly, I use “BC_” for “Base Cell” when working with the OFFSET function.

By using these prefixes, I understand the purpose of the range name, and names with a common purpose are grouped together in a list. This is not to say all range names should contain a prefix. “Tax_Rate”, for instance, makes sense on its own, and adding a prefix would only detract from the name given, potentially confusing the end user.

Creating range names quickly

There is a nifty shortcut for creating range names using existing names. Consider the following list:

Imagine you were to highlight cells N12:N18 in the above example and then use the shortcut Ctrl+Shift+F3(alternatively, select Create from Selection in the Defined Names grouping of the Formulas tab on the ribbon):

With the first box (Top row) checked, by clicking on OK the range N13:N18 (not N12:N18) will be named “Phonetic_Alphabet” (ie, the underscore will be added automatically). Ranges across rows can be named in seconds similarly using Left column.

The reason this dialog box uses check boxes (rather than option buttons) is to allow users to select more than one at a time. For example:

Highlighting N31:R34 and using the keyboard shortcut Ctrl+Shift+F3 once more should generate the Create Names from Selection dialog box, as shown above, with both Top row and Left column checked. This means that O32:O34 will be called “Jan”, O33:R33 will be called “COGS”, and so on. This would take considerably longer to perform manually.

This example also illustrates why spaces are illegal characters in range names (and should not be added to formulas either). Space is the intersect operatorin Excel. If you were to type the following formula:

=Gross_Margin Feb,

Excel would return the value in cell P34 (the intersection of the two ranges, above), ie, $4,183. This can be a powerful yet quick and simple analytical tool for key outputs — faster than VLOOKUP or INDEX MATCH.

Using range names quickly

One of the reasons I like using the Ctrl+F3 shortcut is that it is part of the F3 Names family of shortcuts. We have just seen how Ctrl+Shift+F3can be useful — and so can F3 on its own.

Perhaps superseded by the fact that in Excel 2007 and later versions, Excel will now prompt as you type formulas, F3 has been very useful in the past as the Paste Name shortcut. For example, as you type a formula, you can refer to a range name by simply typing F3to get the Paste Name dialog box, as shown in the screenshot below.

If you select one of the names and click OK, this will insert the range name.

However, look closer at the dialog box. The Paste List button in the bottom left-hand corner, if depressed, will paste the list and their definitions into a preselected range of cells (ie, before you use the shortcut F3) in an Excel worksheet, which can be invaluable for model auditing purposes.

Sometimes, formulas have been written before the range name was created. In some circumstances, it is possible to apply these names retrospectively using Apply Names within the Define Names group of the Formulas tab (see screenshot below).

Note that the keyboard shortcut Alt+I+N+A will work in all versions of Excel. Selecting the required range names in the resulting dialog box will see formulas on the active worksheet(s) updated accordingly.

Deleting range names

If I got paid just $1 for every time I have been asked how to delete range names, I would probably have retired by now. This was chiefly attributable to the counterintuitive menu in Excel 2003 and earlier versions:

From the resulting dialog box, you would then select the range name (unfortunately, only one at a time could be selected) and hit Delete, as shown in the screenshot below.

Excel 2007 and later makes this much simpler. In this case, users are more likely to go to the Name Manager rather than the confusing Insert drop-down menu:

The other marked improvement is that multiple names may be deleted simultaneously by using the Ctrl or Shift keys to make multiple selections before hitting the Delete button.

Relative referencing

By default, range names are referenced absolutely (ie, contain the $ sign so that references remain static). However, imagine a scenario where you are modelling revenue and you wish to grow the prior-period value by inflation (already given a range name, say cell C3 on Sheet1). Simply click on any cell (eg, I will use D17 arbitrarily), then define the new range name as follows:

Note the Refers to: entry. Cell C17 (the cell to the left of D17) has been chosen without the dollar signs. This is a relative reference. Once we click on OK, the range name “Prior_Period” will be defined as the cell immediately to the left of the active cell. We can then inflate values easily by copying the formula

=Prior_Period*(1+Inflation)

across the row.

Other types of names

As I said earlier, names may also refer to functions, dates, and constants — the latter can be useful (eg, “Months_in_Year” is defined as 12) in order to avoid inserting hard code into a formula.

Word to the wise

This article discusses just the tip of the names iceberg. Experimenting can pay big dividends. The aim is not to go overboard, however, as a preponderance of names in a workbook may actually make formulas — and hence your model — more difficult to follow.

Further, be careful if you name ranges that are then deleted. The range names will not be deleted (even though they will no longer appear in the Name Box). They will need to be deleted as described above in order to avoid potential errors in formulas, etc. It’s important to remember this as this has often been a cause of file corruption or crashing in Excel.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of An Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at . To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at .