Microsoft Office Tutorials and References
In Depth Information
Using structured references
The capability to create structured references automatically is an option that is
ordinarily turned on. To disable this feature, click the File tab, Options, and then in the
Formulas category clear the Use Table Names In Formulas check box.
All Excel tables contain the following areas of interest, as far as structured references are
The table Excel automatically applies a table name when you create a table, which
appears in the Table Name text box in the Properties group on the Table Tools Design
tab that appears when you select a table. Excel named our table Table3 in this
example, but we changed it to Regional13 by typing in the Table Name text box, as shown
in Figure 12-22. The table name actually refers to all the data in the table, excluding
the header and total rows.
Individual columns of data Excel uses your column headers in column specifiers ,
which refer to the data in each column, excluding the header and the total row. A
calculated column is a column of formulas inside the table structure, such as F4:F7 in
our example, which, again, does not include the header or total rows.
Special items These are specific areas of a table, including the total row, the header
row, and other areas specified by using special item specifiers —fixed codes that are
used in structured references to zero in on specific cells or ranges in a table. We’ll
explain these later in this section.
For details about calculated columns in tables, see Chapter 22.
No more natural-language formulas
In previous versions of Excel, you could use adjacent labels instead of cell references
when creating formulas, which was like using names without actually having to define
them. This was called the natural-language formulas feature, but it was riddled with
problems and was replaced a couple of versions ago with structured references, which
work much better. When you open an older workbook containing natural-language
formulas, an error message appears letting you know that these formulas will be
converted. This mandatory conversion has little effect on your worksheets other than
changing some of the underlying formulas. Excel correctly identifies the offending
labels and replaces them for you with the correct cell references. If you still want your
formulas to be more readable, you can then rebuild them using names or structured