Introduction
A cell is a single element in a worksheet. Its address is the intersection of the row number and column character, such as A1 for the top-left cell in a worksheet. (This applies to A1 reference style. Excel also has an R1C1 reference style, not covered in this module.)
A range is a group of cells. Its address is given by the address of the top-left cell and the address of lower-right cell, separated by the range operator (:). An example is B2:C4
.
Examples of range addresses
B2
– a single cell in row 2 of column BA1:A4
– four cells in rows 1 to 4 of column AB:B
– all the cells in column B2:2
– all the cells in row 22:10
– all the cells in row 2 to row 10A1:XFD1048576
– all the cells in the entire worksheet
Selecting ranges
Methods include
- A contiguous range is a group of cells in one rectangular block. This could be a group in one row, or one column, or multi row and multi column
- Use the mouse to Click-and-Drag the selection – either square or rectangle – called a continuous range
- Enter the address in the Name Box
- On the ribbon use Home > Editing > Go To > Reference
Selecting non contiguous ranges
Methods include
- Hold the Ctrl key and use the mouse to Click-and-Drag each selection
- Enter the addresses in the Name Box with each separated by a comma (the union operator)
- On the ribbon use Home > Editing > Go To > Reference
Example – to simultaneously select the Date, Close, and Volume data for AMP.AX shown in figure 1, firstly note the data addresses including the column labels. The Date vector is A4:A16
, the Close price is E4:E16
, and the Volume vector is F4:F16
. The collective address is A4:A16,E4:E16,F4:F16
. Because Close and Volume are contiguous, the reference A4:A16,E4:F16
would achieve the same outcome. You can click the animation in figure 1 to enlarge the view. Once the selection is made, it could be use to produce a Chart object.

Naming ranges
These sections could also be titled “Defining names”, and “defined name specifications”, etc. Those from a Lotus 1-2-3 background would remember Range > Name > Create and refer to a range name, rather than the Defined Name nomenclature from the Excel ribbon Defined Names group
Benefits of using defined names:
- Suppose that the sales data for the Melbourne store is in the range
F64:F29169
on the worksheet namedQ1Y16 Victoria
-
-
- Applying the name Melb.Sales makes it easier to refer to or find the data, or use the range reference in a formula or chart
- By default, range names use absolute addressing. This can be identified by the Refers to: property having the value
='Q1Y16 Victoria'!$F$64:$F$29169
- Their use in formulas is easier to understand.
=SUM(Melb.Sales)
is clearer than=SUM(F64:F29169)
- A list of defined names is readily available in the Formula AutoComplete drop-down list (see figure 2) and the Paste Names dialog box (figure 3)
Fig 2: Formula autocomplete drop-down list – with the name selected, press the Tab key to complete entry of the name in the formula Fig 3: The Paste Names dialog box – with the name selected, click OK to insert the name in the formula -
Range name specifications:
- A maximum of 255 characters. The name should be descriptive and concise ie.
InterestRateANZ
. Avoid {x,y,z} - ‘c’ and ‘r’ are not allowed because c refers to all of the cells in the column of the active cell, and r refers to all of the cells in row of the active cell
- Must begin with a letter (or underscore or backslash)
- Periods are allowable. For example
Tax.2016
- Cannot contain spaces or special characters. Permitted A to Z, a to z, 0 to 9, _, .,and \
- Cannot be a valid cell address.
Tax2016
is illegal because it refers to a cell in column TAX, row 2016 - Are not case sensitive. Use case to enhance readability. Use
TaxRate
rather thantaxrate
- Must be unique within a workbook or worksheet (depending on the scope). But a unique name can refer to multiple ranges. Collectively, these multiple ranges form a noncontiguous range
- Avoid using the names created by Excel such as
Print_Area
, andCriteria
- A cell or a group of cells can have more than one name
- The number of names in a workbook is limited only by available memory
Ranges can be named three (four) ways
-
- Using the Name Box – figure 4
-
- Formulas > Defined Names > Define Name – figure 5
-
-
Fig 5: Name a cell or range – using the Define Name item in the Defined Names group - To apply the name
SalesN
- Select the range
G64:G69
- Select Formulas > Defined Names > Define Name on the ribbon
- The New Name dialog box will appear. Excel has preselected the column label as the Name. Name:
SalesN
, Refers to:='worksheet name'!$G$64:$G$69
- Click OK
- To apply the name
-
-
- Formulas > Defined Names > Create from Selection – figure 6
-
-
Fig 6: Name a cell or range – using the Create from Selection item in the Defined Names group - The idea is to apply the labels {Stock, Exercise, Time, Risk Free, Volatility} in column E, to the adjacent values in column F. That is, cell
F81
with the value 12 will be given the name Stock. - Select the labels and the values, that is, the range
E81:F85
- Select Formulas > Defined Names > Create from Selection on the ribbon
- The Create Names from Selection dialog box will appear. In the
Create names from values in the:
property, Excel has preselected theLeft column
. The values referred to are in fact the data labels - Click OK
- The label in cell
E84
does not satisfy the specifications for a Name, because it contains a space character. Excel automatically replaces illegal characters with an underscore. The Risk Free label becomes theRisk_Free name
- A label with an illegal leading character such as the number in 2Price would be named
_2Price
- A label with an illegal cell reference such as Tax2015 would be named
Tax2015_
- The idea is to apply the labels {Stock, Exercise, Time, Risk Free, Volatility} in column E, to the adjacent values in column F. That is, cell
-
-
- Formulas > Defined Names > Name Manager > New – figure 7
-
Fig 7: Name a cell or range – using the New button on the Name Manager from the Defined Names group - Clicking the New button brings up the same New Name dialog box as described in point 2
- The process is shown in figure 3
Managing range names
Finding a list of names
- The Name Manager in figure 5 provides a list of all Names – range, table, and constants – with global (workbook) scope and local (worksheet) scope. Range names can be identified by the baggage label or tag to the left of the name in the Name column
- The Name Box drop down list displays a list of names – global, and local to the active worksheet
- The Go To dialog box. This list can be displayed from the ribbon using Home > Editing > Find & Select > Go To, or by pressing the F5 function key
Printing a list of names
- To print a list of names to the worksheet, use the ribbon sequence Home >Formulas > Use in Formula > Paste Name then click the Paste List button, or
- Press F3 to display the Paste Name dialog box, then click the Paste List button
- See paste list of defined names for details including global, local, and constants
The Name Manager
The Name Manager shown in figure 5 provides:
- A Filter, with items:
-
- Clear list
- Names Scoped to Worksheet
- Names Scoped to Workbook
- Names with Errors
- Names without Errors
- Defined Names
- Table Names
-
- New, Edit, and Delete items. Note: If you delete a name used by a formula reference, a
#NAME!
error will be returned - A Sort facility. Click on any of the Name, Value, Refer To, Scope, Comment labels to sort on that field
Scope
The workbook available in the figure 8 Excel Web App contains local scope names on the three month worksheets {January, February, March}. A list of the names is shown in figure 9.
-
Fig 8: Excel Web App #1: – global (workbook) and local (worksheet) scope. All GetCF functions have been converted to text
Looking at figure 8, worksheet January, the total for salesperson John in cell C7
has the formula =SUM(John)
. The reference for the SUM function is the John range on the worksheet where the formula appears. In other words, it is local.
On worksheet Summary, the total for salesperson John (January) in cell C3
has the formula =SUM(Janaury!John)
. The reference for this SUM
function is the John range on the worksheet other than the one where the formula appears. In other words, the reference is not local to the calling cell. In this case, the range name must be preceded by the worksheet name January.
All of the references (names) shown in figure 9 have worksheet (local) scope. Compare this list to the scope list in figure 7 which has only 2 local scope names {amp.ax.data, 4.Cells Range} .
A final point, avoid naming a worksheet with the name Workbook
. The reason is obvious!
- This example was developed in Excel 2013 Pro 64 bit.
- Last modified: 1 Aug 2020, 1:34 pm [Australian Eastern Standard Time (AEST)]