Cells and ranges

0. 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.

0.1 Examples of range addresses

1. Selecting ranges

Methods include

1.1 Selecting non contiguous ranges

Methods include

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.

Fig 1: Selecting multiple ranges - xlfAnimatedPreview Win7 - using 1.the Name Box, and 2, the menu Go To dialog box. Note: the Reference key sequence is Alt H FD G ie. press the Alt key

2. 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

2.1 Benefits of using defined names:

2.2 Range name specifications:

2.3 Ranges can be named three (four) ways

  1. Using the Name Box - figure 4

  2. xlf-name-name-box
    Fig 4: Name a cell or range - Win7 - using the Name Box
    • To apply the name SalesV
    • Select the range F64:F69
    • Click in the Name Box and type the name SalesV
    • Press Enter

  3. Formulas > Defined Names > Define Name - figure 5

  4. xlf-name-defined-name-new
    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

  5. Formulas > Defined Names > Create from Selection - figure 6

  6. xlf-name-create-from-selection
    Fig 6: Name a cell or range - Win7 - 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 the Left 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 the Risk_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_

  7. Formulas > Defined Names > Name Manager > New - figure 7

  8. xlf-name-name-manager
    Fig 7: Name a cell or range - Win7 - 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

3. Managing range names

3.1 Finding a list of names

3.2 Printing a list of names

3.3 The Name Manager

The Name Manager shown in figure 5 provides:

3.4 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.

  1. 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.

  1. xlf-name-scope
    Fig 9: Name Manager - Win7 - with local (worksheet) scope names, sorted on Refers To field

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!

3.5 Naming constants and formulas

A constant is a set value that does not change. It can be a number enter directly in is a cell. A number returned by a cell formula is not a constant.

Although cells can be protected to prevent changes, you can also assign a numeric value, string (text), or formula to a Defined Name.

  1. Figure 10 examples:
    1. Named numeric constant. Insert a line break in a comment with ASCII 10. Use Alt + 010 on the numeric keypad
    2. Named text constant
    3. Named formula with named reference
    4. Named numeric array constant
  2. xlf-defined-varous
    Fig 10 A to D: Named constants - A. Numeric constant, B. Text constant, C. Formula constant, D. Numeric array constant
  3. xlf-defined-names-manager-view
    Fig 11: Named constants - Name Manager view