Define and use names in formulas

By using names (name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value.), you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.

What do you want to do?

Learn more about using names

Learn about syntax rules for names

Define a name for a cell or cell range on a worksheet

Define a name by using a selection of cells in the worksheet

Define a name by using the New Name dialog box

Manage names by using the Name Manager dialog box

Change a name

Delete one or more names

Learn more about using names

A name is a meaningful shorthand that makes it easier to understand the purpose of a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), constant (constant: A value that is not calculated. 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.), formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or table (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).), each of which may be difficult to comprehend at first glance. The following information shows common examples of names and how they can improve clarity and understanding.

Types of names

There are several types of names that you can create and use.

Defined name  A name that represents a cell, range of cells, formula, or constant value. You can create your own defined name, and Microsoft Office Excel sometimes creates a defined name for you, such as when you set a print area.

Table name  A name for an Excel table, which is a collection of data about a particular subject that is stored in records (rows) and fields (columns). Excel creates a default Excel table name of Table1, Table2, and so on, each time that you insert an Excel table, but you can change a table's name to make it more meaningful. For more information about Excel tables, see Using structured references with Excel tables.

The scope of a name

All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognized without qualification. For example:

• If you have defined a name, such as Budget_FY08, and its scope is Sheet1, that name, if not qualified, is recognized only in Sheet1, but not in other sheets without qualification.

To use a local worksheet name in another worksheet, you can qualify it by preceding it with the worksheet name, as the following example shows:

Sheet1!Budget_FY08

• If you have defined a name, such as Sales_Dept_Goals, and its scope is the workbook, that name is recognized for all worksheets in that workbook, but not for any other workbook.

A name must always be unique within its scope. Excel prevents you from defining a name that is not unique within its scope. However you can use the same name in different scopes. For example, you can define a name, such as GrossProfit that is scoped to Sheet1, Sheet2, and Sheet3 in the same workbook. Although each name is the same, each name is unique within its scope. You might do this to ensure that a formula that uses the name, GrossProfit, is always referencing the same cells at the local worksheet level.

You can even define the same name, GrossProfit, for the global workbook level, but again the scope is unique. In this case, however, there can be a name conflict. To resolve this conflict, by default Excel uses the name that is defined for the worksheet because the local worksheet level takes precedence over the global workbook level. If you want to override the precedence and you want to use the workbook name, you can disambiguate the name by prefixing the workbook name as the following example shows:

WorkbookFile!GrossProfit

You can override the local worksheet level for all worksheets in the workbook, with the exception of the first worksheet, which always uses the local name if there is a name conflict and cannot be overridden.

Defining and entering names

You define a name by using the:

• Name box on the formula bar   This is best used for creating a workbook level name for a selected range.
• Create a name from selection   You can conveniently create names from existing row and column labels by using a selection of cells in the worksheet.
• New Name dialog box  This is best used for when you want more flexibility in creating names, such as specifying a local worksheet level scope or creating a name comment.

 Note    By default, names use absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

You can enter a name by:

• Typing   Typing the name, for example, as an argument to a formula.
• Using Formula AutoComplete  Use the Formula AutoComplete drop-down list, where valid names are automatically listed for you.
• Selecting from the Use in Formula command  Select a defined name from a list available from the Use in Formula command in the Defined Names group on the Formulas tab.

Auditing names

You can also create a list of defined 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 Formulas tab, in the Defined Names group, click Use in Formula, click Paste and then, in the Paste Names dialog box, click Paste List.

 Top of Page

Learn about syntax rules for names

The following is a list of syntax rules that you need to be aware of when you create and edit names.

• Valid characters  The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

 Note    You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

• Cell references disallowed  Names cannot be the same as a cell reference, such as Z$100 or R1C1.
• Spaces are not valid   Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators, such as, Sales_Tax or First.Quarter.
• Name length  A name can contain up to 255 characters.
• Case sensitivity  Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names. For example, if you created the name Sales and then create another name called SALES in the same workbook, Excel prompts you to choose a unique name.

 Top of Page

Define a name for a cell or cell range on a worksheet

1. Select the cell, range of cells, or nonadjacent selections (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) that you want to name.
2. Click the Name box at the left end of the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.).

Name box

3. Type the name that you want to use to refer to your selection. Names can be up to 255 characters in length.
4. Press ENTER.

 Note    You cannot name a cell while you are changing the contents of the cell.

 Top of Page

Define a name by using a selection of cells in the worksheet

You can convert existing row and column labels to names.

1. Select the range that you want to name, including the row or column labels.
2. On the Formulas tab, in the Defined Names group, click Create from Selection.

3. In the Create Names from Selection dialog 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.

 Top of Page

Define a name by using the New Name dialog box

1. On the Formulas tab, in the Defined Names group, click Define Name.

2. In the New Name dialog box, in the Name box, type the name that you want to use for your reference.

 Note    Names can be up to 255 characters in length.

3. To specify the scope of the name, in the Scope drop-down list box, select Workbook or the name of a worksheet in the workbook.
4. Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

 Note    If you save the workbook to Microsoft Office SharePoint Server 2007 Excel Services, and you specify one or more parameters, the comment is used as a ScreenTip in the Parameters Task Pane.

5. In the Refers to box, do one of the following:
   • To enter a cell reference, type the cell reference.

Tip  The current selection is entered by default. To enter other cell references as an argument, click Collapse Dialog (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog .

• To enter a constant, type = (equal sign) and then type the constant value.
• To enter a formula, type = and then type the formula.
6. To finish and return to the worksheet, click OK.

Tip  To make the New Name dialog box wider or longer, click and drag the grip handle at the bottom.

 Top of Page

Manage names by using the Name Manager dialog box

Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.

To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

View names

The Name Manager dialog box displays the following information about each name in a list box:

 Notes 

• You cannot use the Name Manager dialog box while you are changing the contents of the cell.
• The Name Manager dialog box does not display names defined in Visual Basic for Applications (VBA), or hidden names (the Visible property of the name is set to "False").

Resize columns

• To automatically size the column to fit the largest value in that column, double-click the right side of the column header.

Sort names

• To sort the list of names in ascending or descending order, alternately click the column header.

Filter names

Use the commands in the Filter drop-down list to quickly display a subset of names. Selecting each command toggles the filter operation on or off, which makes it easy to combine or remove different filter operations to get the results that you want.

To filter the list of names, do one or more of the following:

 Top of Page

Change a name

If you change a defined name or table name, all uses of that name in the workbook are also changed.

1. On the Formulas tab, in the Defined Names group, click Name Manager.

2. In the Name Manager dialog box, click the name that you want to change, and then click Edit.

Tip  You can also double-click the name.

3. In the Edit Name dialog box, in the Name box, type the new name for the reference.
4. In the Refers to box, change the reference , and then click OK.
5. In the Name Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name.
   • To cancel unwanted or accidental changes, click Cancel , or press ESC.
   • To save changes, click Commit , or press ENTER.

 Note    The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

 Top of Page

Delete one or more names

1. On the Formulas tab, in the Defined Names group, click Name Manager.

2. In the Name Manager dialog box, click the name that you want to change.
3. Select one or more names by doing one of the following:
   • To select a name, click it.
   • To select more than one name in a contiguous group, click and drag the names, or press SHIFT and click the mouse button for each name in the group.
   • To select more than one name in a noncontiguous group, press CTRL and click the mouse button for each name in the group.
4. Click Delete. You can also press DELETE.
5. Click OK to confirm the deletion.

 Note    The Close button only closes the Name Manager dialog box. It is not required to commit changes that have already been made.

 Top of Page

See Also

• Create a 3-D reference to the same cell range on multiple worksheets
• Create an external reference (link) to a cell range in another workbook
• Create or change a cell reference
• Create or delete a formula
• Overview of formulas
• Using structured references with Excel tables

Excel > Formula and name basics > Working with names

Using structured references with Excel tables

Structured references make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. They are especially useful because table data ranges often change, and the cell references for structured references adjust automatically. This minimizes the need to rewrite formulas as rows and columns are added and deleted in a table, or when external data is refreshed.

In this article

The Department Sales table example

Components of a structured reference

Table names and column specifiers

Reference operators

Special item specifiers

Qualifying structured references in calculated columns

Examples of using structured references

Working with structured references

Structured reference syntax rules

The Department Sales table example

The following is an example, referenced throughout this article, of a table that is based on sales in a department of six employees with the latest sales amounts and commissions.

A Department Sales table

The entire table (A1:E8)

The table data (A2:E7)

A column and column header (D1:D8)

A calculated column (E1:E8)

The Totals row (A8:E8)

 Top of Page

Components of a structured reference

To work with tables and structured references effectively, you need to understand how to create the syntax of structured references when you are creating formulas. The components of a structured reference are illustrated in the following example of a formula that adds up total sales amounts and commission amounts:

A table name is a meaningful name that you provide to reference the actual table data (excluding the headers row and totals row, if any).

A column specifier is derived from the column header, enclosed in brackets, and references the column data (excluding the column header and total, if any).

A special item specifier is a way to refer to specific portions of the table, such as the Totals row.

The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name.

A structured reference is the entire string beginning with the table name and ending with the table specifier.

 Top of Page

Table names and column specifiers

Each time that you insert a table, Microsoft Office Excel creates a default table name (Table1, Table2, and so on) at the global workbook level or scope. You can easily change the name to make it more meaningful to you. For example, to change Table1 to DeptSales, you can use the Edit Name dialog box. (On the Design tab, in the Properties group, edit the table name in the Table Name box.)

A table name refers to the entire range of data in the table with the exception of the header and total rows. In the Department Sales table example, the table name, DeptSales, refers to the cell range A2:E7.

Similar to table names, column specifiers represent references to the entire column of data with the exception of the column header and total. In the Department Sales table example, the column specifier, [Region], refers to the cell range B2:B7, and the column specifier, [ComPct], refers to the cell range D2:D7.

 Top of Page

Reference operators

For added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.

 Top of Page

Special item specifiers

For added convenience, you can also use special items to refer to various portions of a table, such as just the Totals row, to make it easier to refer to these portions in formulas. The following are the special item specifiers that you can use in a structured reference:

 Top of Page

Qualifying structured references in calculated columns

When you create a calculated column, you commonly use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column called, ComAmt, that calculates the amount of commission in dollars, you can use the following formulas:

The general rule to follow is this: If you are using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.

 Top of Page

Examples of using structured references

There are a number of ways that you can use these special items and combine them with table names and column references as the following information shows:

 Top of Page

Working with structured references

Consider the following when you work with structured references.

Using Formula AutoComplete  

You may find that using Formula AutoComplete is very useful when you enter structured references and to ensure the use of correct syntax. For more information, see Use Formula AutoComplete.

Deciding whether to generate structured references for tables in semi-selection  

By default, when you create a formula, clicking a cell range within a table semi-selects the cells and automatically enters a structured reference, instead of the cell range in the formula. This semi-selection behavior makes it much easier to enter a structured reference. You can turn this behavior on or off by selecting or clearing the Use table names in formulas check box in the Working with formulas section of the Formulas category in the Excel Options dialog box.

Converting a range to a table and a table to a range  

When you convert a table to a range, all cell references change to their equivalent A1 style references. When you convert a range to a table, Excel does not automatically change any cell references to this range to their equivalent table names and column references.

Turning off column headers  

If you turn off table column headers (On the table Design tab, in the Table Style Options group, clear Header Row), structured references that use these headers are not affected, and you can still use them in formulas.

Adding or deleting columns and rows to the table  

Because table data ranges often change, the cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all of the cells of data in the Department Sales table, such as =COUNTA(DeptSales) in The Department Sales table example, the returned value is 30 because the data range is A2:E7. If you then added a row of data, the cell reference automatically adjusts to A2:E8, and the new returned value is 35.

Renaming a table or column  

If you rename a column or table, Excel automatically changes the use of that table and column header in all structured references that are used in the workbook.

Moving, copying, and filling structured references  

All structured references remain the same when you copy or move a formula that uses a structured reference.

When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.

 Top of Page

Structured reference syntax rules

The following is a list of syntax rules that you need to be aware of when you create and edit structured references.

 Note    Table names follow the same rules as defined names. For more information, see Use names to clarify formulas.

The use of brackets in specifiers  

All table, column, and special item specifiers must be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers.

Example   =DeptSales[[SalesPers]:[Region]]

Column headers are text strings  

All column headers are text strings, but do not require quotes when they are used in a structured reference. If a column header contains numbers or dates, such as 2004 or 1/1/2004, these are still considered text strings. Because column headers are text strings, you cannot use expressions within brackets.

Example   =DeptSalesFYSummary[[2004]:[2002]]

Special characters in table column headers  

If a table column header contains one of the following special characters, the entire column header must be enclosed in brackets. Effectively, this means double brackets are required in a column specifier with the following special characters: Space, tab, line feed, carriage return, comma (,), colon (:), period (.), left bracket ([) , right bracket (]), pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), karet (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/).

