Excel Sheet & Cell References

Cell Addresses

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,321^st row is LM2321.

Relative Cell Addresses

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);

column C's formula is =SUM(C3:C17), etc. When you copied the formula into the new columns, it continued to refer to the same rows as the original formula (rows 3-17), but changed the column reference to reflect the column into which you copied the formula.

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:

=SUM(R[-15]C:R[-1]C)

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

If D3 is the active cell, the program knows that you mean it should back up 3 cells and then add the contents of that cell and the next 2 cells to the right and print the total in D3. Here's the formula the way the program sees it:

=SUM(RC[-3]:RC[-1])

which means to back up 3 columns in the same row [-3] and add up the numbers in that cell, the cell 2 places back, and the cell just before the one with the formula.

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 References

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.

Sheet References

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:

=Sheet7!T4

Notice that there are no spaces between the elements and that the sheet designation is followed by an exclamation point (!).

Renamed Sheets

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 yourself:

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.

3-D References

"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.):

=SUM(Sheet1:Sheet3!B125)

The same calculation, without using the SUM function, would look like this:

=Sheet1!B125+Sheet2!B125+Sheet3!B125

To reference a range of cells in the same sheets:

=SUM(Sheet1:Sheet3!B5:B123)

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:

1. In the location where you want the referenced data to appear, type in an equal sign (=).
    
2. Click on the tab of the first sheet you want to include in your calculation. Hold down the [Shift] key and click on the tab of the last sheet to be included, then use the mouse to select the cell or range of cells you want to use.

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

Referencing With Names

Please Note: Much of the material in this section was either taken directly or paraphrased from Excel 97 Help.

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:

=T97*SalesTaxRate

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

=Support Sales.

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

=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 constants.

Note: By default, names use absolute cell references.

Guidelines For Naming Cells, Formulas, & Constants

• The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
   
• Names cannot be the same as a cell reference, such as $Z$100 or R1C1.
   
• Spaces are not allowed. Underscore characters and periods may be used as word separators – for example, First.Quarter or Sales_Tax.
   
• A name can contain up to 255 characters.
   
• Names can contain uppercase and lowercase letters. Excel 97 does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
   

To change cell references in formulas to names

1. Select the range that contains formulas in which you want to replace references with names.
• To change the references to names in all formulas on the worksheet, select a single cell.
   
2. On the Insert menu, point to Name, and then click Apply.
    
3. In the Apply names box, click one or more names.

To create a name to represent a formula or a constant value

1. On the Insert menu, point to Name, and then click on Define.
    
2. In the Names in workbook box, enter the name for the formula.
    
3. In the Refers to box, type = (an equal sign), followed by the formula or the constant value.

To determine what a name refers to

1. On the Insert menu, point to Name, and then click on Define.
    
2. In the Names in workbook list, click the name whose reference you want to check.
   

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.

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

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.

Name a Cell or 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.

Use Existing Row & Column Labels to Name Cells

1. Select the range you want to name, including the row or column labels.
    
2. On the Insert menu, point to Name, and then click on Create.
    
3. In the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box.

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.

Name cells on more than one worksheet by using a 3-D reference

1. On the Insert menu, point to Name, and then click on Define.
    
2. In the Names in workbook box, type the name.

   If the Refers to box contains a reference, select the equal sign (=) and the reference and press [Backspace].
    

3. In the Refers to box, type = (an equal sign).
    
4. Click the tab for the first worksheet to be referenced.
    
5. Hold down [Shift] and click the tab for the last worksheet to be referenced.
    
6. Select the cell or range of cells to be referenced.

MS Office Contents

Home