Define named cell references or ranges

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 (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) to represent cells, ranges of cells, formulas, or constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name.

You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.

ShowUse labels as references

This option must be set when you first create a workbook, or you will need to re-enter the formulas that use labels.

  1. Click Options on the Tools menu, and then click the Calculation tab.
  2. Under Workbook options, select the Accept labels in formulas check box.
  3. To re-enter a formula that used a label before setting this option, select the cell that contains the formula, press F2, and then press ENTER.

 Note   To have this option recognized by default for all new workbooks, create a new workbook, follow the procedure above, and save the setting in the template book.xlt.

ShowHow?

  1. Decide which type of template you want:

Workbook template

Create a workbook that contains the sheets, default text (such as page headers and column and row labels), formulas, macros (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.), styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), and other formatting you want in new workbooks based on the template.

Worksheet template

Create a workbook that contains one worksheet. On the worksheet, include the formatting, styles (style: A combination of formatting characteristics, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.), text, and other information you want to appear on all new sheets of the same type.

  1. To display a picture of the first page of a template in the Preview box of the Templates dialog box (General Templates..., New Workbook task pane), click Properties on the File menu, click the Summary tab, and then select the Save preview picture check box.
  2. On the File menu, click Save As.
  3. In the Save as type box, click Template.
  4. In the Save in box, select the folder where you want to store the template.

C:\Program Files\Microsoft Office\Office11\XLStart

  • To create a custom workbook or worksheet template, select the Templates folder, which is usually

C:\Documents and Settings\user_name\Application Data\Microsoft\Templates

  1. Enter the name of the template in the File name box. Do one of the following:

Workbook template

  • Type book to create the default workbook template.

To create a custom workbook template, type any valid file name.

Worksheet template

  • Type sheet to create a template for default worksheets.

To create a custom sheet template, type any valid file name.

  1. Click Save, and then click Close on the File menu.

ShowName a cell or a range of cells

ShowConvert existing row and column labels to names

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

Specifying labels     When you select cells in labeled ranges to create formulas, Microsoft Excel can insert the labels in place of the cell references in your formulas. Using labels can make it easier to see how a formula is constructed. You can use the Label Ranges dialog box (Insert menu, Name submenu, Label command) to specify the ranges that contain column and row labels on your worksheet.

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

This is also called a 3-D reference (3-D reference: A reference to a range that spans two or more worksheets in a workbook.).

  1. On the Insert menu, point to Name, and then click Define.
  2. In the Names in workbook box, type the name.
  3. If the Refers to box contains a reference, select the equal sign (=) and the reference and press BACKSPACE.
  4. In the Refers to box, type = (equal sign).
  5. Click the tab for the first worksheet to be referenced.
  6. Hold down SHIFT and click the tab for the last worksheet to be referenced.
  7. Select the cell or range of cells to be referenced.
 
 
Applies to:
Excel 2003