NOTE: The intersection feature is not available by default in Microsoft Excel 2000. If you want to make the intersection feature available, click Accept labels in formulas on the Calculation tab of the Options dialog box.
Named Ranges and Natural Language Formulas Compared
To refer to an intersection in versions Excel earlier than Excel 97, you must know the ranges of cells in advance, or you must define range names for the cells before you refer to the range. If the ranges change, you must also update the formulas and defined names.With natural language formulas, you no longer need to create defined names or determine the cell ranges in advance. Excel 2000 determines the range based on the row and column labels that you provide in the table on the worksheet. For example, in the following table, the formula returns the value 100 for "Product A" in the first quarter:
= Product A First Quarter A1: B1: First Quarter C1: Second Quarter A2: Product A B2: 100 C2: 50 A3: Product B B3: 110 C3: 60 A4: Product C B4: 120 C4: 70
Rules for Labels
Natural language formulas analyze the row and column headings of all tables in the current worksheet to determine the validity of the labels. In most instances, you can use any string as a label, but there are some restrictions. These restrictions include the following:- You can use any letter of the alphabet, a backslash, or an underscore as the starting character.
- You cannot use labels that consist solely of the international decimal point, the plus sign, the minus sign, or the letter "e."
- You cannot use labels that appear to be cell references; for example,
you cannot use "FY97."
You can use any string of characters if the string is enclosed in apostrophes (single quotation marks). For example, if you want to use the following string=FY97type:='FY97' - Excel ignores leading and trailing spaces in labels.
However, you can use spaces between characters in labels; for example, you can use "Tax Rate." - You can use a number as a label as long as the number is between 1,900 and 9,999.
- You can use a date as a label as long as it uses a built-in date format that contains a day; for example, you can use "12/3/00" or "Dec-3-00".
- You cannot use a label that appears to be a function; for example, you cannot use "Sum()" or "Average()."
- You cannot use a natural language formula in an array formula.
- If Excel finds both a label and a defined name, the defined
name takes precedence over the label.
To force Excel to use the reference as a label, enclose the reference in apostrophes (single quotation marks).
For example, in the formula "=Sales Software", if a defined name exists for Sales, the formula references the defined name. However, if you type the following formula='Sales' Softwarethe range referenced by the row or column label is always used. - If multiple tables on the same worksheet contain labels that are exactly the same,
the table that Excel uses is determined by the location of the cell
that contains the formula. In general, the table that is referenced is
to the left or above the cell that contains the formula.
For example, if a worksheet contains a table in cells A1:E10 and a table in cells A15:E25, if you type a formula in cell A30, Excel uses the labels in the table in cells A15:E25. However, if you type the same formula in cell A12, Excel uses the first table in cells A1:E10. If Excel cannot determine to which table you are referring, it displays the "Identify Label" dialog box. This dialog box prompts you to select the cell that contains the label you want to use.
Stacked Column Labels
Tables may contain more than one column or row label heading. You can create natural language formulas that refer to multiple headings. To do this, type a space between each of the labels in the formula. The following sample table and formula use a stacked column label in a formula.A1: B1: 1995 C1: D1: 1996 E1: A2: B2: North C2: South D2: North E2: South A3: Jan B3: 100 C3: 50 D3: 200 E3: 70 A4: Feb B4: 105 C4: 60 D4: 205 E4: 80 A5: Mar B5: 110 C5: 70 D5: 210 E5: 90
=1995 North Feb
Error Values
The following error means that Excel cannot determine the label:
#NAME?:
The following error means that the label is valid but does not refer to a valid intersection:
#NULL!: