Microsoft Office Tutorials and References
In Depth Information
Using structured references
The following formula calculates the average sales for quarters 2 and 3 by using colons (the
range operator) to specify contiguous ranges of cells in each of the two structured
references within the parentheses and by using a space character (the intersection operator)
between the two structured references, which combines only the cells that overlap (Qtr 2
and Qtr 3):
=AVERAGE(Regional13[[Qtr 1]:[Qtr 3]] Regional13[[Qtr 2]:[Qtr 4]])
About the special item specifiers
Excel provides five special codes you can use with your structured references that refer
to specific parts of a table. You already saw the special item specifier @ used in previous
examples. Here are all five special item specifiers:
@ This specifier identifies cells at the intersection created in conjunction with
column specifiers; you cannot use it with any of the other special item specifiers in this
[#Totals] This refers to cells in the total row (if one exists) and returns a null value if
there is no total row.
This refers only to cells in the header row.
This refers only to cells in the data area between the header row and the
This refers to the entire table, including the header row and the total row.
Are your references qualified?
Two kinds of structured references exist: qualified and unqualified . Generally, you can
use unqualified references in formulas you construct within a table because the
formulas are insulated from errors that could be introduced by inserting, deleting, or
moving cells by virtue of the robust infrastructure of the table. When you build formulas
outside the protective structure of a table, it is advisable to use qualified references to
protect against such errors. Here is an example of an unqualified reference that works
only within a table, followed by a qualified reference that produces the same result
outside the table: