Cells and ranges

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 B
  • A1:A4 – four cells in rows 1 to 4 of column A
  • B:B – all the cells in column B
  • 2:2 – all the cells in row 2
  • 2:10 – all the cells in row 2 to row 10
  • A1: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.

xlf-multi-range-select
Fig 1: Selecting multiple ranges – xlfAnimatedPreview – 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

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 named Q1Y16 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)

    xlf-formula-autocomplete-dropdown
    Fig 2: Formula autocomplete drop-down list – with the name selected, press the Tab key to complete entry of the name in the formula

    xlf-paste-names-dialog
    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 than taxrate
  • 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, and Criteria
  • 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

  1. Using the Name Box – figure 4
  2. xlf-name-name-box
    Fig 4: Name a cell or range – 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 – 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 – 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.

  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 – 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!

  • This example was developed in Excel 2013 Pro 64 bit.
  • Last modified: 2 Mar 2016, 6:58 pm [Australian Eastern Standard Time (AEST)]