Example   =DeptSalesFYSummary[[Total$Amount]]

The only exception to this is if the only special character that is used is a space character.

Example  =DeptSales[Total Amount]

Special characters in column headers that require the use of the escape character  

The following characters have special meaning and require the use of a single quotation mark (') as an escape character: Left bracket ([), right bracket (]), pound sign(#), and single quotation mark (').

Example   =DeptSalesFYSummary['#OfItems]

Using the space character to improve readability in a structured reference  

You can use space characters to improve the readability of a structured reference in the following way:

• One space after the first left bracket ([) and preceding the last right bracket (]).
• One space after a comma.

Example   =DeptSales[ [SalesPers]:[Region] ]

Example   =DeptSales[[#Headers], [#Data], [ComPct]]

 Top of Page

Excel > Formula and name basics > Working with names

Create or change a cell reference

Excel 2007

A cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) refers to a cell or a range of cells on a worksheet and can be used in a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) so that Microsoft Office Excel can find the values or data that you want that formula to calculate.

In one or several formulas, you can use a cell reference to refer to:

• Data from one cell on the worksheet.
• Data that is contained in different areas of a worksheet.
• Data in cells on other worksheets in the same workbook.

For example:

 

What do you want to do?

Create a cell reference on the same worksheet

Create a cell reference to another worksheet

Create a cell reference by using the Link Cells command

Change a cell reference to another cell reference

Change a cell reference to a named range

Create a cell reference on the same worksheet

1. Click the cell in which you want to enter the formula.
2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , type = (equal sign).
3. Do one of the following:
   • Cell reference  To create a reference, select a cell or range of cells on the same worksheet.

Cell references and the borders around the corresponding cells are color-coded to make it easier to work with them.

The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.

The second cell reference is C3, the color is green, and the cell range has a green border with square corners.

You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.

• Defined name  To create a reference to a defined name, do one of the following:
  • Type the name.
  • Press F3, select the name in the Paste name box, and then click OK.

 Note    If there is no square corner on a color-coded border, the reference is to a named (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.) range.

4. Do one of the following:
   • If you are creating a reference in a single cell, press ENTER.
   • If you are creating a reference in an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), press CTRL+SHIFT+ENTER.

The reference can be a single cell or a range of cells, and the array formula can be one that calculates single or multiple results.

 Top of Page

Create a cell reference to another worksheet

You can refer to cells that are on other worksheets by appending the name of the worksheet followed by an exclamation point (!) to the start of the cell reference. In the following example, the worksheet function named AVERAGE calculates the average value for the range B1:B10 on the worksheet named Marketing in the same workbook.

Reference to a range of cells on another worksheet in the same workbook

Refers to the worksheet named Marketing

Refers to the range of cells between B1 and B10, inclusively

Separates the worksheet reference from the cell range reference

1. Click the cell in which you want to enter the formula.
2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , type = (equal sign).
3. Click the tab for the worksheet to be referenced.
4. Select the cell or range of cells to be referenced.

 Note    If the name of the other worksheet contains nonalphabetical characters, you must enclose the name (or the path) within single quotation marks (').

 Top of Page

Create a cell reference by using the Link Cells command

Alternatively, you can copy and paste a cell reference and then use the Link Cells command to create a cell reference. You may want to use this command to:

• Easily display important information in a more prominent position. Let's say that you have a workbook with a number of worksheets, and on each worksheet is a cell that displays summary information about the other cells on that worksheet. To make these summary cells more prominent, you can create a cell reference to them on the first worksheet of the workbook, which enables you to see summary information about the whole workbook on the first worksheet.
• Make it easier to create cell references between worksheets and workbooks. The Link Cells command automatically pastes the correct syntax for you.

1. Click the cell that contains the data you want to link to.
2. On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut  You can also press CTRL+C.

3. Click the cell that you want to link from.
4. On the Home tab, in the Clipboard group, click Paste .

By default, the Paste Options button appears when you paste copied data.

5. Click the Paste Options button, and then click Link Cells.

 Top of Page

Change a cell reference to another cell reference

1. Double-click the cell that contains the formula that you want to change. Excel highlights each cell or range of cells with a different color.
2. Do one of the following:
   • To move a cell or range reference to a different cell or range, drag the color-coded border of the cell or range to the new cell or range.
   • To include more or fewer cells in a reference, drag a corner of the border.
   • In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , select the reference in the formula, and then type a new reference.
   • Press F3, select the name in the Paste name box, and then click OK.
3. Press ENTER, or, for an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), press CTRL+SHIFT+ENTER.

 Top of Page

Change a cell reference to a named range

Frequently, if you have defined a name to a cell reference after you have entered a cell reference in a formula, you may want to update the existing cell references to the defined names.

1. Do one of the following:
   • Select the range of cells that contains formulas in which you want to replace cell references with defined names.
   • Select a single, empty cell to change the references to names in all formulas on the worksheet.
2. On the Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.

3. In the Apply names box, click one or more names, and then click OK.

 Top of Page

See Also

• Create a 3-D reference to the same cell range on multiple worksheets
• Create an external reference (link) to a cell range in another workbook
• Create or delete a formula
• Define and use names in formulas
• Guidelines and examples of array formulas
• Switch between relative, absolute, and mixed references
• Using structured references with Excel tables

Excel > Formula and name basics > Working with names

Create a 3-D reference to the same cell range on multiple worksheets

Excel 2007

A reference that refers to the same cell or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) on multiple sheets is called a 3-D reference. A 3-D reference is useful and convenient way to reference several worksheets that follow the same pattern and cells on each worksheet contain the same type of data, such as when you consolidate budget data from different departments in your organization.

What do you want to do?

Learn about a 3-D refererence

Learn how 3-D references change when you move, copy, insert, or delete worksheets

Create a 3-D reference

Create a name for a 3-D reference

Learn about a 3-D refererence

You can use a 3-D reference to add up budget allocations between three departments, Sales, HR, and Marketing, each on a different worksheet, by using the following 3-D reference:

=SUM(Sales:Marketing!B3)

You can even add another worksheet, and then move it into the range that your formula refers to. For example, to add a reference to cell B3 in the Facilities worksheet, move the Facilities worksheet between the Sales and HR worksheets as shown in the following example.

Because your formula contains a 3-D reference to a range of worksheet names, Sales:Marketing!B3, all worksheets in the range are included in the new calculation.

 Top of Page

Learn how 3-D references change when you move, copy, insert, or delete worksheets

The following examples explain what happens when you insert, copy, delete, or move worksheets that are included in a 3-D reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on worksheets 2 through 6.

Insert or copy   If you insert or copy worksheets between Sheet2 and Sheet6 (the endpoints in this example), then Excel includes all values in cells A2 through A5 from the added worksheets in the calculations.

Delete   If you delete worksheets between Sheet2 and Sheet6, then Excel removes their values from the calculation.

Move   If you move worksheets from between Sheet2 and Sheet6 to a location outside of the referenced worksheet range, then Excel removes their values from the calculation.

Move an endpoint   If you move Sheet2 or Sheet6 to another location in the same workbook, then Excel adjusts the calculation to include the new worksheets between them unless you reverse the order of the endpoints in the workbook. If you reverse the end points, the 3-D reference changes the endpoint worksheet. For example, say that you have a reference to Sheet2:Sheet6: If you move Sheet2 after Sheet6 in the workbook, then the formula will point to Sheet3:Sheet6. If you move Sheet6 in front of Sheet2, the formula will adjust to point to Sheet2:Sheet5.

Delete an endpoint   If you delete Sheet2 or Sheet6, then Excel removes the values on that worksheet from the calculation.

 Top of Page

Create a 3-D reference

1. Click the cell where you want to enter the function.
2. Type = (equal sign), enter the name of the function, and then type an opening parenthesis.

You can use the following functions in a 3-D reference:

3. Click the tab for the first worksheet that you want to reference.
4. Hold down SHIFT and click the tab for the last worksheet that you want to reference.
5. Select the cell or range of cells that you want to reference.
6. Complete the formula, and then press ENTER.

 Top of Page

Create a name for a 3-D reference

1. On the Formulas tab, in the Defined Names group, click Define Name.
2. In the New Name dialog box, in the Name box, type the name you want to use for your reference. Names can be up to 255 characters in length.
3. In the Refers to box, select the equal sign (=) and the reference, then press BACKSPACE.
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.

 Top of Page

Excel > Formula and name basics > Working with names

Switch between relative, absolute, and mixed references

Excel 2007

1. Select the cell that contains the formula.
2. In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , select the reference that you want to change.
3. Press F4 to switch between the reference types.

The following table summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.