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 A10:A20 B15:E15

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 5:5 5:10 H:H H:J

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 => Options… dialog box and select the General tab:

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, Sheet1:Sheet3!B5:D10 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(SalesC20:C30). In this example, the name FirstQuarterSales represents the range C20:C30 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 A20:A30 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 A20:A30 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.