Microsoft Office Tutorials and References
In Depth Information
Using structured references
Using structured references
Creating names to define cells and ranges makes complex formulas easier to create and
easier to read, and structured references offer similar advantages—and much more—
whenever you create formulas in tables or formulas that refer to data in tables. Structured
references are dynamic; formulas that use them automatically adjust to any changes you
make to the table.
Structured references are created automatically when you create a table using the Table
button in the Tables group on the Insert tab. Excel recognizes distinct areas of the table
structure as separate components you can refer to by using specifiers that are either
predeined or derived from the table. Figure 12-22 shows a modified version of the Regional
Sales worksheet that we converted to a table. We’ll refer to this table as we discuss
structured references.
For more information about creating tables, see Chapter 22, “Managing information in
tables.”
When you refer to data in tables by using formulas created through direct manipulation—
that is, when you click or drag to insert cell or range references in formulas—Excel creates
structured references automatically in most cases. (If a structured reference is not
applicable, Excel inserts cell references instead.) Excel builds structured references using the table
name and the column labels. (Excel automatically assigns a name to the table when you
create one, as shown in Figure 12-22.) You can also type structured references by following
strict syntax guidelines that we’ll explain later in this section.
Figure 12-22 We created this table to illustrate the power of structured references.
Search JabSto ::




Custom Search