Structured references are used to refer toExcel tablesin formulas.
The syntax for structured references allows you to precisely target different parts of the table.
Let’s walk through some examples.
All these formulas refer to Table1 at the left.
Since the formulas are outside the table, they all include the table name.
The formulas in column I are actually entered as text.
The most basic syntax is the table name only.
This refers to the table data.
The table name with the #Data specifier also refers to all of the table data.
The #Data specifier is seldom used this way since it’s not necessary.
To refer to all headers, use the #Headers specifier.
To refer to a specific header, you’ll need a double set of square brackets.
To target a specific column, use the table name, with the column name in square brackets.
To refer to the entire column, including headers, and total row, use the #All specifier.
To reference multiple columns, use a double set of brackets.
Wrap each column name in brackets, and separate with a colon.
The @ symbol inside a table means “this row”.
To reference the Total row, use the #Totals specifier.
Finally, let me mention a few things you should keep in mind with respect to structured references.
First, structured references always evaluate to a range of cells, usually with multiple values.
Second, it’s often easy to point and click and let Excel complete the structured reference.
Alternately, as you start typing a table name in a formula, Excel will match the name.
