More on Cell and Range References

1
More on Cell and Range References
2

• A reference identifies a cell or a range of cells
  on a worksheet and tells Microsoft Excel where to
  look for the values or data you want to use in a
  formula.
• With references, you can use data contained in
  different parts of a worksheet in one formula or
  use the value from one cell in several formulas.

3

• You can also refer to cells on other sheets in
  the same workbook, to other workbooks, and to
  data in other programs.

4

• References to cells in other workbooks are called
  external references.
• References to data in other programs are called
  remote references.

5

• By default, Microsoft Excel uses the A1 reference
  style, which labels columns with letters (A
  through IV, for a total of 256 columns) and
  labels rows with numbers (1 through 65536).

6

• To refer to a range of cells, enter the reference
  for the cell in the upper-left corner of the
  range, a colon (), and then the reference to the
  cell in the lower-right corner of the range.
• The following are examples of references.

7

• To refer to
• The cell in column A and row 10
• The range of cells in column A and rows 10
  through 20
• The range of cells in row 15 and columns B
  through E

• Use
• A10
• A10A20
• B15E15

8

• All cells in row 5
• All cells in rows 5 through 10
• All cells in column H
• All cells in columns H through J

• 55
• 510
• HH
• HJ

9

• You can also use a reference style where both the
  rows and the columns on the worksheet are
  numbered--R1C1 style.
• In R1C1 style, Microsoft Excel indicates the
  location of a cell with an "R" followed by a row
  number and a "C" followed by a column number.

10

• R1C1 style is useful for computing row and column
  positions in macros and can be useful for showing
  relative cell references.
• To switch into or out of the R1C1 reference
  style, use the Tools gt Options dialog box and
  select the General tab

11
(No Transcript)
12

• If you switch to the R1C1 style, your worksheet
  will look like this

13
Cell D50 now referenced as R50C4
14

• You can use the labels of columns and rows on a
  worksheet to refer to the cells within those
  columns and rows,
• or you can create descriptive names to represent
• cells,
• ranges of cells,
• formulas, or
• constant values.

15

• If you want to analyze data in the same cell or
  range of cells on multiple worksheets within the
  workbook, use a 3-D reference.
• A 3-D reference includes the cell or range
  reference, preceded by a range of worksheet
  names. The symbol ! is used to separate the
  worksheet name from the cell names.
• Microsoft Excel uses any worksheets stored
  between the starting and ending names of the
  reference.

16

• For example,
• Sheet1Sheet3!B5D10
• is a 3-D reference to all the cells in Sheet1,
• Sheet2, and Sheet3 that lie between cells B5 and
  D10 on these worksheets. There are 54 such cells
  in this 3-D range. (We assume that the workbook
  contains Sheet1, Sheet2, and Sheet3, in that
  order.)

17
Labels and names in formulas

• Worksheets usually have labels at the top of each
  column and to the left of each row that describe
  the data within the worksheet.
• You can use these labels within formulas when you
  want to refer to the related data.
• You can also create descriptive names that are
  not labels on the worksheet to represent cells,
  ranges of cells, formulas, or constants.

18

• When you create a formula that refers to data in
  a worksheet, you can use the column and row
  labels in the worksheet to refer to the data.
• For example, if a table contains sales amounts in
  a column labeled Sales and a row for a division
  labeled Support, you can find the sales amount
  for the Support division by entering the formula
• Support Sales.

19

• The space between the labels is the intersection
  operator, which designates that the formula
  should return the value in the cell at the
  intersection of the row labeled Support and the
  column labeled Sales.

20
The formula
The Result (unformatted)
21
Multiple labels in formulas

• When you have labels for the columns and rows on
  your worksheet, you can use those labels to
  create formulas that refer to data on the
  worksheet.
• If your worksheet contains stacked column
  labels in which a label in one cell is followed
  by one or more labels below it, you can use the
  stacked labels in formulas to refer to data on
  the worksheet.

22

• For example, if the label Projected is in cell E5
  and the label 1996 is in cell E6, the formula
  SUM(Projected 1996) returns the total value for
  the Projected 1996 column.
• If row 8 contains sales amounts and the label
  Sales is in cell D8, you can refer to the
  projected sales amount for 1996 with the formula
  Projected 1996 Sales.

23

• When you refer to information by using stacked
  labels, you refer to the information in the order
  in which the labels appear, from top to bottom.

24

• If the label 1996 is in cell E5 and the label
  Actual is in cell E6, you can refer to the actual
  figures for 1996 by using 1996 Actual in a
  formula.
• For example, to calculate the average of the
  actual figures for 1996, use the formula
  AVERAGE(1996 Actual).

25

• If your data does not have labels or if you have
  information stored on one worksheet that you want
  to use on other sheets within the same workbook,
  you can create a name that describes the cell or
  range.
• A descriptive name in a formula can make it
  easier to understand the purpose of the formula.

26
Naming cells in a workbook

• In Microsoft Excel, you can name a single cell or
  a range of cells to make formulas easier to read
  and remember.
• If you have row and column labels on a worksheet,
  you can refer to them directly in a formula, or
  you can use them as names that represent the
  associated cells.

27
To name a cell or a range of cells

• 1 Select the cell, range of cells, or nonadjacent
  selections that you want to name.
• 2 Click the Name box at the left end of the
  formula bar.
• 3 Type the name for the cells.
• 4 Press ENTER.
• Note You cannot name a cell while you are
  changing the contents of the cell.

28

• You can also create a name that represents the
  same cell or range of cells on more than one
  worksheet.

29

• For example, the formula SUM(FirstQuarterSales)
  might be easier to identify than
  SUM(SalesC20C30). In this example, the name
  FirstQuarterSales represents the range C20C30 on
  the worksheet named Sales.

30

• Names are available to any sheet within the
  workbook.
• For example, if the name ProjectedSales refers to
  the range A20A30 on the first worksheet in the
  workbook, you can use the name ProjectedSales on
  any other sheet in the same workbook to refer to
  range A20A30 on the first worksheet.

31

• Names can also be used to represent formulas or
  values that do not change (constants). For
  example, you can use the name SalesTax to
  represent the sales tax amount (such as 6.2
  percent) applied to sales transactions.
• Note that, by default, names use absolute cell
  references.