|Cell Addresses - A Brief Definition||Guidelines for Naming Cells, Formulas, & Constants|
|Relative Cell Addresses||Using Multiple Labels in Formulas|
|Absolute Cell Addresses||Naming A Cell or A Range of Cells|
|Sheet References||Use Existing Labels to Name Cells|
|3-D References||Name Cells on More Than 1 Sheet Using 3-D References|
|References With Names|
|MS Office 97 Contents||Home|
In a spreadsheet, a cell is the intersection of a column and a row, and each cell has a unique address (or reference), such as B3 or D1, that indicates its location. The columns are named with letters of the alphabet -- A, B, ... Z, AA, AB, ... AZ, BA, BB, ... BZ, and so on, through IA, IB, ... IZ -- for a total of 256 columns. Rows are numbered 1 through 65,536!
The address of a cell located in column C, row 24, is C24. The address of the cell in column LM in the 2,321st row is LM2321.
Cell addresses written this way -- M47, Q13, CT109, for instance -- in a formula, are called relative addresses because they refer to the cell relative to the position of the active cell or the cell which contains a formula - the distance and direction the cursor has to move to reach that address from its present position.
For example: you've entered several columns of figures -- A3 through A17, B3 through B17, etc. -- and you want to total them at the bottom of each column. You type the following formula in cell A18 to total the entries in column A: =SUM(A3:A17). If you copy the formula into cells B18, C18, D18, etc., and then look at the formulas, you'll see that the original reference (A3:A17) has changed in every column. The formula in column B is=SUM(B3:B17);
Here's how it works: The column headings A, B, C, ML, IZ are there for the end-user's convenience only, to provide an easy reference for us human types. When you place the cursor in cell B18 and tell Excel to insert the sum of the values in cells B3-B17, it adds up the numbers in the 15 rows above the current cursor position (the cell in which you are placing the formula, called the active cell).
When you copy the formula into columns C, D, E, etc., all Excel cares about is that you still want to total the numbers in the 15 rows above the cell containing the formula. The formula that the program 'sees' looks like this:
which simply means: back up 15 rows (R[-15]) and add up everything between that cell and the cell one row above this formula.
The column reference, C, is not in brackets and doesn't reference a number, so the program knows that you want it to stay in the same column when it does the addition. The square brackets enclose a number that represents a location relative to the present location: a minus sign means up with respect to the present row or left of the present column. A number in square brackets without a minus sign means that the relative location is to the right of or down from the current location.
These are relative addresses: they tell the program how many cells to the left, right, up, or down to look for the values to use, for example, in a calculation.
If you enter a cell address without the square brackets -- R2C5, for example -- you have entered an absolute address: when the program sees an absolute address, it goes directly to the referenced cell. The absolute address is an actual cell in the worksheet, specifically, the cell located at the intersection of row 2 and column 5 (in this example). You can copy a formula containing an absolute address anywhere in your worksheet and -- unlike the relative address -- it will always refer to exactly the same cell (here, R2C5 or, in more familiar language, E2).
If you want to add up the numbers in a row instead of a column (A3+B3+C3, for example), the formula would look like this:=SUM(A3:C3).
Whenever you use relative addresses and copy formulas from one column or row to another column or row, the addresses will change to reflect the new location, but will continue to calculate the same relative range of cells.
Now, isn't that perfectly clear and simple!
Absolute cell references, on the other hand, always refer to a particular cell. Perhaps you're doing a calculation in which you use a constant (a discount or interest rate, for example, or another calculation). You can place the constant in a cell anywhere in the worksheet or workbook, then use the absolute address of that cell (including the work sheet name, when appropriate) in any calculations with the constant. Wherever you copy the formula that refers to the constant, it will still refer to the same cell for the constant portion of the formula.
Absolute cell addresses are written using dollar signs. For example, if we want to write a reference that always refers to row 1, column 1, the absolute address would be $A$1 (or R1C1, without square brackets enclosing the numbers). When you copy formulas using relative addresses, the row and/or column references will change as illustrated above: if you copy the formula =SUM(A3:A17) from A18 into B18, the formula will change to read =SUM(B3:B17).
If you create another calculation to add whatever value is in cell A1 to the sums you just did, your formula might look like this: =A18+$A$1. When you copy the formula to B18, the formula will look like this: =B18+$A$1. In column C, it will be =C18+$A$1.
The relative part of the formula will change, as we saw above. The absolute address will always remain the same. All of the formulas will refer to exactly the same cell, the one in row 1, column 1 of your worksheet.
Excel's multiple sheet workbooks make it convenient and easy to keep related information together in a single file. You can use a separate worksheet for each related category, then use data from more than one sheet to calculate totals or determine trends. To do this, you need to be able to give Excel specific information about where your data is located. In the same worksheet, a cell address is enough.
If your workbook contains 6 or 11 or 20 sheets, you obviously need to specify which sheet contains the data you want to use. This is done by including a sheet name or range in the formula.
To insert the contents of a particular cell in another sheet, if you have not renamed your worksheets, the reference would look like this:
Notice that there are no spaces between the elements and that the sheet designation is followed by an exclamation point (!).
If you have named your worksheets (e.g., Qtr. 1 Totals or
Aircraft Sales 95) and the names include any spaces, you must
enclose the entire sheet name in single quotes if you type it in
Example: January Sales!$A$1
The example is an absolute reference to cell A1 in the sheet named January Sales.
If you enter references by selecting cells directly (described below), Excel will add the single quotes for you.
"A 3-D reference is a range that spans two or more sheets in the workbook."
The syntax for entering a 3-D reference is very much the same as for entering a range of cells: the name of the first sheet in the range, a colon, and the name of the last sheet in the range; an exclamation point (to separate the sheet reference from the cell reference); and the cell or range of cells being referenced.
Example: Your workbook contains sales figures for the year, with each month on a separate sheet. You want to create a summary sheet that shows the quarterly totals for each of several categories: gross sales by region, variable production costs, fixed costs, selling expense, etc.
For a 3-D reference to work, the layout must be consistent from one sheet to the next. If the total for the Northeast Region January gross sales is located in Sheet1, cell B125, then the Northeast Region gross sales totals for February through December must also be located in cell B125 on sheets 2 through 12.
In the 1st Quarter Summary area of your worksheet, you will enter the gross sales for January, February, and March, located in worksheets 1-3. Click on the cell where you want the total to appear, type in an equal sign (=), then either type in the reference or select it.
To type in the reference: Obviously, before you can type in a reference, you must know which sheets and which cell ranges contain the data you want to access. If you have the information, enter the reference as follows (we'll use Excel's SUM function so we don't have to type in all of the individual addresses.):
The same calculation, without using the SUM function, would look like this:
To reference a range of cells in the same sheets:
The same calculation without using the SUM function...well, don't even bother! First you have to add B5 through B123: =B5+B6+B7+... B123 for sheet1, then do the same for sheet2, then for sheet 3... so...
=(Sheet1!B5+B6+ B7+... B123)+ (Sheet2!B5+B6+ B7+... B123)+ (Sheet3!B5+B6+B7+... B123)
but you can't type in little dots; you have to type in all the numbers between B7 and B123. Three times! Use the SUM function!!!
To Select a Reference:
NOTE: Moving, copying, adding, or deleting cells or sheets can affect references. Most of the time when cells are moved or copied, Excel adjusts the references and there is no problem. However, if you paste or copy a cell into a location that contained referenced data, the cells that used the original data in the cell may show an error or might just produce incorrect results.
Results of calculations may also be affected if you add, delete, or change the position of worksheets within a specified range of sheets.
The Excel User's Guide has explanations of possible problems in the section Moving and Copying Formulas and References, pages 138-141. (For my fellow employees, call Corporate Training at extension 4898 to either check out the book or have these pages photocopied for you.)
Please Note: Much of the material in this section was either taken directly or paraphrased from Excel 97 Help.
Excel 97 lets you use cell names, range names, and constant names in formulas instead of referencing cells by column and row addresses. Not only do names make it more obvious what the formula refers to, but when it becomes necessary to change the value in a constant, you won't have to find every formula in your worksheet that uses that constant.
If you enter a numeric value in each occurrence of a formula you'll have to find and change each one manually when, for example, the sales tax rate changes. Perhaps you multiply each total sale by the tax rate, then add the result to the total sale amount. If the sales tax increases from 7.5% to 8%, you'll need to find and replace each occurrence of 7.5% with 8.0%. Even with the Find and Replace function, that could be a tedious job in a large workbook.
And there's a good possibility that you'll miss some occurrences, which will result in errors in your calculations.
If you create a named constant for example, SalesTaxRate then refer to it in your formula, you'll only have to make a single change the next time taxes go up.
When you enter the formula to calculate the tax amount on a particular sale, you'll use the name you created instead of the amount. The calculation might look like this:
The contents of cell T97 will be multiplied by whatever value it finds in the constant SalesTaxRate. When the tax rate changes, you only change the value once in the named constant.
You can also use the column and row labels in a worksheet to refer to 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
The space between the labels is called the intersection operator, and specifies that that value the formula will return is the value it located in the cell at the intersection of the row labeled Support and the column labeled Sales.
If you haven't put labels in your worksheet or if you have information on one worksheet that you want to use on other sheets in the same workbook, you can create a name that describes the cell or range. A descriptive name in a formula makes it easier to understand what the formula does. For example, the formula
might be easier to identify than
In this example, the name FirstQuarterSales represents the range C20:C30 on the worksheet named Sales.
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. Names can also be used to represent formulas or constants.
Note: By default, names use absolute cell references.
To change cell references in formulas to names
To create a name to represent a formula or a constant value
To determine what a name refers to
The Refers to box displays the reference, formula, or constant the name represents.
Tip: You can also create a list of the available names in a workbook. Locate an area with two empty columns on the worksheet (the list will contain two columns one for the name and one for a description of the name). Select a cell that will be the upper-left corner of the list. On the Insert menu, point to Name, and then click Paste. In the Paste Name dialog box, click Paste List.
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.
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.
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. 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).
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.
For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(Sales!C20:C30). In this example, the name FirstQuarterSales represents the range C20:C30 on the worksheet named Sales.
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. 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: By default, names use absolute cell references.
Note: You cannot name a cell while you are changing the contents of the cell.
Note: A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels.